Examples and scenarios of ORA-00060

For batch update, the principle of operations on multiple tables in the preceding transaction is the same, and a large concurrency also causes deadlock. You can either reduce the concurrency or avoid batch updates. De

For batch update, the principle of operations on multiple tables in the preceding transaction is the same, and a large concurrency also causes deadlock. You can either reduce the concurrency or avoid batch updates. De

Create table eg_60 (num number, txt varchar2 (10 ));

Insert into eg_60 values (1, 'first ');

Insert into eg_60 values (2, 'second ');

SQL> select rowid, num, txt from eg_60;

ROWID NUM TXT
--------------------------------------
AAAQT2AAHAAAEdYAAA 1 First
AAAQT2AAHAAAEdYAAB 2 Second

Session1:

Update eg_60 set txt = 'ses1' where num = 1;

Session2:

Update eg_60 set txt = 'ses2' where num = 2;

Update eg_60 set txt = 'ses2' where num = 1;

Session1:

Update eg_60 set txt = 'ses1' where num = 2;

In this case, Session2 reports the following error:

Update eg_60 set txt = 'ses2' where num = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

In Session1, update eg_60 set txt = 'ses1 'where num = 2; The hang status still exists. In this case, Session2 Exits normally after exit

Session1:

Update eg_60 set txt = 'ses1' where num = 2;

1 row updated.

The reason is that it Exits normally. Oracle's PMON will automatically rollback the non-Commit operation, releasing the resource with num = 2, so Session1 can be executed.

If the 60 error occurs, a trace file is generated. view the location of the trace file:

Show parameter user_dump
NAME TYPE VALUE
-----------------------------------------------------------------------------
User_dump_dest string/opt/app/ora10g/admin/petest/udump

View the trace file:

Deadlock graph:
--------- Blocker (s) -------- --------- Waiter (s )---------
Resource Name process session holds waits
TX-00090004-00019887 25 478X24 459 X
TX-000a002d-00032a8d 24 459X25 478 X
Session 478: DID 0001-0019-00027AEC session 459: DID 0001-0018-000CDDD8
Session 459: DID 0001-0018-000CDDD8 session 478: DID 0001-0019-00027AEC
Rows waited on:
Session 459: obj-rowid = 000366f6-AAAQT2AAHAAAEdYAAB
(Dictionary objn-66806, file-7, block-18264, slot-1)
Session 478: obj-rowid = 000366f6-AAAQT2AAHAAAEdYAAA
(Dictionary objn-66806, file-7, block-18264, slot-0)
Information on the OTHER waiting sessions:
Session 459:
Pid = 24 serial = 34722 audsid = 899246 user: 65/DCSOPEN
O/S info: user: dcsopen, term: pts/0, ospid: 8838, machine: vm-vmw4131-t
Program: sqlplus @ vm-vmw4131-t (TNS V1-V3)
Application name: SQL * Plus, hash value = 3669949024
Current SQL Statement:
Update eg_60 set txt = 'ses1' where num = 2
End of information on OTHER waiting sessions.
Current SQL statement for this session:
Update eg_60 set txt = 'ses2' where num = 1
========================================================== ==============

Here, the OBJECT_ID represented by 66806 corresponds to the object eg_60. The currently executed SQL statement is update eg_60 set txt = 'ses2 'where num = 1, which is the 60 error reported by this SQL statement, the reason is that the SQL statement update eg_60 set txt = 'ses1' where num = 2. Because here is two sessions on the same machine, if it is different machine client to access the database to do this experiment, you can according to the machine: vm-vmw4131-t know which client to execute this SQL.

In the process state section, the O/S info: user: dcsopen, term: pts/1, ospid: 13112, machine: vm-vmw4131-t can also know which client executes the SQL Report for the 60 error.

These are deadlocks generated between different sessions in the same table. There is another scenario, which is also a problem encountered in the previous application, that is, the deadlock between different tables. We just got a preliminary solution. In fact, the above two are transaction-level deadlocks, the possible inaccuracy here is that after an SQL statement is executed, there is no commit or rollback, and another SQL statement is executed. These two sqls form a transaction, resulting in a possible deadlock.

Concept:

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all

SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database ).

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly

With a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

For example:

Session1:

UPDATE TABLE1, UPDATE TABLE2...

Session2:

DELETE TABLE2, DELETE TABLE1...

In this case, deadlock may occur because each other may have the resources required by the other party.

In this case, the possible solution is to change the table order to the same, or split smaller transactions to avoid poor updates.