SQL Server 2008中两个并行事务的死锁问题

Question

我有两个事务：T1使用SERIALIZABLE隔离级别，而T2（我认为使用默认的READ COMMITTED隔离级别，但这并不重要）。

事务T1执行SELECT，然后WAITFOR 2秒，然后SELECT。

事务T2对T1读取的数据进行UPDATE。

这会导致死锁，为什么事务T2不等待T1结束？

当T1具有REPEATABLE READ隔离级别时，一切正常，即会出现幻行。我认为当我将隔离级别提高到SERIALIZABLE时，T2将等待T1的结束。

这是我的大学练习的一部分。我必须展示在具有不正确隔离级别和缺乏这些效果的两个并行事务中的负面影响，并展示在正确的隔离级别下没有这些效果。

这里是代码，遗憾的是字段名称是用波兰语写的。

T1:

USE MR;
SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- 1.查询
SELECT
    www.IdSamochodu，s.Model，s.Marka，s.NrRejestracyjny，o.PESEL，o.Nazwisko，o.Imie，o.NrTelefonu
FROM
    WizytyWWarsztacie www
JOIN
    Samochody s
    ON s.IdSamochodu = www.IdSamochodu
JOIN
    Osoby o
    ON o.PESEL = s.PESEL
WHERE
    www.[Status] ='gotowy_do_odbioru'
ORDER BY www.IdSamochodu ASC
;
WAITFOR DELAY '00:00:02';
-- 2.查询
SELECT
    u.IdSamochodu，tu.Nazwa，tu.Opis，u.Oplata
FROM
    Uslugi u
JOIN
    TypyUslug tu
    ON tu.IdTypuUslugi = u.IdTypuUslugi
JOIN
    WizytyWWarsztacie www
    ON www.IdSamochodu = u.IdSamochodu AND
        www.DataOd = u.DataOd
WHERE
    www.[Status] ='gotowy_do_odbioru'
ORDER BY u.IdSamochodu ASC，u.Oplata DESC
;
COMMIT;

T2:

使用MR;
SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE
    Uslugi
SET
    [Status] = '已完成'
WHERE
    IdUslugi = 2
;
UPDATE
    www
SET
    www.[Status] = '准备接收'
FROM
    WizytyWWarsztacie www
WHERE
    www.[Status] = '正在执行服务' AND
    EXISTS (
        SELECT 1
        FROM
            Uslugi u
        WHERE
            u.IdSamochodu = www.IdSamochodu AND
            u.DataOd = www.DataOd AND
            u.[Status] = '已完成'
        GROUP BY u.IdSamochodu, u.DataOd
        HAVING COUNT(u.IdUslugi) = (
            SELECT
                COUNT(u2.IdUslugi)
            FROM
                Uslugi u2
            WHERE
                u2.IdSamochodu = www.IdSamochodu AND
                u2.DataOd = www.DataOd
            GROUP BY u2.IdSamochodu, u2.DataOd
        )
    )
;
COMMIT;

我使用SQL Management Studio，每个事务都在不同的文件中。我通过在T1中单击F5然后快速切换到包含T2的文件再次单击F5来运行此操作。

我阅读了有关mssql中死锁和锁定机制的文章，但显然我还没有理解这个主题。

SQL Server 2008 R2 (.Net 2.0应用程序)中的死锁问题

SQL Server在select/update或多个select之间死锁

选择/更新死锁

http://msdn.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

http://www.sql-server-performance.com/2004/advanced-sql-locking/

编辑

我发现T2中的第一个UPDATE语句引起了问题，为什么？

- rzymek

---

请提供包括索引在内的表定义以及 SELECT 和 UPDATE 查询。 - Martin Smith

如果您发布代码/查询，您将获得更好的指导。我特别想看看您所说的并行事务是什么意思（您的查询代码/批处理将帮助我弄清楚）。 - DWright

Answer 1

解决死锁问题的第一步是获取死锁图。这是一个XML文档，告诉您涉及的事务和资源的相关部分。您可以通过Profiler、扩展事件或事件通知获得它（我相信还有其他方法，但现在这样做就可以了）。一旦获得了死锁图，请检查它，以查看每个事务对哪些资源上有哪些类型的锁。从那里开始取决于图表中发生了什么，因此我会在这里停止。底线：获取死锁图并挖掘其中的细节。

顺便说一句，说某个事务“导致”死锁有些误导人。所有涉及到死锁的事务都是引起死锁情况所必需的，因此没有谁更有错。

- Ben Thul

---

谢谢你的提示，我不知道有像 Profiler 和死锁图这样的东西。我是 MS SQL Server 的新手。 - rzymek

没关系。这就是任何事情的美妙之处：每个人都从一无所知开始。 :) - Ben Thul

Answer 2

我在使用SQL Management Studio时遇到了一些问题（Profiler无法工作），但最终我获得了死锁图。这篇文章对我很有帮助。

为了理解这张图表，我不得不学习锁定机制和符号。我认为在这里已经很清楚地解释了。

现在，当我了解了所有这些东西，死锁的原因就非常明显了。我为所描述的情况制作了顺序图：

正如我之前所写的，当我们从事务T2中删除第一个UPDATE语句时，死锁就不会发生。

在这种情况下，T2不会在pk_uslugi索引上获取锁定，因此来自事务T1的第二个SELECT语句将成功执行，并且pk_wizytywwarsztacie索引将被解锁。之后，T2也将完成。

- rzymek

Answer 3

我遇到了一个类似的问题，我从可用项目列表中进行选择，然后将这些项目插入到一个暂存队列表中。当我有太多并发请求时，选择语句会返回在另一个并行请求期间同时被选择的项目。当尝试将它们插入到暂存队列表中时，我会收到唯一约束错误（因为相同的项目不能两次进入暂存表）。

然后我尝试在整个过程中包装一个SERIALIZABLE事务，但是我遇到了死锁错误，因为两个事务都持有UC索引上的锁（由我的死锁图确定）。

最终我通过在选择语句中使用独占行锁来解决了这个问题。

您可以尝试在相关的表/行上使用独占行锁。这将确保T1中的行锁定在T2尝试更新相同行之前完成。

例如：

SELECT *
FROM Uslugi u WITH (XLOCK, ROWLOCK)

我还不确定这会对性能产生什么影响，但在使用多个线程进行负载测试时，似乎没有负面影响。

- Jared Conti

Answer 4

问题可能是这样的：

1. T1选择行并施加S锁
2. T2更新行并施加U锁（成功）
3. T2更新行并施加X锁（等待，锁排队）
4. T2再次尝试施加S锁，但S锁与排队的X锁不兼容。

SQL Server中的锁是排队的。如果队列的首项在等待，队列中的所有其他项也会等待。

实际上，我并不完全确定这是否是原因，因为相同的问题应该会在“可重复读”中发生。我仍然发布这个想法，希望它有所帮助。

- usr