Deadlock (Practical) – Simulating and Identifying

Today I will show you , how to overcome with Deadlock.

First , we will simulate the deadlock scene between 2 users

And  then solve that situation.

Login as SYSDBA

$ Sqlplus / as sysdba

SQL> create user a1 identified by a1;

User created.

SQL> grant connect , resource to a1;

Grant succeeded.

SQL> create user b1 identified by b1;

User created.

SQL> grant connect , resource to b1;

Grant succeeded.

Terminal 1

SQL> conn a1/a1;

Connected.

SQL> create table student_info (roll_no number (4));

Table created.

SQL> insert into student_info values(1001);

1 row created.

SQL> insert into student_info values(1002);

1 row created.

SQL> insert into student_info values(1003);

1 row created.

SQL> insert into student_info values(1004);

1 row created.

SQL> commit;

Commit complete.

SQL> grant update on a1.student_info to b1;

Grant succeeded.

SQL> update student_info set roll_no=0099;

4 rows updated.

Terminal 2

SQL> update a1.student_info set roll_no=8855;

(it is blocked or hanged as User A1 is updating and not Yet committed..)

Sometimes, you will get error as below

          *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

Terminal 3  (Another Terminal to check the blocker to kill its session)

Login as SYSDBA

SQL> select b.inst_id,b.sid “blocker”,w.inst_id,w.sid “waiter”

           from gv$lock b,gv$lock w

           where b.block>0 and w.request>0 and b.id1=w.id1 and b.id2=w.id2;

   INST_ID            BLOCKER             INST_ID                WAITER

------------            -------------           ------------              ------------

                 1                            1                             1                             33

SQL> select username,sid, serial# from gv$session where sid in (1,33) ;

USERNAME                                                 SID      SERIAL#

------------------------------------------ ------------ ----------

A1                                                                            1               9

B1                                                                           33          151

SQL> alter system kill session '1,9';

System altered.

Note: To know what table (id) is accessed by the users

SQL> desc v$locked_object

SQL> select inst_id,object_id from gv$locked_object

            where session_id in (1,33);

 

 INST_ID  OBJECT_ID

---------- ------------------

1                   13008

SQL> select owner,object_name from dba_objects

            where object_id=13008;

OWNER                               OBJECT_NAME

---------------------------   ------------------------------------

A1                                          EMP_INFO

 See also : General Tips for DBA Interview

Join Us on FACEBOOK HERE