//
you're reading...
Administration, Database, Oracle

On Database Deadlock, Foreign Key, and Indexing

This is in 11g database.

A database deadlock is a situation in which two user sessions are waiting for data locked by each other. Oracle database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, release one set of the conflicting row locks. The statement rolled back belongs to the transaction that detects the deadlock. When application explicitly override Oracle default locking, acquiring table lock for example, it may cause deadlock situation.

However when SQL statements of two sessions involve parent/child tables, a deadlock may occur with Oracle default locking mechanism if the foreign key column of the child table is not indexed. This is because database places a full table lock on the child table when there is a modification of the primary key value in the parent table. This full table lock prevents other sessions to do DMLs on the child table. On the other hand if there are already row exclusive locks on some rows of the child table due to DML activities, the modification of the primary key value in the parent table has to wait. The full table lock is released once the modification of the primary key is finished (user gets the prompt back, for example).

If the foreign key is indexed, Oracle does not acquire full table lock on the child table when the parent table is going through some modification of its primary key. Instead it places a index leaf block lock on the index.

For this reason Oracle recommends you always index foreign key column in the child table unless the primary key column never gets updated or deleted (INSERT into parent table does not get full table lock in the child table).

Things become more interesting when the primary key is composite. In other word, there are two or more columns in the primary key. So the foreign key is on more than one column too, of course. Should you create one composite index on all foreign key columns or multiple indexes on each column to prevent the full table lock? When we create index(es) on foreign key columns, besides preventing the full table lock on the child table we also hope it/they can be used by the optimizer for queries. Without knowing the application code, individual indexes on each foreign key column seem more likely being used than a composite index for queries. So when I was asked by a developer friend I told him he should create individual indexes on each of two-column foreign key columns. However this is a wrong answer because to prevent the full table lock caused by modification of the primary key columns you need to create a composite index on the foreign key columns. Following example demonstrates how a deadlock occurs on parent/child table situation, how to prevent it by simply creating a composite index on the foreign key columns and individual indexes do not work in this case.

First I create two tables: T1 (parent) and T2 (child).

SQL> create table t1 (
t1c1 varchar2(5),   — primary key column
t1c2 number,         — primary key column
t1c3 varchar2(5));

SQL> create table t2 (
t2c1 number,        — primary key
t2c2 varchar2(5),  — foreign key column referencing T1
t2c3 number,        — foreign key column referencing T1
t2c4 varchar2(5));

Now I add constraints to both tables, composite primary key on T1, single column primary key on T2, and  composite foreign key on T2.

SQL> alter table t1 add constraint PK_T1 primary key (t1c1,t1c2);

SQL> alter table t2 add constraint PK_T2 primary key (t2c1);
SQL> alter table t2 add constraint FK_T2_c2c3  foreign key (t2c2,t2c3) references T1;

Then insert some data into both tables.

SQL> insert into t1 values (‘A’,1,’a’);
SQL> insert into t1 values (‘A’,2,’a’);
SQL> insert into t1 values (‘B’,1,’b’);
SQL> insert into t1 values (‘B’,2,’b’);

SQL> insert into t2 values (100,’A’,1,’aa’);
SQL> insert into t2 values (200,’A’,1,’ab’);
SQL> insert into t2 values (300,’A’,2,’ac’);
SQL> insert into t2 values (400,’B’,1,’bb’);

SQL> commit;

Looking at data:

SQL> select * from t1;

T1C1             T1C2  T1C3
————— ———- —————
A                        1      a
A                        2      a
B                        1      b
B                        2      b

SQL> select * from t2;

T2C1     T2C2           T2C3   T2C4
———- ————— ———- —————
100       A                        1      aa
200       A                        1      ab
300       A                        2      ac
400       B                        1      bb

I need to open two sessions in order to generate a database deadlock (a third session is for monitoring lockings). Transactions in each session should follow below time sequence.

At time t0 and in session #1, I update table T1 on row with t1c1=’A’ and t1c2=1 (primary key) to set column t1c3=’c’. The update is successful and prompt comes back immediately. Note here I am updating a non-primary-key column in a parent table. Since I am not changing the primary key values, Oracle does not check the child table (T2) for integrity constrain voilations. No database lock will be applied on the child table. But one row exclusive lock is placed on the parent table T1 to prevent other sessions make change to the same row. This lock will be released when I commit or rollback in session #1.

