Causes and solutions for common deadlock occurrences in Oracle

Causes and solutions for common deadlock occurrences in Oracle

One, the deadlock caused between the deletion and the update

The cause of the deadlock is the scramble or interdependence of multiple threads or processes against the same resource. Here is a list of instances where a scramble for the same resource causes a deadlock.

Oracle 10g, PL/SQL version 9.2

CREATE TABLE Testlock (ID number,

Test VARCHAR (+)

COMMIT

INSERT into testlock VALUES (1,' test1 ');

INSERT into testlock VALUES (2,' test2 ');

COMMIT;

SELECT * from testlock

1. ID TEST
2. ---------- ----------------------------------
3. 1 test1
4. 2 Test2

The reappearance of the deadlock phenomenon:

1) Execute in SQL window: SELECT * from Testlock for UPDATE; --Add a row level lock and modify the content, do not submit

2) Open another command window, execute: Delete from Testlock WHERE id=1;

A deadlock occurs at this point (note that a different window will be opened, otherwise you'll be prompted: POST the change RECORD to the DATABASE. Click Yes to force commit):

3) Deadlock View:

2. sql> Select s.username,l.object_id, l.session_id,s.serial#, S.lockwait,s.status,s.machine,s.program from V$session s,v$locked_object l where s.sid = L.session_id;</p><p>username session_id SERIAL# LOCKWAI T STATUS Machine Program
4. ----------  ----------  ---------- -------- -------- ----------------------  ------------
6. SYS 146 104 INACTIVE Workgroup\j-think PLSQLDev.exe
8. SYS 144 145 20834474 ACTIVE Workgroup\j-think PLSQLDev.exe

Field Description:
Username: The database user used by the deadlock statement;
Sid:session identifier, session identifier, session is a context in which communications parties start communication to the end of a communication.
serial#: The SID is reused, but when the same SID is reused, the serial# is incremented and not duplicated.
Lockwait: This field allows you to query for information about the lock you are currently waiting for.
Status: Used to determine the session state. Active: Executing the SQL statement. Inactive: Wait for operation. Killed: is marked as deleted.
Machine: The device where the deadlock statement resides.
Program: The main application that generated the deadlock statement.

4) View the statement that caused the deadlock:

Sql> 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));

2. Sql_text
3. ------------------------------------------------------------

1. Delete from testlock where ID = 1

5) Handling of deadlocks:

sql> alter system kill session ' 144,145 ';

2. System Altered
4. Executed in 1.061 seconds

The window at which the DELETE statement is executed appears:

sql> Delete from testlock where ID = 1;

2. Delete from testlock where ID = 1
4. ORA-00028: Your session has already been terminated

If you look at the deadlock again, you will see that there is no record of stauts active:

sql> Select S.username, l.session_id,s.serial#, S.lockwait,s.status,s.machine,s.program from v$session s,v  $locked _object l where s.sid = l.session_id;

1.   
2. username                  SESSION_ID SERIAL#  LOCKWAIT  status   machine              program  
3. ------------- ---------- ---------- --------  -------- ---------------------------  ----------------  

1. SYS 146 104 INACTIVE Workgroup\j-think PLSQLDev.exe
3. Executed in 0.032 seconds

The statement that the deadlock occurred has been terminated.

Two, there is no index on the foreign key caused by the deadlock

The client's 10.2.0.4 RAC for AIX environment frequently occurs with a ORA-60 deadlock problem that prevents the application from executing successfully.
After a series of diagnoses, it is found that the final problem is due to the lack of indexing on the foreign key, because the program deletes the data on the master table, the missing index causes row-level locks to escalate to table-level locks, resulting in a large number of lock waits and deadlocks.
Here is an example of a simple simulation of the problem:
Sql> CREATE TABLE t_p (ID number primary key, name VARCHAR2 (30));
Table created.
Sql> CREATE TABLE T_f (FID number, f_name varchar2 (), foreign key (FID) references t_p);
Table created.
sql> INSERT INTO t_p values (1, ' a ');
1 row created.
sql> INSERT INTO T_f values (1, ' a ');
1 row created.
sql> INSERT INTO t_p values (2, ' B ');
1 row created.
sql> INSERT INTO T_f values (2, ' C ');
1 row created.
Sql> commit;
Commit complete.
sql> Delete T_f where FID = 2;
1 row deleted.
At this point in session 2 the same child table is deleted:
sql2> Delete T_f where fid = 1;
1 row deleted.
Back to session 1 perform the deletion of the main table:
sql> Delete t_p where id = 2;
The session is locked back to session 2 to perform the deletion of the main table:
sql2> Delete t_p where id = 1;
The session is also locked, when the statement for session 1 is rolled back and a ORA-60 deadlock error occurs:
Delete t_p where id = 2
*
ERROR at line 1:
Ora-00060:deadlock detected while waiting for resource
sql> rollback;
Rollback complete.
The session 1 operation is rolled back and session 2 is also rolled back and the index on the foreign key column is established:
1 row deleted.
sql2> rollback;
Rollback complete.
Sql2> CREATE index Ind_t_f_fid on t_f (FID);
Index created.
Repeat the above steps session 1 To delete the child table records:
sql> Delete T_f where FID = 2;
1 row deleted.
Session 2 Delete a child table record:
sql2> Delete T_f where fid = 1;
1 row deleted.
Session 1 Delete the main table record:
sql> Delete t_p where id = 2;
1 row deleted.
Session 2 Delete the main table record:
sql> Delete t_p where id = 1;
1 row deleted.
All delete operations can be executed successfully, in two cases, the difference between the lock information is not in-depth analysis, the focus is on the foreign key column to build the index.
Although there are some articles mentioned that can not be indexed on the Foreign key column if certain conditions are met, my view has always been that since the creation of an external key, do not care about one more index, because the cost of an index is insignificant compared to the problem caused by missing this index.

