Eine Queue leerräumen mit SELECT FOR UPDATE

Man braucht es nicht oft aber ab und an ist ein blockierender SELECT ein sogenannter SELECT FOR UPDATE notwendig, z.B. bei dem Klassiker:
Gegeben ist eine Datenbankqueue von der sich viele Jobs bedienen, nun soll sichergestellt werden das Daten nicht mehrfach aus dieser gelesen und z.B. gelöscht werden, wie geht man vor.
Im Gegensatz zu Oracle kennt MySQL kein RETURNING sonst wäre ein DELETE mit einem rowcount und einem RETURNING das Mittel der Wahl, irgendwas so in der Art:

DELETE FROM TABLE WHERE rownum < 100 RETURNING id;

In MySQL müssen wir das in eine Transaktion verpacken und erst die Daten selektieren und dann können wir sie löschen (und genau dafür brauche ich nun eine gefüllte Testdatenbank aus meinem letzten Posting):

BEGIN;
SELECT id,f1,f2 FROM strintab ORDER BY ts ASC LIMIT 100;
#mach was mit den Results
DELETE FROM stringtab WHERE id in (..vermutlich alle abgeholten....);
COMMIT;

Damit hole ich exklusiv die ersten 100 Zeilen aus der DB und keiner kann auf die Daten zugreifen.
Kurz ausprobiert und tatsächlich niemand kann zugreifen weder auf die 100 records noch auf die anderen rows…

Wieso?
MySQL sperrt alle Zeilen über die sei zum zusammenstellen des Ergebnisses „drüber streicht“.
Will heißen so lange MySQL einen full table scan machen muss, sind alle records gesperrt sobald ein Index im Spiel ist und nur ein index range scan notwendig wird ist dieser Bereich gesperrt (mehr zu Explain).
Und auch ein LIMIT wird berücksichtigt also tatsächlich nur 100 Zeilen gesperrt wenn ich das Ergebnis mit einem LIMIT einschränke.

Also dann, legen wir einen Index an:

ALTER TABLE `test`.`stringtab` 
 DROP INDEX `Index_timestamp`,
 ADD INDEX `Idx_stringtab_ts` USING BTREE(`ts`);

Und führen auf je einer Connection obiges Statement aus und wundern uns zuerst wieso eins davon wieder in einem Lock wait hängt (auch ein LIMIT 10,10 hilft nix)
Wenn man weiß das MySQL LIMIT immer bei 0 anfängt wird es klarer, die gesperrte Range startet immer bei 0 das Offset ist nur für die Ausgabe relevant.
Abhilfe schafft eine Änderung des Sortierkriteriums z.B.: DESC oder eine andere WHERE clause.
Alternativ und eher die empfehlenswerte Variante, im aufrufenden Programm sich die zu bearbeitenden resultsets merken und dann sofort auf der DB löschen und die Transaktion mit COMMIT freigeben und dabei halt das Risiko der Persistenz im Programm tragen.

Fassen wir zusammen:

  • ohne Index immer ein lock auf die ganze Tabelle
  • mit Index immer ein lock auf alle rows die durch die WHERE/ORDER BY clause eingeschränkt werden
  • LIMIT wird berücksichtigt und sorgt für weniger row locks
  • EXPLAIN ist wichtig

Leave a Comment