INSERT操作会导致死锁吗? [英] Can an INSERT operation result in a deadlock?
问题描述
假设:
- 我正在使用REPEATABLE_READ或SERIALIZABLE事务隔离(每次访问一行时都会保留锁)
- 我们正在谈论同时访问多个表的多个线程。
我有以下问题:
-
INSERT
操作是否可能导致死锁?如果是这样,请提供详细的场景,说明如何发生死锁(例如,线程1会这样做,线程2会那样,...,死锁)。 - 奖励积分:答案对于所有其他操作(例如SELECT,UPDATE,DELETE),该问题相同。
- Is it possible for an
INSERT
operation 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). - For bonus points: answer the same question for all other operations (e.g. SELECT, UPDATE, DELETE).
UPDATE :
3.对于超级奖励积分:在以下情况下如何避免僵局?
UPDATE: 3. For super bonus points: how can I avoid a deadlock in the following scenario?
给定表:
- 权限
[id BIGINT主键]
- companies
[id BIGINT主键,名称为VARCH AR(30),permission_id BIGINT非空,外键(permission_id)参考Permissions(id))
- 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))
我按如下方式创建新公司:
I create a new Company as follows:
- 插入INTO权限; -插入Permissions.id = 100
- 插入公司(名称,permission_id)VALUES(任天堂,100); -插入companies.id = 200
我按如下方式删除公司:
I delete a Company as follows:
- 从具有ID = 200的公司中选择SELECT_ID -返回Permissionid_id = 100
- 从ID为200的公司删除;
- 从ID为100的公司删除;
在上面的示例中,INSERT锁定顺序为[权限,公司],而DELETE锁定顺序为[公司,权限]。有没有办法解决此示例的 REPEATABLE_READ
或 SERIALIZABLE
隔离的问题?
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_READ
or SERIALIZABLE
isolation?
推荐答案
通常,所有修改都可能导致死锁,而selects则不会(稍后再介绍)。所以
Generally all modifications can cause a deadlock and selects will not (get to that later). So
- 不,您不能忽略这些。
- 您可以略微忽略select,具体取决于您的数据库和设置,但其他设置会给您带来僵局。
您甚至不需要多个表。
创建死锁的最佳方法是以不同的顺序执行相同的操作。
The best way to create a deadlock is to do the same thing in a different order.
SQL Server示例:
SQL Server examples:
create table A
(
PK int primary key
)
会话1:
begin transaction
insert into A values(1)
第2阶段:
begin transaction
insert into A values(7)
会话1:
delete from A where PK=7
会话2:
delete from A where PK=1
您将陷入僵局。这样就证明了&
You will get a deadlock. So that proved inserts & deletes can deadlock.
更新类似:
会话1:
begin transaction
insert into A values(1)
insert into A values(2)
commit
begin transaction
update A set PK=7 where PK=1
会话2:
begin transaction
update A set pk=9 where pk=2
update A set pk=8 where pk=1
会话1:
update A set pk=9 where pk=2
死锁!
SELECT绝不应死锁,但在某些数据库上它将死锁,因为它使用的锁会干扰一致的读取。不过,这只是糟糕的数据库引擎设计。
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.
如果使用快照隔离,SQL Server将不会锁定SELECT。甲骨文公司我认为Postgres将永远不会锁定SELECT(除非您有FOR UPDATE,无论如何显然都保留更新)。
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).
因此,基本上我认为您有一些错误的假设。我想我已经证明了:
So basically I think you have a few incorrect assumptions. I think I've proved:
- 更新会导致死锁
- 删除会导致死锁
- 插入会导致死锁
- 您不需要多个表
- 您要做需要多个会话
- Updates can cause deadlocks
- Deletes can cause deadlocks
- Inserts can cause deadlocks
- You do not need more than one table
- You do need more than one session
您只需要对SELECT表示同意;)但这取决于您的数据库和设置。
You'll just have to take my word on SELECT ;) but it will depend on your DB and settings.
这篇关于INSERT操作会导致死锁吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!