[PHP/MySQL] Row level locking

[ixi]

Ey! Ik ben bezig een systeem op te zetten in PHP icm MySQL (innodb) dat door veel mensen tegelijkertijd gebruikt gaat worden. Tot dusver heb ik nooit met transactions of table/row locking gewerkt, maar ik kwam gister tot de conclussie dat dit nodig gaat zijn. Na wat gezocht te hebben kreeg ik niet echt een concreet antwoord, dus heb ik m'n eigen 'lock' functie geschreven, versimpeld:

do { $result = query("INSERT INTO table_lock (table, row_id, timestamp) VALUES (..)");} while ($result == false);

Zo blijven alle scripts naast elkaar draaien en duurt het af en toe wat langer om te lock te krijgen. Als de insert succesvol is is de lock gemaakt en gaat het script verder; om de lock te verwijderen delete ik de row weer. Ik sla de timestamp op om deadlocks te voorkomen (lock mag max. 2 seconde duren).

Nu werkt dit allemaal wel redelijk netjes. Nadeel is dat er bij veel threads constant een 'insert'-fest bezig is die allemaal gruwelijk falen zolang er al een lock is; lijkt me niet erg efficient. Ik doe nu een usleep(50000) tussen de inserts en heb van table_lock een memory-tabel gemaakt om het wat te beperken, maar dat is maar behelpen natuurlijk.

De vraag dus: is er een betere manier om dit te bereiken? De bedoeling is dus dat zolang 1 record gelockt is geen enkel ander script dat record mag lezen of schrijven. De lock wordt altijd binnen hetzelfde script gemaakt en verwijdert (en zal dus nooit lang bestaan). Als er een lock is moeten de andere scripts geen foutmelding krijgen, maar even 'on hold' blijven staan.

[_JGC_]

Misschien dit eens lezen:
http://dev.mysql.com/doc/...en/innodb-lock-modes.html

Je kunt in PHP ook gewoon transaction queries gebruiken, zolang je connectie open is kan je alles in een transactie stoppen.

Het zelf bijhouden van locks is niet praktisch, al helemaal niet met een goede tablehandler zoals innodb die dat voor je kan doen.

[ixi]

De MYSQL documentatie heb ik helemaal gelezen. Hiermee krijgen m'n scripts echter een foutmelding als er een lock is, en blijven ze dus niet wachten tot ze een lock krijgen.

Misschien nog een toevoeging: de lock is nodig voor updates in bestaande rows. Ik heb een functie die ongeveer zo in elkaar zit:

$record = query("SELECT * FROM blah WHERE id = 1");
doe_iets_met_gegevens();
query("UPDATE blah SET ....");

Probleem is dus dat als 2 scripts tegelijkertijd draaien de wijziging van de eerste niet blijft bestaan.

[_JGC_]

Je kunt bij die select een exclusive lock aanvragen bij MySQL. Als je dat ding krijgt ga je verder, krijg je dat ding niet dan probeer je na een bepaalde timeout weer. Je verschuift je locking systeem van een tijdelijke memory table dan naar het locking systeem van innodb. Zolang alles in 1 scriptrun blijft is er niets aan de hand, ga je echter meerdere transacties met meerdere pagerequests doen, dan zal je zelf een soort locking moeten implementeren.

In dat laatste geval zou ik persoonlijk iets anders dan PHP pakken en met java servlets of iets vergelijkbaars gaan werken, daar kan je gewoon een servlet laten draaien die je synchronized declareert op het moment dat je iets met de database gaat doen, je frontend scripts gaan dan gewoon wachten tot ze de functie mogen uitvoeren om updates te gaan doen.

[igmar]

ixi schreef op donderdag 15 maart 2007 @ 22:57:
$record = query("SELECT * FROM blah WHERE id = 1");
doe_iets_met_gegevens();
query("UPDATE blah SET ....");

Probleem is dus dat als 2 scripts tegelijkertijd draaien de wijziging van de eerste niet blijft bestaan.

Waarom zet je die dingen niet in een transactie, desnoods met MySQL lock ?

[Verwijderd]

creeer gewoon een simple while loop voor je query dat op elke iteratie een lock aanvraagt. als die lock wordt verkregen verlaat je de loop en voer je je query uit. zoniet dan sleep je een tijdje en probeer je het later opnieuw.

kortom het is onzinnig om dit in php zelf te gaan bijhouden ook omdat wat gebeurt er als 2 scripts tegelijk in de tabel aangeven dat ze een lock hebben? precies. je verplaats het probleem en hebt eigenlijk helemaal geen lock. en dit probleem wordt alleen maar erger naarmate het aantal scripts toeneemt. dus laat dat locken over aan de enige die ook echt die lock kan garanderen: de database server zelf.

[ACM]

ixi schreef op donderdag 15 maart 2007 @ 22:57:
Misschien nog een toevoeging: de lock is nodig voor updates in bestaande rows. Ik heb een functie die ongeveer zo in elkaar zit:

$record = query("SELECT * FROM blah WHERE id = 1");
doe_iets_met_gegevens();
query("UPDATE blah SET ....");

Daar is 'SELECT ... FOR UPDATE' voor. Daarnaast kan het nuttig zijn een en ander in een (evt serializable) transaction te verpakken.

[ Voor 10% gewijzigd door ACM op 16-03-2007 15:53 ]

[Verwijderd]

Hierin worden de locking strategie-en besproken voor concurrent inserts

http://dev.mysql.com/doc/...n/concurrent-inserts.html

edit:
srry, dit geldt alleen voor MyIsam tables

[ Voor 17% gewijzigd door Verwijderd op 16-03-2007 16:23 ]