SSIS transaktion blockerad av SPID -2

Tänk dig att du ska uppdatera innehållet i en tabell med helt nytt data, alltså först tömma den och sedan fylla den på nytt. Det är ganska rakt fram att lösa i både T-SQL:

truncate table dbo.PersonKopia;

insert into dbo.PersonKopia (Id, FirstName, LastName)
select Id, FirstName, LastName
from dbo.Person;

Och med SSIS:

Det här fungerar utmärkt så länge vi har kontroll över hur – och framföra allt när – tabellen skall användas. Har vi inte det finns det ju trots allt en risk att någon försöker att använda sig av tabellen när den är tom – mellan vår truncate och insert. Inte heller det är så svårt att lösa med T-SQL, vi utför de båda kommandona som en transaktion.

begin transaction

   truncate table dbo.PersonKopia;

   insert into dbo.PersonKopia (Id, FirstName, LastName)
   select Id, FirstName, LastName
   from dbo.Person;

commit transaction

Med SSIS får vi däremot problem. Även SSIS har stöd för transaktioner genom att man placerar de båda stegen i en gemensam container och anger att den skall fungera som en transaktion. (Ändra parametern TransactionOption från Supported (default) till Required.)

När vi nu testkör SSIS-paketet fungerar det inte riktigt som vi tänkt oss. Tabellen trunkeras, men sedan ”hänger sig” exekveringen.

Vi öppnar SQL Server Management Studio (SSMS) och kör sp_who2 för att ta reda på vem eller vad det är som blockerar vår körning. Det visar sig vara session (SPID) -2, en session som inte finns med i listan.

SPID -2 innebär att det inte finns någon aktiv session, men att det fortfarande finns en aktiv transaktion. I normala fall när vi öppnar en anslutning till SQL Server (t.ex. med SSMS eller .NET-kod) och skickar kommandon kan detta inte uppstå. Då kommer transaktionen att rullas tillbaka (ROLLBACK) i samma ögonblick sessionen avbryts.

Men SSIS använder sig av MS Distributed Transaction Coordinater (DTC) för att hantera transaktioner. Detta är logiskt eftersom SSIS ofta har flera olika datakällor att arbeta med, men det gäller även om det är samma datakälla under hela transaktionen. När DTC är med i bilden är det där transaktionen hanteras. DTC skickar respektive kommando i tur och ordning till datakällorna som utför kommandot ”nästan klart” och meddelar det till DTC. När sedan alla datakällor har gett sitt ”nästan klart” till DTC bekräftar DTC till samtliga att avsluta respektive kommando (COMMIT). Under mellantiden är transaktionen aktiv – men sessionen finns inte kvar längre.

Varför fungerar det då inte för oss i vårt SSIS-paket? För att vi använder oss av TRUNCATE istället för DELETE när vi skall tömma tabellen. SQL Server hanterar dessa kommandon på helt olika sätt och en av dessa skillnader är att när TRUNCATE används låses inte bara tabellens data utan även dess struktur (schema). Av någon anledning verkar detta lås inte hanteras fullt ut av DTC. Vi kan se att det är det låset som vårt INSERT-kommando väntar på (wait_type = LCK_M_SCH_S).

Det vi behöver göra är att använda DELETE istället för TRUNCATE. På så sätt undviker vi det schemalås som orsakade problemen.

Sammanfattningsvis:

• Om du måste använda transaktioner i SSIS-paketet använd enbart DML-kommandon inuti transaktionen (SELECT/INSERT/UPDATE/DELETE).
• Har du en session som är blockerad av SPID -2 så är MS DTC inblandad, alternativt någon annan transaktionshanterare. Dessa SPID är inte helt enkla att bli av med, men det får vi ta en annan gång.