How to resolve locking situations

FAQ about Detecting and Resolving Locking Conflicts [ID 15476.1]

3. Which lock modes are required for which table action?

The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.

Operation                  Lock Mode LMODE Lock Description ------------------------- --------- ----- ---------------- Select                     NULL      1     null Select for update          SS        2     sub share Insert                     SX        3     sub exclusive Update                     SX        3     sub exclusive Delete                     SX        3     sub exclusive Lock For Update            SS        2     sub share Lock Share                  S        4     share Lock Exclusive              X        6     exclusive Lock Row Share             SS        2     sub share Lock Row Exclusive         SX        3     sub exclusive Lock Share Row Exclusive   SSX       5     share/sub exclusive Alter table                 X        6     exclusive Drop table                  X        6     exclusive Create Index                S        4     share Drop Index                  X        6     exclusive Truncate table              X        6     exclusive

4. How compatibility of locks work

The compatibility of lock modes are normally represented by following matrix:

NULL     SS  SX  S   SSX X -----    --- --- --- --- --- NULL    YES  YES YES YES YESSS      YES  YES YES YES YES SX      YES  YES YES no  no  S       YES  YES no  YES no SSX     YES  YES no  no  no X       YES  no  no  no  no

8. How to resolve locking situations?

Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:a. Asking the HOLDER to commit or rollback,b. Killing the session which holds the lock,e.g. ALTER SESSION KILL SESSION sid, serial#;c. If the database is running on unix or vms, it is possible to kill the  unix/vms shadow process directly. It is not recommended. When killing the unix/vms shadow process, please be careful of shared servers in a  multi-threaded environment.d. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction