Deadlock – Part 2: Unindexed Foreign keys

I the Part-1 of the deadlock graph interpretation, I have introduced the deadlock graph that have been generated by the use of bitmap indexes in a heavy concurrent OLTP application. Part 2 of this series aims to show two points:

1. How a session can deadlock itself
2. How to know from the deadlock graph that this deadlock is due to un-indexed Foreign Keys

Let’s simulate this kind of deadlock via a simple demo:

SQL> create table p as select * from all_users;

Table créée.

SQL> select count(1) from p;

COUNT(1)
----------
31

SQL> alter table p add constraint p_pk primary key (user_id);

Table modifiée.

SQL> create table c (user_id references p, data varchar2(10));

Table créée.

SQL> select min(user_id), max(user_id) from p;

MIN(USER_ID) MAX(USER_ID)
------------ ------------
0             2147483638

SQL> insert into c(user_id,data) values (0,'test');

1 ligne créée.

SQL> declare
2  pragma autonomous_transaction;
3  begin
4  delete from p where user_id = 72;
5  commit;
6  end;
7  /

declare
*
ERREUR à la ligne 1 :
ORA-00060: détection d'interblocage pendant l'attente d'une ressource
ORA-06512: à ligne 4

SQL> rollback;

Annulation (rollback) effectuée.

So, here we are; I have simulated the ORA-00060 deadlock error when I was deleting from a parent table having a child table with un-indexed Foreign Key. Below is the corresponding deadlock graph generated internally by Oracle:

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
 ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000121c1-00000000        24      18    SX             24      18           S

session 18: DID 0001-0018-00000052    session 18: DID 0001-0018-00000052

Rows waited on:
 Session 18: obj - rowid = 000121C1 - AAAAAAAAAAAAAAAAAA
 (dictionary objn - 74177, file - 0, block - 0, slot - 0)

----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=6y61nc5zbc9w5) -----
DELETE FROM P WHERE USER_ID = 72
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
23C9D90C         4  anonymous block
===================================================

Two remarks from the above deadlock graph should be emphasized:

• (a) we have the same session 18 that is deadlocking itself and
• (b) the deadlock is due to a TM-enqueue :

 ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000121c1-00000000        24      18    SX             24      18           S

And two conclusions can also be done from the above deadlock graph

1. It is possible that a single session can deadlock itself when using autonomous transaction. This is why it is worth to mention that you have to use the autonomous transaction carrefully
2. a TM-Enqueue in the deadlock graph is generally a clear indication that the deadlock is due to a DML on parent table with child table having a non-indexed Foreign Key

Partition DDL and global indexes

This is just a simple reminder for me and for those who have experienced ORA-01502 error after a DDL on a partitioned table

ORA-01502: index 'XXX.ZZZ_TII_PK' or partition of such index is in unusable state

Let’s explain this situation through a simple demo:

create table TAB1 ( Value number(10))
partition by range(value)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50)
);

insert into tab1
select rownum
from dual connect by rownum < 50;

commit;
sql>create index tab1_indx1 on tab1(value);

sql> select index_name, status
2  from user_indexes
3  where index_name = 'TAB1_INDX1';

INDEX_NAME                     STATUS
------------------------------ --------
TAB1_INDX1                     VALID

sql> alter table tab1 drop partition p2 UPDATE GLOBAL INDEXES;

Table altered.

sql> select index_name, status
2  from user_indexes
3  where index_name = 'TAB1_INDX1';

INDEX_NAME                     STATUS
------------------------------ --------
TAB1_INDX1                     VALID

sql> alter table tab1 drop partition p3;

Table altered.

sql> select index_name, status
2  from user_indexes
3  where index_name = 'TAB1_INDX1';

INDEX_NAME                     STATUS
------------------------------ --------
TAB1_INDX1                     UNUSABLE

Footnote: always think about the status of the global indexes when doing DDL on a partitioned table. If you want to keep those indexes in a valid status then you need to add the UPDATE GLOBAL INDEXES clause to your partition DDL

Deadlock – Part 1: bitmap Index

How many times I heard and read that it is absolutely not conceivable to create bitmap indexes into a heavy OnLine Transactional Process (OLTP) application into tables that are subject to concurrent DML operations. The main reason for that is because of the deadlock threat the bitmap indexes can throw into this kind of applications. The purpose of this article, in its first part, is mainly to show how to identify, via the deadlock graph that the deadlock you are experiencing is due to a bitmap index in an OLTP system.

Here below is a deadlock graph automatically generated from a real life OLTP production application I have been asked to have a close look to:

