Experiments on ORA-60 deadlock
Create a table:
SQL> create table tbl_ora_60 (
Id number (5 ),
Name varchar2 (5)
);
SQL> insert into tbl_ora_60 values (1, 'A ');
1 row created.
SQL> insert into tbl_ora_60 values (2, 'B ');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tbl_ora_60;
ID NAME
---------------
1
2 B
Lab started
Session1:
SQL> update tbl_ora_60 set name = 'C' where id = 1;
1 row updated.
Session2:
SQL> update tbl_ora_60 set name = 'D' where id = 2;
1 row updated.
Session1:
SQL> update tbl_ora_60 set name = 'E' where id = 2;
Hang residence
Session2:
SQL> update tbl_ora_60 set name = 'F' where id = 1;
Hang residence
In this case, Session1:
SQL> update tbl_ora_60 set name = 'E' where id = 2;
Update tbl_ora_60 set name = 'E' where id = 2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Note:
Session1 Session2
Obtain the resource lock with id = 1
Obtain the resource lock with id = 2
Wait for the resource lock with id = 2
Wait for the resource lock with id = 1
Id = 2 SQL reports ORA-60, automatic rollback
1. Because the resource lock of id = 2 is obtained first by Session2, Oracle will automatically roll back the SQL statement that requires the resource lock when the deadlock occurs. The update id = 2 Operation of Session1 is rolled back.
2. You can find that the resource (id = 2 in this example) obtained by the SQL statement that actually reports a ORA-60 error is not the resource that triggers the deadlock (id = 1 in this example ), this example uses different rows of the same table, the same rows of different tables, also can explain the previous night dimension when the ORA-60 is displayed between the SQL table is different reasons, because of the mutual lock between a table update executed by the night dimension and a table update executed by the current application, the night dimension SQL reports an ORA-60 or an application reports an ORA-60 error.
In this case, Session1:
SQL> select * from tbl_ora_60;
ID NAME
---------------
1 c
2 B
Note: After an error is reported, the rollback operation automatically executed by Oracle is based on a single SQL statement, not the entire transaction. Therefore, only the records with id = 2 are rollback, the execution of id = 1 is still normal.
Session2:
SQL> update tbl_ora_60 set name = 'F' where id = 1;
Hang residence
Continue, Session1:
SQL> commit;
Commit complete.
Session2:
SQL> update tbl_ora_60 set name = 'F' where id = 1;
1 row updated.
Session1:
SQL> select * from tbl_ora_60;
ID NAME
---------------
1 c
2 B
Only id = 1 is updated successfully.
Session2:
SQL> select * from tbl_ora_60;
ID NAME
---------------
1 f
2 d
Both id = 1 and id = 2 are updated successfully, but not COMMIT.
SQL> commit;
Commit complete.
Session1:
SQL> select * from tbl_ora_60;
ID NAME
---------------
1 f
2 d
Because Session2 executes COMMIT and submits the update, it is shown that it is the same as Session execution.
ORA-01172, ORA-01151 error handling
ORA-00600 [2662] troubleshooting
Troubleshooting for ORA-01078 and LRM-00109
Notes on ORA-00471 Processing Methods
ORA-00314, redolog corruption, or missing Handling Methods
Solution to ORA-00257 archive logs being too large to store
