The difference between Oracle deadlock and lock wait

The so-called lock wait: is a transaction A to a data table DDL or DML operation, the system will be added to the table-level lock, when the other transaction to the table operation will wait for a commit or rollback, before you can continue the operation of B

The so-called deadlock: When two or more users wait for locked data to deadlock, when these users are stuck in the business can not continue to process, Oracle can automatically detect deadlocks and resolve them, by rolling back a deadlock in the statement, release the locked data, rollback will encounter ora-00060 Deadlock detected while waiting for resource

Analog Lock Wait:

Two transactions A and B, create t1,t2 separately, and initialize a single piece of data,

A changes the data of T1 and does not commit at this time, when B changes the same column, and B is in the waiting state

We can query the contents of lock waits:

Wait_lock.sql

Select
(select username from v$session where sid = A.sid) Username,
A.sid,
(select serial# from v$session where sid = A.sid) serial#,
A.type,
A.ID1,
A.id2,
A.lmode,
A.request,
A.block,
B.sid Blocking_sid
From V$lock A,
(SELECT * FROM V$lock
Where request > 0
and type <> ' MR '
) b
where a.id1 = B.ID1 (+)
and A.id2 = B.id2 (+)
and A.lmode > 0
and A.type <> ' MR '
ORDER BY Username,a.sid,serial#,a.type

At this point you can query the phenomenon of lock wait, the last column is not empty is waiting for the event

At this point we can tell the a user to commit the transaction or rollback, or you can directly kill

Alter system kill session ' sid,serial# ';

Keep the status quo, in a transaction Change T2 table at this time in a transaction will produce

sql> Update T1 set id=1000 where id=1;
Update T1 set id=1000 where id=1
*
An error occurred on line 1th:
ORA-00060: Deadlock detected while waiting for resource

At this point, Oracle has helped me solve this deadlock problem.

The creation of a deadlock requires four required conditions:

1, mutual execution (mutex) resources cannot be shared and can only be used by one process

2,hold and wait (request and continue) a process that has already received resources can request new resources again

3,no pre-emption (inalienable) the allocated resources cannot be forcibly deprived by the corresponding process

4,circular Wait (cyclic wait condition) a number of processes in the system compose loops that each process in the loop waits for resources that the neighboring process is occupying

To locate a deadlock:

System-level targeting

Select Username,lockwait,status,machine,program from V$session where SID in (select session_id from V$locked_object)

Username Deadlock user, lockwait deadlock state, status active means deadlock, machine deadlock, program deadlock comes from the application

Statement-level positioning

Select Sql_text from V$sql where Hash_value in (select Sql_hash_value from v$session where SID in (select session_id from V$locked_object));

Process-level targeting

Select s.username,l.object_id,l.session_id,s.serial#,l.oracle_usrename,l.os_user_name,l.process from V$locked_ Object L,v$session s where L.session_id=s.sid;

General strategy for handling deadlocks

1, Ostrich algorithm ignores the problem

2, locating deadlocks and recovering

3, carefully allocate resources dynamically to avoid deadlocks

4, destroys a condition in the deadlock

If a deadlock is not resolved by Oracle, we need to navigate to the process level to find the corresponding SID and Serial#

alter system kill ' sid,serail# '

Fail, find the corresponding process force close

Select P.spid from V$session S, v$process p where s.sid=xx and S.PADDR=P.ADDR

Ps-ef | grep spid

kill-9 XX

Querying Oracle for Deadlocks

Lock.sql

Select Bs.username "Blocking user", bs.username "DB user",
Ws.username "Waiting User", BS. Sid "Sid", WS. SID "Wsid",
bs.serial# "serial#", bs.sql_address "address",
Bs.sql_hash_value "SQL Hash", bs.program "Blocking App",
Ws.program "Waiting App", bs.machine "Blocking machine",
Ws.machine "Waiting Machine", Bs.osuser "Blocking OS User",
Ws.osuser "Waiting OS User", bs.serial# "serial#",
ws.serial# "wserial#",
DECODE (wk. TYPE,
' MR ', ' Media Recovery ',
' RT ', ' Redo Thread ',
' UN ', ' USER Name ',
' TX ', ' Transaction ',
' TM ', ' DML ',
' UL ', ' PL/SQL USER LOCK ',
' DX ', ' Distributed xaction ',
' CF ', ' Control FILE ',
' Is ', ' Instance state ',
' FS ', ' FILE SET ',
' IR ', ' Instance Recovery ',
' ST ', ' Disk SPACE Transaction ',
' TS ', ' Temp Segment ',
' IV ', ' Library Cache invalidation ',
' LS ', ' LOG START OR Switch ',
' RW ', ' ROW Wait ',
' SQ ', ' Sequence number ',
' TE ', ' Extend TABLE ',
' TT ', ' Temp TABLE ',
Wk. TYPE
) Lock_type,
DECODE (Hk.lmode,
0, ' None ',
1, ' NULL ',
2, ' Row-s (SS) ',
3, ' Row-x (SX) ',
4, ' SHARE ',
5, ' S/row-x (SSX) ',
6, ' EXCLUSIVE ',
To_char (Hk.lmode)
) Mode_held,
DECODE (Wk.request,
0, ' None ',
1, ' NULL ',
2, ' Row-s (SS) ',
3, ' Row-x (SX) ',
4, ' SHARE ',
5, ' S/row-x (SSX) ',
6, ' EXCLUSIVE ',
To_char (Wk.request)
) mode_requested,
To_char (HK.ID1) lock_id1, To_char (HK.ID2) Lock_id2,
DECODE
(HK. BLOCK,
0, ' not Blocking ',/**//* not Blocking any other processes */
1, ' Blocking ',/**//* this lock blocks other processes */
2, ' Global ',/**//* this lock are Global, so we can ' t tell */
To_char (HK). BLOCK)
) blocking_others
From V$lock HK, v$session BS, V$lock wk, v$session ws
WHERE HK. BLOCK = 1
and Hk.lmode! = 0
and Hk.lmode! = 1
and wk.request! = 0
and WK. TYPE (+) = HK. TYPE
and WK.ID1 (+) = Hk.id1
and Wk.id2 (+) = Hk.id2
and HK. SID = BS. SID (+)
and WK. SID = ws. SID (+)
and (Bs.username is not NULL)
and (Bs.username <> ' SYSTEM ')
and (Bs.username <> ' SYS ')
ORDER by 1;

The execution of these statements is best done in plsql or sqldeveloper if you need to format the table directly in the database, otherwise it will make you dizzy.

Types of locks for Oracle databases

definitions of locks in Oracle's official documentation:Locks is mechanisms that prevent destructive interaction between transactions AC Cessing the sameResource-either user objects such as tables and rows or system objects not visible to users, such as S hared data structures in memory and data dictionary rows.

The general meaning of this passage is that the function of the lock is used to protect tables, rows, shared data structures in memory and data dictionary rows and so on when the interactive access is not destroyed.

Depending on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock) to protect the integrity of the data, DDL locks (Dictionary locks, Dictionary locks) to protect the structure of database objects, such as tables, Structure definitions for indexes, internal locks and latches (internal locks and latches), protect the internal structure of the database.

DML locks are designed to guarantee data integrity in the case of concurrency, where DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks.

There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.

When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in ORA-60 errors. These phenomena can cause great harm to the actual application, such as long time not responding, large number of transaction failures, etc.

1. Querying for locks in the database

SELECT * from V$lock;
SELECT * from V$lock where block=1;

2. Querying for Locked objects

SELECT * from V$locked_object;

3. Query blocking

Check for blocked sessions
SELECT * from V$lock where lmode=0 and type in (' TM ', ' TX ');

Check for blocking other session locks
SELECT * from V$lock where lmode>0 and type in (' TM ', ' TX ');

4. Querying the process that the database is waiting for a lock

SELECT * from v$session where lockwait are NOT null;

5. Query the relationship between session lock waits

Select A.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from V$lock A,v$lock b

where A.id1=b.id1 and A.id2=b.id2 and A.block=1 and b.block=0;

6. Query the lock wait event

SELECT * from v$session_wait where event= ' enqueue ';

7. Find locked Tables and locks

Select B.owner Tableowner, B.object_name TABLENAME, C.osuser Lockby, C.username LOGINID, C.sid Sid, C.serial# SERIAL
From V$locked_object a,dba_objects B, v$session c
where b.object_id = a.object_id and a.session_id =c.sid;
--through SID, serial unlock
--alter system kill session ' SID, SERIAL ';

The difference between Oracle deadlock and lock wait