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.
You could:
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.
You could:
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.
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.
> 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.
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.
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.
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.
> how to analyze which block is causing the problem?
A block with transaction property. COMPILE LISTING will show the block's properties.