Oracle lock table troubleshooting example: Oracle lock table troubleshooting

Oracle lock table troubleshooting example: Oracle lock table troubleshooting

One time a developer directly runs the delete table operating system on the database server during peak hours, resulting in no response for a long period of execution. Then, the SQLPLUS client is closed and the delete statement is run again, this causes the database to run slowly.
The query operation knows that it is caused by the lock table and is finally processed. This article only simulates the lock table processing process and can quickly handle the same problem next time.

1. view database wait events

If you do not know the cause, you can analyze it by waiting for the event.

SQL> select sid, EVENT from v $ session_wait where wait_class <> 'idle ';

SID EVENT
--------------------------------------------------------------------------
158 SQL * Net message to client
159 enq: TX-row lock contention
SQL>

 

2. Check whether the database has a lock

SQL> select * from v $ lock where block = 1;

 

3. query the lock to see who has locked the lock.

Note: a row with a BLOCK of 1 indicates that the resource is locked by it. REQUEST indicates that the lock is required.

SQL> SELECT sid, id1, id2, lmode, block, request, type
From v $ LOCK
WHERE id1 IN (SELECT id1 from v $ lock where lmode = 0)
Order by id1, request;

SID ID1 ID2 LMODE BLOCK REQUEST TY
--------------------------------------------------------------
153 655397 308 6 1 0 TX
157 655397 308 0 0 6 TX

 

4. query the SID, Serial #, user, start time of the lock table, and then you can kill the process.

SQL> select t1.sid, t1.serial #, t1.username, t1.logon _ time from v $ session t1, v $ locked_object t2 where t1.sid = t2.session _ id order by t1.logon _ time;

Sid serial # USERNAME LOGON_TIME
---------------------------------------------------------------------
153 12 ABC 09:19:33
157 106 ABC 09:54:24

 

5. You can further query SID, SPID, and other information.

SQL> select s. username, s. osuser, s. sid, s. serial #, p. spid, s. program, s. STATUS
From v $ session s, v $ process p
Where s. paddr = p. addr and s. username is not null;

Username osuser sid serial # SPID PROGRAM STATUS
----------------------------------------------------------------------------------------------------------------------------------------------------
ABC oracle 153 12 4290 sqlplus @ oradb (TNS V1-V3) INACTIVE
SYS oracle 150 12 4417 sqlplus @ oradb (TNS V1-V3) ACTIVE
ABC oracle 157 119 4830 sqlplus @ oradb (TNS V1-V3) ACTIVE

You can either kill it in oracle or in the operating system. For statements whose status is killed, you can use the kill command of the operating system to kill it as quickly as possible.

Alter system kill session '2017, 12 ';
Alter system kill session '123 ';

Or in the operating system

Kill-9 4290
Kill-9 4830

 

6. query the SID and related resources of the locked table

Note: The tables and SQL statements to be locked must be manually processed in conjunction with the preceding query. You can also find the SQL statements by using the SID.

Set pagesize 999;
Set linesize 200;
Col PROGRAM for a25;
Col TERMINAL for a10;

SQL> select s. status, s. sid, s. serial #, p. spid,
S. last_call_et as exec_seconds, t. SQL _text as curr_ SQL
From gv $ session s, v $ process p, v $ instance I, v $ lock k, v $ sqltext t
Where s. paddr = p. addr
And s. type! = 'Background'
And s. lockwait = k. kaddr
And s. SQL _hash_value = t. hash_value
And s. username is not null;
Status sid serial # SPID EXEC_SECONDS CURR_ SQL
--------------------------------------------------------------------------------------------------------------------
ACTIVE 159 28 4072 update test_lock set name = 'aa4' where id = 1

SQL>