[MSSQL] Deadlocks bij veel queries van verschillende clients

[bigben04]

Na het lezen van allerlei info over IsolationLevels en verschillende soorten locks kom ik helaas nog niet uit mijn probleem. Het betreft hier een C#-applicatie die documenten (images+metadata) verwerkt door achter elkaar een reeks van Jobs uit te voeren. Deze applicatie gebruikt een MS SQL Server database om de data op te slaan, wat er ook voor zou moeten zorgen dat met meerdere clients tegelijk gewerkt kan worden. Dit laatste gaat echter bij hoge volumes mis, er beginnen dan deadlocks te ontstaan in SQL Server welke dan een van de queries/transacties afbreekt. Het probleem is op dit moment reproduceerbaar op SQL Server 2008 R2 maar treedt volgens mij ook op op 2005 en 2008 (2000 ondersteunt onze applicatie niet vanwege het gebruik van bepaalde taalconstructies die later geintroduceerd zijn).

Hier volgt wat data definitie, waarbij ik irrelevante kolommen zoveel mogelijk heb weggelaten. De tabel 'Document' representeert 1 document en bevat een verwijzing naar de laatste Job waarin deze verwerkt is en de volgende Job waarin deze verwerkt moet worden en een verwijzing naar een Sessie. Een Sessie is gekoppeld aan 1 client. Een client verwerkt steeds 1 Job tegelijk en verwerkt hierin achter elkaar alle Documenten die klaarstaan voor deze Job. Wanneer een client een Document wil verwerken doet hij dit door in de session_id kolom de id van zijn sessie te zetten. Als het Document klaar is in de betreffende Job wordt de session_id kolom door de client weer op NULL gezet zodat deze in een volgende Job weer door dezelfde of een andere client opgepakt kan worden. Vaak zijn Jobs erg snel klaar zodat er achter elkaar queries worden afgevuurd.

MSSQL:

CREATE TABLE [Document] (
  [document_id] INTEGER NOT NULL
    IDENTITY CONSTRAINT PK__Document PRIMARY KEY NONCLUSTERED,
  [last_Job_id] INTEGER NOT NULL
    CONSTRAINT [FK__Document_last_Job_id] FOREIGN KEY REFERENCES [Job] ([job_id]),
  [next_Job_id] INTEGER NULL
    CONSTRAINT [FK__Document_next_Job_id] FOREIGN KEY REFERENCES [Job] ([job_id]),
  [session_id] INTEGER NULL
    CONSTRAINT [FK__Document_session_id] FOREIGN KEY REFERENCES [Session] ([session_id])
    ON DELETE SET NULL)

CREATE NONCLUSTERED INDEX [FK__Document_last_Job_id] ON [Document]([last_Job_id])

CREATE NONCLUSTERED INDEX [FK__Document_next_Job_id] ON [Document]([next_Job_id])

CREATE NONCLUSTERED INDEX [FK__Document_session_id] ON [Document]([session_id])

Naast deze tabel is er ook een DocumentStatistics tabel. Deze bevat informatie over aantallen verwerkte documenten. Deze wordt niet rechtstreeks aan een Job gekoppeld, maar aan de GUID van de Job, omdat Jobs weggegooid kunnen worden en vervangen door andere Jobs, maar we op dat moment de historische informatie wel graag willen bewaren.

MSSQL:

CREATE TABLE [DocumentStatistics] (
  [documentStatistics_id] INTEGER NOT NULL
    IDENTITY CONSTRAINT PK__DocumentStatistics PRIMARY KEY NONCLUSTERED,
  [job_s] [Ts] NOT NULL,
  [year_int] [Tint] NOT NULL,
  [month_int] [Tint] NOT NULL,
  [documentCount_int] [Tint] NOT NULL,
  [pageCount_int] [Tint] NOT NULL)

Om deze tabel bij te werken wordt een stored procedure gebruikt. Deze bekijkt of er al een record is voor de huidige maand. Zo ja, dan wordt de counter hierin opgehoogd, zo niet dan wordt een nieuw record gemaakt.

MSSQL:

CREATE PROCEDURE UpdateDocumentStatistics(@job_id int, @pageCount_int int)
AS
    DECLARE @jobIdentifier_s varchar(255)
    SELECT @jobIdentifier_s = identifier_s
    FROM [Job]
    WHERE job_id = @job_id
    DECLARE @id int
    SELECT @id = documentStatistics_id
    FROM [DocumentStatistics] WITH (UPDLOCK, ROWLOCK)
    WHERE job_s = @jobIdentifier_s
    AND year_int = DATEPART(yyyy, GETDATE())
    AND month_int = DATEPART(mm, GETDATE())
    IF @id > 0
    BEGIN
        UPDATE [DocumentStatistics]
        SET documentCount_int = documentCount_int + 1,
        pageCount_int = pageCount_int + @pageCount_int
        WHERE documentStatistics_id = @id
    END
    ELSE
    BEGIN
        INSERT INTO [DocumentStatistics]
        (job_s, year_int, month_int, documentCount_int, pageCount_int)
        VALUES (@jobIdentifier_s, DATEPART(yyyy, GETDATE()), DATEPART(mm, GETDATE()), 1, @pageCount_int)
    END

De query om een Document aan de huidige client te koppelen is de volgende:

MSSQL:

-- query 1
UPDATE [Document]
SET session_id = @currentSession_id
OUTPUT INSERTED.document_id 
WHERE document_id =
    (SELECT TOP 1 document_id 
    FROM Document WITH (UPDLOCK, ROWLOCK) 
    WHERE next_Job_id = @currentJob_id 
    AND session_id IS NULL 
    ORDER BY document_id ASC)

De query om een Document weer vrij te geven is de volgende:

MSSQL:

-- query 2
UPDATE Document
SET last_job_id = @last_job_id, next_job_id = @next_job_id, session_id = NULL
WHERE document_id = @document_id

De stored procedure en de beide getoonde queries worden uitgevoerd in een transactie met IsolationLevel 'ReadUncommitted'. Het idee hierachter was dat de performance hiervan het beste is omdat zo min mogelijk locks gebruikt worden. In de stored procedure en query 1 wordt vervolgens de hint 'WITH (UPDLOCK, ROWLOCK)' meegegeven om te zorgen dat dezelfde rij niet toch door 2 clients tegelijk gebruikt kan worden.

In het bovenstaande verhaal zitten helaas wat onvolkomenheden, want we lopen bij het processen met 5 clients tegelijk tegen deadlocks aan. Deze treden op in de volgende situaties, waarbij ik met SQL Server Profiler een log hiervan heb gemaakt en het bijbehorende schema erbij post:

1. Query 1 wordt door een client uitgevoerd en query 2 wordt tegelijk op een andere client uitgevoerd:


2. Query 1 wordt door twee clients tegelijk uitgevoerd:


3. De stored procedure wordt door twee clients tegelijk uitgevoerd:


3. De stored procedure wordt door vier clients tegelijk uitgevoerd?:
4.

De vraag is nu dus: hoe kom ik van de deadlocks af en houd/krijg ik een goede performance?

• Is IsolationLevel 'ReadUncommitted' helemaal fout en moet ik bijvoorbeeld 'Serializable' gebruiken? Of is dat fataal voor de performance?
• Moeten we de stored procedure en de DocumentStatistics tabel dan maar zo aanpassen dat er gewoon een record per pagina ingeschoten wordt (dit worden er wel erg veel...) of is er een 'best practice' voor het uitvoeren van een 'update als er al een record is, anders insert je er een' query?
• Kan query 1 herschreven worden zodat er geen subquery nodig is? Oftewel een manier om te zeggen 'update maximaal 1 record dat aan bepaalde criteria voldoet'?

[4VAlien]

de query hint UPDLOCK lijkt me gevaarlijk :

Specifies that update locks are to be taken and held until the transaction completes.

staat het geheel van de ze procedures in een transaction (bijvoorbeeld vanaf de client?). Dan blokeert je lock behoorlijk!

Kun je niet proberen om 100 jaar aan stats entries voor uit te genereren (is maar 1200 rows) en dan alleen de udpate query te runnen met alleen rowlock? Ook bij de andere query met update lock lijkt het me beter deze hint weg te halen en ervoor te zorgen dat je serie statements binnen een transactie draait.

[David Mulder]

Klopt @4VAlien, maar los daarvan, mssql is bekend erom (en ja ik gebruik het zelf ook op werk, dus ben niet bezig met ms bashing oid.) dat het relatief snel instabiel word naarmate het gebruik toe neemt. Wat betreft je tweede vraag, in de meeste database software bestaan daar on duplicate e.d. set ups voor, maar in mssql zijn stored procedures meestal the way to go (ook al word het gebruik van stored procedures afgeraden wat betreft preformance en stabiliteit). En wat betreft je laatste vraag, er bestaat zo iets volgens mij in mssql 2008 zag ik laatst ergens in de docs staan toen ik iets anders zocht, maar kan het niet zo 123 vinden.

[4VAlien]

Aanvullend: heb je wel andere isolation levels getest zonder query hints? En wat is de latency? ik kan me voorstellen dat als de netwerk latency significant is dat je een server proces wil draaien op of dichtbij de DB server. Of een hele grote stored procedure wil maken die al het werk in 1x doet.

[RobIII]

GreatSlovakia schreef op donderdag 23 juni 2011 @ 19:23:
mssql is bekend erom (en ja ik gebruik het zelf ook op werk, dus ben niet bezig met ms bashing oid.) dat het relatief snel instabiel word naarmate het gebruik toe neemt.

offtopic:
Heb je daar een (betrouwbare) bron van Want MSSQL is in mijn jarenlange ervaring met vele RDBMS'en in diverse omgevingen (van piepklein tot mothereffin' large) veruit de stabielste; ik zal je sterker vertellen: ik kan me niet heugen een instabiele MSSQL ooit te hebben gezien tenzij de oorzaak lag bij instabiele/krappe/gare hardware of geblunder van systeembeheerders.

[PolarBear]

RobIII schreef op donderdag 23 juni 2011 @ 21:33:
[...]

offtopic:
Heb je daar een (betrouwbare) bron van Want MSSQL is in mijn jarenlange ervaring met vele RDBMS'en in diverse omgevingen (van piepklein tot mothereffin' large) veruit de stabielste; ik zal je sterker vertellen: ik kan me niet heugen een instabiele MSSQL ooit te hebben gezien tenzij de oorzaak lag bij instabiele/krappe/gare hardware of geblunder van systeembeheerders.

Met RobIII en query hints is het begin van veel ellende vaak.

[EfBe]

Deadlocks krijg je vaak wanneer je met 1 proc of method, meerdere acties gaat doen. Jij leest data in je procedure, waardoor readlocks op de rows worden geplaatst die gescanned en gelezen zijn. De update of insert (NOOIT IF statements gebruiken op deze manier, je proc compileert dan nl. iedere keer. 2 procs maken, maar dat terzijde) gaat dan iets doen maar als DIE moet wachten op een andere transactie blokkeert deze. Maar omdat de select al readlocks heeft geplaatst (en met de hint ook updatelocks) gaat wellicht de transactie waar de insert / update op staat te wachten niet verder want die staat wellicht op de update locks / readlocks van de select te wachten.

Over die stored procedure kan ik kort zijn: dat is een rampzalig stukje code. Je kunt niet even een counter ophogen in een proc, je gaat nl. met meerdere connecties tegelijk die database te lijf. Je moet sowieso de 2 selects en de update mergen in 1 query, anders is het absoluut onbetrouwbaar. Verder zou ik geen locks zetten met de hand, dat kan sqlserver namelijk prima zelf.

