Oracle DBA FAQ - Understanding SQL Transaction Management

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

How Oracle Handles Dead Locks?

Oracle server automatically detects dead locks. When a dead lock is detected, Oracle server will select a victim transaction, and fail its statement that is blocked in the dead lock to break the dead lock. The tutorial exercise below shows you an example of statements failed by Oracle server because of dead locks:

(session 1)
SQL> connect HR/fyicenter

SQL> UPDATE fyi_links 
  SET notes='Session 1' 
  WHERE id=101;
1 row updated.
                                   (session 2)
                                   SQL> connect HR/fyicenter

                                   SQL> UPDATE fyi_links 
                                     SET notes='Session 2' 
                                     WHERE id=110;
                                   1 row updated.
                                     
                                   SQL> UPDATE fyi_links 
                                     SET notes='Session 2' 
                                     WHERE id=101;
                                   (blocked by lock id=101)

SQL> UPDATE fyi_links 
  SET notes='Session 1' 
  WHERE id=110;
(blocked by lock on row id=110)

                                   ORA-00060: deadlock 
                                   detected while waiting 
                                   for resource
                                   (statement failed)

Part: 1 2 3 4 5 6 7

More...