Dotaz: Jak nejlépe zabránit přepisování dat při dvou UPDATEch

[fean]

Ahoj.

V jiném dotazu byl uveden příklad, kdy dojde k souběhu dvou updatů, které si navzájem přepíšou data.

Konkrétně jde o toto:

A/ SELECT id, name FROM t WHERE id = 8 AND name != "Pepa";
B/ SELECT id, name FROM t WHERE id = 8 AND name != "Pepa";
A/ UPDATE t SET name = "Pepa" WHERE id = 8;
B/ UPDATE t SET name = "Josef" WHERE id = 8;

Ve výsledku chceme, aby v tabulce byla uložena hodnota "Pepa", a ten "Josef" se zamítl. Zatímco normálně to prostě přepíše a nikdo se nic nedozví. A to není dobře.

Protože dotyčný tvrdil, že to lze řešit a jako příklad uvedl Wikipedii a Hibernate, zkusil jsem si o tom něco nastudovat. Třeba u Hibernate jsem našel, že si nějak kontroluje verzi dat, a při update kontroluje, zda ukládá do stejné verze. Vypadalo to zajímavě.

Existuje nějaký osvědčený způsob? Co by ste doporučili?

Odpovědi

[Filip Jirsák]

Přidáte sloupeček verze, v každém selectu načtete, v update dáte do podmínky i tu verzi a při updatu ji zvýšíte o jedničku. A pak musíte nastavit izolaci transakcí v databázi tak, aby různé transakce nemohly souběžně updatovat stejné záznamy, nebo používat zamykání řádků pomocí klauzule FOR UPDATE.

[tacoberu]

Jsi si jist, že jsou potřeba ty transakce?

[Filip Jirsák]

Bez toho může dojít k tomu, že jedna transakce vyhodnotí podmínky, pak se provede celá druhá transakce (provede se update), a následně se dokončí první transakce (provede se její update). Záleží ale na tom, co jednotlivé úrovně izolace transakcí zajišťují o té které konkrétní databáze. Ty transakce potřeba nejsou, můžete také spoléhat na to, že k souběžným transakcím, které budou aktualizovat stejné záznamy, nebude docházet moc často - pokud vám nevadí, že takový souběžný update, pokud už k němu dojde, přepíše předchozí data. Třeba pokud uživatelé editují záznam o osobě, vidí celý záznam osoby a jeden uživatel změní datum narození a druhý adresu, a náhodou by došlo souběžnému provedení updatů, můžete prohlásit, že je to chyba uživatele, protože viděl kompletní záznam a takto kompletní jej nechal uložit. Ale nechtěl bych, aby tímhle způsobem moje banka evidovala zůstatek na mém účtu, a pokud by přišly dvě příchozí platby najednou, jedna částka by se k zůstatku na účtu nepřičetla.

[Kit]

Je možné docílit téhož i bez transakce tak, že při optimistický zámek vložím přímo do updatu.

[tacoberu]

To vypadá jak?

[Kit]

Google dnes nefunguje?

Optimistický zámek nemusí vypadat jako zámek. Při UPDATE do WHERE přidáš podmínku, která otestuje, zda je v přepisovaném poli původní hodnota. Pokud není, UPDATE se neprovede. Aplikace pak z počtu ovlivněných řádků pozná kolizi a pomocí třístranného porovnání může rozhodnout následující reakci.

[Filip Jirsák]

Jenom bych doplnil, že to ale funguje jenom tehdy, pokud jsou takhle chráněny všechny updaty v dané tabulce. Tj. nelze třeba udělat dávkový update, kde v podmínce nebudou verze:

UPDATE platy SET plat = plat * 1.1, verze = verze + 1 WHERE oddeleni = 1

[Kit]

Optimistické zamykání se uplatní spíš v situacích, kdy aplikace dlouho čeká na uživatelský vstup. Například pokud se dva redaktoři současně rozhodnou editovat jeden článek nebo dva úředníci modifikovat záznam o stejné osobě. Na hromadné změny se nehodí.

Místo (podle mne nebezpečného) číslování verzí raději volím timestamp.

[tacoberu]

