Czy mogę utworzyć typ tabeli zdefiniowanej przez użytkownika i użyć go w tej samej transakcji?

Kiedy wykonam następujące czynności (w studio zarządzania GO podzieli polecenia na partie)

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

Otrzymuję komunikat o błędzie zakleszczenia. Mój proces utknął w martwym punkcie. Widziałem to zachowanie w 2008, 2008 R2 i 2012.

Czy istnieje sposób na użycie mojego nowo utworzonego typu w ramach tej samej transakcji, którą utworzono?

Zgłoszono to nie mniej niż cztery razy. Ten został zamknięty jako naprawiony:

http://connect.microsoft.com/SQLServer/feedback/details/365876/

Ale to nie była prawda. (Zobacz także sekcję obejść - zaproponowane przeze mnie obejście nie zawsze będzie możliwe do zaakceptowania).

Ten został zamknięty z założenia / nie naprawi się:

http://connect.microsoft.com/SQLServer/feedback/details/581193/

Te dwa są nowsze i wciąż aktywne :

http://connect.microsoft.com/SQLServer/feedback/details/800919/ (teraz zamknięty, ponieważ nie zostanie naprawiony )

http://connect.microsoft.com/SQLServer/feedback/details/804365/ (teraz zamknięty z założenia )

Dopóki Microsoft nie będzie w stanie przekonać się inaczej, będziesz musiał znaleźć obejście - po prostu zainstaluj wszystkie typy przed uruchomieniem testu lub podziel go na wiele testów.

Spróbuję uzyskać potwierdzenie od moich kontaktów na temat tego, co Umachandar rozumiał jako naprawiony w najwcześniejszym punkcie, ponieważ oczywiście jest to sprzeczne z późniejszymi stwierdzeniami.

AKTUALIZACJA # 1 (z, mam nadzieję, dokładnie 2)

Pierwotny błąd (który został zamknięty jako naprawiony) dotyczył typów aliasów, ale nie typu TABLE. Zgłoszono to w stosunku do SQL Server 2005, który oczywiście nie miał typów tabel i TVP. Wygląda na to, że UC zgłosił, że błąd związany z nie-tabelowymi typami aliasów został naprawiony na podstawie tego, jak obsługują transakcje wewnętrzne, ale nie obejmował podobnego scenariusza wprowadzonego później z typami tabel. Nadal czekam na potwierdzenie, czy ten oryginalny błąd powinien kiedykolwiek zostać zamknięty jako naprawiony; Zasugerowałem, aby wszystkie cztery były zamknięte zgodnie z projektem. Wynika to częściowo z tego, że spodziewałem się, że zadziała, a częściowo z tego, że UC ma wrażenie, że „poprawianie” działania w inny sposób jest niezwykle złożone, może złamać kompatybilność wsteczną i byłoby pomocne w bardzo ograniczona liczba przypadków użycia. Nic przeciwko tobie lub twojemu przypadkowi użycia, ale poza scenariuszami testowymi „

AKTUALIZACJA # 2

Napisałem na blogu o tym problemie:

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

Udało mi się to odtworzyć. Wykres impasu jest dość ciekawy:

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

Wygląda mi to na błąd i polecam otworzyć dla niego element połączenia.

Aby obejść swój bezpośredni problem, możesz użyć tSQLt.NewConnection(zakładam, że używasz 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

Nadal nie rozumiem, skąd bierze się potrzeba tworzenia typu tabeli w locie i zakładam, że nadmiernie komplikujesz swój test. Wyślij mi e-mail, jeśli chcesz porozmawiać.

Chyba, że ​​ktoś wie inaczej, nie sądzę, że można to zrobić w jednej transakcji. Nie sądzę, że to błąd.

Najpierw musisz wziąć blokadę modyfikacji schematu (Sch-M) podczas tworzenia typu. Ponieważ nie zatwierdzasz transakcji, zamek pozostaje otwarty. Następnie próbujesz zadeklarować zmienną tego typu w tej samej transakcji. To próbuje przejąć blokadę stabilności schematu (Sch-S). Te dwa typy są jednocześnie niezgodne w tym samym obiekcie. Ponieważ są w tej samej transakcji, SQL traktuje to jako impas, ponieważ Sch-S nigdy nie może zostać przyznany, gdy transakcja jest otwarta.

Uruchom każdą partię pojedynczo i wybierz opcję sys.dm_tran_locks, gdy tylko spróbujesz zadeklarować zmienną. Zobaczysz ten sam proces trzymający Sch-M i czekający na Sch-S na tym samym obiekcie.