Differences between deadlocks and lock waits

 

During the interview today, I asked how to solve the query deadlock. In my opinion, the query was impossible to encounter a deadlock. So I casually talked about the SQL statement that found the deadlock, check SQL statements.

The more I thought about it, the less I understood it. So I sorted out the differences and connections between the lock wait and the deadlock.

The so-called lock wait: when a transaction a performs ddl or dml operations on a data table, the system will add a table-level exclusive lock to the table, at this time, when other transactions operate on the table, they will wait for a to commit or roll back before continuing the operation of B.

The so-called deadlock: When two or more users wait for data to be locked, a deadlock will occur. At this time, these users are stuck and cannot continue to process the business, oracle can automatically detect deadlocks and solve them, by rolling back a statement in a deadlock, releasing locked data, rollback will encounter ora-00060 deadlock detected while waiting for resource

Simulated lock wait:

Two transactions, a and B, create t1 and t2 respectively, and initialize a piece of data,

A changes the data of t1. At this time, B does not submit the data. At this time, B changes the same column, and B remains in the waiting state.

We can query the lock wait content:

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,

(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 time, the lock wait phenomenon can be queried. The last column is not empty, but the waiting event.

At this time, we can prompt user a to submit a transaction or roll back it, or directly kill it.

Alter system kill session 'sid, serial #';

The status quo remains unchanged. When transaction a changes table T2.

SQL> update t1 set id = 1000 where id = 1;

Update t1 set id = 1000 where id = 1

*

Row 3 has an error:

ORA-00060: deadlock detected while waiting for resources

Now oracle has helped me solve this deadlock problem.

The creation of deadlocks requires four conditions:

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

2. processes that hold and wait (request and continue) obtain resources can apply for new resources again.

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

4. Several processes in the circular wait (Cyclic wait condition) system form a loop. Each process in this loop is waiting for resources occupied by adjacent processes.

Locate deadlocks:

System-level positioning

Select username, lockwait, status, machine, program from v $ session where sid in (select session_id from v $ locked_object)

The user of the Username deadlock, the lockwait deadlock status, the active State indicates the deadlock, the machine where the machine deadlock is located, the program deadlock comes from that program

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 positioning

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 deadlock handling policies

1. The ostrich algorithm ignores this issue.

2. Locate the deadlock and restore it.

3. Carefully allocate resources dynamically to avoid deadlocks

4. Destroy a condition in the deadlock

If the deadlock cannot be solved by oracle, We need to locate the process level and find the corresponding sid and serial #

Alter system kill 'sid, serail #'

If the process fails, find the corresponding process to force it to 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

 

Query the deadlock of oracle www.2cto.com

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 ',

'Redo thread', 'redo thread ',

'Non', 'user name ',

'Tx ', 'Transaction ',

'Tm', 'dml ',

'Ul ', 'pl/SQL USER lock ',

'Dx ', 'stributed 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, 'clusive ',

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, 'clusive ',

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 is 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;

It is best to execute these statements in plsql or sqldeveloper. If the statements are executed directly in the database, you need to format the table. Otherwise, the statements will be very dazzling.

 

From Dream19881003