Try OpenEdge Now
skip to main content
SQL Development
Data Control Language and Transaction Behavior : Understanding transactions and locking : How lock levels and lock modes interact
 

How lock levels and lock modes interact

The following tables describe how the SQL OpenEdge Engine uses locking to produce a desired transaction behavior. The tables identify the requested lock strength based on the transaction isolation level in effect for a given transaction.
Table 11. Insert, update, or delete record operations
Isolation
Info schema lock
Table lock
Record lock
READ UNCOMMITTED
S
NL
NL
READ COMMITTED
S
IX
X
REPEATABLE READ
S
IX
X
SERIALIZABLE
S
SIX
X
Table 12. Fetch or select record operations
Isolation
Info schema lock
Table lock
Record lock
READ UNCOMMITTED
S
NL
NL
READ COMMITTED
S
IS
S
REPEATABLE READ
S
IS
S
SERIALIZABLE
S
S
S
There are no table or record locks acquired when the transaction isolation level is READ UNCOMMITTED.
In the READ UNCOMMITTED transaction isolation level you maximize concurrency, but you might also read dirty data.
The primary difference between the READ COMMITTED and REPEATABLE READ transaction isolation levels is that while in REPEATABLE READ, individual record locks are held for the duration of the transaction. For example, if your fetch criteria include all companies in the state of Idaho, each record in the result set will remain locked until all of the records meeting the criteria have been read. In the READ COMMITTED transaction isolation level, the record locks are released once the record has been read.
In the SERIALIZABLE transaction isolation level, a share lock on a table is held for the duration of the transaction, preventing any other transaction from updating the table. Any SQL operation that modifies the information schema is upgraded to SERIALIZABLE, regardless of the user's current transaction setting.