Foreign key and deadlock

Time:2021-7-8

To analyze the deadlock problem that foreign key without index may cause, you need to understand several table locks of Oracle, refer toOfficial documents

  • A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

    A table lock can be held in any of the following modes:

    • Row Share (RS)

      This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

    • Row Exclusive Table Lock (RX)

      This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.

    • Share Table Lock (S)

      A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

    • Share Row Exclusive Table Lock (SRX)

      This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.

    • Exclusive Table Lock (X)

      This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

Several table lock modes are translated as follows:

  • Row sharing (RS)

    This lock, also known as a child shared table lock (SS), indicates that the transaction holding the lock on the table has locked the rows in the table and intends to update them. Row shared lock is the least restricted mode of table lock, which provides the highest degree of concurrency for tables.

  • Row exclusive table lock (Rx)

    This lock, also known as a sub exclusive table lock (SX), usually indicates that the transaction holding the lock has updated the table row or issued a select… For update. SX lock allows other transactions to query, insert, update, delete or lock rows in the same table at the same time. Therefore, SX lock allows multiple transactions to acquire SX and SS of the same table at the same time.

  • Shared table lock

    A shared table lock held by a transaction allows other transactions to query the table (without using select… For update), but only allows updates when a single transaction holds a shared table lock. Since multiple transactions may hold a shared table lock at the same time, holding this lock is not sufficient to ensure that the transaction can modify the table.

  • Shared row exclusive table lock (SRX)

    This lock, also known as shared sub exclusive table lock (SSX), is more restrictive than shared table lock. Only one transaction at a time can acquire an SSX lock on a given table. SSX locks held by transactions allow other transactions to query tables (except select… For update), but cannot update tables.

  • Exclusive table lock

    This lock is the most restrictive, which prevents other transactions from executing any type of DML statement or placing any type of lock on the table.

For locks and foreign keys, refer to the official document:

Locks and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys.

Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

Locks and Unindexed Foreign Keys

The database acquires a full table lock on the child table when no index exists on the foreign key column of the child table, and a session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

When both of the following conditions are true, the database acquires a full table lock on the child table:

  • No index exists on the foreign key column of the child table.
  • A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

That is, if no index is created on the foreign key of the child table, the deletion or modification of the primary key of the parent table will lock the whole child table.

Foreign key has no index

We know that foreign key without index may cause deadlock. Let’s see how it is generated.

First, create a test table

SQL> create table emp as select * from employees;

Table created.

SQL> create table dept as select * from departments;

Table created.

SQL> alter table dept modify department_id primary key;

Table altered.

SQL> alter table emp add constraint fx_emp_deptid foreign key(department_id) references dept(department_id);

Table altered.

In session 1, delete the sub table record based on the foreign key

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
           197

SQL> delete from emp where department_id=10;

1 row deleted.

Check the lock status in session 3:

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;

       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       None                        0                  NO HOLDER

In session 2, delete the sub table record based on the foreign key

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
           131

SQL> delete from emp where department_id=20;

2 rows deleted.

Check the lock status in session 3:

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;
 
       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       131 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       131 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       131 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER

In session 1, delete the parent table record based on the primary key

SQL> delete from dept where department_id=10;

At this time, the operation is blocked. Check the lock condition in session 3

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;
 
       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       131 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       131 TM EMP                  Row Exlusive       None                        1                  NO HOLDER
       131 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       Share Row Exlusive          1              131 VALID
       197 TX                      Exclusive          None                        0              131 VALID
       197 TM DEPT                 Row Exlusive       None                        0              131 VALID

You can see that session 1 (sid = 197) requests share row exclusive on the EMP table, which is blocked by the row exclusive on session 2 (sid = 131).

In session 2, delete the parent table record based on the primary key

SQL> delete from dept where department_id=20;

Session 1 detects a deadlock:

SQL> delete from dept where department_id=10;
delete from dept where department_id=10
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Check the lock in session 3

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;
 
       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       131 TM DEPT                 Row Exlusive       None                        0              197 VALID
       131 TM EMP                  Row Exlusive       Share Row Exlusive          1              197 VALID
       131 TX                      Exclusive          None                        0              197 VALID
       197 TM EMP                  Row Exlusive       None                        1                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER 

Foreign key has index

Index foreign keys:

SQL> create index idx_emp_deptid on emp(department_id);

Index created.

In session 1, delete the sub table record based on the foreign key

SQL> delete from emp where department_id=10;

1 row deleted.

Check the lock status in session 3:

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID; 

       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       None                        0                  NO HOLDER 

In session 2, delete the sub table record based on the foreign key

SQL> delete from emp where department_id=20;

2 rows deleted.

Check the lock status in session 3:

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;
 
       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       131 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       131 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       131 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER 

In session 1, delete the parent table record based on the primary key

SQL> delete from dept where department_id=10;

1 row deleted.

At this time, the operation is not blocked. Check the lock in session 3

SELECT S.SID         SID,
       L.TYPE        TYPE,
       O.OBJECT_NAME OBJECT_NAME,
       DECODE(L.LMODE, 0, 'None', 
                       1, 'Null', 
                       2, 'Row Share', 
                       3, 'Row Exlusive', 
                       4, 'Share', 
                       5, 'Share Row Exlusive', 
                       6, 'Exclusive')   lmode, 
       DECODE(L.REQUEST, 0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Exlusive', 
                         4, 'Share', 
                         5, 'Share Row Exlusive', 
                         6, 'Exclusive') request, 
       L.BLOCK       BLOCK,
       S.BLOCKING_SESSION,
       S.BLOCKING_SESSION_STATUS
  FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND USERNAME != 'SYSTEM'
   AND O.OBJECT_ID(+) = L.ID1
   AND S.SID IN (197, 131)
   AND L.TYPE NOT IN ('AE')
 ORDER BY S.SID;
 
       SID TY OBJECT_NAME          LMODE              REQUEST                 BLOCK BLOCKING_SESSION BLOCKING_SE
---------- -- -------------------- ------------------ ------------------ ---------- ---------------- -----------
       131 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER
       131 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       131 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM EMP                  Row Exlusive       None                        0                  NO HOLDER
       197 TX                      Exclusive          None                        0                  NO HOLDER
       197 TM DEPT                 Row Exlusive       None                        0                  NO HOLDER 

You can see that session 1 (sid = 197) did not request share row exclusive on the EMP table.

In session 2, delete the parent table record based on the primary key

SQL> delete from dept where department_id=20;

1 row deleted.

It’s not blocked.

Welcome to my official account and study together.

Foreign key and deadlock