主页 / dba / 问题 / 52900
Accepted
Michael J Swart
Asked: 2013-11-07 09:43:16 +0800 CST

我可以创建一个用户定义的表类型并在同一个事务中使用它吗？

  • 772

当我执行以下操作时（在管理工作室中，GO 会将命令分成批次）

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

我收到一条死锁错误消息。我的过程与自身陷入僵局。我在 2008、2008R2 和 2012 年看到过这种行为。

有没有办法在它创建的同一个事务中使用我新创建的类型？

sql-server sql-server-2012

3 个回答

  1. Best Answer

    这已被报告了至少四次（但自从 Connect 被谋杀以来，所有痕迹都已从WayBack 机器中删除）。这个是固定关闭的：

    • connect.microsoft.com/SQLServer/feedback/details/365876/

    但那不是真的。（另请查看解决方法部分 - 我建议的解决方法并不总是可以接受的。）

    这个是按设计关闭的/不会修复：

    • connect.microsoft.com/SQLServer/feedback/details/581193/

    这两个是较新的并且仍然活跃：

    • connect.microsoft.com/SQLServer/feedback/details/800919/ （现在关闭为Won't Fix）
    • connect.microsoft.com/SQLServer/feedback/details/804365/ （现在按设计关闭）

    除非 Microsoft 可以说服其他人，否则您将不得不找到一种解决方法 - 只需在运行测试之前部署所有类型，或者将其分解为多个测试。

    我将尝试从我的联系人那里确认 Umachandar 在最早的项目中所指的固定，因为显然这与后来的陈述相冲突。

    更新＃1（希望正好是2）

    原始错误（已修复为关闭）涉及别名类型，但不是类型TABLE. 它是针对 SQL Server 2005 报告的，它显然没有表类型和 TVP。似乎 UC 报告说，非表别名类型的错误已根据它们处理内部事务的方式进行了修复，但它没有涵盖后来引入表类型的类似场景。我仍在等待确认该原始错误是否应该被关闭为已修复；我建议按照设计将所有四个都关闭。这部分是因为它是我期望它工作的方式，部分是因为我从 UC 那里得到了一种感觉，即“修复”它以不同的方式工作是非常复杂的，可能会破坏向后兼容性，并且会有助于用例数量非常有限。没有什么对您或您的用例不利，但在测试场景之外我'

    更新#2

    我在博客上写过这个问题：

    http://www.sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

    • 17

  2. 我能够重现这一点。死锁图非常奇怪：

    <deadlock-list>
  <deadlock victim="process47f948">
    <process-list>
      <process id="process47f948" taskpriority="0" logused="0" waitresource="METADATA: database_id = 2 USER_TYPE(user_type_id = 257)" waittime="3607" ownerId="14873" transactionname="@myPK" lasttranstarted="2013-11-06T13:23:12.177" XDES="0x80f6d950" lockMode="Sch-S" schedulerid="1" kpid="2672" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2013-11-06T13:23:12.167" lastbatchcompleted="2013-11-06T13:23:12.163" clientapp="Microsoft SQL Server Management Studio - Query" hostname="xxxxx" hostpid="5276" loginname="xxxxx\xxxxx" isolationlevel="read committed (2)" xactid="14867" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="adhoc" line="2" sqlhandle="0x010002002d9fe3155066b380000000000000000000000000">
declare @myPK dbo.IntIntSet;     </frame>
        </executionStack>
        <inputbuf>

declare @myPK dbo.IntIntSet;
    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <metadatalock subresource="USER_TYPE" classid="user_type_id = 257" dbid="2" id="lock8009cc00" mode="Sch-M">
        <owner-list>
          <owner id="process47f948" mode="Sch-M" />
        </owner-list>
        <waiter-list>
          <waiter id="process47f948" mode="Sch-S" requestType="wait" />
        </waiter-list>
      </metadatalock>
    </resource-list>
  </deadlock>
</deadlock-list>

    在我看来它像一个错误，我建议您为它打开一个连接项。

    为了解决您的直接问题，您可以使用tSQLt.NewConnection（我假设您使用的是 tSQLt）

    use tempdb

begin tran
go
EXEC tSQLt.NewConnection '
CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
';
go

declare @myPK dbo.IntIntSet;
go

rollback

    我仍然不明白动态创建表类型的需求来自哪里，我认为您的测试过于复杂。如果您想讨论，请给我发送电子邮件。

    • 1

  3. 除非有人知道不同，否则我认为没有办法在一次交易中做到这一点。我不认为这是一个错误。

    首先，您需要在创建类型时获取模式修改锁 (Sch-M)。由于您不提交事务，因此锁保持打开状态。然后，您尝试在同一事务中为该类型声明一个变量。这会尝试获取架构稳定性锁 (Sch-S)。这两种类型在同一个对象上同时不兼容。由于它们在同一个事务中，SQL 将其视为死锁，因为在事务打开时永远无法授予 Sch-S。

    一次运行每个批次，并在您尝试声明变量时立即选择 sys.dm_tran_locks。您将看到相同的进程持有 Sch-M 并在同一对象上等待 Sch-S。

    • -2

相关问题