Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will discuss checking for LOCKs in the database, which is one of the checks we perform when there is a problem in the database.
1. First, we check if there are any UNCOMMITTED transactions.
1 2 3 4 5 | SQL> select sysdate, t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname, s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time from v$transaction t, v$session s where s.saddr = t.ses_addr order by start_time; |
2. We identify which table the LOCK caused by this operation is on and how long it has been there. Additionally, the type of LOCK is displayed as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> select /*+RULE */ s.username, o.name "Locked object", l.sid, s.serial#, p.spid, l.type, round(l.ctime/60,0) "Minutes", l.ctime, decode(l.lmode,'1','-','2','RS','3','RX','4','S','5','SRX','6','X') "Mode", substr(s.osuser,1,16)"OS user", substr(s.machine,1,16) "Machine", s.STATUS from v$process p, sys.obj$ o, v$session s, v$lock l, v$locked_object lo where l.sid = lo.session_id and l.sid > 5 and (l.id2 = lo.xidsqn or l.id1 = lo.object_id) and s.sid = lo.session_id and o.obj# = lo.object_id and p.addr = s.paddr and l.type != 'AE' order by 3,7; |
3. Finally, we can obtain more detailed data with the following queries.
For example, information such as who caused the LOCK, who was LOCKed, is the session currently ACTIVE or INACTIVE? can be obtained.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | SQL> SELECT s.sid BLOCKER_SID, s.serial# BLOCKER_SERIAL, s.username BLOCKER_USERNAME, s.machine BLOCKER_MACHINE, q.SQL_FULLTEXT BLOCKER_SQL, s.status BLOCKER_STATUS, s.event BLOCKER_EVENT, SUBSTR (s.program, 1, 40) BLOCKER_PROGRAM, s.SECONDS_IN_WAIT BLOCKER_SECONDS_IN_WAIT, s.row_wait_obj# BLOCKER_row_wait_obj, s.row_wait_file# BLOCKER_row_wait_file, s.row_wait_block# BLOCKER_row_wait_block, s.row_wait_row# BLOCKER_row_wait_row, CASE WHEN s.status = 'INACTIVE' AND w.SECONDS_IN_WAIT >= 300 THEN 'blocker is inactive and will be killed' WHEN s.status != 'INACTIVE' AND w.SECONDS_IN_WAIT >= 600 THEN 'blocker is active but will be killed' ELSE 'blocker not killed yet' END BLOCKER_KILL_STATUS, w.event BLOCKED_EVENT, SUBSTR (w.program, 1, 40) BLOCKED_PROGRAM, w.sid BLOCKED_SID, w.serial# BLOCKED_SERIAL, w.username BLOCKED_USERNAME, x.SQL_FULLTEXT BLOCKED_SQL, w.status BLOCKED_STATUS, w.WAIT_CLASS BLOCKED_wait_class, w.WAIT_CLASS_ID BLOCKED_wait_class_id, w.SECONDS_IN_WAIT BLOCKED_SECONDS_IN_WAIT, SYSDATE run_time FROM v$session s, v$session w, v$sql x, v$sql q WHERE w.blocking_session = s.sid AND w.SQL_ID = x.sql_id(+) AND s.PREV_SQL_ID = q.sql_id(+) AND w.blocking_session_status = 'VALID'; |
4. A similar query to the one above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | SQL> SELECT s.sid BLOCKER_SID, s.serial# BLOCKER_SERIAL, s.username BLOCKER_USERNAME, s.machine BLOCKER_MACHINE, q.SQL_FULLTEXT BLOCKER_SQL, s.status BLOCKER_STATUS, s.event BLOCKER_EVENT, SUBSTR (s.program, 1, 40) BLOCKER_PROGRAM, s.SECONDS_IN_WAIT BLOCKER_SECONDS_IN_WAIT, s.row_wait_obj# BLOCKER_row_wait_obj, s.row_wait_file# BLOCKER_row_wait_file, s.row_wait_block# BLOCKER_row_wait_block, s.row_wait_row# BLOCKER_row_wait_row, CASE WHEN s.status = 'INACTIVE' AND w.SECONDS_IN_WAIT >= 300 THEN 'blocker is inactive and will be killed' WHEN s.status != 'INACTIVE' AND w.SECONDS_IN_WAIT >= 600 THEN 'blocker is active but will be killed' ELSE 'blocker not killed yet' END BLOCKER_KILL_STATUS, w.event BLOCKED_EVENT, SUBSTR (w.program, 1, 40) BLOCKED_PROGRAM, w.sid BLOCKED_SID, w.serial# BLOCKED_SERIAL, w.username BLOCKED_USERNAME, x.SQL_FULLTEXT BLOCKED_SQL, w.status BLOCKED_STATUS, w.WAIT_CLASS BLOCKED_wait_class, w.WAIT_CLASS_ID BLOCKED_wait_class_id, w.SECONDS_IN_WAIT BLOCKED_SECONDS_IN_WAIT, SYSDATE run_time FROM v$session s, v$session w, v$sql x, v$sql q WHERE w.blocking_session = s.sid AND w.SQL_ID = x.sql_id(+) AND s.PREV_SQL_ID = q.sql_id(+) AND w.blocking_session_status = 'VALID' AND (s.username IS NOT NULL AND s.username NOT IN ('SYS', 'SYSTEM')); |
