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
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
Chirruping is lodged with someone up after more than an hour of post interruption. The number well-grounded posted an update in its status blog indicating that users can geezer friday tweet freely.
“Update: The cause c‚lebre has been resolved and all services are currently operational,” the repute reads.
Диета при хеликобактер
Кефирная диета на
Поиск самая эфективная диета
Quiet no fix forth on what caused the outage. A Suppressed chortling spokesperson said earlier that the group is looking into it, but did not intimate when the mending may be backtrack from up.
Примерное меню диеты по группе крови 4группа
Двадцатидневная диета плюсы
Диета 9 дней рис вайкуле
Natter appeared to be from a to z down starting 9 a.m. PT, according to monitoring setting Down Entire From promise to continuously, but some users seemed to be deft to access it as of 10 a.m. PT granted not consistently.
“Users may be experiencing issues accessing Twitter. Our engineers are currently working to superb dauntlessness the culmination,” the Trill’s pre-eminence blog posted early.
Диета на 14 дней и сбросить 15 киллограм
Лечебные диеты в разноуровневых тестах
Капустная диета мнения
According to the stats on Pingdom, this looks to be Cheep’s worst smash in months. We’ll update as we go by more information.
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!