Deadlock Solution for Oracle Data Table

Time:2019-8-21

A Simple Method of Investigation and Solution

Deadlock Error Reporting: ORA-00060: deadlock detection while waiting for resources

Corresponding Chinese error is: ORA-00060: deadlock detected while waiting for resources

Execute the following SQL to view the locked table:

select object_name, machine, s.sid, s.serial#
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id = o.object_id
   and l.session_id = s.sid

Executing the following SQL can force unlocking

alter system kill session '277,1817'
Among them, 277 corresponds to the SID field found by the previous sentence SQL, and 1817 corresponds to the specific value of the serial field.

Specific operations can be seen in the following examples, more intuitive understanding of deadlocks, and to solve deadlocks

More in-depth understanding through an example operation

Reference to a common ORA-00060 deadlock phenomenon

If deadlock occurs in Oracle database, the database will report the error code of ORA-00060. This deadlock phenomenon is usually caused by errors in application logic design, and has nothing to do with the design of the database itself. Now we simulate a deadlock phenomenon through experiments:

Simulated deadlock phenomenon

Be sure to actually do it yourself, it will take an hour, but you can have a very intuitive understanding of deadlocks!

Create a data table for testing and add several test data:

create table practice(uno varchar(8), uname varchar(20));

insert into practice values ('198', 'xm198-1');
insert into practice values ('198', 'xm198-2');
insert into practice values ('200', 'xm200-1');
insert into practice values ('200', 'xm200-2');
commit;

Open a PLSQLOpen two Command Windows in PLSQL to perform the following update order (the following session is Command Window)

Session 1: Perform field updates for UNO 198, and note that commit is not executed;

SQL> update practice set uname = 'cj' where uno = '198';
2 rows updated

Session 2: Perform field updates for UNO 2000, and be careful not to commit.

SQL> update practice set uname = 'hh' where uno = '200';
2 rows updated

Session 1: Perform the field update for uno 2000 again, pay attention not to commit; at this time, the statement has been hang (that is, jammed, unlike the previous two executions will output the result information such as 2 rows update), you need to wait until Session 2 issues commit or rollback action.

SQL > Update practice set uname ='cj'where uno ='200'; - - Session 1 hangs here

Session 2: Once the following update is executed, Session 2 will hang, and when you return to Session 1, you will find Session 1 error.

SQL> update practice set uname = 'sdf' where uno = '198';

Back to Session 1, you can see Session 1 error message

SQL> update practice set uname = 'cj' where uno = '200';
update practice set uname = 'cj' where uno = '200'
ORA-00060: Deadlock detected while waiting for resources

Query alert log to find an error: ORA-00060: Deadlock detection. More info in file/u01/app/oracle/admin/prod/udump/prod_ora_4273.trc.

Explain this kind of deadlock in detail. Ask a person how deadlock is generated. If someone knows something about computer, he will say that circular waiting. How can circular waiting occur in this example?

  • Session 1 updates uno = 198’s record first, but without commit or rollback, session 1 “occupies” uno = 198’s record all the time.

  • Similarly, session 2 updates uno=’200’records first, but there is no commit or rollback, so session 2 always “occupies” uno=’200′ records.

  • Session 1 then tries to update the UNO = 200’records, but these records have been occupied by Session 2, so the situation that Session 1 hangs is that Session 1 is waiting for Session 2 to “release” the UNO = 200’records it “occupies”.

  • Session 2 then tries to update the UNO = 198’record, which produces a circular wait with Session 1.

  • So there’s a deadlock.

  • When database uses SQL to modify data, it needs to be locked, generally at the row level, so the so-called occupancy above is actually to lock qualified rows, because there is no commit or rollback, so it has been locked all the time.

  • This requires further study.Database LockThe Execution Principle of SQLPrinciple of databaseIn order to learn database knowledge more deeply, we should not just stay at the present superficial level.

  • In addition, as mentioned above, if there is a deadlock problem in the running process of the program developed by ourselves:

    • This deadlock phenomenon is usually caused by errors in application logic design and is not related to the design of the database itself.

    • So you need to check that your program is not designed to deal with database operations.

    • If it’s a simple program, it’s probably easy to find.

    • But if it’s a big project, and there are many development teams involved in the operation of the database, it may be difficult to find it.

    • This requires a bigger and more macro perspective, to have a grasp of the whole large project architecture, and to have a deep understanding and mastery of the knowledge of the database principle level.

Supplementary Notes:

If the update SQL above is not executed for the second time in Session 2, but commit or rollback is executed; then Session 1 will not report an error.

For example, session 2 execution

SQL> rollback;
Rollback complete

Go back to session 1 and find out where hang used to live. Now it’s settled.

SQL > Update practice set uname ='cj'where uno ='200'; -- previously hang could not be updated here, now because session 2 performs rollback, session 1 recovers from Hang residence
2 rows updated

Forced unlocking

Note that there is a need to use system users here, because ordinary users do not have permission, I amOpen a PLSQL againUser login with SYSTEM to perform the following operations, and then open a Command Window in the new PLSQL. The reason why we re-open a PLSQL is to ensure that the original PLSQL users can log in using the SYSTEM users without affecting them. Of course, the new PLSQL is only a superficial phenomenon, there is no need to explore anything in depth!

In the new session of PLSQL, the ID of the hang session can be queried through the HOLDING_SESSION field in the dba_blockers table:

SQL> select * from dba_blockers;
HOLDING_SESSION
---------------
             76

Use the V $session view to get Sid and serial of hang-in session#

SQL> select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        76        271 TRADE

After finding the session where hang lives, execute the alter system command to kill the corresponding session.

SQL> alter system kill session '76,271' immediate;
System altered

Check the previous session to confirm it.

After executing the above forced unlock, return to the previous PLSQL and check the two sessions. The session in Session 1 will be automatically killed.

When querying SQL in Session 1, an error will be found:

SQL> select * from practice;
Warning: connection was lost and re-established
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

Executing queries in Session 2 finds that changes in Session 2 take effect. In Session 2, the following query SQL is executed without error. Actually, the current query is

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

But then in the current PLSQL, open a Command Window (called Session 3), execute the query statement, and find that the result is inconsistent with Session 2.

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      xm198-1
198      xm198-2
200      xm200-1
200      xm200-2

Returning to Session 2, we found that we had not submitted yet, so after submitting, we executed the query to see the results.

SQL> commit;
Commit complete

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

Then go back to Session 3, execute the query, and find that it’s now the same as Session 2.

SQL> select * from practice;
UNO      UNAME
-------- --------------------
198      sdf
198      sdf
200      hh
200      hh

In fact, when deadlocks occur, Oracle will unlock them after a period of time. This situation records the following information in the alert log: ORA-00060: Deadlock detection. More info in file/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.linux