(un)Lock all

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

(un)Lock all

Postby Pete Tabord » Fri Nov 25, 2011 10:42 am

There is much confusion about these two statements. This is what they actually do.

'Lock all files' when executed not only locks all files but changes the processing locking strategy to 'lock files', and that will last for the duration of the procedure (or until the 'Unlock all files' command is encountered).

'Unlock all files' does unlock all files at that point, but also changes the processing locking strategy for the procedure to 'lock records' _and_ implements whatever happened to be set in the selection locking option as the selection locking strategy. Note this means that 'Unlock all files ' can actually result in thousands (or hundreds of thousands) of record locks beiing set.

(Note: You can't edit the selection option if lock files is set, but it does display what it currently is in the preferences screen, and you can set it with the 'Query Selection' DQL statement, which actually works. Of course you can temporarily change the first option to lock records in the dialog, change the second option, then change the first back. As its only of significance if you are using the 'unlock all' DQL commands I'm happy to leave it like that.)

You can see from this that you really shouldn't use the '(un)lock all' statements inside a for loop, nor the query selection statement.

Also, there is no need to have 'unlock all files' at the end of a procedure because one is automatically issued.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: (un)Lock all

Postby Graham Smith » Fri Nov 25, 2011 1:18 pm

I thought those commands no longer did anything.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: (un)Lock all

Postby Pete Tabord » Fri Nov 25, 2011 9:23 pm

Not so. They are entirely functional. I have examined the code (because I ended up there when following through the use of the parameters discussed under 'report and procedure locking').

What may be being confused is that at one point Sapphire were telling people the following:

1) that you had to use one of these commands at the start of the query if you were going to use the other locking commands.

2) that using 'unlock all files' was necessary at the end of a query to make sure all locks were cleared.

3) that doing 'lock all files' in a control procedure (DFD style) would speed up any called procedures.

None of the above are true.

There is no dependance of the other lock or query selection commands on (un)lock all, they will all work anyway. BUT, if you want to change the _data processing_ strategy - the one that can only be lock records or lock files, then , as described above, you _do_ use (un)lock all files to achieve that. That may be the source of the confusion. An automatic 'unlock all files' is issued at the end of every query anyway, so 2) is pointless, and the statements ONLY apply to files and strategy in the current procedure and then only if the procedure contains a 'for' loop, therefore 3) is pointless also.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: (un)Lock all

Postby Graham Smith » Sat Nov 26, 2011 2:00 am

Pete Tabord wrote:...the one that can only be lock records or lock files, then , as described above, you _do_ use (un)lock all files to achieve that. That may be the source of the confusion. An automatic 'unlock all files' is issued at the end of every query anyway, so 2) is pointless, and the statements ONLY apply to files and strategy in the current procedure and then only if the procedure contains a 'for' loop, therefore 3) is pointless also.

"And a horse has no udders, and a cow can’t whinny. And up is down, and sideways is straight ahead."
Cord The Seeker, "Circle of Iron"
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: (un)Lock all

Postby Pete Tabord » Sat Nov 26, 2011 9:57 am

:) 8)

I know it appears confusing. But its not _inherently_ confusing, its more that people have never bothered to actually read the effing manual, they've made it up for themselves.

The manual is actually 99% correct, and always has been. The 'received Wisdom' distributed by the alleged experts throughout the 90's was wrong.

I first had to investigate exactly what the DQL locking commands do about 6/7 years ago now while still working for Sapphire. I think its fair to say that the results - that they behaved almost exactly as the manual said - were a surprise to virtually all concerned, including myself. I think only Brian McCabe of the UK experts was on the right track prior to that.

The only recent new discovery is the one I was highlighting above, that the 'all' commands also change the processing strategy for the rest of the proc they are in (only if it disagrees with the current strategy, of course)

PS. The point the manual fails to make clear is that you will _always_ get record locking if you modify records, unless you have a file lock on the file. In other words, whatever you do you cannot create a 'lock nothing lock nothing' strategy.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: (un)Lock all

Postby Graham Smith » Sat Nov 26, 2011 12:21 pm

Pete Tabord wrote:The 'received Wisdom' distributed by the alleged experts throughout the 90's was wrong.

Susanne (remember her) is the only person I knew who regularly used these commands and I was never convinced that she actually knew what they were doing or that they actually did much of anything for that matter.

I actually have read the manual on this - several times - and with the exception of lock exclusive, never really saw a need for anything else. In fact, you and I hosted a forum on locking at one of the IDUA conferences and if I recall, you felt the same way.

Pete Tabord wrote:PS. The point the manual fails to make clear is that you will _always_ get record locking if you modify records, unless you have a file lock on the file. In other words, whatever you do you cannot create a 'lock nothing lock nothing' strategy.

