Início / dba / Perguntas / 52900
Accepted
Michael J Swart
Asked: 2013-11-07 09:43:16 +0800 CST

Posso criar um tipo de tabela definido pelo usuário e usá-lo na mesma transação?

  • 772

Quando executo o seguinte (no estúdio de gerenciamento, o GO separa os comandos em lotes)

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

Recebo uma mensagem de erro de impasse. Meu processo entrou em um impasse consigo mesmo. Eu vi esse comportamento em 2008, 2008R2 e 2012.

Existe uma maneira de usar meu tipo recém-criado dentro da mesma transação em que foi criado?

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Isso foi relatado nada menos que quatro vezes (mas todos os vestígios foram removidos da WayBack Machine desde que Connect foi assassinado). Este foi fechado como corrigido:

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

    Mas isso não era verdade. (Veja também a seção de soluções alternativas - a solução alternativa que sugeri nem sempre será aceitável.)

    Este foi fechado por design / não será corrigido:

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

    Estes dois são mais recentes e ainda ativos :

    • connect.microsoft.com/SQLServer/feedback/details/800919/ (agora fechado como Won't Fix )
    • connect.microsoft.com/SQLServer/feedback/details/804365/ (agora fechado como By Design )

    Até que a Microsoft possa ser convencida do contrário, você terá que encontrar uma solução alternativa - apenas tenha todos os tipos implantados antes de executar o teste ou divida-o em vários testes.

    Tentarei obter confirmação de meus contatos sobre o que Umachandar quis dizer com fixo no primeiro item, porque obviamente isso entra em conflito com declarações posteriores.

    ATUALIZAÇÃO #1 (de, espero, exatamente 2)

    O bug original (que foi fechado como corrigido) envolvia tipos de alias, mas não de tipoTABLE. Foi relatado no SQL Server 2005, que obviamente não tinha tipos de tabela e TVPs. Parece que a UC relatou que o bug com tipos de alias não tabela foi corrigido com base em como eles lidam com transações internas, mas não cobriu um cenário semelhante introduzido posteriormente com tipos de tabela. Ainda estou esperando a confirmação se esse bug original deveria ter sido fechado como corrigido; Sugeri que todos os quatro fossem fechados conforme planejado. Isso ocorre em parte porque é como eu esperava que funcionasse e, em parte, porque entendo da UC que "consertar" para funcionar de uma maneira diferente é extremamente complexo, pode quebrar a compatibilidade com versões anteriores e seria útil em um número muito limitado de casos de uso. Nada contra você ou seu caso de uso, mas fora dos cenários de teste eu

    ATUALIZAÇÃO #2

    Eu escrevi sobre este problema:

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

    • 17

  2. Eu consegui reproduzir isso. O gráfico de impasse é bastante curioso:

    <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>

    Parece-me um bug e eu recomendo que você abra um item de conexão para ele.

    Para contornar seu problema imediato, você pode usar tSQLt.NewConnection(suponho que você esteja usando 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

    Ainda não entendo de onde vem a necessidade de criar um tipo de tabela em tempo real e presumo que você esteja complicando demais seu teste. Envie-me um e-mail se você quiser discutir.

    • 1

  3. A menos que alguém saiba diferente, não acho que haja uma maneira de fazer isso em uma única transação. Eu não acho que isso é um bug.

    Primeiro, você precisa obter um bloqueio de modificação de esquema (Sch-M) ao criar o tipo. Como você não confirma a transação, o bloqueio permanece aberto. Então você tenta declarar uma variável para esse tipo na mesma transação. Isso tenta obter um bloqueio de estabilidade de esquema (Sch-S). Esses dois tipos são incompatíveis simultaneamente no mesmo objeto. Como eles estão na mesma transação, o SQL trata isso como um impasse porque o Sch-S nunca pode ser concedido enquanto a transação estiver aberta.

    Execute cada lote, um de cada vez, e selecione sys.dm_tran_locks assim que tentar declarar a variável. Você verá o mesmo processo segurando um Sch-M e esperando por um Sch-S no mesmo objeto.

    • -2

relate perguntas