[MSSQL] Transaction deadlock - oplossingen?

[YopY]

Ik heb in Java een systeem (mee) ontwikkelt die stemmen afvangt van gebruikers (simpel gezegd). Dit is een systeem dat maandelijks enkele tienduizenden stemmen moet afvangen (tot 100.000, waarschijnlijk, het oude systeem (een PHP script) doet meestal tussen de 30.000 en 70.000 per maand).

Daar ik het niet nodig vindt om elke stem apart in de database op te gaan slaan, heb ik het zo ontwikkeld dat er een tabel is met daarin de velden, en een 'aantal stemmen'-kolom.

Hier komt er echter twee andere problemen om de hoek:

* Het uniek houden van de rijen (dwz dat er slechts één rij per unieke keuze geinsert wordt)
* Threading (dwz dat bovenstaand maar één keer uitgevoerd wordt, dat het niet mogelijk is om twee keer een insert te doen terwijl er eigenlijk één insert en één update gedaan moet worden).

Beide problemen dacht ik opgelost te hebben (na wat Google werk) met als resultaat onderstaande query:

SQL:

BEGIN tran
    UPDATE votes WITH (serializable)
    SET votes = votes + 1
    WHERE MijnElfId = @id
    AND playerId = @playerId
    AND position = @position

    IF @@rowcount = 0 BEGIN
        INSERT INTO votes VALUES (@id, @position, @playerId, 1)
    END
COMMIT tran

Deze query:

* Begint een transactie en zorgt ervoor dat er maar één query tegelijk uitgevoerd wordt
* Probeert een update uit te voeren
* Indien de update mislukt (bijgewerkte rijen = 0):
* Maakt een nieuwe rij aan met 1 stem.

Dit leek goed te werken tijdens de tests (waarin ik met honderd+ threads mbv Apache JMeter duizend+ stemmen gepost heb), echter bij de livegang van dit project ging de hele site offline (pijnlijk). Uit de logs kregen we de volgende melding:

code:

Dec 29, 2009 11:40:48 AM dit.dat.Dao.executeQuery

SEVERE: Transaction (Process ID 168) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

java.sql.SQLException: Transaction (Process ID 168) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Het lijkt er dus op dat een aantal threads tegelijk de query uit proberen te voeren, dat dit te lang duurt, en dat MSSQL 2000 een deadlock detecteert (of iets in die trant). Nu is / zijn mijn vraag / vragen als volgt:

1. Zit er iets fout in mijn query waardoor hij vast loopt?
2. Kan ik dit 'oplossen' door gewoon de query nogmaals uit te laten voeren?
3. Waarom zorgt dit ervoor dat de hele (database) server (die overigens stevig uitgevallen is) op zijn knieën gaat?
4. Hoe kan ik #3 voorkomen?
5. Is het omvormen naar een 'een rij per stem' structuur toch beter / sneller / veiliger / effectiever?
6. Of zit dit in Java? De stemmen worden dmv een servlet naar de server gestuurd, dus ook meerdere tegelijk.

Het systeem moet sowieso tientallen stemmen in een minuut kunnen verwerken (daar het stemmen elke maand uitgevoerd wordt en in het begin zeer populair zal zijn, verwacht ik).

[Matis]

Is het sws niet verstandig om je databasegebruik af te schermen met een Lock?
http://java.sun.com/j2se/...oncurrent/locks/Lock.html
Dan weet je of het aan jouw code ligt, of aan een ander stuk code.

Ik maak er in C# en C++ in MTA's erg veel gebruik van.

[ Voor 13% gewijzigd door Matis op 04-01-2010 11:44 ]

[cariolive23]

Met 100k records per maand ben je 1000 jaar verder voordat je een serieus aantal records in de database hebt staan... Ik zou gewoon INSERT's gebruiken en pas in de SELECT een berekening loslaten op de gegevens. Dit heeft ook als voordeel dat je meer mogelijkheden hebt, je kunt bv. ook gaan achterhalen wie wanneer stemt en wat het stemgedrag is. Dat is nu allemaal onmogelijk.

Met de juiste indexen hou je de snelheid er ook in, geen enkel probleem. Deze aanpak is niet veiliger of onveiliger dan je huidige aanpak, al kun je dubbelstemmers er met een betere aanpak ook uithalen.

[marco_balk]

Of het beter/meer optimaal is, weet ik niet zeker, maar persoonlijk zou ik eerst een check doen en niet direct een update:

SQL:

BEGIN tran
    IF EXISTS (SELECT MijnElfId FROM votes WHERE MijnElfId = @id)
    BEGIN
        UPDATE votes WITH (serializable) 
        SET votes = votes + 1 
        WHERE MijnElfId = @id 
        AND playerId = @playerId 
        AND position = @position
    END
    ELSE
    BEGIN
        INSERT INTO votes VALUES (@id, @position, @playerId, 1) 
    END 
COMMIT tran

[djluc]

Wellicht gewoon een update doen, als die mislukt met een affected_rows=0 dan de insert doen? Uiteraard met een unique index. Dat lijkt me de meest simpele en logische optie. Anders optie is standaard de opties al in de database zetten.

[roy-t]

djluc schreef op maandag 04 januari 2010 @ 12:05:
Wellicht gewoon een update doen, als die mislukt met een affected_rows=0 dan de insert doen? Uiteraard met een unique index. Dat lijkt me de meest simpele en logische optie. Anders optie is standaard de opties al in de database zetten.

Volgens mij kun je bij beter dit doen. (Eigen blog). in plaatst van die affected rows telkens te controleren.

Even belangrijkste stukje:

SQL:

CREATE PROCEDURE InsertUniqueUser
02  @Username nvarchar(50),
03  @Password nvarchar(50),
04  @LastName nvarchar(50),
05  @FirstName nvarchar(50),
06  @Email nvarchar(50),
07  @UserID int OUTPUT
08  AS
09  IF NOT EXISTS(SELECT Username FROM Users WHERE Username=@Username OR Email=@Email)
10   BEGIN
11   INSERT INTO Users
12   (Username, Password, LastName, FirstName, Email)
13   VALUES (@Username, @Password, @LastName, @FirstName, @Email)
14   SET @UserID = @@IDENTITY
15   END
16  ELSE
17   BEGIN
18   SET @UserID = -1
19   END

(Andere code natuurlijk, maar het gaat om de if not exists)

Verder plaatste iemand daarbij als comment de volgende link die ook erg interessant kan zijn:
http://weblogs.sqlteam.co...PDATE-Race-Condition.aspx

[ Voor 10% gewijzigd door roy-t op 04-01-2010 12:24 ]

[leuk_he]

die serialize zou ik verdenken, De deadlock komt aarschijnlijk enkel doordat er teveel hangt. (hoort niet... maar ja).

deadlock zelf 1 maar 1 transactie die vastloopt, dus niet je probleem.


Die logmelding is niet relevant voor het plat gaan.

als je perse denktdat dat er iets tussen de update en de insert kan komen(?) is merge wellicht iets voor je. als je perse de db verdenkt zou je de serialze eruit kunnen halen. Die lokct nogal wat soms. (hoewel als het over 2 statements gaat geen probleem zou opleveren)

[Janoz]

Ikzelf heb iets dergelijks een keer als volgt geimplementeerd (zonder transactie):

code:

update 
als rows = 0
  insert
  als unique constraint
    update

Mocht er dus een race probleem optreden omdat er 2 keer een insert geprobeert wordt dan wordt dit afgevangen door de tweede update. Die zal in dat geval altijd moeten slagen.

[Verwijderd]

Eventueel kun je inserts gebruiken, en dan eens per uur met een cron de inserts optellen, deze op de stack zetten, en je andere tabel weer clearen? (300 nieuwe inserts --> 300 bij je countertabel optellen, en de inserts trashen) Op die manier krijg je volgens mij geen racecondities, en loopt je database niet vol (Al vraag ik me af hoe bang je hier voor moet zijn, zoals hierboven ook al genoemd wordt.)

/edit

Enige raceconditie die je kan krijgen is als iemand wat insert terwijl jij al geteld heb, en vervolgens je temp tabel cleart. Hierdoor gaat zijn/haar stem verloren. Dit is vast met de normale methoden om racecondities te voorkomen te voorkomen (te voorkomen ).

/edit 2

Geen deadlock: Stel je telt er 300, dan gooi je er ook maar 300 weg met LIMIT 300. Wel even op de sorteervolgorde letten. Mocht het ooit voorkomen dat nummer 301 ondertussen net gevote heeft, gaattie tijdens de volgende run mee op de countstack

[ Voor 45% gewijzigd door Verwijderd op 04-01-2010 13:41 ]

[Malthus]

Is het niet mogelijk om de verschillende keuzes in de database op te slaan zodra deze optie beschikbaar komt (dus met aantal stemmen = 0). Dan hoef je voor het registreren van een stem alleen maar een update te gebruiken, en hoef je verder niets uit te vragen of er al een rij aanwezig is, of rekening te houden met race conditions of zo.

En ik weet niet of je een index (of zelfs meer dan één) op de tabel hebt liggen, maar het bijwerken van een index kan ook de nodige vertragingen veroorzaken.

[Verwijderd]

Ik zou iig zoveel mogelijk het UPDATE statement vermijden.

Betere optie is inderdaad om zoals eerder aangegeven eerst een SELECT te doen om te kijken of het record al bestaat. Zo ja alsnog een UPDATE uitvoeren en anders een INSERT.

Veel beter is natuurlijk om juist wel elke stem op te slaan in je database. Met een goed databasemodel en een aantal indexen moeten deze aantallen geen probleem opleveren. Ik weet niet precies hoe jouw systeem werkt maar je zou er natuurlijk ook voor kunnen zorgen dat eens in de zoveel tijd oude stemmingsrondes worden opgeruimd en je dus je database schoon houdt.

Zal ook nog even verder op je vragen ingaan:

1. Zit er iets fout in mijn query waardoor hij vast loopt?
Zie bovenstaand verhaal.

2. Kan ik dit 'oplossen' door gewoon de query nogmaals uit te laten voeren?
Dit gaat op dit moment enkel voor meer problemen zorgen aangezien je dan nog meer load naar je database genereerd.

3. Waarom zorgt dit ervoor dat de hele (database) server (die overigens stevig uitgevallen is) op zijn knieën gaat?

Wat is stevig uitgevallen? Je geeft aan dat je MSSQL 2000 gebruikt, deze is nogal verouderd en met de standaardversie kan je max. 2 gb aan geheugen gebruiken. MSSQL 2005 en 2008 bieden veel meer mogelijkheden zeker de 64bits versies.

4. Hoe kan ik #3 voorkomen?

Door de server te monitoren bij de verschillende load tests die je uitvoert. Je zou hier een trace voor kunnen gebruiken (MSSQL profiler), ook de traces van Windows kunnen behulpzaam zijn (geheugen/CPU/iops). Eigenlijk moet je load testen totdat je net het kritieke punt bereikt zodat je precies weet wat jouw "limiet" is.

