DEMO: HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK
Query to check table lock in Oracle database.
GOAL
How do I know if an Oracle table is locked or not.
DEMO
Create a DEMO user
SQL> SHO USER
USER is "SYS"
CREATE USER DEMO IDENTIFIED BY DEMO;
GRANT CONNECT, RESOURCE TO DEMO;
GRANT CREATE DATABASE LINK TO DEMO;
GRANT SELECT ON DBA_DB_LINKS TO DEMO;
GRANT SELECT ON V_$MYSTAT TO DEMO;
GRANT SELECT ON V_$DATABASE TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO;Create a dummy table
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"
CREATE TABLE DEMO.DUMMY(COL1 NUMBER);
INSERT INTO DEMO.DUMMY VALUES(1);
COMMIT;SQL> SELECT * FROM DEMO.DUMMY;
COL1
----------
1Connect to database with 3 sessions by DEMO user
-- Session #1
set sqlprompt "_user'@'SES_1 > "
-- Session #2
set sqlprompt "_user'@'SES_2 > "
-- Session #3
set sqlprompt "_user'@'SES_3 > "In the first session UPDATE the row of DUMMY table. It will create a table lock.
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"
SQL> set sqlprompt "_user'@'SES_1 > "
DEMO@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
284
DEMO@SES_1 > UPDATE DEMO.DUMMY SET COL1=2 WHERE COL1=1;
1 row updated.The first session is now holding the lock on the table. The following query gives details of all locks.
USERNAME SID SERIAL# PID STATUS BLO BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ --------------- --- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 284 52279 30569 INACTIVE NO 28 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table LockIn the second session INSERT a new row into DUMMY table. Do not COMMIT it. It will create another table lock.
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHOW USER
USER is "DEMO"
SQL> set sqlprompt "_user'@'SES_2 > "
DEMO@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
36
DEMO@SES_2 > INSERT INTO DEMO.DUMMY VALUES(2);
1 row created.The second session is now holding another lock on the table. The following query gives details of all locks.
USERNAME SID SERIAL# PID STATUS BLO BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ --------------- --- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 284 52279 30569 INACTIVE NO 87 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 36 36732 30629 INACTIVE NO 6 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table LockIn the third session UPDATE the first row of the DUMMY table that's already been locked by the first session. It will create another table lock and a blocking session as well.
SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"
SQL> set sqlprompt "_user'@'SES_3 > "
DEMO@SES_3 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
4
DEMO@SES_3 > SET TIMING ON
DEMO@SES_3 > UPDATE DEMO.DUMMY SET COL1=3 WHERE COL1=1;The third session is now holding another lock on the table. The following query gives details of all locks.
USERNAME SID SERIAL# PID STATUS BLOCKED BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ -------------------- -------------------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 284 52279 30569 INACTIVE NO 227 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 36 36732 30629 INACTIVE NO 146 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 4 47113 30681 ACTIVE AND BLOCKED YES 284 89 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY AAAStsAAHAAAAFfAAA 3 ROW_X (SX): Row Exclusive Table Lock
The first and the third sessions are updating the same row of DEMO.DUMMY table. Since the first session hasn't commited/rollbacked its transaction, the first session (SID 284) is blocking the third session(SID 4).
In this demo, I have 3 locks on DEMO.DUMMY table and 1 blocking session.
BLOCKING BLOCKED SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- ---------------------------------------- -------------------- ---------------
284,52279(DEMO) 4,47113(DEMO) 90 2 DEMO.DUMMY AAAStsAAHAAAAFfAAA gfy4bb332tjq6Next, COMMIT transactions trough 1 to 3 and issue the query to get details of all left locks.
USERNAME SID SERIAL# PID STATUS BLOCKED BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ ------------------ ---------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 284 52279 30569 INACTIVE NO 292 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 36 36732 30629 INACTIVE NO 211 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 4 47113 30681 ACTIVE AND BLOCKED YES 284 154 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY AAAStsAAHAAAAFfAAA 3 ROW_X (SX): Row Exclusive Table Lock-- The first session
DEMO@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
284
DEMO@SES_1 > COMMIT;
Commit complete.-- The first session committed its transaction and released its table lock.
USERNAME SID SERIAL# PID STATUS BLOCKED BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ --------------- -------------------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 36 36732 30629 INACTIVE NO 240 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock
DEMO 4 47113 30681 INACTIVE NO 4 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock-- The second session
DEMO@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
36
DEMO@SES_2 > COMMIT;
Commit complete.-- The first and the second sessions committed its transactions and released its table locks.
SQL> /
USERNAME SID SERIAL# PID STATUS BLOCKED BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM PROGRAM_NAME OBJECT_NAME LOCKED_ROWID LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ --------------- -------------------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO 4 47113 30681 INACTIVE NO 15 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY 3 ROW_X (SX): Row Exclusive Table Lock-- The third session
DEMO@SES_3 > SET TIMING ON
DEMO@SES_3 > UPDATE DEMO.DUMMY SET COL1=3 WHERE COL1=1;
0 rows updated.
Elapsed: 00:02:59.18
DEMO@SES_3 > COMMIT;
Commit complete.
Elapsed: 00:00:00.00-- All 3 sessions commmited its transactions and released all table locks.
SQL> /
no rows selectedThat's it. Drop DEMO user.
SQL> SHOW USER
USER is "SYS"
SQL> DROP USER DEMO CASCADE;
User dropped.
Written At
08 MAY 202219:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0