This Oracle DML Locks Technical Documentation describes about the following

  • What is a Lock?
  • What are the types of Locks?
  • Why Locks Occur?
  • What we can do to prevent locks?
  • An Example scenario of when a lock occurs and how it can be prevented next time.

 Attached Document contains the below table of contents


Oracle Lock Modes

Share Lock Mode

Exclusive Lock Mode

Difference between Exclusive and shared lock

Oracle DML Lock Types

Row Level Locks [TX]

Table Level Locks [TM]

Row Share Table Lock (RS)

Row Exclusive Table Lock (RX)

Share Table Lock (S)

Share Row Exclusive Table Lock (SRX)

Exclusive Table Lock (X)

TM Lock Example

Locking with No Index on a Foreign Key

Locking with Index on a Foreign Key

Effects of Table Lock

Table Dead Lock Recovery

Preventing the Deadlocks

Identifying and fixing the cause for locks

Query to find Blocking Locks

Query to find all the Missing Foreign Key Indexes


Download Oracle DML Locks Technical Documentation