英文:
SQL Server deadlock on read uncommitted isolation level
问题
我收到错误消息"事务（进程ID 60）与另一个进程在锁资源上发生死锁..."。我有两个简单的查询：
查询1：
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE RestaurantAccount SET CreatedOn = GETDATE()
WHERE Id = 1
WAITFOR DELAY '00:00:6'
UPDATE RestaurantInvoice SET CreatedOn = GETDATE()
WHERE Id = 1
COMMIT TRAN
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN
SELECT
CAST(1 AS BIT) AS hasError,
ERROR_LINE() AS ErrorLine,
(ISNULL(ERROR_MESSAGE(), '') + ISNULL(ERROR_PROCEDURE(), '')) AS ErrorMessage
END CATCH
查询2：
BEGIN TRY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE RestaurantInvoice SET CreatedOn = GETDATE()
WHERE Id = 1
WAITFOR DELAY '00:00:6'
UPDATE RestaurantAccount SET CreatedOn = GETDATE()
WHERE Id = 1
COMMIT TRAN
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN
SELECT
CAST(1 AS BIT) AS hasError,
ERROR_LINE() AS ErrorLine,
(ISNULL(ERROR_MESSAGE(), '')) AS ErrorMessage
END CATCH
我将事务隔离级别设置为读未提交，并运行第一个查询，然后立即运行第二个查询，但仍然收到了死锁错误。据我所知，在读未提交的隔离级别下不应该有锁。那么，死锁的原因是什么？（我使用的是SQL Server 2014）
英文:
I get error message "Transaction (Process ID 60) was deadlocked on lock resources with another process ...". I have two simple queries like:
Query 1:
BEGIN try
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN tran
update RestaurantAccount set CreatedOn = GETDATE()
where Id = 1
WAITFOR DELAY '00:00:6'
update RestaurantInvoice set CreatedOn = GETDATE()
where Id = 1
commit tran
END try
BEGIN catch
IF(@@TRANCOUNT > 0)
rollback tran
SELECT
cast(1 as bit) as hasError
,ERROR_LINE() AS ErrorLine
,(isnull(ERROR_MESSAGE(),'') + isnull(ERROR_PROCEDURE(),'')) AS ErrorMessage
END catch
Query 2:
BEGIN try
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN tran
update RestaurantInvoice set CreatedOn = GETDATE()
where Id = 1
WAITFOR DELAY '00:00:6'
update RestaurantAccount set CreatedOn = GETDATE()
where Id = 1
commit tran
END try
BEGIN catch
IF(@@TRANCOUNT > 0)
rollback tran
SELECT
cast(1 as bit) as hasError
,ERROR_LINE() AS ErrorLine
,(isnull(ERROR_MESSAGE(),'') ) AS ErrorMessage
END catch
I set the transaction isolation level to read uncommitted for both, I run the first query, and immediately run the second one, but I still get the deadlock error. As I know, there should be no lock with read uncommitted isolation level. So, what is the reason of deadlock?
(I use SQL Server 2014)
答案1
得分: 2
"READ UNCOMMITTED"适用于读取而不写入（INSERT、UPDATE、DELETE、TRUNCATE、MERGE...）。
Thom所说的非常正确。NOLOCK并不意味着不会放置锁。
甚至在某些情况下，即使在读取时，也会忽略NOLOCK，比如在使用ENCRYPT或DECRYPT函数时。
另外，READ UNCOMMITTED可能会在结果集中给出错误的行。阅读我写的论文（但它是用法语写的）。
英文:
As the name says "READ UNCOMMITTED" apply to READ no writes (INSERT, UPDATE, DELETE, TRUNCATE, MERGE...).
What Thom says is very true. NOLOCK does not mean that a lock will not be put.
And even in READs, under certain circumstances, NOLOCK is ignored, like when there is ENCRYPT or DECRYPT functions used.
And by the ways, READ UNCOMMITTED can give you false positive rows in the results set. Read the paper I wrote (but it is in french)...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论