测试的时候数据库外键导致死锁_外键与死锁

最新推荐文章于 2022-06-16 13:33:47 发布

季仪娅

最新推荐文章于 2022-06-16 13:33:47 发布

阅读量232

点赞数

文章标签：测试的时候数据库外键导致死锁

本文链接：https://blog.csdn.net/weixin_31829293/article/details/114020817

版权

要分析外键无索引可能会导致死锁问题，需要先了解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.

没有被阻塞。

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

季仪娅

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
测试的时候数据库外键导致死锁_外键与死锁

要分析外键无索引可能会导致死锁问题，需要先了解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 cla...
复制链接

扫一扫