Místo (podle mne nebezpečného) číslování verzí raději volím timestamp.

Vidiš to. A já bych za nebezpečné považoval spíše ten timestamp. (Už se mi stalo, že v daném okamžiku se vygenerovali stejné konfliktní timestampy.)

[Kit]

Že byl stejný timestamp i data? V tom případě to přece nevadí, protože se to přepíše správně. V podstatě jsou timestamp i číslování verzí zbytečné, pokud porovnáváš data.

[Josef Kufner]

Na číslo verze potřebuješ rostoucí posloupnost, timestamp je neklesající posloupnost. Celkem běžně můžeš získat několik timestampů se stejnou hodnotou. Jakmile se ti tohle stane, bude se to chovat všelijak a výhody verzování okamžitě ztrácíš. A pokud se ti o aplikaci začnou přetahovat dva programy, např. importovací scripty, máš zaděláno na průser, neboť ty zvládnou vygenerovat požadavků se stejným timestampem pěknou hromadu.

[Kit]

Na importovací skripty se používá pesimistické zamykání, ne?

[logik]

Porovnávat data ale vždy možné není - např. pokud máš v db uloženy nějaké multimediální data, tak těžko budeš kvůli novému popisu videa porovnávat celé video....

[Kit]

Pokud mám v db multimediální data, mám je v samostatné tabulce. Jako ID pak používám SHA1 těchto multimediálních dat. Porovnání těch pár bajtů mi pak nečiní problém.

[logik]

Jo, to je rozumný - velká data jsou ale víc než jen multimédia a navíc člověk by rád měl nějaké "univerzální řešení", které jde integrovat do frameworku, by to nemusel furt psát. Z tohodle pohledu mi porovnávání podle verze přijde jako asi nejrozumnější kompromis cena/výkon/univerzálnost/pracnost.

Navíc porovnávání dat - tam je otázka: provnáváš-li všechna data, je výsledek stejný, jako když porovnáváš verzi (pominu-li updaty nedělající změnu). Porovnáváš-li pouze změněná data, tak to neni fail-proof: např. nevarovat při změně čísla popisného v adrese, když kolega vedle právě změnil ulici, asi není nejlepší nápad.... Takže porovnávání dat se buď musí psát "na míru", nebo nepřináší nic moc navíc oproti číslu verze, ta je ale na výkon nenáročná a univerzální.

[Kit]

Univerzální řešení neexistuje.

Multimédia často zabírají hodně místa a přesto jsou považována za atomická. Dalšími velkými daty bývají dokumenty, které se často kvůli indexování rozbíjí na atomy.

Číslo popisné a ulice bývají v jedné tabulce, obvykle tedy není problém porovnávat celý záznam - všechny sloupce adresy.

[logik]

"Číslo popisné a ulice bývají v jedné tabulce, obvykle tedy není problém porovnávat celý záznam - všechny sloupce adresy."

To není problém - ale úplně stejnou funkci mi udělá porovnávání čísla verze - a tam se nemusím starat o to, zdali náhodou není nějaký sloupec v tabulce příliš velký apod. Takže moc nevidím důvod, proč to nedělat pomocí čísla verze. Možná to není univerzální, ale rozhodně to je univerzálnější a přitom na výkon méně náročné řešení.

[tacoberu]

Ne, nebavíme so o situaci, kdy nám to nevadí

Jen si nedovedu představit že by se někdo byl schopen trefit dovnitř takovéhoto příkazu:

UPDATE t 
SET name = "Pepa", version=version+1
WHERE id = 8 AND version=1;

ale to je možná jen má neznalost, a záleží, zda je to takto pro tu kterou databázi atomický příkaz.

Až po odeslání toho dotazu jsem si uvědomil, že teprve v případě vícero příkazů v jedné transakci to začne být sranda.

Takže beru zpět.

[Filip Jirsák]

Pokud by se takhle jednoduchý UPDATE neprovedl atomicky, byla by ta databáze opravdu divná. Jenže v tom UPDATE mohou být subselecty nebo volání funkcí, a to pak už záleží na konkrétní databázi, co zaručuje. Ale pokud se s tou databází pracuje např. jen přes ORM, všechny updaty mají v podmínce i rovnost verze a nastavují se v nich jen předem zadané hodnoty (tj. žádné SELECTy nebo volání funkcí v rámci updatu), pak to bude stačit.

[Michal Kubeček]

Pokud by se takhle jednoduchý UPDATE neprovedl atomicky, byla by ta databáze opravdu divná.

Proč by měla být divná? Pokud chci atomicitu, mám prostředek, jak ji zajistit (transakce). Pokud ho nepoužiju, pak ji zajištěnou nemám; spoléhat na to, že ten či onen dotaz je tak jednoduchý, že prostě musí být atomický, je holý nesmysl.

Obecně mne nepřestává fascinovat, jak krkolomné konstrukce jsou lidé schopni vymyslet, aby se nemuseli naučit používat transakce.

[Filip Jirsák]

Transakce zajišťuje atomicitu celé transakce (buď se provede celá transakce, nebo nic), ale s atomicitou jednotlivých příkazů to nemá vůbec nic společného. Atomicita transakce neříká vůbec nic o tom, jestli version ve výrazu version + 1 bude mít stejnou hodnotu, jako version v podmínce WHERE, ani nic o tom, že v okamžiku zápisu té hodnoty version bude mít kteroukoli z předchozích dvou hodnot.

BEGIN
  UPDATE table SET version = version + 1 WHERE version = 1 AND id = 1;
  -- version == 1, podmínka WHERE splněna
                                                                           BEGIN
                                                                             UPDATE table SET version = 2 WHERE id = 1;
                                                                           COMMIT;
  -- version == 2, hodnota version + 1 je 3
                                                                           BEGIN
                                                                             UPDATE table SET version = 4 WHERE id = 1;
                                                                           COMMIT;
  -- version == 4
COMMIT;
-- version přepsáno ze 4 na 3

Tohle je atomická transakce, přesto se v průběhu UPDATE dvakrát změnila hodnota version.

[Michal Kubeček]

Jestli jsem dobře pochopil, co má ten obrázek znamenat, tak něco takového vám má šanci projít nanejvýš tak s dirty read, což je úroveň, u které jsou oprávněné pochybnosti, jestli se vůbec ještě dá mluvit o transakcích, a kterou některé databáze ani nenabízejí.

[Filip Jirsák]

Může to nastat i u READ COMMITTED. Nejpodstatnější je ta změna před zápisem, a k té může dojít ve všech případech mimo SERIALIZABLE. V případě toho zápisu záleží především na tom, jak je udělané zamykání, a o tom transakce nic neříkají (respektive jediná úroveň, která v tomto smyslu něco zaručuje, je SERIALIZABLE). Podstatné je to, že úplné oddělení transakcí zajišťuje jen úroveň SERIALIZABLE, a že nižší úrovně nezajišťují vše, co lidé od transakcí intuitivně očekávají. Ale ne všude se SERIALIZABLE používá, takže pak je potřeba zjišťovat, co mi zvolená úroveň oddělení transakcí doopravdy zajišťuje, resp. jestli daný požadavek zajišťuje např. způsob implementace zamykání v dané databázi.

[logik]

V tomdle máš sice na jednu stranu pravda, na druhou stranu pokud Ti jde o konzistenci dat, tak nikdo rozumný nepoužívá izolaci nižší než Repeatable read. Read uncommited snad nepoužívá nikdo a read commited má smysl používat jen u dat, u kterých Ti na nějaké chybě nesejde.

Protože když chceš validní data, tak tak či tak budeš muset zjistit, zdali repeatable read platí - a pak není důvod, proč to nenechat na DB, která to udělá 100% dobře a daleko rychleji, než uživatelský skript.

[Filip Jirsák]

Repeatable read zajistí, že version bude v té transakci stále stejné. Ale pořád to neřeší problém, že je potřeba aktualizovaná data zamknout. V případě aktualizace jednoho nezávislého řádku tabulky se o ten zámek postará databáze (ale je to záležitost konkrétní implementace, repeatable read transakce nic takového nevyžaduje). Jenže pokud ten update bude záviset na dalších datech, databáze o tom nemusí vědět, nezamkne vše potřebné a stále může dojít k tomu nechtěnému přepisu dat. Repeatable read přitom situaci paradoxně zhoršuje, protože zvyšuje pravděpodobnost, že se bude pracovat se starými daty.

[logik]

1) Definice repeatable reads se liší (jinak ji definuje standard, jinak každá databáze), ale je poměrně široká shoda, že zámek nebo jiný mechanismus zajišťující totéž požaduje, neboť zakazuje změnu hodnoty prvku v databázi, který je přečten nějakou transakcí. Např. http://arxiv.org/pdf/cs.DB/0701157.pdf

2) Pokud update záleží na dalších datech, tak je stejně jako u těchto dat musím podmínit verzí těch dat. Pak to databáze zamkne readlockem, čili ta data nikdo pod rukama nezmění.

[Filip Jirsák]

Pokud zámek brání změně prvku přečteného jinou transakcí, je to už úroveň izolace transakcí serializable, ne?

[logik]

To teda rozhodně není. Serializable je úroveň, která oproti repeatable reads navíc zabraňuje tzn. phantom reads. To je případ, kdy stejný select vrátí dvakrát jiný rozsah záznamů, protože mezi tím nějaké záznamy přibyly.

Proto serializable - kdyby transakce běželi jedna za druhou, tak se toto stát nemůže. Repeatable read zajišťuje pouze neměnnost záznamů, které přečtu, nikoli toho, co "nečtu, protože to tam ještě není". Proto repeatable read - záznam co jednou přečtu, tak přečtu při každém následujícím čtení stejně.

Phantom reads se nelze vyhnout zamykáním jednotlivých záznamů, ale pouze range-lockem , popř. nějakou verzí snapshotové architektury, kdy nové záznamy nejsou pro starší transakci vidět.

[Filip Jirsák]

Aha, já to bral podle PostgreSQL, kde repeatable read znamená, že se čtou data (ne jen přečtené záznamy, ale vše) ve stavu, v jakém byla na začátku transakce - takže phantom reads nemůžou nastat. Nenapadlo mne, že standard má v tomhle mírnější požadavky. Jak jsem psal, vždycky je potřeba si zjistit, co přesně která databáze zaručuje...

[logik]

To sice jo, ale ani "snapshot isolation", kterej používá postgresql od verze 9.1 pro úroveň repeatable read nedává jistotu serializovatelného rozvrhu transakcí. PostgreSQL má striktnější nároky na jednotlivé úrovně, ale ani v ní neplatí, že repeatable read a serialization je stejná úroveň.

Pro zajištění verzování Ti pak stačí repeatable read za předpokladu, že do všech záznamů, co na nich závisí validita Tvých dat a není zajištěna constrainy, zapisuješ, nebo serializable pokud nezapisuješ.

Serializable potřebuješ pouze pokud data závisí na dvou různých záznamech a Ty měníš pouze jeden (a to ještě pouze u "snapshot" architektury, u zámků ani toto nemůže nastat). V tu chvíli ale z definice verzování Ty dva spojené záznamy jsou defakto jedním logickým záznamem a verzovat by se měli "spolu".

Již repeatable read Ti zajišťuje, že se data nezmění mezi Tvým čtením a zápisem i v ANSI SQL normě, natož v striktnější postgresql, která má na tuto úroveň striktnější nároky. K prevenci změny dat před zápisem prostě Serializable třeba není ani ve významu ANSI ani postgres.

[rADOn]

Pokud databáze umí transakce (tj. není to MyISAM) a řádek je zamčený FOR UPDATE tak žádné verzování není potřeba. A pokaždý když takhle vyprasíš standartní vlastnosti databází ježíšek zabije koťátko…

[Filip Jirsák]

Pokud SELECTem vybírám data, která zobrazím na webu uživateli a on nad tím bude pár hodin dumat, než něco upraví, asi nechci mít celou dobu otevřenou transakci a zamčený řádek v tabulce.

[rADOn]

Jasně že ne. Transakci chceš mít otevřenou jen tak dlouho abys zjistil jestli se ti nezměnila data pod rukama. Na to verzování nepotřebuješ. Na prostý souběh dvou updatů na který se ptal OP to už vůbec nepotřebuješ.

