How to analyze a lock issue?

Posted by shivaprasath143 on 05-Sep-2018 16:47

We have a screen in our application when 1st user goes in to the screen i see a share lock for table xXx in promon --> 4. And when the next user access the same screen while the first user is in the same screen, the beloww error pops up on the screen.

xXx in use by user1 ,444 on <server> 99. Wait or press STOP to stop. (121)

how do i analyze the issue? I dont see any direct share lock on the UI program. I generated xref, listing files but not sure what to check.

Posted by Rob Fitzpatrick on 05-Sep-2018 17:13

You could:

  • Step through the code for that screen in the debugger, watching promon and the tables/buffers that are in scope in the debugger.
  • Run the code with -clientlog client.log -logentrytypes QryInfo:3 (or equivalent LOG-MANAGER attributes) and look in the resulting client log for queries on that table; you'll find it unless it's FIND statement.
  • Prior to the second client entering that screen/querying that table, enable Database Client Request Statement Caching (aka Client Statement Caching; promon R&D, 1, 18, 1, 1, usr#).  You should then be able to read their current ABL call stack frame info from their _connect record or from promon R&D, 1, 18, 7, usr #.  This should give you the program name and line number (from a COMPILE DEBUG-LIST) where the second client blocked.
    If this is production, don't forget to turn off Client Statement Caching when you're done.  ;)
  • Do the above via ProTop, if you use that.
  • Run a COMPILE LISTING of the program and look at the buffer scope info.
  • Look through the code for queries on that table that don't explicitly specify a lock strength (i.e. NO-LOCK or EXCLUSIVE-LOCK).  Static queries without a lock strength will default to SHARE-LOCK, unless the client was started with -NL.

Since it sounds like you can reproduce the problem in the second client right at the point of the query you're trying to find, I'd probably start with the third suggestion above.  It will give you the line number of the query.

All Replies

Posted by Rob Fitzpatrick on 05-Sep-2018 17:13

You could:

  • Step through the code for that screen in the debugger, watching promon and the tables/buffers that are in scope in the debugger.
  • Run the code with -clientlog client.log -logentrytypes QryInfo:3 (or equivalent LOG-MANAGER attributes) and look in the resulting client log for queries on that table; you'll find it unless it's FIND statement.
  • Prior to the second client entering that screen/querying that table, enable Database Client Request Statement Caching (aka Client Statement Caching; promon R&D, 1, 18, 1, 1, usr#).  You should then be able to read their current ABL call stack frame info from their _connect record or from promon R&D, 1, 18, 7, usr #.  This should give you the program name and line number (from a COMPILE DEBUG-LIST) where the second client blocked.
    If this is production, don't forget to turn off Client Statement Caching when you're done.  ;)
  • Do the above via ProTop, if you use that.
  • Run a COMPILE LISTING of the program and look at the buffer scope info.
  • Look through the code for queries on that table that don't explicitly specify a lock strength (i.e. NO-LOCK or EXCLUSIVE-LOCK).  Static queries without a lock strength will default to SHARE-LOCK, unless the client was started with -NL.

Since it sounds like you can reproduce the problem in the second client right at the point of the query you're trying to find, I'd probably start with the third suggestion above.  It will give you the line number of the query.

Posted by Rob Fitzpatrick on 05-Sep-2018 17:15

You didn't mention your version.  I should note that Client Database-Request Statement Caching (I always get that name wrong...) is available in 10.1C and later.

Posted by George Potemkin on 05-Sep-2018 17:41

> how do i analyze the issue? I dont see any direct share lock on the UI program. I generated xref, listing files but not sure what to check.

It's a buffer scope issue. Your program used an explicite exclusive lock but outside a transaction block the record lock is downgraded to a share lock. Second user is trying to lock exclusively the same record but it can't due to the share lock hold by first user.

Posted by shivaprasath143 on 05-Sep-2018 19:43

Version: 11.7

Thanks for the inputs. Appreciate it.

Even I believe its scope issue. how to analyze which block is causing the problem? because the same table is called many times in the program with exclusive-lock and also few .i reference too.

Posted by James Palmer on 06-Sep-2018 03:35

In a development or test environment you can run all clients with the -NL parameter. If there are any queries without a specific lock statement that subsequently update the data you will get a nice error. Turn on -debugalert as well to get a stack trace.

I wouldn't recommend turning it on in production though.

Posted by Mike Fechner on 06-Sep-2018 03:50

I disagree. The -NL behavior should be consistent in dev, test and prod.
 
Record locking is important to the application behavior. So developers need to experience the same as users in prod.
 

Posted by James Palmer on 06-Sep-2018 03:56

I agree that this is ok once the locking issues are resolved, but you don't want to just turn it on in Prod when you know you have locking issues. You don't want the user to be presented with a big error, particularly if you have transaction scoping issues that mean the error from not having a lock on a record mean you undo all the work for today. So turn it on in Dev and Test, fix the errors, then turn it on in Prod.

Posted by Mike Fechner on 06-Sep-2018 04:36

OK – I can agree with that. This wasn’t clear to me from the earlier post.

Posted by George Potemkin on 06-Sep-2018 07:04

> how to analyze which block is causing the problem?

A block with transaction property. COMPILE LISTING will show the block's properties.

This thread is closed