Actually, someone and I had a rather hot and heavy discussion on this many years ago (DFD). I kept insisting that in a pure list records situation there should be no reason for there to be any lock issued but there always was just the same. She (I think it was a she) kept telling me that there always had to be locking whenever records were selected. I understand her side of it (keeping a record set from being changed) but my point was that there were times when I did not care and I should be able to make that decision.

In any case, since early in DFW days, I found that the old standby lock exclusive was not only no longer necessary, it often mucked things up. So, I have since used no commands at all.

Perhaps, in light of what you have found, it might be worth revisiting this whole area, particularly with the 150 user app that Phil is going to be working on.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: (un)Lock all

Postby Stewart Allen » Sat Nov 26, 2011 12:44 pm

Does this command actually have any effect and if so what? (other than what is seems to imply)

QUERY SELECTION LOCK NOTHING .
Stewart Allen
 
Posts: 88
Joined: Thu Sep 27, 2007 11:25 am
Has thanked: 0 time
Been thanked: 0 time
 

Re: (un)Lock all

Postby Pete Tabord » Sun Nov 27, 2011 9:20 am

Maybe this whole discussion should be under 'advanced'. To the vast majorty of people it will be irrelevant (especially now we've sorted out the strategy settings properly).

To reply to Stewart first:

Yes it does. In Ffenics and DFD at least. If your current selection strategy is other than 'lock nothing' and your current processing strategy is 'lock records' it means that you will prevent the selected record set being locked. Assuming the given query has a selection phase, of course. And of course it needs to be placed before the relevant for loop to have any effect.

Graham:

Several points. I think your lobbying was listened to because at some point both DFD and DFW were modified so that they only set record locks if there is a modify statement in the query. In Ff setting "place shared locks when 'List Only'" to 'on' reactivates the original setting.

Having shared locks on list procedures is important to some people. Without it there is a genuine possibility that records will be modified between selection and actual production of the report, and of course the longer the reports take to run the greater the risk. For other apps it matters not a jot. I imagine the 'she' was Brett :)

I remember Suzanne well - however, you are probably right in saying that for most people the DQL lock commands should be used rarely if at all. But in the UK, many apps converted to DFW by Sapphire make extensive use of lock commands, and much of the use was misguided - they imagined they were reducing the amount of locking going on but they were actually increasing it.

Where the lock commands are potentially useful - well, it strikes me there are two major ways they can be handy:

a) in an app where most reporting is done with few or no people in the system, but one or two processes are run while there is heavy traffic, it allows the fast 'lock files' data processing strategy to be used, and then overriden in the few procs that run while people are working.

and, perhaps with relevance to your big app,
b) they allow precise control when locks are set or perhaps more importantly released, for example unlocking a record immediatly after a modify in a long and complex for loop.

Just for clarity, I'll repeat the missing bit that makes everything fall into place - DataEase and Ffenics will always lock a record for the pass round the for loop where the record is modified. This is the minimum available, and will always happen unless the file is locked or the record is already locked from the selection phase. You can minimise the time it is locked by using the lock or unlock commands. Whether it is locked from the point when it is selected to the point when it is modified depends on the 'selection strategy' - that is, lock nothing, lock records, lock files - that one.

Summary -

Only very complex or performance critical apps are likely to need DQL locking commands.
All locking commands only affect the current query, and then only if there is a 'For' loop and either there is a modify records command or the 'shared locks on list only' is set.
Placing file locks speeds up a query but reduces access to resources.
Unlocking a file triggers record locking if done before a for loop processing that file.
Explicitly locking a record is rarely necessary.
Explicitly unlocking a record may reduce resource conflicts.
(un)lock all and Query selection can be used to change the default locking strategy for a given query. They are not needed to somehow 'enable' the other locking statements.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: (un)Lock all

Postby Graham Smith » Sun Nov 27, 2011 1:14 pm

As I think about it, I think you are right about it being Brett and I think that this goes back to 4.5x and I do seem to recall some discussion about this being addressed at a later time but could not recall if "lock nothing" really did do just that.

Advanced - that is an understatement. There is nothing more complicated than locking and trying to alter it on the fly without a full understanding of what you are doing is worse than doing nothing at all.

As to altering locking on the fly, I have learned over the years that the best solutions are often the simplest solutions. Doing procedures as a series of steps is often more effective even if it does seem like more work.

It might be worth starting a new threads in advanced to review the locking preferences and what they actually do. I think I might start a new thread that covers a practical example of breaking procedures down into pieces.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: (un)Lock all

Postby Pete Tabord » Sun Nov 27, 2011 5:40 pm

This topic now in 'advanced' . The locking stements _are_ advanced and most people don't need to use them as the automatic locking is quite sufficient.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest