Scenariot är följande:
I min MS SQL databas har jag en tabell som innehåller bl a binärt sparade bilder. I denna tabell finns en trigger på update. Denna trigger anropar i sin tur en stored procedure som med hjälp av xp_cmdshell kör en exe-fil. Detta program (en konsollapplikation skriven i VB.NET) hämtar med hjälp av en SqlConnection och en SqlDataReader in en binär bild från ovan nämnda databas och skriver bilden till disk med ett Filestream objekt.

Problemet består i att det inte går att köra vissa databasfrågor från programmet mot databasen, när anropet görs via triggern. "Vissa frågor" betyder i det här fallet select, update & deletefrågor mot tabeller som har något med funktionaliteten i triggern eller ursprungstabellen att göra. Det blir en timeout "Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired."
Var har jag tänkt fel? Går det komma runt låsningen på nåt sätt?

Mer info:
I anropet till programmet skickar jag med parametrar i "kommandoraden". Med hjälp av dessa vet jag vilken rad/bild i databasen jag ska läsa in och sen skriva till disk. Det är (inte helt oväntat...) samma rad som nyss blivit uppdaterad, den som aktiverade triggern.
Jag har deklarerat min trigger som "after update".
Om jag gör anropet till sp:n eller direkt mot programmet från Query Analyzern istället blir det ingen timeout.


Fråga gärna om det är någon väsentlig detalj du tycker att jag utelämnat.

Jag är mycket tacksam för eventuella tankar & förslag på lösningar..! =)


Mvh,
Magdalena

Jag skulle göra så här:

Ta bort triggern och skapa en lagrad procedur som uppdateringen görs via. Direkt efter update-satsen i den lagrade proceduren anropas exe-filen (ev via din befintliga procedur). Pseudokod:
<CODE>
--Uppdatera tabellen
UPDATE tblPix SET pict = @PIC WHERE PicId = @PICID
--Anropa sp som sparar på disk
EXEC UpdateFileSP
</CODE>

/Pelle

Okej, tack för den tanken. Men (för det finns ju alltid ett men ;-)) jag har dessvärre ingen kontroll över själva uppdateringarna av tabellen, de görs från en helt annan applikation.

Jag funderade ett tag på att ha ett program som kollar igenom tabellen (tabellerna, de är egentligen flera) med jämna mellanrum för att se om något blivit ändrat, men kom sen fram till att jag inte "har råd med" den tidsfördröjningen. Jag behöver helt enkelt göra vissa saker omgående när innehållet i tabellen ändras.



Magdalena

Ditt problem är (troligen) att dinn trigger är med i samma transaktion som updatesatsen. Det betyder att din rad (som kommandoprogrammet läser från) är låst och inte kan läsas av andra program/klienter.

Detta är helt som det skall vara, men (borde) gå att komma runt om din SELECT specificerar kommandot NOLOCK. Syntaxen har jag för mig är ungefär:

SELECT binärdata FROM tabell (NOLOCK) WHERE id = ?


Det är då viktigt att du är medveten om att du läser data i en transaktion som inte är klar, alltså inte "OK" ännu.

/micke

OK. Problemet verkar i alla fall bero på att den implicita transaktionen som uppdaterar bilden inte är färdig förrän xp_cmdshell-körningen är färdig, vilket i princip leder till en implicit deadlock. Något som borde fungera är att vid läsningen av bilden sätta en locking hint.
<CODE>
SELECT pict FROM tblPix WITH (READUNCOMMITTED) WHERE PicId = @PICID
</CODE>
WITH (READUNCOMMITTED) säger åt sql server att strunta i att transaktionen som uppdaterar den aktuella bilden inte har körts färdigt och läsa bilden ändå.

/Pelle

Hoppsan! Jag hann visst inte först med att svara!

Tusen tack Pelle och Micke! Jag uppskattar verkligen att ni tog er tid att fundera och svara =)
Problemet är löst, jag ler brett och kodar vidare. Tack återigen!

Mvh,
Magdalena

"Ledsen" att ta upp tråden igen, men jag måste påpeka att det vore en idé att fundera över om det inte kan gå att hitta en annan lösning. Till att börja med känns lösningen att anropa en extern applikation i en trigger väldigt osäker. Finns det verkligen ingen annan lösning? Hanterar du om flera rader blir uppdaterade samtidigt? Är du säker att du hanterar alla tänkbara scenarion som den andra applikationen kan generera? Problemet med denna lösning är att uppdateringar från den andra applikationen (för att inte tala om alla möjliga framtida applikationer) blir beroende av din VB-applikations funktionalitet. Om din applikation misslyckas på något vis (vad händer om det inte finns plats att lagra bilden, etc?) så misslyckas deras uppdatering, utan att de egentligen vet varför (om de inte har tillgång till triggern och din applikation).

Vidare innehåller tråden två stora varningslampor. Dels NOLOCK/READUNCOMMITTED, vilket Per och Micke redan varnat om i sina inlägg. Det finns förstås situationer när de är korrekta att använda, men oftast bör man fundera över om man verkligen satt sig i rätt situation då. Det är sällan rätt att tvinga SQL Server till något, snarare ska man sträva efter att ge den rätt förutsättningar att göra rätt från början. Det andra problemet, vilket jag som säkerhetsintresserad anser vara större än föregående, är användandet av xp_cmdshell. Återigen är det så att i vissa specifika situationer kan det vara korrekt att använda den, men ofta är tillgången till den så hårt begränsad (eller xp-n gärna tom bortplockad ur servern) av säkerhetsskäl att det är svårt att använda den. Det är möjligt att ni själva har full kontroll över servern och rättigheterna där, men som DBA är man alltid tveksam till att låta någon utnyttja xp_cmdshell.

Du sa att du funderat på att låta applikationen ligga och kolla efter förändringar med jämna mellanrum men att du inte har råd med tidsfördröjningen. Men det behöver knappt bli någon fördröjning alls. Om det är möjligt, så hade jag antagligen designat en situation likt denna så att triggern i tabellen enbart lägger in en rad i en separat 'applikationsstyrningstabell' om att den specifika bilden (raden) blivit uppdaterad. Sedan kan applikationen ligga och läsa denna tabell 'konstant', dvs bara ligga och loopa och läsa tabellen så ofta du vill. På så vis blir det inga låsningsproblem, och den uppdaterande applikationen hindras inte heller i sina uppdateringar av eventuella problem i bildhanteringsapplikationen.

En annan möjlighet är att titta på Notification Services för SQL Server, vilken kan hjälpa till att utföra denna typ av uppgifter.