Time t0, Session #1:
SQL> update t1 set t1c3=’c’ where t1c1=’A’ and t1c2=1;
1 row updated.

Checking locks from session #3:
SQL> select o.object_name, l.session_id,decode(l.locked_mode,2,’Row Share’, 3,’Row Exclusive’,4,’Share’,5,’S/Row X’,6,’Exclusive’,’Not Known’) “Lock Mode”
2   from dba_objects o, v$locked_object l
3  where o.object_id=l.object_id;

OBJECT_NAME           SID    Lock Mode
—————————— —–    —————
T1                                  282    Row Exclusive

Next at time t1 and in session #2, I update table T2 on row with t2c1=300 (primary key) to set column t2c4=’ab’. Again I am updating a non-primary-key, non-foreign-key column. Oracle again places a row exclusive lock on table T2. The update is successful and prompt comes back immediately.

Time t1, Session #2:
SQL> update t2 set t2c4=’ab’ where t2c1=300;
1 row updated.

Checking locks from session #3:
OBJECT_NAME           SID    Lock Mode
—————————— —–    —————
T1                                  282    Row Exclusive
T2                                  443    Row Exclusive

So far we have got two row exclusive locks on two tables from two sessions, respectively. Now at time t2 in session #1 I try delete a row from table T1 (parent). As stated above, I have not create any index on the foreign key columns of table T2 (child), Oracle will place a full table lock on T2 for the deletion of a row in table T1 (parent). However because session #2 already has a row exclusive lock on table T2, the DELETE statement from session #1 has to wait (on session #2). So session #1 hangs (waiting). Prompt does not come back. Note my DELETE here deletes a row with primary key values that don’t exist in the child table: t1c1=’B’ and t1c2=2. Still Oracle needs to place a table lock on T2.

Time t2, Session #1:
SQL> delete from t1 where t1c1=’B’ and t1c2=2;
session hangs…

Checking locks from session #3:
OBJECT_NAME          SID   Lock Mode
—————————— —–   —————
T2                                443    Row Exclusive
T2                                282    Not Known
T1                                282    Row Exclusive

Now session #1 has a row exclusive lock on T1 and a full table lock on T2, but is waiting on session #2 to release its row exclusive lock on T2 before it can finish the DELETE on T1.

At time t3 and in session #2, I try to update T1 with t1c1=’A’ and t1c2=1 (primary key) to set column t1c3=’d’. Because this row is locked by session #1 earlier session #2 has to wait on session #1. But session #1 is already waiting on session #2, So I get a situation of database deadlock! As soon as I issue following update statement in session #2, I get deadlock message in session #1.

Time t3, Session #2:
SQL> update t1 set t1c3=’c’ where t1c1=’A’ and t1c2=1;
session hangs…

At time t4 (right after t3) and in session #1, an error message appeared:

Time t4, Session #1:
SQL> delete from t1 where t1c1=’B’ and t1c2=2;
delete from t1 where t1c1=’B’ and t1c2=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

This example clearly shows without index on foreign key column(s) a deadlock can happen when Oracle places a full table lock on the child table with primary key value changes/deletions in the parent table. Now I create a composite index on the foreign key columns of table T2.

SQL> create index ix_t2_1 on t2(t2c2,t2c3);
SQL> exec dbms_stats.gather_index_stats(‘SCOTT’,’IX_T2_1′);

Repeating above steps from time t0 to time t4,  no deadlock happens. Session #1 gets prompt back immediately for its DELETE statement at time t2. Session #2 still waits at time t3, because the row was locked by session #1 earlier.

Coming back to the question at the beginning, will two separate indexes on the foreign key columns do the same thing as the composite index, preventing deadlock? The answer is NO. I am not listing the details here. But it can easily be proved using the above steps after dropping the composite index and creating two separate indexes as the SQL statements show below.

SQL> drop index ix_t2_1;
SQL> create index ix_t2_2 on t2(t2c2);
SQL> exec dbms_stats.gather_index_stats(‘OPS$ORACLE’,’IX_T2_2′);

SQL> create index ix_t2_3 on t2(t2c3);
SQL> exec dbms_stats.gather_index_stats(‘OPS$ORACLE’,’IX_T2_3′);

The deadlock comes back again.

Conclusion: to avoid full table lock on child table always create a composite index on the foreign key columns of the child table. This will help reducing the frequency of database deadlock occurrence.

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: