SQL Locks
Multiple database transactions can access the same database object at the same time. To synchronize access, SQL locks are used.
The following database objects can be locked:
-
Tables
-
Rows
-
Entries in the database catalog
|
Lock Type |
Objects that Can Be Locked |
Description |
|---|---|---|
|
Exclusive |
|
The system sets exclusive locks for all SQL statements that change data, such as INSERT, UPDATE, or DELETE. Other transactions have neither read nor write access to the locked database object. This means that other users cannot access the locked database object. If another transaction also tries to set an exclusive lock, a lock collision occurs (the lock request is rejected). Caution
Exclusive locks can significantly interfere with the performance of the database and the SAP system. |
|
Share |
|
Other transactions have read access to the locked database object. They can still set share locks for the database object. However, other transactions cannot set exclusive locks for the database object and therefore cannot change it. |
|
Optimistic |
|
An optimistic lock keeps track of changes to a database object. This enables database applications to determine whether the data that they have read while setting the lock is still up-to-date, or whether they must read it again. Other transactions can still set share, exclusive, or optimistic locks for the locked database object. Before a transaction can change a row for which it has set an optimistic lock, the system checks whether the row has been changed by another user since the optimistic lock was set.
An optimistic lock is only useful in isolation levels 0, 1, 10, and 15. |
Who Sets Locks?
In general, locks are implicitly requested and released by the database system. The overall locking behavior of the database system is determined by the isolation level (see Isolation Level).
Independent of the isolation level, you can also explicitly request table and row locks using the LOCK statement (see SQL Reference Manual, LOCK Statement). Note that you cannot explicitly lock entries in the database catalog.
To change the row locking behavior for a specific SQL statement, you can use the LOCK option (see SQL Reference Manual, LOCK Option (lock_option)).
How Many Locks Can Be Set?
The general database parameter MaxSQLLocks determines the maximum number of concurrent table and row locks (see Database Administration, General Database Parameters). If this number is exceeded, all further lock requests are rejected by the database system.
The special database parameter RowLocksPerTransactionThreshold (see Database Administration, Special Database Parameters) determines how the database system handles lock escalations. Lock escalation means that the database system attempts to convert multiple row locks in tables into table locks to reduce the overall number of locks.
|
- |
A transaction has... |
|||||
|---|---|---|---|---|---|---|
|
- |
Table |
Row |
Entry in the database catalog |
|||
|
Another transaction can request... |
Exclusive Lock |
Share Lock |
Exclusive Lock |
Share Lock |
Exclusive Lock |
Share Lock |
|
Exclusive lock for the table |
No |
No |
No |
No |
No |
Yes |
|
Share lock for the table |
No |
Yes |
No |
Yes |
No |
Yes |
|
Exclusive lock for any row in the table |
No |
No |
- |
- |
No |
Yes |
|
Exclusive lock for the locked row |
- |
- |
No |
No |
- |
- |
|
Exclusive lock for a different row |
- |
- |
Yes |
Yes |
- |
- |
|
Share lock for any row in the table |
No |
Yes |
- |
- |
No |
Yes |
|
Share lock for the locked row |
- |
- |
No |
Yes |
- |
- |
|
Share lock for a different row |
- |
- |
Yes |
Yes |
- |
- |
|
Another transaction can... |
||||||
|
Change the definition of the table in the database catalog |
No |
No |
No |
No |
No |
No |
|
Read the definition of the table in the database catalog |
Yes |
Yes |
Yes |
Yes |
No |
Yes |
When Are Locks Released?
In general, the system releases all locks at the end of a transaction the latest. Exceptions: If a COMMIT statement or a ROLLBACK statement contains a LOCK statement, the database system does not release the locks at the end of the transaction.
You can also explicitly release row locks using the UNLOCK statement (see SQL Reference Manual, UNLOCK Statement). Note that you can only release exclusive row locks if the rows have not been changed yet.
The special database parameters RequestTimeout determines the maximum amount of time that a transaction waits when it has requested a lock (see Database Administration, Special Database Parameters). After this time has elapsed without the lock being set, the system terminates the transaction with an error.
How Does the System Handle Deadlocks?
A deadlock occurs when two or more users mutually prevent each other from proceeding by the locks that they have set.
The database system automatically detects deadlocks down to a certain depth. If a deadlock occurs, the system sends an error message to the user who is causing the deadlock and resolves the deadlock.
The system cannot detect deadlocks that involve share locks.
The special database parameter DeadlockDetectionLevel (see Database Administration, Special Database Parameters) determines maximum depth to which the database system can detect deadlocks.
Deadlocks that are not detected by the database system are resolved by the time-outs (transactions will be rolled back).
-
Database Administration, Analyzing Database Performance
-
Database Studio, Displaying SQL Locks and Displaying Information About Database Activities
-
Database Administration in CCMS, Locks
-
SAP Note 1243937
(FAQ: MaxDB SQL Locks)