[Josef Kufner]

To mi tak připomíná etag.

[Filip Jirsák]

Verzování nepotřebuju, ale je jednodušší porovnat jedno číslo verze, než porovnávat celý záznam. V příkladu uvedeném v dotazu UPDATE B přepíše změny, které provedl UPDATE A. Tazatel se ptal, jak tomu skrytému přepsání změn předejít.

[tacoberu]

Jak píše @Filip Jirsák:

UPDATE t 
SET name = "Pepa", version=version+1
WHERE id = 8 AND version=1;

Ale musíš si odchytit, zda se ten dotaz provedl či ne.

Případně je možnost si na to napsat assertovací funkci. Něco jako:

UPDATE t 
SET name = "Pepa", version=assert_version(version, 1)
WHERE id = 8;

Ale to je fakt jen taková vychytávka, aby se ti jednotně vraceli výjimky.

[Kit]

Většinou se řeší případ, aby dva uživatelé nemodifikovali stejný záznam. Jedna modifikace by v tichosti modifikovala výsledek té druhé a uživatel by se o tom nedozvěděl. Obvykle se to řeší buď optimistickým, anebo pesimistickým zamykáním. Každý z nich má své výhody a nevýhody v různých případech užití.

[AraxoN]

Ak ide o automatizované spracovanie, tak na to je ideálny row-level locking (SELECT ... FOR UPDATE).

Ak ide o údaje vkladané používateľom, kde sa medzi SELECT a UPDATE čaká na akciu používateľa, tak mne sa osvedčilo ku často meneným tabuľkám pridať log so zmenami, ktorý sa zobrazuje pri danom zázname, napr. 2014-10-11 11:47:35 John Doe changed 'name' from 'Ludvig' to 'Pepa', 2014-10-11 11:47:59 Jane Roe changed 'name' from 'Pepa' to 'Jozef'. Tak sa dá dopátrať k pôvodnému zneniu, ako aj k tomu, čo sa stalo, kedy a prečo.

Vidím jeden problém s verziovaním, ktoré tu navrhujú kolegovia. Keď druhý UPDATE neprebehne, tak čo potom spraví používateľ? Otvorí si aktuálny záznam a začne svoje zmeny písať znova? Čo keď robí zložitejšie zmeny a kým ich dokončí tak mu to zase niekto updatne? Alebo čo keď bol so zákazníkom na telefóne, zmeny mu zákazník diktoval a sám si ich nepamätá?

[Kit]

Pokud se změny neprovedou, aplikace si stále pamatuje vstup od uživatele a původní hodnotu. K tomu dalším dotazem získá novou hodnotu z databáze. Může provést třístranné porovnání, jehož interpretace záleží na případu užití. Jiné to bude v případě atomických hodnot a jiné v případě editace článku. Také může uživateli nabídnou všechny tři varianty, ať si z nich vybere nebo ať udělá nějaký merge.

[tacoberu]

Vidím jeden problém s verziovaním, ktoré tu navrhujú kolegovia. Keď druhý UPDATE neprebehne, tak čo potom spraví používateľ? Otvorí si aktuálny záznam a začne svoje zmeny písať znova? Čo keď robí zložitejšie zmeny a kým ich dokončí tak mu to zase niekto updatne? Alebo čo keď bol so zákazníkom na telefóne, zmeny mu zákazník diktoval a sám si ich nepamätá?

Mrkni jak to dělá Wikipedia. Minimálně pro ilustraci.

[AraxoN]

