Parallel Locks 
The following table shows which shared locks (SHARE LOCKS) and exclusive locks (EXCLUSIVE LOCKS) can exist in parallel.
|
A transaction has.. |
|||||
|
on a table |
on a row |
on the database catalog |
|||
Another transaction can... |
Exclusive lock |
Shared lock |
Exclusive lock |
Shared lock |
Exclusive lock |
Shared lock |
Request an exclusive lock for the table |
No |
No |
No |
No |
No |
Yes |
Request a shared lock for the table |
No |
Yes |
No |
Yes |
No |
Yes |
Request an exclusive lock for any row in the table |
No |
No |
- |
- |
No |
Yes |
Request an exclusive lock for the locked row |
- |
- |
No |
No |
- |
- |
Request an exclusive lock for a different row |
- |
- |
Yes |
Yes |
- |
- |
Request a shared lock for any row in the table |
No |
Yes |
- |
- |
No |
Yes |
Request a shared lock for the locked row |
- |
- |
No |
Yes |
- |
- |
Request a shared lock for a different row |
- |
- |
Yes |
Yes |
- |
- |
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 |
In the cases marked with a “No”, there is a lock collision. If a lock is requested within a transaction, then you have to wait for the lock to be released until one of the situations marked with a “Yes” occurs.
The following also applies:
· If no lock is set for a database object, then a transaction can request a read or an exclusive lock and the database system sets the lock immediately.
· If transaction T1 holds a shared lock for a database object and no other transaction holds a lock for this database object, then transaction T1 can request an exclusive lock for this database object and receives it immediately.
· If a transaction holds an exclusive lock for a database object, then this transaction can request a shared lock (although this is not necessary).
See also: