We are experiencing issues with users locking each other out of our GUI application when they are seemingly accessing unique records by recid.
If one user access record 1 by exclusive-lock and recid then another user accessing record 2 by the same means is locked out as well until the first users completes their transaction.
Looking at SQL server it appears that it is locking at the page level as well as the row level for the recid index.
Has anyone seen similar incidents and have they turned off page level locks on their indexes?
In my opinion, simple accessing/read the record should not lock out other user if the record that other user is trying to access is different from the 1st user. You can try this in SQL Serv er management studio, if you know what records are being accessed from the application.
you can also check the lock status using sp_lock command in SQL Server Management studio e.g. if the session id for two sessions are spid1 and spid2
sp_lock spid1, spid2
This command will provide the details of lock information.
hope this helps
What is your OE version?
Even though the sessions are started with -Dsrv TXN_ISOLATION,1 ( default setting), the locking error is eager to occur if the 2 following conditions are met:
1- The table has a reduced number of records
2- the progress_recid index is non-clustered.
The solution is to set progress_recid as a clustered index on MS SQL server