Ik zou iig minimaal gaan voor MSSQL 2005 64 bits editie met min. 4 GB geheugen en 2 quad core processors. maar ik kan jouw situatie natuurlijk niet goed inschatten. Ook harde schijven zijn natuurlijk belangrijk. Zelfs in een high tech omgeving kan MSSQL erg slecht performen (gefragmenteerde indexen/datafiles, te grote datafiles en niet gepartioneerd en zo kan ik nog wel even doorgaan.

5. Is het omvormen naar een 'een rij per stem' structuur toch beter / sneller / veiliger / effectiever?
niet meer van toepassing

6. Of zit dit in Java? De stemmen worden dmv een servlet naar de server gestuurd, dus ook meerdere tegelijk.
niet meer van toepassing

[ Voor 58% gewijzigd door Verwijderd op 04-01-2010 23:36 ]

[Verwijderd]

[b][message=33210105,noline]
/edit 2

Geen deadlock: Stel je telt er 300, dan gooi je er ook maar 300 weg met LIMIT 300. Wel even op de sorteervolgorde letten. Mocht het ooit voorkomen dat nummer 301 ondertussen net gevote heeft, gaattie tijdens de volgende run mee op de countstack

LIMIT werkt niet in MSSQL:

SELECT top 10 FROM tabel

[YopY]

Interresante opties allemaal, ik zal ze eens tegen elkaar afwegen. Voorlopige reacties:

Matis schreef op maandag 04 januari 2010 @ 11:44:
Is het sws niet verstandig om je databasegebruik af te schermen met een Lock?
http://java.sun.com/j2se/...oncurrent/locks/Lock.html
Dan weet je of het aan jouw code ligt, of aan een ander stuk code.

Ik maak er in C# en C++ in MTA's erg veel gebruik van.

Dat zou een oplossing kunnen zijn, maar het is op zich niks meer dan het verplaatsen van het probleem van de databaselaag naar de tussenlaag. Persoonlijk vindt ik het mooier (en dit is waarschijnlijk persoonlijk) om alles in een enkele query te kunnen doen. Daar komt nog bij, hoe zie je dat dan? Dezelfde query die uitgevoerd wordt, of twee queries (een select en daaropvolgend een insert of update, of iets dergelijks)?

djluc schreef op maandag 04 januari 2010 @ 12:05:
Wellicht gewoon een update doen, als die mislukt met een affected_rows=0 dan de insert doen? Uiteraard met een unique index. Dat lijkt me de meest simpele en logische optie. Anders optie is standaard de opties al in de database zetten.

Dat wordt op zich nu ook gedaan - alleen wordt er mbv with(serializable) aan locking gedaan.


De IF NOT EXIST(select) van marco_balk en roy-t klinkt ook goed, maar krijg je dan geen last van threading issues? Dat wil zeggen, wanneer twee gebruikers tegelijk stemmen, kan in sommige gevallen de SELECT conditie twee maal 'tegelijk' false teruggeven, en de insert ook twee maal uitgevoerd. Dat is natuurlijk allemaal theoretisch, ik heb geen idee hoe MSSQL met dat soort situaties omgaat, laat staan hoe vaak het voorkomt, maar het kan gebeuren.

leuk_he schreef op maandag 04 januari 2010 @ 12:29:
als je perse denktdat dat er iets tussen de update en de insert kan komen(?) is merge wellicht iets voor je.

Dat zou op zich ideaal zijn voor deze situatie, ware het niet dat dit een feature is die pas in MSSQL 2k8 zit, en we (voorlopig) nog 2000 draaien. Een upgrade is wel in het verschiet, maar ik weet niet wanneer die gepland is, noch naar welke versie dat gaat.

Janoz schreef op maandag 04 januari 2010 @ 12:50:
Ikzelf heb iets dergelijks een keer als volgt geimplementeerd (zonder transactie):

code:

update 
als rows = 0
  insert
  als unique constraint
    update

Mocht er dus een race probleem optreden omdat er 2 keer een insert geprobeert wordt dan wordt dit afgevangen door de tweede update. Die zal in dat geval altijd moeten slagen.

Ah, double-checked locking of iets dat daarop lijkt. Vangt MSSQL automagisch de fout op als er tweemaal een insert gedaan wordt met eenzelfde primary key?

quote: epic12

Enige raceconditie die je kan krijgen is als iemand wat insert terwijl jij al geteld heb, en vervolgens je temp tabel cleart. Hierdoor gaat zijn/haar stem verloren. Dit is vast met de normale methoden om racecondities te voorkomen te voorkomen (te voorkomen ).

Dat er eens een stem verloren gaat is op zich (en op dit moment) niet zo erg, stemmen worden niet aan een gebruiker gekoppeld, gebruikers kunnen op een later tijdstip hun eigen stem niet meer bekijken, en op tienduizenden stemmen die lukken hebben een of twee stemmen die niet lukken weinig invloed.

Malthus schreef op maandag 04 januari 2010 @ 20:07:
Is het niet mogelijk om de verschillende keuzes in de database op te slaan zodra deze optie beschikbaar komt (dus met aantal stemmen = 0). Dan hoef je voor het registreren van een stem alleen maar een update te gebruiken, en hoef je verder niets uit te vragen of er al een rij aanwezig is, of rekening te houden met race conditions of zo.

Dat zou ook een oplossing zijn. Er zijn per stemming (euh) ongeveer 33x12 = ~400 stembare combinaties mogelijk, soms meer, soms minder. Dat zou ook nog kunnen.

En ik weet niet of je een index (of zelfs meer dan één) op de tabel hebt liggen, maar het bijwerken van een index kan ook de nodige vertragingen veroorzaken.

Nee, op het moment niet. De tabel slaat slechts stemmen op, en de tabel wordt alleen in de back-end, bij het berekenen van het aantal stemmen (en de percentages) ingelezen. Het uiteindelijke resultaat wordt door een redacteur aangevinkt, en op een andere manier (vraag asjeblieft niet welke, ) opgeslagen / weergegeven aan de gebruiker aan het eind van de stemperiode.

quote: psycot

Wat is stevig uitgevallen? Je geeft aan dat je MSSQL 2000 gebruikt, deze is nogal verouderd en met de standaardversie kan je max. 2 gb aan geheugen gebruiken. MSSQL 2005 en 2008 bieden veel meer mogelijkheden zeker de 64bits versies.

Goeie vraag, nu je het zo zegt. Het is een database die ladingen statistieken bevat voor een website van circa 250.000 bezoeken / dag. Ik krijg hierweg op het moment geen verbinding ermee anders zou ik nog meer statistieken opzoeken. Maar dat hij maar 2 GB aan geheugen gebruiken kan valt me wat tegen.

Maar voor de toekomst (ik hoop ergens dit jaar) staat een update gepland, nieuwe software, misschien zelfs een complete redesign van de (tien jaar oude) database. Maar dat is een grote klus, en heeft verder weinig te maken met deze issue.

Ik zal, zodra ik weer voor de betroffen partij werk (eind deze week), deze oplossingen proberen en afwegen. Zijn er nog tools die ik kan gebruiken die dit soort situaties kunnen simuleren? Een tooltje die parralel dit soort queries uit kan voeren met tientallen (zo niet honderden, om ook de zeldzame situaties te vinden) tegelijk?

[leuk_he]

Verwijderd schreef op maandag 04 januari 2010 @ 20:38:
Ik zou iig zoveel mogelijk het UPDATE statement vermijden.

Betere optie is inderdaad om zoals eerder aangegeven eerst een SELECT te doen om te kijken of het record al bestaat. Zo ja alsnog een UPDATE uitvoeren en anders een INSERT.

Huh? je stelt voor 2 statements voor 3 te vervangen. Dat levert je dus altijd 2 access op de database ipv soms 1 en soms 2. En in het voorbijgaan vergeet je het locking probleem. (wat als er iets gebeurd tussen select en insert in een andere sessie)

[whoami]

Waarom gebruik je een locking hint op dat UPDATE statement, ipv de transaction isolation level te specifieren op transactie-niveau ?

code:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
...
COMMIT TRAN

`

leuk_he schreef op dinsdag 05 januari 2010 @ 09:48:
[...]


Huh? je stelt voor 2 statements voor 3 te vervangen. Dat levert je dus altijd 2 access op de database ipv soms 1 en soms 2.

Als dat in een stored procedure gedaan wordt, is dat te verwaarlozen

En in het voorbijgaan vergeet je het locking probleem. (wat als er iets gebeurd tussen select en insert in een andere sessie)

Dat is op te lossen door transaction isolation level te specifieren, en evt een lock te specifieren als je de select doet.

[ Voor 57% gewijzigd door whoami op 05-01-2010 09:56 ]

[Verwijderd]

leuk_he schreef op dinsdag 05 januari 2010 @ 09:48:
[...]

Huh? je stelt voor 2 statements voor 3 te vervangen. Dat levert je dus altijd 2 access op de database ipv soms 1 en soms 2. En in het voorbijgaan vergeet je het locking probleem. (wat als er iets gebeurd tussen select en insert in een andere sessie)

Ik stel voor om het databasemodel om te gooien zodat elke stem wel wordt opgeslagen en je daarop een select kan uitvoeren voor uitkomsten van de desbetreffende stemming. De oplossing die ik eerst beschrijf gaat meer in op hoe hij het ook kan proberen zonder heel zijn applicatie overhoop te moeten gooien.

[Zoijar]

YopY schreef op maandag 04 januari 2010 @ 11:41:
* Begint een transactie en zorgt ervoor dat er maar één query tegelijk uitgevoerd wordt

Daar ga je al fout; transacties lopen ook gewoon door elkaar heen en vermijden op zich dus geen race condities etc. Zie ook commentaar van whoami.

[Janoz]

Nogmaals. Waarom doet iedereen zo moeilijk terwijl de (eerder door mij in pseudo code uitgdrukte) oplossing zo simpel is?

Nergens heb je locking nodig. Er is maar 1 mogelijkheid tot een raceconditie die keurig gedetecteerd wordt door een unique constraint.Het afhandelen van de situatie waarbij deze constraint optreed is ook duidelijk (alsnog een update doen).

Zolang elke query atomair uitgevoerd wordt heb je dus alle situaties mbt het raceprobleem afgedekt.

[Zoijar]

Janoz schreef op dinsdag 05 januari 2010 @ 14:52:
Nogmaals. Waarom doet iedereen zo moeilijk terwijl de (eerder door mij in pseudo code uitgdrukte) oplossing zo simpel is?

Ja, dat ziet er goed uit. Ik zie zo snel ook geen fout daarin.

[YopY]

Ter info, ik heb voor nu de oplossing van Malthus geimplementeerd (ook na overleg). In plaats van die hele query die in de TS staat, is het nu gelimiteerd tot slechts een 'update'.

Bij het opslaan van de selectie maakt het systeem nu een rij aan in de tabel per mogelijke keuze, 300nogwat rijen.

Wat nu moeilijker is is te bewijzen dat deze versie beter / veiliger is. Ik zit al met JMeter een poging te doen veel simultane queries uit te voeren (met 20 threads tegelijk / door mekaar), waarschijnlijk moet dit ook nog vanaf meerdere PC's. Probeer ook contact met de host te krijgen zodat hun de load op de server in de gaten houden. Zit alleen met een responstijd die soms oploopt tot 20 seconden, moet nog zien na te kijken waar dat door veroorzaakt wordt.

In ieder geval, bedankt voor de vele (behulpzame) reacties. Indien deze oplossing niet werkt probeer ik een volgende.