Je hebt dan wel het probleem dat er in den beginne geen documentstatistics row is. Maar ik zou er nog eens goed naar kijken hoe je de data aan elkaar knoopt, want het is raar: je gaat uit Job een job identifier halen en die als predicate gebruiken in documentstatistics, maar dat is overbodig: wanneer je een job aanmaakt, dan maak je ook de statistics rows aan. En waarom is de id van job niet de key in documentstatistics? De query die je nu gebruikt om de id van documentstatistics op te halen (op een brakke manier) kan meerdere rows opleveren, maar jij gaat uit van 1.

[bigben04]

4VAlien schreef op donderdag 23 juni 2011 @ 19:50:
Aanvullend: heb je wel andere isolation levels getest zonder query hints? En wat is de latency? ik kan me voorstellen dat als de netwerk latency significant is dat je een server proces wil draaien op of dichtbij de DB server. Of een hele grote stored procedure wil maken die al het werk in 1x doet.

Ik heb een reeks testen gedaan door op 1 systeem 6 clients tegelijk te laten draaien, en dit met alle verschillende isolation levels en ook met en zonder de WITH (UPDLOCK). Bij elke combinatie kwamen er echter ofwel deadlocks ofwel werden er documenten door 2 verschillende clients tegelijk verwerkt doordat ze beiden hetzelfde document gelockt hadden (waarbij er dan natuurlijk 1 lock weer overschreven was, maar dat weten de clients niet). Ik heb wel de problemen tussen query 1 en 2 kunnen verhelpen (met de 6 clients op mijn systeem, vandaag ben ik niet op de zaak maar wordt er een test gedraaid met meerdere systemen) door van de UPDLOCK in query 1 een TABLELOCK te maken en ook in query 2 een WITH(TABLELOCK) toe te voegen, zodat clients echt op elkaar moeten wachten. Dit zorgt dat de deadlocks niet meer optreden, maar nog steeds lijkt het me niet de bedoeling dat ik zelf dit soort query hints ga gebruiken.

Ik heb voor query 1 ook de methode van http://rusanu.com/2010/03/26/using-tables-as-queues/ geprobeerd, omdat mijn situatie in feite neer komt op een queue, maar dit levert dezelfde resultaten op als het helemaal weglaten van de UPDLOCK, hierbij worden toch documenten dubbel verwerkt. Dit leverde ongeveer de volgende query op:

MSSQL:

-- query 1
WITH d AS (SELECT TOP 1 *
    FROM Document WITH (UPDLOCK, ROWLOCK) 
    WHERE next_Job_id = @currentJob_id 
    AND session_id IS NULL 
    ORDER BY document_id ASC)
UPDATE d
SET session_id = @currentSession_id
OUTPUT INSERTED.document_id

De latency qua netwerkverbinding is overigens normaliter niet zo hoog. Bij mijn test draaiden alle clients op mijn eigen systeem, bij klanten wordt normaliter een database server gebruikt binnen het eigen lokale netwerk.

EfBe schreef op vrijdag 24 juni 2011 @ 09:20:
Deadlocks krijg je vaak wanneer je met 1 proc of method, meerdere acties gaat doen. Jij leest data in je procedure, waardoor readlocks op de rows worden geplaatst die gescanned en gelezen zijn. De update of insert (NOOIT IF statements gebruiken op deze manier, je proc compileert dan nl. iedere keer. 2 procs maken, maar dat terzijde) gaat dan iets doen maar als DIE moet wachten op een andere transactie blokkeert deze. Maar omdat de select al readlocks heeft geplaatst (en met de hint ook updatelocks) gaat wellicht de transactie waar de insert / update op staat te wachten niet verder want die staat wellicht op de update locks / readlocks van de select te wachten.

Over die stored procedure kan ik kort zijn: dat is een rampzalig stukje code. Je kunt niet even een counter ophogen in een proc, je gaat nl. met meerdere connecties tegelijk die database te lijf. Je moet sowieso de 2 selects en de update mergen in 1 query, anders is het absoluut onbetrouwbaar. Verder zou ik geen locks zetten met de hand, dat kan sqlserver namelijk prima zelf.

Je hebt dan wel het probleem dat er in den beginne geen documentstatistics row is. Maar ik zou er nog eens goed naar kijken hoe je de data aan elkaar knoopt, want het is raar: je gaat uit Job een job identifier halen en die als predicate gebruiken in documentstatistics, maar dat is overbodig: wanneer je een job aanmaakt, dan maak je ook de statistics rows aan. En waarom is de id van job niet de key in documentstatistics? De query die je nu gebruikt om de id van documentstatistics op te halen (op een brakke manier) kan meerdere rows opleveren, maar jij gaat uit van 1.

De stored procedure verdient inderdaad nog wel wat aandacht . Misschien dat we inderdaad van tevoren al rijen moeten genereren zoals 4VAlien suggereert, dit zou de boel een heel stuk versimpelen, dan zijn we van het insert/update onderscheid af.

We gebruiken niet de id van de Job, omdat in onze database Jobs verwijderd kunnen worden en nieuwe Jobs toegevoegd kunnen worden, maar we wel de historie qua statistieken willen bewaren. Hiervoor gebruiken we daarom een GUID die bij het maken van een nieuwe Job gegenereerd wordt. Als we bij het opslaan van een Job dan rijen genereren voor in de DocumentStatistics tabel kunnen we de stored procedure vervangen door een simpele update-query, omdat we er dan vanuit kunnen gaan dat de bijbehorende rij bestaat. Dit lijkt me al een stuk beter dan de huidige stored procedure.

[Voor 37% gewijzigd door bigben04 op 24-06-2011 11:47]

[leuk_he]

Heel kort,

-"Select for update " gebruiken. in update statistics, helpt weer bij dat kleine gaatje.
-Altijd tabbellen locken in zellfde volgorde (dus wellicht bij update statistics eerst even de document locken for update.

-Een tabel locken kost je je multiuser performance.


Andere oplossing:

Als een transactie mislukt (door deadlock) de error afvangen (en transactie geheel rollbacken) en opnieuw proberen is ook een mogelijkheid. Ik weet echter niet of de deadlock detectie van MSSQL erg traag is of niet.

[bigben04]

leuk_he schreef op vrijdag 24 juni 2011 @ 13:57:
-"Select for update " gebruiken. in update statistics, helpt weer bij dat kleine gaatje.
-Altijd tabbellen locken in zellfde volgorde (dus wellicht bij update statistics eerst even de document locken for update.

Tnx, die constructie kende ik nog niet, dat kan zeker wel van pas komen. Daarmee kan ik ook nog aan de slag in de stored procedure.

-Een tabel locken kost je je multiuser performance.

Klopt, dat wil ik inderdaad het liefst vermijden.

Andere oplossing:

Als een transactie mislukt (door deadlock) de error afvangen (en transactie geheel rollbacken) en opnieuw proberen is ook een mogelijkheid. Ik weet echter niet of de deadlock detectie van MSSQL erg traag is of niet.

Dat heb ik her en der ook als mogelijke oplossing gevonden, maar dat vind ik niet echt een mooie oplossing, dat is meer om het probleem heen werken. Bedankt voor de tip in elk geval.

[Remus]

bigben04 schreef op vrijdag 24 juni 2011 @ 15:21:
[...]

Tnx, die constructie kende ik nog niet, dat kan zeker wel van pas komen. Daarmee kan ik ook nog aan de slag in de stored procedure.

Slaat dit terug op resources altijd in dezelfde volgorde locken? Dat is in ieder geval de standaard ('textbook') oplossing om deadlock te voorkomen. Niet alleen in databases, maar in het algemeen.

[bigben04]

Remus schreef op vrijdag 24 juni 2011 @ 17:01:
[...]


Slaat dit terug op resources altijd in dezelfde volgorde locken? Dat is in ieder geval de standaard ('textbook') oplossing om deadlock te voorkomen. Niet alleen in databases, maar in het algemeen.

Nee sorry, ik heb de post nog een beetje gehusseld, dat sloeg op de 'select for update'.