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

7 comments »

  1. LnddMiles said,
    Juli 26, 2009 @ 22:12

    Pretty cool post. I just stumbled upon your blog and wanted to say
    that I have really liked reading your blog posts. Anyway
    I’ll be subscribing to your blog and I hope you post again soon!

  2. tultaddex said,
    März 28, 2011 @ 06:04

    Have you considered adding some relevant links to the article? I think it will really enhance everyone’s understanding.

    hoodia gordonii side effects
    hoodia juice

  3. Mai 20, 2013 @ 09:34

    Someone essentially assist to make significantly articles I’d state. That is the very first time I frequented your web page and so far? I surprised with the analysis you made to make this actual submit incredible. Fantastic task!

  4. Chris said,
    Januar 7, 2014 @ 09:51

    Wonderful site. Plenty of useful

    information here. I’m sending it to several pals
    ans also

    sharing in delicious. And of course, thank you to your

    sweat!

  5. Steffen said,
    Januar 16, 2014 @ 18:23

    I savor, cause I discovered just what I was having a look
    for.

    You’ve ended my four day long hunt! God Bless you man. Have
    a great day. Bye

  6. Fredericka said,
    Januar 29, 2014 @ 13:48

    you are really a good webmaster. The website loading speed is incredible.
    It seems that

    you are doing any unique trick. Moreover, The contents are

    masterwork. you have done a fantastic job on this topic!

  7. April 17, 2014 @ 13:22

    Hello my family member! I want to say that this article is amazing, great written and include almost all vital infos.

    I’d like to seee extra posts like his .

Leave a Comment

 

1 Trackback \ Ping »

  1. Juli 16, 2014 @ 09:54

    business keynote speaker conference speakers

    drittenormalform *Beta* » Blog Archive » Eine Queue leerräumen mit SELECT FOR UPDATE