Share-lock No-lock Exclusive-lock

[PrJlndni]

Hello Masters,


I would like to ask for your help regarding the accessing of records in the database when there are a lot of users using it at the same time. Some users are in pending mode because someone is still using the database. This problem is a disturbance to other users who would like to access records.

How do you usually solve this problem? I tried using share-lock, no-lock and exclusive-lock. NONE of them are working. Please help. Thank you so much in advance Masters.


Regards,

PrJlndni

 

[RealHeavyDude]

You need to understand how the different locks are managed by the databse and how buffer and transaction scope influence these.

The solution to your problem is an optimistic locking strategy. IMHO, using a pessimistic locking strategy has become obsolete with the dawning of n-tier architectures and web development at the end of the last century.

Your generic statement does not give us any hint what causes the share-lock in your scenario.

Therefore, from my experience, there are two points you should check your code for:

1. The default lock is share-lock unless you use the -NL startup parameter to the client session. Therefore it is good practice to always explicitely specify the lock that you need. Not explicitely specifying the lock is a recipe for generating share-locks on the database.
2. If the buffer scope is larger than the transaction scope, then, at the end of the transaction scope, an exclusive-lock is downgraded to a share-lock until the buffer scope ends. The buffer scope being larger than the transaction scope is the usual suspect for seeing share-locks on the database. Therefore it is good practice to use defined buffers whenever you update the database and strong scope them to the transaction scope. Most code I've seen does not do that and is prone to cause share-locks as the buffer scope of the default buffer is usually much larger than the transaction scope.

 

[TomBascom]

"Optimistic locking" means that you assume a record is going to be available and code to handle the exceptions. You are also optimistically anticipating that nobody else is modifying the record -- or that any such modifications are not relevant to your usage. On the surface this seems like an awful idea. But in reality it is true that most of the time it doesn't matter if someone else changes something. Plus read operations are much, much more common than updates so the odds are very much on your side.

"Pessimistic locking" means that you assume it is not available and that your code is surprised to find it is

In an optimistic locking world you generally use NO-LOCK for almost all data access that is not updating a record. So you write a lot of:

find customer no-lock where customer.custId = someId no-error.
if available( customer ) then
  do:
    message "no such customer!".
    return.
  end.

/* assume that the customer is available and carry on with whatever...  */

The error is this case is not a locking error -- you are simply noticing that there was no customer with that id. This snippet of code will not block anyone else from accessing or updating the record. There can be as many NO-LOCK readers of the record as you would like. This is the method that gives you the greatest concurrency and performance.

(Also: there is no need for nor any benefit to slapping FIRST on every FIND -- it is a stupid habit taken from some very bad and quite ancient code in certain well known applications that everyone really should know better than to copy.)

To update code you should *always* use a named buffer and a strong scoped FOR block. Like this:

define buffer updCustomer for customer.

do for updCustomer transaction:
  find updCustomer exclusive-lock where custId = someId no-wait no-error.
  if available( customer ) then
    do:
      discount = 0.10.  /* or whatever it is we wanted to update... */
    end.
   else
    do:
        /* don't prompt the user!  you are in a transaction -- if the user goes to get a cup of coffee and then leaves for the day your bi file could grow a few terabytes... */
    end.
end.

If you compile your code and you get warnings and errors about transactions already existing or being unable to reference buffers the problem is NOT with the "do for updCustomer transaction". The problem is that you have been sloppy outside of that block and you need to fix the sloppiness.

Code in this manner and you will not have problems with lock conflicts or transaction scope.

Code without specifying lock types and without controlling transaction scope and you will have a mess on your hands.

 

[PrJlndni]

Good day Masters,

I would like to expound my concern.
My concern instance is like this scenario:

Client1: She's using one record as of the moment because she updated something in it.
Client2: She's browsing another record for details/information checking.
Client3: She is generating a report everything like, getting all the # of records in the same database used by client 1 and 2. She used FOR EACH in this matter. While generating a report, client 1 and 2 went to client 3 because they cannot proceed checking and updating the records they are accessing because it is used by client 3.

Concern: Is there a way that while client 3 is using the same database and same table of records, client 1 and 2 will still be able to use same database and table at the same time? Please help. Thank you

Regards,

PrJlndni

 

[RealHeavyDude]

