[PostgreSQL] Progressive locking?

[RetepV]

Of hoe dat dan ook heet...

Ik wil in PostgreSQL een transactie beginnen en dan met bijvoorbeeld een SELECT statement een row locken zodat die row niet meer gelezen kan worden door anderen totdat de transactie afgelopen is. Ik geloof dat dat progressive locking wordt genoemd.

Ik zou echter niet weten hoe ik dat moet doen.

Ik kan bij het SELECT statement de optie 'FOR UPDATE' meegeven, maar dan wordt het record alleen voor update's gelocked, terwijl ik er voor wil zorgen dat het record niet eens meer gelezen kan worden.

Iemand een idee?

Hmz, het probleem is wel veel ingewikkelder dan het hier lijkt, het gaat namelijk om caching van resultaten in verschillende threads, waardoor er concurrency problemen kunnen ontstaan. Ik heb namelijk een class die de resultaten cached, en die class wordt in verschillende threads gebruikt.

Ik zou het misschien op kunnen lossen door een globale semafoor/mutex te gebruiken in die class. Maar ik had het liever opgelost in de database zelf.

[Verwijderd]

Heeft dit niet meer met het isolatie niveau van je transactie te maken?

En gezien je concurrency probleem wil je dan waarschijnlijk het hoogste niveau (serializable)?

[ACM]

Is het echt zo'n groot probleem dat er af en toe toevallig een oudere cache geserveerd wordt ?

Als dat het wel is denk ik dat je beter idd een semafoor/mutex kan maken.
Maar had je hier al es gekeken trouwens?
http://www.postgresql.org/idocs/index.php?locking-tables.html

RowExclusiveLock

Acquired by UPDATE, DELETE, INSERT and LOCK TABLE for IN ROW EXCLUSIVE MODE statements.

Conflicts with ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock modes.

DAT lijkt me wat je zoekt?

[RetepV]

Nou, als je serializable gebruikt, dan ZIE je andere transacties niet, volgens de docs. Maar dat betekent niet dat ze niet tegengehouden worden.

Maar ik denk dat ik het probleem zelf nog niet helemaal goed begrijp/begreep. Ook met wat ik wilde hebben gaat het niet lukken. Het probleem is het volgende:

* Ik heb 2 threads.

* In deze threads heb ik een class die beide een SELECT hebben gedaan en de data uit de row lokaal gecached in een array (het is C++ overigens, maar ik probeer het taal-onafhankelijk te beschrijven).

* Nou wil ik in de eerste thread een getal in een record gaan updaten. Stel: ik trek 10 van de al aanwezige waarde af. Nu start ik daarvoor een transactie en lees het record opnieuw, waarna ik het ga aanpassen en het met een UPDATE query terug ga schrijven.

* Vanaf het moment van de laatste SELECT in de eerste thread wil ik zeker zijn dat de tweede thread het record niet meer opnieuw kan lezen totdat de transactie/update van het eerste record afgerond is. De tweede thread moet op het resultaat van de SELECT wachten totdat de transactie afgerond is.

Het probleem is dat ik er wel achter ben dat dit niet echt efficient is, want ik wil namelijk de tweede thread laten wachten omdat hij het record niet mag veranderen zo lang een andere thread de data kan veranderen. De tweede thread kan dan oude data lopen veranderen doordat hij nog oude data in de cache heeft staan. Maar zoals ik het hier beschrijf blokkeer ik _alle_ SELECT statements, en lezen mag in principe best, de tweede thread moet alleen maar wachten als hij wil gaan updaten.

Moeilijk probleem. Eigenlijk wil ik gewoon handmatig een lock op een record kunnen zetten, maar dat zit niet in de SQL taal, lijkt het .

Het hele doel van dit geneuzel is dat ik een generieke classdefinitie wil maken om vanuit C++ met mijn tabellen te praten, een beetje als OLE-DB van Microsoft.

[RetepV]

Op dinsdag 09 juli 2002 16:07 schreef RetepV het volgende:
Het hele doel van dit geneuzel is dat ik een generieke classdefinitie wil maken om vanuit C++ met mijn tabellen te praten, een beetje als OLE-DB van Microsoft.

Concreter, ik wil het volgende in mijn code kunnen doen:

code:

CTabel    Tabel;

Tabel.BeginUpdate( 1000 );
Tabel.get_CurRecord().Getal -= 10;
Tabel.EndUpdate();

