Deadlocks : 2 -- Deadlock on INSERT

Two days ago, I demonstrated a deadlock on UPDATE between two sessions.

Deadlocks are "generally" considered as Application Design issues. However, it is possible that deadlocks are not noticed when performance is fast enough and/or when the user/application "processing/wait" time between acquiring the first set of resources (row lock(s)) and the second set suddenly varies.


In this demo, I show how a deadlock can occur on INSERTs. (Yes, it may not be obvious that INSERTs, too, can cause deadlocks). Here we have a situation with two tables, each table having a primary key. We also have two sessions inserting the same (duplicated) values into the two tables. This is quite likely an Application Design issue -- the two sessions should not have been generating the same key values !

I first create the demo tables :

SQL> create table table_1 (col_1 number not null primary key, col_2 varchar2(5));

Table created.

SQL> insert into table_1 values (1,'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create table table_2 (col_1 number not null primary key, col_2 varchar2(5));

Table created.

SQL> insert into table_2 values (1,'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> 

So we have two tables, each with a Primary Key defined. I have preloaded one row just to demonstrate that the two tables may have already existant rows before the "day of the deadlock".

Now, this is Session 1 :

23:14:22 SQL> select 'My Session : ' || s.sid, s.serial#, p.pid, p.spid
23:14:22   2  from v$session s,  v$process p
23:14:22   3  where s.paddr=p.addr
23:14:22   4  and s.sid = (select distinct sid from v$mystat);

'MYSESSION:'||S.SID                                      SERIAL#        PID SPID
----------------------------------------------------- ---------- ---------- ------------
My Session : 151                                              18         15 4353

23:14:22 SQL>
23:14:22 SQL> pause Press ENTER to proceed .....
Press ENTER to proceed .....

23:14:34 SQL>
23:14:34 SQL>
23:14:34 SQL> -- my first set of row/rows that are locked
23:14:34 SQL> --  here it is an INSERT -- this will cause another session to wait on the Primary Key !
23:14:34 SQL> insert into table_1 values (2,'S1');

1 row created.

23:14:34 SQL> pause Press ENTER to proceed .....
Press ENTER to proceed .....

23:14:38 SQL>
23:14:38 SQL> -- subsequent operations
23:14:38 SQL> select count(*) from my_obj;

 COUNT(*)
----------
    50694

23:14:38 SQL>
23:14:38 SQL> select count(*) from table_1;

 COUNT(*)
----------
        2

23:14:38 SQL> select col_1,  col_2 from table_1 where col_1=2;

    COL_1 COL_2
---------- -----
        2 S1

23:14:38 SQL>
23:14:38 SQL> -- my second set of row/rows that are locked
23:14:38 SQL> insert into table_2  values (2,'S1');
insert into table_2  values (2,'S1')
           *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


23:14:42 SQL>
23:14:42 SQL> rollback;

Rollback complete.

23:14:47 SQL> 

And this is Session 2 :

23:14:31 SQL> select 'My Session : ' || s.sid, s.serial#, p.pid, p.spid
23:14:31   2  from v$session s,  v$process p
23:14:31   3  where s.paddr=p.addr
23:14:31   4  and s.sid = (select distinct sid from v$mystat);

'MYSESSION:'||S.SID                                      SERIAL#        PID SPID
----------------------------------------------------- ---------- ---------- ------------
My Session : 158                                              21         19 4358

23:14:31 SQL>
23:14:31 SQL> pause Press ENTER to proceed .....
Press ENTER to proceed .....

23:14:35 SQL>
23:14:35 SQL>
23:14:35 SQL> -- my first set of row/rows that are locked
23:14:35 SQL> --  here it is an INSERT -- this will cause another session to wait on the Primary Key !
23:14:35 SQL> insert into table_2 values (2,'S2');

1 row created.

23:14:35 SQL> pause Press ENTER to proceed .....
Press ENTER to proceed .....

23:14:39 SQL>
23:14:39 SQL> -- subsequent operations
23:14:39 SQL> select count(*) from my_obj;

 COUNT(*)
----------
    50694

23:14:40 SQL>
23:14:40 SQL> select count(*) from table_1;

 COUNT(*)
----------
        1

23:14:40 SQL> select col_1,  col_2 from table_2 where col_1=2;

    COL_1 COL_2
---------- -----
        2 S2

23:14:40 SQL>
23:14:40 SQL> -- my second set of row/rows that are locked
23:14:40 SQL> insert into table_1 values (2,'S2');

1 row created.

23:14:47 SQL>
23:14:47 SQL> commit;

Commit complete.

23:14:50 SQL> 

Thus, we can see that there is a deadlock between the two sessions, both attempting to insert the same key values into the two tables (in my demo above, the key value is "2", however, the value could have been, say "2" for table_1 and "200" for table_2).

As with the previous demo, Oracle detects the deadlock very quickly -- in 2 seconds. Oracle actually creates a deadlock graph and verifies it when it finds sessions waiting on locks.

Since Session 1 did a ROLLBACK in this demo, the values in the two tables reflect only Session 2's INSERTs. (i.e. Session 1's first INSERT got rolled back).

SQL> select col_1, col_2 from table_1;

    COL_1 COL_2
---------- -----
        1 ABC
        2 S2

SQL> select col_1, col_2 from table_2;

    COL_1 COL_2
---------- -----
        1 ABC
        2 S2

SQL>  

Thus, it is possible to have Deadlocks on INSERTs if the Schema and Application design are not sturdy.

.
.
.