Mehrfach - Select verhindern

[thomas69]

Hallo,

ich muss mit mehreren Clients auf eine (MySQL-) DB zugreifen.
Die Clients sollen sich aus dem Datenpool je 1 Datensatz herausnehmen und die
anderen Datensätze den anderen überlassen. Es darf jedoch KEINE Überschneidung
der selektierten DS geben.
Wie kann man das lösen ? Mir fehlt z.Z. jeder Ansatz (Update / Delete?) .
PS: Das ganze soll mehrfach je Sekunde laufen.

Danke für eure Hilfe

[mortino]

Das macht man über Locks.
Entweder kann die Datenbank eh schon Datensatzweise Locks absetzen (was ich bei MySQL nicht glaube, aber auch nicht genau weiss) oder du müsstest so etwas in einfacher Form selbst machen.

Tabelle Lock mit dem PK der Tabelle welche du lockst und einem user (id oder eindeutiger name des clients). Dann musst nur noch vor dem 'ergreifen' des nächsten Datensatzes auf die Locks schauen, ob du auch darfst und falls ja, dann einen neuen setzen.
Locks löschen nicht vergessen nach dem bearbeiten.
Das übliche locken halt

Wenns Zeitkritisch wird (mehrfach pro Sekunde) kannst das über ne stored procedure oder ähnliches lösen

[thomas69]

Nochmal Hi,

muss man in stored procedure auch Lock verwenden?
Ich finde leider kein Angaben wie "sicher" stored procedure sind.
Ich habe mir komplexe DB-Aufgaben (trigger,stored procedure,...) meist
verkniffen, um von den DBs nicht so Abhänig zu sein. Bis jetzt lies
sich alles über mehr oder weniger ausgefeilte Querys lösen.

[isabeau]

Mit diesen Suchbegriffen findest du bei google eigentlich eine ganze Menge zur ersten Frage:
Datensatz sperren optimistisch pessimistisch

[thomas69]

Hi isabeau,

optimistisch, pessimistisch, ... kenne ich nur von ADO und von MSSQL.
Dort funktioniert das GANZE aber nicht. MS nennt dies "Dirty Reads".
Ich benötige aber eine 100% funktionierende Lösung. Dieses "fehlverhalten"
viel mir erst bei höhern Lasten auf, da ich mich anfangs auch auf eine richtige Implemtierung verlassen hatte. Wenn ich nicht 100% Funktion (+Tempo) erhalten
kann, müsste ich mir evtl. einen Server(der nur die Ergebnisse verteilt) selbst schreiben. Aber das ganze sollt doch über eine DB lösbar sein!?
Hat jemand Erfahrungen mit Locks?

[isabeau]

Öhm, ich meinte eigentlich optimistisch oder pessimistisch selber sperren.
Also z.B. eine Übersicht über alle Datensätze der Tabelle allen Benutzern zur Verfügung stellen.

Wählt ein Benutzer einen Datensatz zur Bearbeitung aus, sofort ein Flag setzen, dass dieser Datensatz für alle anderen nur noch Lesezugriff hat. Nach der Bearbeitung beim Speichern das Flag wieder entfernen.

Oder ein Benutzer wählt einen Datensatz zur Bearbeitung aus, dann diesen merken. Vor dem Speichern vergleichen, ob ein anderer Benutzer den Datensatz inzwischen verändert hat. Wenn "nein", dann speichern.

[tntnet]

Du fügst ein Feld in der Tabelle ein, welches den Zustand "in bearbeitung" merkt. Dann suchst Du Dir einen Satz aus, welcher nicht in bearbeitung ist und aktualisierst das Feld. Aber nur wenn es nicht bereits gesetzt ist. Wenn die Aktualisierung erfolgreich war, hast Du einen Satz. Und das ganz ohne Sperren. Im Pseudo-code etwa so:

do
{
  row = db.select("select * from tabelle where not inbearbeitung limit 1");
  if (not found)
    throw exception("es gibt keine freien Sätze");
  update tabelle set inbearbeitung = 1 where pk = row.pk and not in bearbeitung;
while (anzahl aktualisierter Sätze == 0);
-- hier hast Du in row einen Satz, der Dir ganz persönlich gehört

Dadurch, daß das update nicht nur den Primärschlüssel von row abfragt, sondern zusätzlich die Selektionsbedingung nochmal, wird verhindert, daß zwischen den select und dem update jemand anderes den Satz zur Bearbeitung markiert.

Tntnet

[Neku]

tntnet schrieb:

Du fügst ein Feld in der Tabelle ein, welches den Zustand "in bearbeitung" merkt. Dann suchst Du Dir einen Satz aus, welcher nicht in bearbeitung ist und aktualisierst das Feld. Aber nur wenn es nicht bereits gesetzt ist. Wenn die Aktualisierung erfolgreich war, hast Du einen Satz. Und das ganz ohne Sperren. Im Pseudo-code etwa so:

do
{
  row = db.select("select * from tabelle where not inbearbeitung limit 1");
  if (not found)
    throw exception("es gibt keine freien Sätze");
  update tabelle set inbearbeitung = 1 where pk = row.pk and not in bearbeitung;
while (anzahl aktualisierter Sätze == 0);
-- hier hast Du in row einen Satz, der Dir ganz persönlich gehört

Dadurch, daß das update nicht nur den Primärschlüssel von row abfragt, sondern zusätzlich die Selektionsbedingung nochmal, wird verhindert, daß zwischen den select und dem update jemand anderes den Satz zur Bearbeitung markiert.

Tntnet

Theoretisch ist es jetzt aber noch möglich, dass ein anderes Programm den gleichen Datensatz holt, während bei deinem Code z.B. Zeile 4 'abgearbeitet' wird.
Besser wäre es, wenn sich ein Client erst die Exklusivrechte setzt und erst danach den Datensatz abholt. Also das ganze etwa so:

($ID ist die eindeutige ID des jeweiligen Clients - möglich wäre auch eine Zufallszahl aus einem sehr großen Zahlenbereich)

UPDATE tabelle SET clientID = $ID WHERE clientID = 0 ORDER BY eintragszeit ASC LIMIT 1
  -- Der Client ordnet sich selbst einen Datensatz zu, der noch nicht verwendet wird und dabei am ältesten ist.

SELECT * FROM tabelle WHERE clientID = $ID ORDER BY eintragszeit ASC LIMIT 1
  -- Der Client holt einen reservierten Datensatz ab, der am ältesten ist.

Es wäre ebenso möglich, dass Clients sich so mehrere Datensätze reservieren und dann abarbeiten.

[tntnet]

Neku schrieb:

Theoretisch ist es jetzt aber noch möglich, dass ein anderes Programm den gleichen Datensatz holt, während bei deinem Code z.B. Zeile 4 'abgearbeitet' wird.

Nein. Deswegen gibt es dieses "and not in bearbeitung" im Update und die Schleife. Wenn jemand wärend Zeile 4 den Satz in bearbeitung setzt, greift das Update nicht mehr, die Anzahl der aktualisierten Sätze ist 0 und es wird ein weiterer Schleifendurchlauf gemacht.

Tntnet

[thomas69]

Hi, ich war kurz verhindert.
Ich hatte auch schon an solche (ähnliche) Lösungen gedacht, aber sie schienen mir
zu unsicher und zu langsam. Für jede Client-Abfrage 2 min. Querys?!
Die Idee von tntnet funkt nur, wenn nur ein Client arbeiten muss, es sollen
jedoch quasi alle gleichzeitig arbeiten. Ich war der Meinung, das es auf
DB - Ebene eine Möglichkeit gibt, mit der man einen DS "längere Zeit" für alle anderen sperren kann.

[Neku]

tntnet schrieb:

Neku schrieb:

Theoretisch ist es jetzt aber noch möglich, dass ein anderes Programm den gleichen Datensatz holt, während bei deinem Code z.B. Zeile 4 'abgearbeitet' wird.

Nein. Deswegen gibt es dieses "and not in bearbeitung" im Update und die Schleife. Wenn jemand wärend Zeile 4 den Satz in bearbeitung setzt, greift das Update nicht mehr, die Anzahl der aktualisierten Sätze ist 0 und es wird ein weiterer Schleifendurchlauf gemacht.

Tntnet

Du setzt den Datensatz aber auf 'in Bearbeitung' nachdem du ihn abgerufen hast, d.h. es kann passieren, dass zwei Clients den Datensatz abrufen und gleichzeitig auf 'in Bearbeitung' setzen.

thomas69 schrieb:

Hi, ich war kurz verhindert.
Ich hatte auch schon an solche (ähnliche) Lösungen gedacht, aber sie schienen mir
zu unsicher und zu langsam. Für jede Client-Abfrage 2 min. Querys?!
Die Idee von tntnet funkt nur, wenn nur ein Client arbeiten muss, es sollen
jedoch quasi alle gleichzeitig arbeiten. Ich war der Meinung, das es auf
DB - Ebene eine Möglichkeit gibt, mit der man einen DS "längere Zeit" für alle anderen sperren kann.

An meiner Lösung ist weder etwas unsicher noch langsam, wie kommst du da drauf?Wenn es dennoch so ist dann erklär mir bitte wieso.

[thomas69]

Hi .

Die Lösung von tntnet halte ich aus den selben Gründen wie Neku für etwas
unsicher + der Möglichkeit in einer Endlosschleife zu enden. Da jeder Client für sich und schnellstmöglich arbeitet, gibt es keine Synchronisation. Aus Erfahrung weis ich, dass immer einer der Clients schneller ist als die anderen ....
Das "langsam" bezieht sich nur auf die 2 Querys. Bei mehreren Clients
z.Z. 7, später evtl. noch mehr, und mehreren Abfragen pro Sekunde , will ich den
Overhaed in grenzen halten. Die Clients laufen nicht unbedinngt auf dem selben Rechner.

[Fincki]

Unter Oracle kann man es wie folgt lösen:

select felder
  from tabelle
 where dingsfeld = 'bums'
   for update of dingsfeld nowait;

Das sperrt genau den selectierten Satz. Der Satzschutz bleibt solange bestehen bis ein COMMIT oder ein ROLLBACK gefahren wird. Bei Oracle wird auch immer nur genau der entsprechende Satz gesperrt. Andere DBMS locken ganze Bereiche.

[tntnet]

Neku schrieb:

tntnet schrieb:

Neku schrieb:

Theoretisch ist es jetzt aber noch möglich, dass ein anderes Programm den gleichen Datensatz holt, während bei deinem Code z.B. Zeile 4 'abgearbeitet' wird.

Nein. Deswegen gibt es dieses "and not in bearbeitung" im Update und die Schleife. Wenn jemand wärend Zeile 4 den Satz in bearbeitung setzt, greift das Update nicht mehr, die Anzahl der aktualisierten Sätze ist 0 und es wird ein weiterer Schleifendurchlauf gemacht.

Tntnet

Du setzt den Datensatz aber auf 'in Bearbeitung' nachdem du ihn abgerufen hast, d.h. es kann passieren, dass zwei Clients den Datensatz abrufen und gleichzeitig auf 'in Bearbeitung' setzen.

Ich versuche es halt noch einmal zu erklären.

Ich setze ihn im Update nur dann in Bearbeitung, wenn er nicht von jemand anderem gerade in Bearbeitung gesetzt wurde. Wenn nach dem Select also jemand anderes das Flag setzt, greift die Bedingung "and not in bearbeitung", so daß das Update den Satz eben nicht aktualisiert und die Anzahl der aktualisierten Sätze 0 ist, so daß ein weiterer Schleifendurchlauf gemacht wird. Das Update selbst ist immer atomar.

Meistens klappt das Update beim ersten mal. Ich denke, daß bei der Schleife eher selten ein zweiter Durchlauf notwendig ist, so daß das Verfahren durchaus schnell ist. Ein zweiter Durchlauf ist eben nur dann notwendig, wenn zwischen dem Select und dem Update jemand anderes den Satz erwischt hat.

Tntnet

[Unix-Tom]

Das was TNTNET beschrieben hat funktioniert so. Allerdings nur wenn die Datenbank die Anzahl der geänderten Datensätze (in diesem Fall einer) richtig zurückliefert.
Bei MySQL funktioniert dies perfekt.
MySQL mit MYISAM-Tabellen sperrt auf Tabellenebene.
Wenn ein Client das Update schreibt dann kann kein anderer schreiben.
Sollten es beide versuchen dann bekommt nur einer ROWUPDATE = 1 zurück.
Man muss aber eben in der WHERE definieren das nur UPDATES gemacht wird wenn nicht bereits gemacht wurde.

[tntnet]

Unix-Tom schrieb:

Das was TNTNET beschrieben hat funktioniert so. Allerdings nur wenn die Datenbank die Anzahl der geänderten Datensätze (in diesem Fall einer) richtig zurückliefert.

Das liegt in der Natur der Sache. Das funktioniert auch nur, wenn die Datenbank einen Select oder Update richtig ausführen kann und überhaupt funktioniert das nur, wenn die Datenbank funktioniert. Wenn eine verwendete Funktion nicht funktioniert funktioniert das ganze natürlich nicht.

[thomas69]

Ruhig Blut.

Leider stimmt es, das bei einigen DBs die Rückgabewerte nicht stimmen (MS) oder
schlicht fehlen. Dass soll sich erst mit SQLxxx( >SQL92) ändern.
In den meisten Beschreibungen heist es: Ist vom Provider abhenig.

[tntnet]

thomas69 schrieb:

Ruhig Blut.

Leider stimmt es, das bei einigen DBs die Rückgabewerte nicht stimmen (MS) oder
schlicht fehlen. Dass soll sich erst mit SQLxxx( >SQL92) ändern.
In den meisten Beschreibungen heist es: Ist vom Provider abhenig.

Ja - ich bin ja ganz ruhig .

Das sollte kein Aufschrei sein. Ich fand es halt nur lustig, wenn Du sagst, daß das nur funktioniert, wenn das auch funktioniert.

Das hat mit dem SQL-Standard nichts zu tun. Wenn ich eine API verwende, die die Anzahl der aktualisierten Sätze liefert, und diese Anzahl stimmt nicht, dann ist das nichts weiter als ein Bug. Und wenn ich schreibe, daß die Anzahl der aktualisierten Sätze zu prüfen ist, dann muß der Programmierer das halt prüfen. Wenn die API hier keine Funktionalität zur Verfügung stellt (eine Funktionalität, die nicht funktioniert ist keine Funktionalität), kann dieser Algorithmus mit der API nicht implementiert werden.

Tntnet

[thomas69]

Hi,

um die ganze Sache mit der Funktionalität drehte sich ja meine unsprüngliche Frage. Ich bin halt der Meinung, dass viele(Anzahl) Querys, negativ für die Performanz sind .

[Unix-Tom]

@TNTNET:

Hier war nicht von MySQL dir Rede. Andere Datenbanken liefern die Anzahl geänderter Datensätze nicht zurück. Mein Posting war auch nicht gegen Dich gerichtet.

Bei MySQL ist die Abfrage nach geändertem Datensatz schneller (bzw. fragt man das garnicht ab sondern MySQL liefert es immer zurück) schneller als wenn man LOCK TABLES einsetzt.
Es gibt eben nicht immer für alles eine Lösung mit einem Query.