Waarbij CTabel mijn class is, die bij een BeginUpdate() het record met ID 1000 opvraagt, de data cached en tegelijkertijd andere threads/objecten de toegang tot dat ene record weigert.

Na een EndUpdate() moet de data teruggeschreven worden en mogen andere threads het record benaderen.

Ik wil wel dat de data van het record in de tussentijd gewoon leesbaar blijft voor andere threads, alleen als die andere threads de data willen gaan updaten, moeten ze tegengehouden worden tot de ene thread de EndUpdate() heeft gedaan.

Ik ben bang dat ik het in mijn C++ code zelf zal moeten oplossen met behulp van een mutex of iets dergelijks. Alleen zit er nou eenmaal locking in de database, dus het is voor de hand liggender dat ik gebruik maak van het locking mechanisme.

[RetepV]

Op dinsdag 09 juli 2002 15:58 schreef ACM het volgende:
Maar had je hier al es gekeken trouwens?
http://www.postgresql.org/idocs/index.php?locking-tables.html
[..]
DAT lijkt me wat je zoekt?

Inderdaad, ik had dat ook al gelezen. Maar dat zijn toch automatische locks? Het probleem hiermee is volgens mij dat die RowExclusiveLock alleen bij UPDATE, DELETE, INSERT and LOCK TABLE instructies automatisch wordt gegenereerd. Terwijl ik dit met een SELECT statement wil bereiken.

Of snap ik de documentatie niet zo goed?

AccessExclusiveLock
Acquired by ALTER TABLE, DROP TABLE, VACUUM FULL and LOCK TABLE statements.

Conflicts with all modes (AccessShareLock, RowShareLock, RowExclusiveLock, ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock and AccessExclusiveLock).


Note: Only AccessExclusiveLock blocks SELECT (without FOR UPDATE) statement.

Dit klinkt overigens meer als wat ik wil, omdat het de SELECT statement blocked. Maar als het statement waarmee ik een lock kan zetten al blocked is het niet nodig de SELECT statement te blocken.

Wachten is geen probleem, en deadlocks kunnen in dit geval niet optreden.

[RetepV]

Ok, mijn intelligentie lijkt enigszins terug te komen . Ik heb het volgende gevonden:

If a target row found by a query while executing an UPDATE statement (or DELETE or SELECT FOR UPDATE) has already been updated by a concurrent uncommitted transaction then the second transaction that tries to update this row will wait for the other transaction to commit or rollback. In the case of rollback, the waiting transaction can proceed to change the row. In the case of a concurrent transaction commit, a serializable transaction will be rolled back with the message

ERROR: Can't serialize access due to concurrent update because a serializable transaction cannot modify rows changed by other transactions after the serializable transaction began

Dit is precies het omgekeerde van wat ik wil . Ik wil niet achteraf bij een update te horen krijgen dat het niet kon omdat het record in de tussentijd door een andere thread geupdate is. Ik wil vooraf kunnen weten of ik het record mag veranderen.

Met wat hierboven gequote is krijg ik achteraf pas te horen dat een update niet kan. Intussen heb ik al een heleboel berekeningen gedaan die ik dan opnieuw moet doen. Da's niet in de class op te lossen, aangezien de class voor de communicatie met de database zorgt en de applicatie voor de data in de database.

[RetepV]

(tsk, ik BLIJF posten , blijven jullie ook posten a.u.b.!)

Een mutex gebruiken in mijn class is wat lastig. Als ik 1 mutex gebruik, dan kan ik die zetten bij het begin van een update, maar in principe lock ik dan updates voor de totale tabel. Da's weer niet efficient omdat andere records best geupdate mogen worden.

Dus zal ik een soort lock-tabel moeten gaan bijhouden, gevuld met records die andere threads niet mogen updaten. Thread-safe toegang tot die tabel kan ik dan regelen met behulp van een mutex.

Alleen is dat precies wat een database doet met zijn eigen lock-tabel, dus het bestaat al en dan ben ik iets aan het implementeren wat ze al voor me in de database hebben opgelost .

[ACM]

Zou je je ook willen aanwennen wat meer de edit-knop te hanteren?

Je voegt wel zaken toe, maar het zijn steeds aanvullingen op je vorige punten.

Je moet trouwens ook nog naar de LOCK TABLE syntax kijken icm de link die ik je al gaf...