[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0017000b-0000a507        26     164     X             50     160           S
TX-0037000e-000081cc        50     160     X             26     164           S

This is the most fundamental and the first information that should retain your attention: we have two sessions 164 and 160 dead-locking each other. They are both Transaction Enqueues (TX) held on X mode and waited on S mode

Immediately when you see TX held on X mode and waited on S mode you can think of one of the following possibilities which triggered this deadlock

1.       Existence of Bitmap indexes into your OLTP application

2.       Primary key or unique key constraint overlapping during insert statement

The next bit of information within the deadlock graph will help you identifying the real cause as I did in this application. You need to look to the Sql statements done by the two sessions as shown below:

Information on the OTHER waiting sessions:
Session 160:
pid=50 serial=9778 audsid=49307085 user: 54/S102
O/S info: user: SYS_XXX-ZZZ, term: wyannhhF103, ospid: 6592:3660, machine: lkti\HJKULOO03
program: xye.exe
application name: xye.exe, hash value=2799981571

Current SQL Statement:
DELETE xxx_real_life_table WHERE xxx_ID = :B1
End of information on OTHER waiting sessions.

Current SQL statement for this session:
INSERT INTO yyy_real_life_table (yyy_ID ,DET_ID,xxx_ID) VALUES (yyy__SEQ.NEXTVAL ,0 ,:B23 )

----- PL/SQL Call Stack -----

As far as session 160 was doing a delete operation while session 164 was doing an insert operation it is impossible that for this deadlock to be caused by primary key or unique key overlapping values. It remains for me only to check the existence of the bitmap indexes into this application.

sql.world> SELECT count(1)
2    FROM all_indexes
3   WHERE index_type = 'BITMAP';

COUNT(1)
----------
10

I then have dropped those bitmap indexes making the deadlock disappear. I have also asked the local developer to explain me why they’ve created bitmap indexes into tables subject to heavy DML operation; their answer was that all those indexes are on a flag processed which have only two values : Y and N and they told me this is why they opted for this kind of bitmap indexes. My answer for them was this:

Myth: Bitmap Indexes With High Distinct Columns (Blow Out)

On when the alphabetic order of index name matters for the Oracle Optimizer

Do you know that in the presence of, what I now take the habit to name redundant indexes, the Oracle optimizer will use the alphabetic chronological order applied to the names of those indexes in order to decide which of them it will choose to execute the query?

If no, then follow this simple demo:

sql.world> drop table table_a;

Table dropped.

sql.world> CREATE TABLE table_a (col1 NUMBER(10)
 2                          ,col2 NUMBER(10)
 3                          ,col3 NUMBER(10)
 4                          ,col4 NUMBER(10));

Table created.

sql.world> INSERT INTO table_a
 2     SELECT rownum
 3           ,lpad (mod (rownum, 5), 5, '0')
 4           ,lpad (mod (rownum, 5), 5, '0')+11
 5           ,lpad (mod (rownum, 5), 5, '0')+12
 6       FROM all_objects
 7      WHERE ROWNUM < 5000;

4999 rows created.

sql.world> CREATE INDEX a_i1 ON table_a (col1, col2);

Index created.

sql.world> BEGIN
 2     dbms_stats.gather_table_stats
 3                (ownname           => USER,
 4                 tabname           => 'table_a',
 5                 partname          => NULL,
 6                 estimate_percent => 100,
 7                 block_sample     => TRUE,
 8                 method_opt       => 'for all columns size 1'
 9                );
 10  END;
 11  /

PL/SQL procedure successfully completed.

sql.world> SELECT *
 2        FROM table_a
 3       WHERE col1 = 627;

 COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
 627          2         13         14

sql.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  0vt3kwzj1r6ku, child number 0
-------------------------------------
SELECT *       FROM table_a      WHERE col1 = 627

Plan hash value: 3359021421

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_A |     1 |    12 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | A_I1    |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("COL1"=627)

So, I have created a table, table_a, and an index a_i1 to cover my query. The explain plan shows that the index is used. Let’s now create an extra index b_i1 as follows:

sql.world> create index b_i1 on table_a (col1,col3);

Index created.

sql.world>> SELECT *
 2        FROM table_a
 3       WHERE col1 = 627;

 COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
 627          2         13         14

sql.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0vt3kwzj1r6ku, child number 0
-------------------------------------
SELECT *       FROM table_a      WHERE col1 = 627

Plan hash value: 3359021421

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_A |     1 |    12 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | A_I1    |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("COL1"=627)

We see that the index a_i1 is still used by our query. But, let’s now rename the index a_i1 so that its new name will start with a letter greater than b in the alphabetic order

sql.world> alter index a_i1 rename to c_i1;

Index altered.

sql.world>> SELECT *
 2        FROM table_a
 3       WHERE col1 = 627;

 COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
 627          2         13         14

sql.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0vt3kwzj1r6ku, child number 0
-------------------------------------
SELECT *       FROM table_a      WHERE col1 = 627

Plan hash value: 257447779

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_A |     1 |    12 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | B_I1    |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("COL1"=627)

19 rows selected.

Woo!! Our query now decided to choose the b_i1 index? why? because a_i1 and b_i1 indexes are considered by the Oracle Optimizer to be both valid to cover the query; and in this case, the Oracle optimizer will choose the index using the alphabetic order.

If I, again, rename the index b_i1 to be z_i1 for example, then the Oracle optimizer will be back to the oiginal  use of  c_i1 index (initially named a_i1)  as confirmed by the example shown here below:

sql.world> alter index b_i1 rename to z_i1;

Index altered.

sql.world>> SELECT *
 2        FROM table_a
 3       WHERE col1 = 627;

 COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
 627          2         13         14

sql.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0vt3kwzj1r6ku, child number 0
-------------------------------------
SELECT *       FROM table_a      WHERE col1 = 627

Plan hash value: 1414606619

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_A |     1 |    12 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | C_I1    |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("COL1"=627)

19 rows selected.