[pgsql/vb] SELECT FOR UPDATE lockups

[Skinny]

Hoi allemaal,

Voor een project heb ik een Postgres database op een lokale linux server staan. Hier ben ik een front-end voor aan het maken met behulp van Visual Basic (6) en de ODBC drivers van http://odbc.postgresql.org/. Dit werkt als een trein, op (jaja) een ding na.

Het programma moet door meerdere gebruikers tegelijkertijd gebruikt kunnen worden. Met name de tabel 'artikel' moet door meerdere mensen tegelijk bewerkt kunnen worden.

Nu heb ik na wat lezen/zoeken het "SELECT FOR UPDATE" statement gevonden. Dit "locked" het record wat je gelezen hebt, totdat je de transactie commit/rollback-ed.

Pseudo

code:

DB.Execute "SELECT bla FROM artikel FOR UPDATE"

// Bewerk artikel

DB.Commit

Voer ik het programma op twee verschillende pc's uit dan kan ik op PC1 netjes een artikel openen, welke vervolgens op PC2 niet geopend kan worden. Er wordt echter geen time-out/error gegenereerd bij het uitvoeren van de "SELECT FOR UPDATE". VB blijft hier gewoon op hangen, totdat ik op PC1 het record weer vrijgeef.

Ik heb al verschillende dingen als de "ConnectionTimeout" en de "CommandTimeout" properties gebruikt, maar zonder resultaat. Toen kwam ik erachter dat als ik gewoon met de command-line client van postgres een "SELECT" doe op een record dat in VB gelocked is, er ook geen foutmelding/timeout optreedt. Het programma wacht gewoon totdat de transactie voltooid is. Op zich kan dit best handig zijn, maar in mijn geval wil ik de gebruiker gewoon melden dat het record in gebruik is en dat ie het later nog maar eens moet proberen.

Iemand een idee hoe ik dit gedrag van postgres kan veranderen ? (zowel de admin- als de user manual van postgres boden geen soelaas)

[Verwijderd]

Ik neem aan dat je transactie kort is en niet afhankelijk is van user input. Daarmee doel ik op het feit dat gedurende het uitvoeren van //Bewerk artikel
niet gewacht gaat worden op interactie van een gebruiker

Als je een record begint vast te houden, zodra je dit begint te wijzigen zijn de rapen gaar in een multi-user omgeving.

Ik raad je dan ook aan eens te zoeken op lock types.

Er zijn er een aantal, voor de volledigheid zal ik de meest belangrijke voor jouw geval even noemen.

Houdt er rekening mee dat je locks op Table, Page en Row level kunt hebben! En verder ga ik even uit van transaction isolation level Read Committed (Over Isolation levels is er genoeg literatuur te vinden)

1 Shared lock --> Bij dit type lock kan een andere gebruiker weliswaar de data lezen, maar niet wijzigen.
2 Exclusive lock --> Geen enkele andere gebruiker kan de data lezen. (Kun je ook alleen maar krijgen, indien geen enkel ander proces een shared lock heeft op datgene waar jij de exclusive lock nodig hebt)

Ik concludeer uit je verhaal dat jij voor je multi-user omgeving aan pessimistic locking doet. (Als je data haalt, houdt je het meteen vast zodat niemand het kan wijzigen)

Je kunt ook gebruik maken van optimistic locking. Daarvoor moet je gebruik maken van timestamps (Weet niet of Postgress dit ondersteund, maar neem aan van wel)

Dat houdt in dat je aan je tabel een timestamp attribuut moet toevoegen en bij je updates het volgende naar buiten moet sturen. In onderstaande code wordt je een voorbeeld gegeven van het gebruik van een timestamp en dit geldt voor een Sybase omgeving!!

update tabelX
set attribuutA = "WeetIKVeel"
where Primary_key = "Ditte" and
tsequal(timestampAttribuut, MijnTimestamp)

--Indien er geen records aangepast zijn, heeft een andere gebruiker
--het record aangepast / verwijderd
if @@rowcount = 0
begin
--record is door iemand anders aangepast
--Reageer dan op een of andere wijze
end

Een timestamp heeft de volgende eigenschap. Indien iemand anders het record gewijzigd heeft, dan wordt de timestamp ook automatisch aangepast (Onder Sybase). Het grote voordeel van het gebruik van timestamps is, dat je andere gebruikers niet onnodig blokkeert.

NB Binnen een transactie moet je ervoor zorgen dat er nooit ofte nimmer een gebruikersinteractie gevraagd is, want als die gebruiker gaat pauzeren, houdt hij de boel onnodig lang vast, waardoor andere gebruikers geblockt worden.


Ik hoop dat ik je een beetje op weg heb geholpen

[Skinny]

Bedankt voor je uitgebreide reply. Ik weet echter niet of ik er iets mee kan in mijn geval.

Het klopt inderdaad dat er binnen een transactie geen user-input nodig is. Wat er in mijn geval gebeurd is dat er in een "grid" een artikelenlijst word weergegeven. Bij dubbelklikken hierop verschijnt een "edit"-formulier met het gewenste artikel.

De gegevens hiervoor worden uit een remote database getrokken. Hier wordt dus een "SELECT FOR UPDATE" gebruikt om zo het artikel af te schermen voor verdere gebruikers. Een lokale kopie van deze gegevens wordt gebruikt om de velden te vullen. Na het bewerken worden er gechecked of alle gegevens kloppen, waarna deze gegevens in de database bijgewerkt moeten worden. Hierna wordt het record weer vrijgegeven.

Als een gebruiker een artikel geopend heeft en een andere gebruiker start het programma, krijg ik gewoon de hele artikellijst te zijn (klopt) inclusief het geopende artikel. Bij dubbelklikken op andere artikelen gaat alles natuurlijk goed. Echter dubbelklik ik op een artikel wat reeds geopend is, blijf mijn programma wachten totdat de andere gebruiker het artikel gesloten heeft.

De reden waarom ik die "SELECT FOR UPDATE" gebruik en niet een normale "SELECT", is dat ik hiermee dacht te voorkomen dat gebruiker1 een artikel bewerkt, maar nog niet opgeslagen heeft en dat gebruiker2 precies hetzelfde doet. Als gebruiker1 het artikel opslaat, wordt het daarna weer overschreven door gebruiker2 (afhankelijk van wie het het eerst opslaat). Heel simpel gezegd kan je dit ook bereiken door een attribuut "locked" op te nemen in je tabel, en die te zetten op het moment dat je een artikel opent. Dit lijkt mij echter een ontzettend groffe en niet nette oplossing en dacht dat "SELECT FOR UPDATE" mij misschien verder kon helpen. Tot op heden echter niet.

Ik ga nu even zoeken/lezen naar/over die timestamps. Daar moet vast wel meer info over te vinden zijn. Ondertussen blijven hints natuurlijk welkom

[Verwijderd]

Als die "SELECT FOR UPDATE" niet de juiste oplossing biedt voor je dan kan je ook kiezen om een extra veld in je tabel op te nemen (bv Locked) en die op het moment dat iemand een artikel gaat editen op True zet zodat iemand anders het niet kan openen. Daar kan je dan een controle op inbouwen. Als de gebruiker dan klaar is dan kan je Locked weer op False zetten.
Hou er dan wel rekening mee dat je periodiek het veld Locked moet scannen op 'valse' locks zodat er niet artikelen onnodig onbereikbaar zijn.

Succes!

[Verwijderd]

Verwijderd schreef op 30 oktober 2002 @ 09:43:
Als die "SELECT FOR UPDATE" niet de juiste oplossing biedt voor je dan kan je ook kiezen om een extra veld in je tabel op te nemen (bv Locked) en die op het moment dat iemand een artikel gaat editen op True zet zodat iemand anders het niet kan openen. Daar kan je dan een controle op inbouwen. Als de gebruiker dan klaar is dan kan je Locked weer op False zetten.
Hou er dan wel rekening mee dat je periodiek het veld Locked moet scannen op 'valse' locks zodat er niet artikelen onnodig onbereikbaar zijn.

Succes!

Dat raad ik dus ten zeerste af (Een veld opnemen en daar de waarde op Locked te zetten).

Want als om wat voor reden dan ook de applicatie knalt (en dat is zeer realistisch, netwerk kan platgaan, connectie kan op wat voor reden dan ook verloren gaan, e.t.c.), blijft dat attribuut de waarde locked houden en kan niemand dat record aanpassen.

[xoror]

wat je ook kan doen is op het moment dat je gaat updaten, de update query zo formuleren dat alle oude velden erin staan.

dus bijv.

update table set veld1=x where veld1 = oude_veld and veld2 = oude_veld2 ...

je kan controleren of de query goed is uitgevoerd. zo ja -> niemand heeft gewijzigd van het moment dat je het heb opgehaald. zo nee -> iemand heeft lopen wijzigen En lopen updaten. je kan dan mooie fout melding geven.

natuurlijk is dit geen oplossing voor je lock probleem, maar het is gebruiksvriendelijker omdat alle users de records nog gewoon kunnen blijven opvragen ook als iemand het edit. Het gebeurd toch niet vaak dat precies meer mensen tegelijk aan een row zitten te werken....
Als je tevens een lastedit column oid bijhoudt, kan je meteen ook laten zien wie het ge-edit heeft. Dan kan je naar evt naar die persoon stappen om een en ander te bespreken

lijk me veel beter dan compleet dat record locken.