[RetepV]

Op dinsdag 09 juli 2002 16:51 schreef ACM het volgende:
Zou je je ook willen aanwennen wat meer de edit-knop te hanteren?

Hmm, ik dacht dat ik mijn berichtjes maar 2 keer mocht editten. Daarnaast is het voor andere mensen volgens mij vervelend als ze mijn berichtje lezen, reageren en na de posting zien dat mijn posting veranderd is. Maar goed, misschien heb je gelijk . Ik denk zelf dat het weinig uitmaakt, behalve misschien dat de thread steeds omhoog geschopt wordt.

Je moet trouwens ook nog naar de LOCK TABLE syntax kijken icm de link die ik je al gaf...

Hmz, ik dacht dat je met LOCK TABLE een volledige tabel ging locken, maar na nog een keer goed (of beter gezegd: beter) lezen, zie ik dat ik dat verkeerd had. Met LOCK TABLE geef je de locking mode aan voor volgende opdrachten. En dus is dat blijkbaar juist wat ik zocht...

Ik ga er eens even mee experimenteren, TNX! What can I say, als jullie niet hadden gereageerd was ik nu misschien nog aan het zoeken . Helaas was SQL nog niet de standaard toen ik op school zat, ik heb nog geleerd met DBase en Clipper te werken .

[ACM]

LOCK TABLE is erg database specifiek hoor
Soms doet het alleen maar wat de naam suggereert en soms meer.
Het was min of meer toeval dat het bij Postgresql wel kan

[RetepV]

Op dinsdag 09 juli 2002 17:26 schreef ACM het volgende:
LOCK TABLE is erg database specifiek hoor
Soms doet het alleen maar wat de naam suggereert en soms meer.
Het was min of meer toeval dat het bij Postgresql wel kan

Yep, ik zie het in de docs ook staan dat het PostgreSQL specifiek is.

Maar het werkt! Als ik in de ene thread een 'LOCK TABLE Table IN ROW EXCLUSIVE MODE' doe, dan wordt bij de eerste SELECT het betreffende record gelocked. Als ik dan in de tweede thread hetzelfde probeer te doen, dan blijft die hangen op de SELECT totdat de transactie bij de eerste thread afgelopen is.

Liever had ik een errormelding teruggekregen zodat in het geval van een deadlock ik zelf actie kan ondernemen (b.v. een timeout genereren), maar dit zorgt er al voor dat mijn database-acties op applicatieniveau serialized zijn. Zo lang ik zelf geen deadlock inprogrammeer maakt het niet zo uit.

Tjonge, ik ben geen newbie in programmeren, maar database gebeuren is toch wel een hele tak van sport waar ik nog aardig wat in kan leren . Het ergste is nog het jargon, ik weet waar ik over praat, maar omdat ik het jargon niet ken kan ik het niet goed uitleggen .

Tnx aan iedereen die gereageerd heeft, en ACM: jij in het byzonder!

Ik wordt overigens steeds blijer dat ik voor PostgreSQL heb gekozen in plaats van MySQL .

[Verwijderd]

Je hebt je probleem al opgelost, maar toch nog even dit:

Nou, als je serializable gebruikt, dan ZIE je andere transacties niet, volgens de docs. Maar dat betekent niet dat ze niet tegengehouden worden.

Juist dat je de andere transactie totaal niet ziet betekent toch impliciet dat je je beschreven concurrency probleem niet op kan treden?
Ofwel, waarom laat je het je dbms niet afhandelen? Je hoeft dan tenminste niet met (dead) locks rekening te houden. Transacties zijn juist bedoeld om dat gerotzooi met locks te voorkomen.
Uiteraard moet het isolatie niveau van je transactie wel hoog genoeg ingesteld zijn. Bovendien weet je dan ook zeker dat als het systeem crashed midden in de transactie, dat je dbms er voor zorgt dat de transactie nooit heeft plaatsgevonden.

Zie evt. http://www.postgresql.org/idocs/index.php?sql-begin.html :

In SERIALIZABLE mode queries will see only changes committed before the entire transaction began (actually, before execution of the first DML statement in a serializable transaction).

Tenzij ik je probleem verkeerd begrepen hebt en je ervoor wilt zorgen dat thread 2 pas aan de beurt mag komen nadat thread 1 een wijziging heeft gemaakt (maar dan heb je voordat je met queries begint al een concurrency probleem).