SQL INSERT 操作会导致死锁吗？

Can an INSERT operation result in a deadlock?

提问by Gili

Assuming:

假设：

• I am using REPEATABLE_READ or SERIALIZABLE transaction isolation (locks get retained every time I access a row)
• We are talking about multiple threads accessing multiple tables simultaneously.

• 我正在使用 REPEATABLE_READ 或 SERIALIZABLE 事务隔离（每次访问一行时都会保留锁）
• 我们正在谈论多个线程同时访问多个表。

I have the following questions:

我有以下问题：

1. Is it possible for an INSERToperation to cause a deadlock?If so, please provide a detailed scenario demonstrating how a deadlock may occur (e.g. Thread 1 does this, Thread 2 does that, ..., deadlock).
2. For bonus points: answer the same question for all other operations (e.g. SELECT, UPDATE, DELETE).

1. 操作是否有可能INSERT导致死锁？如果是这样，请提供一个详细的场景来演示死锁是如何发生的（例如，线程 1 这样做，线程 2 这样做，...，死锁）。
2. 对于加分：对所有其他操作（例如 SELECT、UPDATE、DELETE）回答相同的问题。

UPDATE: 3. For super bonus points: how can I avoid a deadlock in the following scenario?

更新： 3. 对于超级奖励积分：在以下情况下如何避免死锁？

Given tables:

给定的表：

• permissions[id BIGINT PRIMARY KEY]
• companies[id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))

• 权限[id BIGINT PRIMARY KEY]
• 公司[id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))

I create a new Company as follows:

我创建一个新公司如下：

• INSERT INTO permissions; -- Inserts permissions.id = 100
• INSERT INTO companies (name, permission_id) VALUES ('Nintendo', 100); -- Inserts companies.id = 200

• 插入权限；-- 插入权限.id = 100
• INSERT INTO公司（名称，permission_id）VALUES（'Nintendo'，100）; -- 插入 company.id = 200

I delete a Company as follows:

我删除一个公司如下：

• SELECT permission_id FROM companies WHERE id = 200; -- returns permission_id = 100
• DELETE FROM companies WHERE id = 200;
• DELETE FROM permissions WHERE id = 100;

• SELECT permission_id FROM 公司 WHERE id = 200; -- 返回permission_id = 100
• 从公司中删除 id = 200;
• 删除权限 WHERE id = 100;

In the above example, the INSERT locking order is [permissions, companies] whereas the DELETE locking order is [companies, permissions]. Is there a way to fix this example for REPEATABLE_READor SERIALIZABLEisolation?

在上面的例子中，INSERT 锁定顺序是 [permissions, Companies]，而 DELETE 锁定顺序是 [companys, permissions]。有没有办法修复这个例子REPEATABLE_READ或SERIALIZABLE隔离？

回答by LoztInSpace

Generally all modifications can cause a deadlock and selects will not (get to that later). So

通常所有修改都会导致死锁，而选择不会（稍后再谈）。所以

1. No you cannot ignore these.
2. You can somewhat ignore select depending on your database and settings but the others will give you deadlocks.

1. 不，你不能忽视这些。
2. 根据您的数据库和设置，您可以在某种程度上忽略选择，但其他人会给您带来死锁。

You don't even need multiple tables.

您甚至不需要多个表。

The best way to create a deadlock is to do the same thing in a different order.

造成死锁的最好方法是以不同的顺序做同样的事情。

SQL Server examples:

SQL Server 示例：

create table A
(
    PK int primary key
)

Session 1:

第 1 节：

begin transaction
insert into A values(1)

Session 2:

第 2 节：

begin transaction    
insert into A values(7)

Session 1:

第 1 节：

delete from A where PK=7

Session 2:

第 2 节：

delete from A where PK=1

You will get a deadlock. So that proved inserts & deletes can deadlock.

你会陷入僵局。所以证明插入和删除会死锁。

Updates are similar:

更新类似：

Session 1:

第 1 节：

begin transaction    
insert into A values(1)
insert into A values(2)
commit

begin transaction
update A set PK=7 where PK=1

Session 2:

第 2 节：

begin transaction
update A set pk=9 where pk=2    
update A set pk=8 where pk=1

Session 1:

第 1 节：

update A set pk=9 where pk=2

Deadlock!

僵局！

SELECT should never deadlock but on some databases it will because the locks it uses interfere with consistent reads. That's just crappy database engine design though.

SELECT 永远不会死锁，但在某些数据库上它会死锁，因为它使用的锁会干扰一致性读取。但这只是糟糕的数据库引擎设计。

SQL Server will not lock on a SELECT if you use SNAPSHOT ISOLATION. Oracle & I think Postgres will never lock on SELECT (unless you have FOR UPDATE which is clearly reserving for an update anyway).

如果您使用 SNAPSHOT ISOLATION，SQL Server 将不会锁定 SELECT。Oracle & 我认为 Postgres 永远不会锁定 SELECT （除非你有 FOR UPDATE ，它显然是为更新保留的）。

So basically I think you have a few incorrect assumptions. I think I've proved:

所以基本上我认为你有一些不正确的假设。我想我已经证明了：

1. Updates can cause deadlocks
2. Deletes can cause deadlocks
3. Inserts can cause deadlocks
4. You do not need more than one table
5. You doneed more than one session

1. 更新可能导致死锁
2. 删除会导致死锁
3. 插入会导致死锁
4. 您不需要多于一张桌子
5. 您也需要一个以上的会议

You'll just have to take my word on SELECT ;) but it will depend on your DB and settings.

你只需要相信我的话 SELECT ;) 但这将取决于你的数据库和设置。

回答by Grisha Weintraub

In addition to LoztInSpace's answer, insertsmay cause deadlocks even without deletesor updatespresence. All you need is a unique index and a reversed operations order.

除了 LoztInSpace 的答案，inserts即使没有deletes或updates存在也可能导致死锁。您只需要一个唯一索引和反向操作顺序。

Example in Oracle :

Oracle 中的示例：

create table t1 (id number);
create unique index t1_pk on t1 (id);

--thread 1 :
insert into t1 values(1);
--thread 2
insert into t1 values(2);
--thread 1 :
insert into t1 values(2);
--thread 2
insert into t1 values(1);  -- deadlock !

回答by Deepu

Let us assume you have two relations Aand Band two users Xand Y. Table A is WRITE Locked by user X and Table B is WRITE Locked by Y. Then the following query will give you a dead lock if used by both the users X and Y.

让我们假设您有两个关系AandB和两个用户Xand Y。表 A 被用户 X 写入锁定，表 B 被 Y 写入锁定。 那么如果用户 X 和 Y 都使用了以下查询，则会给您一个死锁。

Select * from A,B

So clearly a Selectoperation can cause a deadlock if join operations involving more than one table is a part of it. Usually Insert and Delete operations involve single relations. So they may not cause deadlock.

很明显，Select如果涉及多个表的连接操作是其中的一部分，则操作可能会导致死锁。通常插入和删除操作涉及单个关系。所以它们可能不会导致死锁。