Tip of the Day ( Monday, December 15, 2025 )

Oracle Database DML Locks

Oracle Database locks is required for Data Consistency and Concurrency. 

Database locks can be categorized as:

• DML Locks
• DDL Locks
• Internal Locks

DML Locks occur when any session is modifying any row data, Following types of locks are acquired during an DML operation:

• Row Lock (TX) in Exclusive Mode
• Table Lock (TM)

Table locks can be of various types such as:

Row Share (RS) Lock

Row Exclusive (RX) Locks

Share (S) Locks

Share Row Exclusive (SRX) Locks

Exclusive (X) Locks

Each type of locks are meant for different types.

Query to check for DML Locks:

-- Looking for Row Lock (TX) in Exclusive mode (lmode=6)

set lines 200

SELECT sid, type, lmode, request, ctime, block, con_id, id1, id2 FROM V$LOCK WHERE lmode=6 and type='TX';

-- To show holders and waiters for locks being waited on, use the following:

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type

FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY id1, request;

Watch the below video for a detailed tutorial & demo on this topic.

https://www.youtube.com/watch?v=I4ETWsPXfYs

=======================================================================

** Email to info@shreyantech.com to get the Tip of the Day in your mailbox.

** Connect with the author / trainer: https://suchitdba.blogspot.com/p/connect-with-author-database-trainer.html