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:

Locking for Database Objects