Since you don't reveal any code one can only speculate what the for each of client 3 does. Plus you should be more specific on what client 1 and client 2 are supposed to do with record ( read access or update access ) when you say use.

If client 1 and client 2 want to access the same record for reading that is used by client 3 for reporting ( which should also be read access too ) and all of the 3 clients would use no-lock to fetch the very same record than every client would get the record for read. If none of the clients explicitely specifies the lock and the -NL startup parameter is not used then only the first of the 3 clients gets the record, all others will get a message that the record is used by another user.

I assume that neither of your client programs that uses a for each or a find does explicitely specify NO-LOCK and you don't use the -NL startup parameter to change the default behavior from SHARE-LOCK to NO-LOCK.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Rule #0 -- *ALWAYS* specify the desired lock type. Never allow it to default.

Correct:

find customer NO-LOCK where name = "Lift Line Skiing".

for each order NO-LOCK:
  display order.
end.

find updEmployee EXCLUSIVE-LOCK...

Incorrect:

find customer.

for each order:
  display order.
end.

If you do NOTHING else this one simple step will resolve 80% of your problems.

99.44% of your FIND and FOR EACH statements will probably be NO-LOCK.

If I had an existing code base with sloppy lock specifications I would start by adding NO-LOCK and EXCLUSIVE-LOCK.

Phase 2 would be to add proper strong-scoping of updates as I have shown above. There probably aren't nearly as many places where you need to do that but it is a more complex task and it will take longer.

There is almost no use for SHARE-LOCK in modern code. (There is a minor use-case but it is unusual and it would be a distraction to talk about it, I have already said too much on the topic. Open a new thread if you really want to know about that.)

 

[PrJlndni]

Hello Masters,

Pardon me for not being clear.
Client 1 :

find Customer where RecordAddress = "000001" no-error.
if avail(Customer) then update Name = "Someone".

Client 2:

find Customer where RecordAddress = "000002" no-error.
if avail(Customer) then Update Name = "Somebody".

Client 3:

for each Customer no-lock.
 display Name RecordAddress with frame f_MAinFrame.
end.

** They are running at the same time. **
Problem: Client 1 and Client 2 cannot proceed updating because they are in PAUSE because Client 3 is still reading Customer database for report generation.
Question: Is there any way that while client 3 is doing report generation, client 1 and 2 should never be disturbed. I mean, is there any way that client 1 and 2 should not be in PAUSE moment just because client 3 is just buffering some info from the same database they are using?

Thank you so much Masters. I hope it is clear now.


Regards,

PrJlndni

 

[TomBascom]

Your code samples show that you are not following Rule #0. Your code does not specify a lock type and, therefore, all of your samples other than #3 use SHARE-LOCKS.

Your samples #1 and #2 would go into my "incorrect" category.

The results are what would be expected. A fuster-cluck of monumental proportions.

Your reasoning, however, is wrong. Session #1 and #2 are blocking each other. Session #3 is not blocking them.

 

[RealHeavyDude]

I can only second Tom - not specifying the lock is a BIG NO-GO.

As soon as any client holds a SHARE-LOCK on a particular record no other client is able to fetch the record with a SHARE-LOCK or EXCLUSIVE-LOCK. The reason being that, as soon as the client which holds the SHARE-LOCK, updates the record, the AVM automatically upgrades the lock to an EXCLUSIVE-LOCK. This can only be done for 1 client and 1 client only.

Therefore, although I can come up with one very valid use case for a SHARE-LOCK, in all other cases an explicitly specified or implicitely SHARE-LOCK in your code is a red flag.

 

[tamhas]

Note also that your examples 1 and 2 do not show any scoping. Any update should be done in a strongly scoped block.

 

[PrJlndni]

Good day Masters,

I understand. Thank you for your help.


Regards,

PrJlndni

 

[JOSE_LUIS_VEGA]

Hello Masters,


I would like to ask for your help regarding the accessing of records in the database when there are a lot of users using it at the same time. Some users are in pending mode because someone is still using the database. This problem is a disturbance to other users who would like to access records.

How do you usually solve this problem? I tried using share-lock, no-lock and exclusive-lock. NONE of them are working. Please help. Thank you so much in advance Masters.


Regards,

PrJlndni

One solution would be to create a temporary table, update its contents and then write to the main table, and immediately release.
DEFINE TEMP-TABLE tt_name-table NO-UNDO
....
LEAVE name-table.