"Supplemental" Differences between Oracle 10g and Oracle 9i TRC log content
The main difference is that two SQL statements are prompted in Oracle 10g to wait for resources, and in Oracle 9i, only the SQL statements that detect deadlocks are displayed
Oracle 10g 10.2.0.3.0:

DEADLOCK detected (ORA-00060) [Transaction deadlock]the following DEADLOCK is not a ORACLE error. It is a adeadlock due to user error in the design of a applicationor from issuing incorrect Ad-hoc SQL. The followinginformation may aid in determining the Deadlock:deadlock graph:---------blocker (s)---- -------------Waiter (s)---------Resource Name Process session holds waits process session holds WAITSTM-0000DD5       5-00000000 146 SX SSX 148 SX ssxtm-0000dd55-00000000 148 SX SSX 146 SX ssxsession 146:did 0001-0010-00000008session 148:did 0001-0011-00000006session 148:did 0001-0011 -00000006session 146:did 0001-0010-00000008rows waited on:session 148:no rowsession 146:no rowInformation on the other Waiting sessions:session 148:pid=17 serial=39 audsid=540046 User:54/scott o/s Info:user:skyhome\sky, Term:skyhome, ospid:3028:7000, machine:workgroup\skyhome Program:Plsqldev.exe application Name:pl/sql Developer, hash value=1190136663 action Name:command window-new, hash value=254 318129 current SQL statement:delete t_p where id = 1End of information in other waiting sessions. Current SQL statement for this session:delete t_p where id = 2

Oracle 9i 9.2.0.7.0:

DEADLOCK detectedcurrent SQL statement for this session:delete t_p where id = 2The following DEADLOCK was not a ORACLE err Or. It is a adeadlock due to user error in the design of a applicationor from issuing incorrect Ad-hoc SQL. The followinginformation may aid in determining the Deadlock:deadlock graph:---------blocker (s)---- -------------Waiter (s)---------Resource Name Process session holds waits process session holds waitsTM-0000260       e-00000000 SX SSX SX ssxtm-0000260e-00000000 SX SSX SX ssxsession 51:did 0001-0015-0000043dsession 20:did 0001-0017-00000397session 20:did 0001-0017-00 000397session 51:did 0001-0015-0000043drows waited on:session 20:no rowsession 51:no rowinformation on the other waitin G sessions:session 20:pid=23 serial=53179 audsid=197296 User:87/scott o/s Info:user:sky, Term:skyhome, ospid:5540:       4984, Machine:workgroup\skyhome     Program:plsqldev.exe Client info:127.0.0.1 application Name:pl/sql Developer, hash value=1190136663 action name : Command window-new, hash value=254318129 current SQL statement:delete t_p where id = 1End of information in other W Aiting sessions.

Three, two tables before a deadlock caused by a mutual update operation between different orders

Deadlocks in Oracle:



Note: The execution order of 4 UPDATE statements is top-down in the image
The left session in the diagram is interrupted (not rolled back or committed at this time, waiting for the user to decide), the right session is blocked, and the left session is waiting to release the lock on Table A.




Deadlock resolution:






Modify the App! Refer to the following methods. 1. Reduce the deadlock to a minimum although it is not possible to avoid deadlocks altogether, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because there are only a few transactions:
? Rollback, and rollback cancels all work performed by the transaction.  Resubmitted by the application because of a deadlock rollback. The following methods help to minimize deadlocks:
? Access the object in the same order. Avoid user interaction in a transaction.
? Keep the transaction short and in one batch. Use a low isolation level.?  Use a bound connection. 2. Access objects in the same order
If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. For example, if two concurrent transactions get a lock on the Supplier table and then get a lock on the part table, another transaction is blocked on the Supplier table until one of the transactions completes. The second transaction continues after the first transaction commits or rolls back. No deadlock occurs. Using stored procedures for all data modifications can standardize the order in which objects are accessed.



3. Avoid user interaction in transactions avoid writing transactions that contain user interaction, because running batches without user interaction is much faster than the user responds to queries manually, such as prompting for replies to application request parameters. For example, if a transaction is waiting for user input, and the user goes to lunch or even goes home for the weekend, the user suspends the transaction so that it cannot be completed. This reduces the throughput of the system because any locks held by the transaction are freed only when the transaction commits or rolls back. Even if there is no deadlock, other transactions that access the same resource are blocked waiting for the transaction to complete. 4. Keep the transaction short and in a batch process
Deadlocks typically occur in the same database while concurrently executing multiple transactions that require long running time. The longer a transaction runs, the longer it takes to hold an exclusive lock or update lock, blocking other activities and potentially causing deadlocks. Keep transactions in one batch, you can minimize the network traffic round-trip of a transaction, reduce the possibility of completing a transaction, and release the lock.

Original Digest from:

http://liwenshui322.iteye.com/blog/722712

Http://biancheng.dnbcw.info/oracle/385142.html

Http://wenku.baidu.com/link?url=MhEcvSfC686wE-GGcSVnOf02R_6y6nsiq9pMOE2sHTlIXSAIIk89mlVm8eTBDaA8IxOAY_F_ 1e2u3s7jyhglpbt5mobwxlcyftdnjxc0uyw

Causes and solutions for common deadlock occurrences in Oracle