On the MS Technet page on "Minimizing Deadlocks", it says that "deadlocks cannot be completely avoided". More specifically, it states that
在关于“最小化死锁”的MS Technet页面上,它说“无法完全避免死锁”。更具体地说,它表明了这一点
If all concurrent transactions access objects in the same order, deadlocks are less likely to occur.
如果所有并发事务以相同顺序访问对象,则不太可能发生死锁。
This implies that deadlocks could theoretically occur, even if all transactions access objects in the same order. Could someone give an example of how that would be possible?
这意味着即使所有事务以相同的顺序访问对象,理论上也可能发生死锁。有人可以举例说明如何做到这一点吗?
2 个解决方案
#1
3
Just to provide a simple code example further to Charles's answer
只是为Charles的答案提供一个简单的代码示例
CREATE TABLE T(X INT)
Then run in two concurrent connections
然后在两个并发连接中运行
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM T
WAITFOR DELAY '00:00:10'
INSERT INTO T VALUES(1)
COMMIT
Deadlock.
The issue is that both connections can aquire shared locks on a resource but then block each other when they need to convert that to an exclusive lock.
问题是两个连接都可以获取资源上的共享锁,但在需要将其转换为独占锁时会相互阻塞。
#2
3
One way is when using Isolation Level Serializable.
一种方法是使用Isolation Level Serializable。
If you have a parent child relationship, as for example in an accounting database with customers, customer accounts, and individual financial debits and credits into those accounts, then:
如果您有父子关系,例如在具有客户,客户帐户以及个人财务借记和信用额的会计数据库中,则:
you have a database transaction consisting of multiple balancing debit and credit entries into the child table, and an accompanying update of an account balance in the customer table.
您有一个数据库事务,其中包含多个平衡借记和贷记条目到子表中,以及随附的客户表中的帐户余额更新。
At isolation level serializable, in order to prevent what are called phantom reads the DB engine places range locks on all records which the SQL statement has to read, (In this case, the credit and debit table). This ensures that no other transaction can insert new credits or debits into the table while this Tx is calculating the new customer balance. Any attempt to insert a new credit or debit is blocked by the lock.
在可序列化的隔离级别,为了防止所谓的幻像读取,数据库引擎会在SQL语句必须读取的所有记录上放置范围锁(在本例中为信用卡和借记表)。这确保了当此Tx计算新的客户余额时,没有其他交易可以在表中插入新的信用或借记。锁定会阻止任何插入新信用卡或借记卡的尝试。
Meanwhile, the first Tx is blocked as well, since the other insert Tx will have placed a lock on the Customer Table row, since it also needs to update the customer Balance. The locks aer not released until the entire Tx is committed, so both Transactions are blocking each other... Deadlock.
同时,第一个Tx也被阻止,因为另一个插入Tx将锁定Customer Table行,因为它还需要更新客户余额。在整个Tx提交之前,锁不会释放,因此两个事务都相互阻塞......死锁。
#1
3
Just to provide a simple code example further to Charles's answer
只是为Charles的答案提供一个简单的代码示例
CREATE TABLE T(X INT)
Then run in two concurrent connections
然后在两个并发连接中运行
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM T
WAITFOR DELAY '00:00:10'
INSERT INTO T VALUES(1)
COMMIT
Deadlock.
The issue is that both connections can aquire shared locks on a resource but then block each other when they need to convert that to an exclusive lock.
问题是两个连接都可以获取资源上的共享锁,但在需要将其转换为独占锁时会相互阻塞。
#2
3
One way is when using Isolation Level Serializable.
一种方法是使用Isolation Level Serializable。
If you have a parent child relationship, as for example in an accounting database with customers, customer accounts, and individual financial debits and credits into those accounts, then:
如果您有父子关系,例如在具有客户,客户帐户以及个人财务借记和信用额的会计数据库中,则:
you have a database transaction consisting of multiple balancing debit and credit entries into the child table, and an accompanying update of an account balance in the customer table.
您有一个数据库事务,其中包含多个平衡借记和贷记条目到子表中,以及随附的客户表中的帐户余额更新。
At isolation level serializable, in order to prevent what are called phantom reads the DB engine places range locks on all records which the SQL statement has to read, (In this case, the credit and debit table). This ensures that no other transaction can insert new credits or debits into the table while this Tx is calculating the new customer balance. Any attempt to insert a new credit or debit is blocked by the lock.
在可序列化的隔离级别,为了防止所谓的幻像读取,数据库引擎会在SQL语句必须读取的所有记录上放置范围锁(在本例中为信用卡和借记表)。这确保了当此Tx计算新的客户余额时,没有其他交易可以在表中插入新的信用或借记。锁定会阻止任何插入新信用卡或借记卡的尝试。
Meanwhile, the first Tx is blocked as well, since the other insert Tx will have placed a lock on the Customer Table row, since it also needs to update the customer Balance. The locks aer not released until the entire Tx is committed, so both Transactions are blocking each other... Deadlock.
同时,第一个Tx也被阻止,因为另一个插入Tx将锁定Customer Table行,因为它还需要更新客户余额。在整个Tx提交之前,锁不会释放,因此两个事务都相互阻塞......死锁。