Wikipediu poznám, používam, editujem. Funguje to výborne na články, resp. na dlhé texty. Je to jeden jediný dátový typ, ktorý tam musia riešiť a riešia ho dobre. Už ťažšie si viem predstaviť rôzne štruktúrované dáta, ktoré sú potrebné v bežnom CRM. Objekt "Zákazník" nie je jeden dlhý text, ale je rozsekaný na jednotlivé políčka - meno, priezvisko, ulica, mesto, atď. a počet políčok sa časom zvykne len zvyšovať. Urobiť k objektu logovanie zmien a výpis z logu je záležitosť na pol hodiny programovania. Urobiť a udržiavať formulár na merge to už je robota na deň alebo viac (podľa toho, aký framework sa v danom systéme používa). Nehovoriac o tom, že po naprogramovaní merge musí nasledovať preškolenie obsluhy, lebo prvá reakcia bežnej tety pri zobrazení niečoho nového je kliknúť čo najrýchlejšie OK, alebo Storno, čím dôjde k tichému prepísaniu, alebo zahodeniu zmien - a to v žiadnom prípade nie je správny postup.

[tacoberu]

Mě přijde naopak více menších políček jednodužší. Zobrazím diff pro každou položku mající konflikt (a jen pro tu), barevně zvýrazním. Pokud si toho nevšimne, tak už to dělá naschvál.

Samozřejmě se dá přidělat verzování změn, plus logování konfliktů pro ověření nějakou třetí osobou. Ale to se imho dostáváme trochu mimo původní technické zaměření, jak řešit konflikt.

[Filip Jirsák]

Tohle je problém toho, že se dnes do relačních databází obvykle ukládají "objekty" nebo struktury - vezme se formulář od uživatele, kde jsou částečně původní data a částečně uživatelem změněná, a vše se to znovu zapíše do databáze. Přitom to většinou není to, co uživatel chce - uživatel chce třeba změnit jméno nebo adresu. Přitom SQL umí updatovat jenom jednu nebo několik položek záznamu. Kdyby aplikace umožnila uživateli provést tu změnu, kterou chce, docházelo by ke konfliktům výrazně méně. A každý takový konflikt by znamenal chybu v procesech nebo na straně uživatele - k té změně od uživatele musí vždy být nějaký podnět, a nemohou existovat dva různé platné podněty na změnu jména člověka ve stejný okamžik.

[tacoberu]

Souhlasím.

Jak by sis představoval, že by to UI mělo vypadat? Okamžité uložení u každé změny políčka?

[Filip Jirsák]

Okamžité uložení určitě ne. Dá se to udělat třeba tak, že se změněná pole zvýrazní, a uživatel bude vědět, že se ukládají jen změněné údaje. Třeba může být zobrazení v needitovatelné formě, a teprve po kliknutí se políčko změní v editační (pak je ale ještě potřeba nějak vyřešit ovládání z klávesnice).

[Tomáš]

Když jsem programoval v PowerBuilderu, tak ten to dělal tak, že měl několik bufferů s daty.

1. buffer - natažená data z DB

2. buffer - data, co mění uživatel

Když se dělal update, tak to v principu fungovalo takto: update set (data z 2.bufferu) where (data z 1. bufferu)

Mezi těmi sloupci musel být i PK.

Když se updatoval jiný počet řádek, než než se chtělo updatovat, tak se v DB udělal rollback a uživatel uviděl hlášku "row change between retrieve and update"

[logik]

Ale toto přeci není problém DB. Vždyť můžeš detekovat, které políčka uživatel změnil, a udělat update pouze těch políček, co byly změněny a udělat update jen z nich.

To, že se dělá update všeho není vůbec problém SQL databáze - ta to umožňuje - ale problém formulářových frameworků, které nejsou zařízeny na detekci provedených změn.

Jediné, co v tom hraje proti databázi je to, že většina databází má zámky max. na úroveň řádků, ale pokud se bavíme o situaci, kdy jde o interaktivní zpracování, tak tam pesimisticky zamykat stejně nejde.

[vlastikroot]

Co databazi poslu, to tam mam. Co vic bych od ni chtel? Atomicitu zarucim na urovni DB pomoci transakce, ale bez atomicity na urovni aplikace je to k nicemu.

Staci treba hlidat zmenu (verzovanim) a v pripade zmeny upozornit uzivatele, pripadne radky v tabulce zamykat.

Ja treba pro realtimovost aplikace pouzivam zvlastni sitovy synchronizacni server, ktery ohlasuje klientum, jaka sdilena data si maji nacist, pripadne co maji udelat. Tim se tomuhle aktivne vyhnu, presto kontroluji verzi dat.