Kbase P177685: Unable to detect whether record is exclusively locked when AUTOCOMMIT is enabled
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/19/2010 |
|
Status: Unverified
SYMPTOM(s):
Unable to detect whether record is exclusively locked when AUTOCOMMIT is enabled
LOCKED function does not detect record locks
Connection is established with -Dsrv AUTOCOMMIT,1
FACT(s) (Environment):
Using DataServer for MS SQL Server product
No problem seen when using an OpenEdge database
Progress 9.x
OpenEdge 10.x
Windows
MS SQL DataServer
CAUSE:
This is expected behaviour. When AutoCommit is enabled, locks will only be held to the point where each SQL statement is committed as a transaction. A lock is typically held longer under normal transaction control because it is often locked some time before the transaction boundary and other units of work participate in the same transaction after the lock is obtained but before the transaction is committed. As a result, it is very unlikely any locks will be detected with "IF LOCKED" using AUTOCOMMIT,1. In addition, using AUTOCOMMIT,1 will reduce resource contention but it prohibits any batching of activity into a single transaction. Per the DataServer for MS SQL Server manual:
Exclusive locks
When you update, delete, or create a record, MS SQL Server puts an exclusive lock on the record; however, the data source does not apply the exclusive lock to a record until all share locks on it are released. Therefore, you cannot perform an update on a record until other users release it. If a record has an exclusive lock on it, no other user can access it until it is released at the end of a transaction. In a OpenEdge transaction block, the data source always holds an exclusive lock until the end of a transaction?s scope if the data source driver supports commitment control boundaries and the ODBC AUTOCOMMIT feature is not turned on.
Transaction Management: the AUTOCOMMIT option
To avoid using the MS SQL Server transaction manager, specify -Dsrv AUTOCOMMIT,1. This option exercises the OpenEdge client?s local before-image mechanism to manage transactions. This creates a transaction from each SQL statement that the DataServer issues. OpenEdge emulates the logical transaction on behalf of the application using the local before-image mechanism. This connection technique is useful in multi-database applications.
FIX:
AUTOCOMMIT must be disabled in order to detect locks when using the DataServer for Microsoft SQL Server product