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
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!
TOKYO – Nearly a half million people in Japan were ordered to higher ground on Sunday, as coastal areas across the vast Pacific region braced for lethal tsunami waves. But only small waves appeared, and there were no reports of damage.
Areas ranging from Sydney, Australia, to the Russian Far East to the Hawaiian islands conducted evacuations and warned residents to be on the lookout for large waves following the 8.8 magnitude earthquake that devastated parts of Chile on Saturday. The Asia-Pacific region waited in suspense for almost 24 hours, the time that scientists predicted it would take shock waves from the powerful earthquake to race across the ocean in the form of massive waves.
But the predicted time of impact came and went, with only waves of up to 10 centimeters reported near Tokyo and of up to 90 centimeters further north along the Japanese coast. The same was true across the region, where officials breathed an almost audible sigh of relief.
“Luckily, these waves are far smaller than the agency’s forecast,” said Kazuaki Ito, director of the Information Institute of Disaster Prevention, a Tokyo-based non-profit group that advises on natural disasters.
The tsunami warning was lifted in Hawaii on late Saturday after waves of about 1.5 meters were sighted, without any apparent damage. Beaches were briefly cleared of swimmers, and tourists were sent to upper floors of hotels. But nations further west left their alerts in place for much of Sunday, even after waves proved small, in case of additional tsunamis triggered by the huge Chilean temblor.
Nations took the warning seriously in a region where raw memories remain of the deadly December 2004 tsunami in the neighboring Indian Ocean that killed nearly 230,000 people in 14 countries.
Some of the biggest preparations were taken by Japan, where meteorological agency officials issued the nation’s first major tsunami warning in 17 years. They initially said they expected walls of water up to 3 meters, or 9 feet, high.
In Tokyo, train lines and highways in densely populated areas along the edge of Tokyo Bay were stopped for hours. Further north, officials said they ordered the evacuation of some 570,000 households from coastal areas mostly on the main Japanese island of Honshu, a areas that has seen killer tsunamis in the past.
Television news programs showed elderly residents in Iwate prefecture sitting on blankets in school gyms that had been turned into makeshift shelters. In the hilly port city of Hakodate, on the northernmost island of Hokkaido, residents sat on hilltops for hours on Sunday watching the sea.
I was watching on & off the MSNBC coverage and frankly not impressed with their ’scare’ tactic coverage – based on scientific fact and investigation the after-effects would be obvious but hey what’s with checking things first these days…. granted the potential for loss of life was there but could news channels act again like news channels – reporting the facts not paranoia & spreading fear…..plus I don’t want to hear at the end of it all “Thank God he saved us”….if you believe that surely God caused it in the first place too…
What do you think about all these tsunamis thing?
_____________________________________
The only time you run out of chances is when you stop taking them.
A winner listens, a loser just waits untill it is their turn to talk.
My guestbook