Fast detection of lock-waiting in Oracle database _oracle

Usually in the large database system, in order to ensure the consistency of the data, when the data in the database operation, the system will be the corresponding lock on the data.

These locks have "read-only lock", "exclusive lock", "shared exclusive lock" and many other types, and each type has a "row-level lock" (one lock at a time), "page-level lock" (one page at a time, the smallest available allocation unit in the database to store records), and "table-level lock" (lock the entire table). For row-level exclusive locks, the other rows in the table can be modified (Update) or delete (delete) by other users except for the locked line, and for table-level exclusive locks, all other users can only query (select) the table. It is not possible to modify or delete any of these records. When the program commits or rolls back (rollback), the locked resource is released, allowing other users to manipulate it.

But in some cases, because of some special reasons in the program, it has not been submitted for a long time after locking the resources, or because of the user's reason, such as the need to modify the data, not timely changes and submit, but put aside; or because the client server in the way clients appear "panic", But the server side is not detected, thus causing the locked resources are not released in time, and ultimately affect other user actions.

Therefore, it is a challenge for database administrators to diagnose the users who lock the resources quickly and solve their locks.
As the database application system is becoming more and more complex, once the lock resource has not been released in time, it will cause a large number of users to operate the same table can not operate, thus affecting the use of the system. At this point, the DBA should resolve the issue as quickly as possible. However, because the query statement "Get the user name that is waiting for the lock resource" in Oracle 8.0.x

Select A.username, A.sid, a.serial#, b.id1 from
 v$session A, V$lock b
 where a.lockwait = b.kaddr

Very slowly, (performed quickly in Oracle 7.3.4), and execute a query that finds blocking user processes for other users

  Select A.username, A.sid, a.serial#, b.id1 from
   v$session A, V$lock b
   where b.id1 in
      (select DISTINCT E.id1
   from v$session D, v$lock e
       where d.lockwait = e.kaddr) and
    a.sid = B.sid and
    b.request = 0

Implementation is also very slow. As a result, it is often only possible to clear a user process that has a v$session status of "inactive" (inactive) and the last operation time to the current more than 20 minutes (last_call_et>20*60 seconds) before the problem is resolved.

But the one-size-fits-all approach is actually "throwing the baby out with the dirty water." Because some users ' processes are "inactive" and have been inactive for a long time, they are in a lock-waiting state.

Therefore, the author thought of a solution. That is, by saving the related records in the V$lock,v$session view when the problem occurs in the table that you set up, and then querying the table, the speed is greatly improved and the problem can be quickly discovered. After practical use, the effect is very good. After the user reflects, a few seconds can be identified as a result of locking resources to affect other users of the process, and the corresponding processing.

First, log in to the database as DBA (not necessarily system), creating three basic tables: My_session,my_lock, My_sqltext, and establishing the appropriate index on the column that will be queried. Statement as follows:
REM takes care of fields from the V$session view, creates my_session tables, and creates indexes on the fields that the query will use to speed up the query

drop table my_session;
CREATE TABLE My_session
as
select A.username, A.sid, a.serial#,
a.lockwait, A.machine,a.status
, A.last_call_et,a.sql_hash_value,a.program from
 v$session a
 where 1=2;

Create unique index my_session_u1 on my_session (SID);
Create INDEX my_session_n2 on my_session (lockwait);
Create INDEX my_session_n3 on my_session (Sql_hash_value);

REM Takes a field out of the V$lock view, creates a My_lock table, and creates an index on the field that the query will use to speed up the query

drop table My_lock;
CREATE TABLE My_lock
as
select Id1, kaddr, sid, Request,type from
 v$lock
 where 1=2;

Create INDEX my_lock_n1 on My_lock (SID);
Create INDEX my_lock_n2 on My_lock (KADDR);

REM Takes a field out of the V$sqltext view, creates a My_sqltext table, and creates an index on the field that the query will use to speed up the query

drop table My_sqltext;
CREATE TABLE My_sqltext
as Select Hash_value, sql_text from
 v$sqltext
 where 1=2;

Create INDEX my_sqltext_n1 on My_sqltext (Hash_value);

Then, create a SQL script file so that it can be called directly from the sql*plus when needed. Where the records in the table are first deleted by using the TRUNCATE TABLE name command. You use the TRUNCATE command, not the delete command, because the delete command is executed, it will produce a replay record, slower, and the space occupied by the index is not really released, if repeatedly insert and delete, the index occupies the space will continue to grow, The query speed also slows down. The truncate command does not produce a replay record, speed execution is faster than delete, and the index space is released accordingly. After you delete the record, insert the related records from the three views into the three tables that you created. Finally, the query, because of the index, and because of the insertion time after the filter, the number of records is relatively small, so the query speed quickly, you can see the results immediately.

At this point, if it is found that the process of blocking other user processes is normal operation, then you can notify the user to commit, so as to release the lock resource, if it is not normal operation, that is, its status is "inactive", and its last_call_et has been more than a long time, You can clear the process by executing the following statement, and the system will automatically rollback it to release the locked resource.

Alter system kill session ' Sid, Serial# '; 

The SQL script is as follows:

Set echo off set feedback off prompt ' Delete old records ... ' TRUNCATE TABLE my_session;
TRUNCATE TABLE My_lock;

TRUNCATE TABLE my_sqltext; Prompt ' Get data ... ' INSERT into my_session select A.username, A.sid, a.serial#, a.lockwait, A.machine,a.status, A.L

Ast_call_et,a.sql_hash_value,a.program from V$session a where NVL (A.username, ' null ') < > ' null;

Insert INTO My_lock select Id1, kaddr, sid, Request,type from V$lock;

Insert INTO My_sqltext Select Hash_value, sql_text from V$sqltext S, my_session m where S.hash_value=m.sql_hash_value;  Column username format A10 column machine format a15 column Last_call_et format 99999 heading "Seconds" column SID format 9999 prompt "is waiting for someone else's user" Select A.sid, a.serial#, A.machine,a.last_call_et, A.username, b.id1 from My_session A, my_loc

K B where a.lockwait = B.KADDR;  Prompt "The waiting user" Select A.sid, a.serial#, A. Machine, A.last_call_et,a.username, B. B.type,a.status,b.id1 from My_session A, My_lock b where b.id1 in (select DISTINCT E. Id1 from My_session D, my_lock e where d.lockwait = e.kaddr) and A.sid = B.sid and b.request=0; Prompt "Find out its SQL" select A.username, A.sid, a.serial#, B.id1, B.type, C.sql_text from My_session A, My_lock B, My_sqltex T C where b.id1 in (select distinct e.id1 from My_session D, my_lock e where d.lockwait = e.kaddr) and a.
 
 Sid = B.sid and b.request=0 and C.hash_value =a.sql_hash_value;

The above ideas can also be used in other large database systems such as Informix, SYBASE,DB2. By using this script, it can greatly improve the situation of acquiring the current lock waiting in the system, so as to solve the lock waiting problem in the database application system in time. Also, since the program name and the corresponding SQL statement have actually been removed, it can be recorded later and given to the developer for analysis and fundamentally resolved.