[Verwijderd]

Skinny schreef op 30 oktober 2002 @ 09:36:
De gegevens hiervoor worden uit een remote database getrokken. Hier wordt dus een "SELECT FOR UPDATE" gebruikt om zo het artikel af te schermen voor verdere gebruikers. Een lokale kopie van deze gegevens wordt gebruikt om de velden te vullen. Na het bewerken worden er gechecked of alle gegevens kloppen, waarna deze gegevens in de database bijgewerkt moeten worden. Hierna wordt het record weer vrijgegeven.

Als een gebruiker een artikel geopend heeft en een andere gebruiker start het programma, krijg ik gewoon de hele artikellijst te zijn (klopt) inclusief het geopende artikel. Bij dubbelklikken op andere artikelen gaat alles natuurlijk goed. Echter dubbelklik ik op een artikel wat reeds geopend is, blijf mijn programma wachten totdat de andere gebruiker het artikel gesloten heeft.

info over te vinden zijn. Ondertussen blijven hints natuurlijk welkom

Hieruit kan ik opmaken dat je bij een dubbelklik inderdaad aan pessimistic locking
doet.

In jouw geval is er dus toch sprake van een gebruikersinteractie, want zodra de gebruiker een artikel begint te editeren, wordt die update lock gelegd. Indien de gebruiker gaat pauzeren, moet eenieder die dat artikel wil editeren gewoon wachten, totdat de persoon in kwestie het artikel gesloten heeft (en dat kan dan in het ergste geval uren duren)

Je wilt in principe voorkomen dat wijzigingen van een gebruiker niet overschreven worden door andere gebruikers en dan zul je dus gebruik moeten maken van een timestamp mechanisme. Voordat je begint te editeren, kijk je na of de timestamp nog steeds ongewijzigd is en mocht dat het geval zijn, dan ben je zeker dat er geen wijzigingen hebben plaatsgevonden. Mocht dat niet zo zijn, dan kun je een melding geven dat een andere gebruiker dit artikel reeds heeft aangepast en de nieuwe waarden (indien artikel niet verwijderd is in de tussentijd uiteraard) ophalen om vervolgens te gaan editeren.

Voor het bewaren kun je dan de eerder beschreven update (met timestamp controle bedoel ik) uitvoeren. Mocht iemand anders in de tussentijd dat artikel reeds hebben gewijzigd, dan kun je dit de gebruiker melden en hem (jammer maar het kan nu niet eenmaal anders, want je wilt niet dat de wijzigingen van andere gebruikers verloren gaan) de wijzigingen op dat aangepaste record laten uitvoeren.

Volgens mij kost het je wel een beetje tijd om met timestamps te leren werken, maar ik kan je garanderen dat dit zeer goed werkt. Ons team maakt ook software voor een multi-user omgeving waar 300 concurrent users op het systeem bezig zijn en het werkt perfect op deze manier. Er treden nauwelijks blocks op en de wijzigingen van andere gebruikers worden gegarandeerd niet overschreven.

[Skinny]

Oke, het lijkt me duidelijk dat die "timestamp"'s wel een goede oplossing kan bieden.

In deze situatie is het dus zo dat iedereen ten allen tijde een artikel kan openen voor bewerking. Bij opslaan van wijzigingen wordt gecontroleerd of de timestamp die ik terug kreeg bij het openen van artikel nog steeds aanwezig is in database. Zo ja, dan bijwerken en zo niet, dan melding "iemand anders heeft gewijzigd".

Heb ik het zo goed begrepen ?

[Verwijderd]

Zo ongeveer wel. Je moet er wel rekening mee houden dat het om het timestamp attribuut gaat van dat artikelrecord (Dus je moet testen of de timestamp van het artikel nog hetzelfde is)

Het gaat als volgt:
1 Je leest de records binnen inclusief timestamp attribuut. (Ieder record heeft dat timestamp attribuut)
2 Na de dubbelklik kun je evt. nagaan a.d.h.v. het timestamp attribuut of het artikel record reeds gewijzigd is.
3 Is het artikel gewijzigd, dan meld je dit aan de gebruiker, ververst het artikel en geeft hem het record inclusief laatste wijziging terug (Je haalt dus weer record op inclusief timestamp indien record aangepast is)
4 Tijdens het bewaren doe je de update van het record inclusief timestamp vergelijking in where clausule (zoals eerder beschreven)
5 Mocht het artikel reeds gewijzigd zijn, dan meld je dit (Er wordt dan immers niets gewijzigd en dat kun je als het goed is nagaan) en herhaal je het verhaal vanaf stap 3

[Skinny]

Top!

Ik ga er lekker mee aan de slag. Nogmaals bedankt!

[Verwijderd]

Geen dank, blij je op weg te hebben geholpen. Succes met je project