关于oracle:外键与死锁

38次阅读

共计 10391 个字符，预计需要花费 26 分钟才能阅读完成。

要剖析外键无索引可能会导致死锁问题，须要先理解 Oracle 的几种表锁，参考官网文档：

  • 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.

几种表锁模式翻译如下：

  • 行共享（RS）

    此锁也称为子共享表锁（SS），示意在表上持有锁的事务已锁定表中的行，并打算更新它们。行共享锁是表锁限度起码的模式，为表提供最高水平的并发性。

  • 行独占表锁（RX）

    此锁也称为子排他性表锁（SX），通常示意持有该锁的事务已更新表行或收回 SELECT…FOR UPDATE。SX 锁容许其余事务在同一表中同时查问、插入、更新、删除或锁定行。因而，SX 锁容许多个事务同时获取同一表的 SX 和子共享表锁（SS）。

  • 共享表锁（S）

    事务持有的共享表锁容许其余事务查问表（不应用 SELECT …FOR UPDATE），但仅在单个事务持有共享表锁时才容许更新。因为多个事务可能同时持有共享表锁，因而持有此锁不足以确保事务能够批改表。

  • 共享行独占表锁（SRX）

    此锁也称为共享子排他表锁（SSX），比共享表锁更具限制性。一次只有一个事务能够在给定的表上获取 SSX 锁。事务持有的 SSX 锁容许其余事务查问表（除了 SELECT…FOR UPDATE），但无奈更新表。

  • 独占表锁（X）

    此锁是限制性最强的，禁止其余事务执行任何类型的 DML 语句或将任何类型的锁放在表上。

对于锁和外键，参考官网文档：

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.

就是说如果在子表的外键下面没有创立索引，对父表主键的删除或者批改操作将会锁住整个子表。

外键无索引

咱们晓得外键无索引有可能会导致死锁，上面来看看具体是怎么产生的。

先创立测试表：

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.

在会话 1 执行基于外键的删除子表记录的操作：

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

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

SQL> delete from emp where department_id=10;

1 row deleted.

在会话 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

在会话 2 执行基于外键的删除子表记录的操作：

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

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

SQL> delete from emp where department_id=20;

2 rows deleted.

在会话 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

在会话 1 执行基于主键的删除父表记录的操作：

SQL> delete from dept where department_id=10;

此时该操作被阻塞，在会话 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

能够看到会话 1（SID=197）申请 EMP 表上的 Share Row Exlusive，被会话 2（SID=131）上的 Row Exlusive 阻塞。

在会话 2 执行基于主键的删除父表记录的操作：

SQL> delete from dept where department_id=20;

此时会话 1 就会检测到死锁：

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

再在会话 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

外键有索引

给外键加上索引：

SQL> create index idx_emp_deptid on emp(department_id);

Index created.

在会话 1 执行基于外键的删除子表记录的操作：

SQL> delete from emp where department_id=10;

1 row deleted.

在会话 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

在会话 2 执行基于外键的删除子表记录的操作：

SQL> delete from emp where department_id=20;

2 rows deleted.

在会话 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

在会话 1 执行基于主键的删除父表记录的操作：

SQL> delete from dept where department_id=10;

1 row deleted.

此时该操作没有被阻塞，在会话 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

这里能够看到会话 1（SID=197）没有申请 EMP 表上的 Share Row Exlusive。

在会话 2 执行基于主键的删除父表记录的操作：

SQL> delete from dept where department_id=20;

1 row deleted.

没有被阻塞。

欢送关注我的公众号，一起学习。

正文完
oracle
发表至： oracle
2021-02-07
 0
PLSQL-练习题6添加汇总行的两种方法
关于oracle:Oracle基础之二oracle视图用户管理序列DML事务索引数据库设计三范式
关于oracle:Oracle等待事件之buffer-busy-waits
关于oracle:从哈夫曼编码再出发原理和现实