Většina programátorů chápe SQL transakce jako cestu k tomu, jak vrátit provedené změny v případě, že dojde k nějaké chybě a zabránit tak nekonzistenci dat.
Tento přístup není špatný, ale může vést k závažným problémům – Deadlock-ům (např. „Lock wait time exceeded, try restarting transaction„), pokud nebudete uvažovat i další aspekty transakcí, zámků (shared a exclusive locks), které vytváří, a režimů izolace (isolation level), které používají.
Pokud jste již narazili na Lock wait time exceeded nebo tomu chcete předejít, tenhle článek je právě pro vás.
Pro MyISAM platí jiná pravidla, protože ta používá pouze table-level zámky, takže k deadlockům moc nedochází (nebo dochází z jiných důvodů).
Deadlock by se do češtiny dalo přeložit jako patová nebo bezvýchodná situace, nebo lidově zásek. Já budu používat anglický výraz deadlock, aby bylo jasné, že jde o tento konkrétní problém.
Rychlá navigace
TL;DR
Tato problematika je hodně rozsáhlá, takže se nedá shrnout do pár vět. Pokud jí chcete rozumět, budete si muset přečíst celý článek.
Pokud jste ale právě s situaci, kdy zrovna teď je váš server zablokován v deadlocku, zde je pár věcí, které můžete provést:
- Nejjednodušší je restartovat procesy, kteéý se připojují do DB (PHP nebo jiný, pokud používáte Javu, Ruby on Rails, apod.) nebo celý web server – tím se odemknou všechny zámky v databázi a server znovu naběhne. Pozor na to, že některé servery mohou mít spuštěny více (PHP) procesů a vy nedokážete jednoduše zjistit, který je v deadlocku – prostě postupně restartujte vše, co se k DB připojuje, dokud se problém nevyřeší.
- Pokud je restart příliš drastický, připojte se k DB přes konzolu (např. mysql.exe), zobrazte si běžící procesy (
show processlist;) a pak postupně zabíjejte ty, které trvají dlouho (kill <process_id>;). Deadlocky obvykle vytváříINSERT,UPDATEneboDELETE, ale uvědomte si, že transakce může být zaseknutá na dalším příkazu, který může býtSELECTnebo dokonceSLEEP. Proces, která aktuálně čeká naUPDATE,INSERTneboDELETEnemusí být původce deadlocku a je jen prostě čeká na uvolnění zámků (což během deadlocku možné není).
Pokud chcete šanci na deadlock minimalizovat (deadlock nemůžete nikdy 100% odstranit, i když nebudete používat transakce), ale nechce se vám číst celý článek, držte se těchto rad:
- Snažte se transakce co nejvíce zkrátit. Neprovádějte SELECTy uvnitř transakce a nekombinujte SELECT, UPDATE a INSERT, kde to není potřeba. Nejlépe je si nejprve stáhnout všechna potřebná data, pak se od DB odpojit (např.
$pdo->disconnect();), provést všechny potřebné výpočty a připravit si data (SQL případy do stringů, data do polí, apod.). Následně s k DB opět připojit a zapsat všechna data najednou ($pdo->connect(...); $pdo->query('START TRANSACTION'); $pdo->query($query, $data); ... ; $pdo->query('COMMIT');) - Snažte se delší transakce rozdělit na menší celky, které dávají smysl. Obvykle se transakce používá pro zachování konzistence dat mezi tabulkami, ale zvažte, která data musí být konzistentní a která ne (např. zapsat data současně do
profile_last_loginaprofile_logged_inmá smysl, aby se last login nezapsal, když selže přihlášení, ale zápis dologin_logalogin_statisticsje pravděpodobně možné provést mimo transakci nebo v oddělené transakci, protože není kritická pro vlastní přihlášen. Stejně jak pokud informaci posíláte na jiný server (např. Google Analytics), dělejte to až po skončení transakce, aby transakce zbytečně nečekala a něco, co jí neovlivní.- To platí i v případě velkých dotazů bez transakce, např. UPDATE na milion řádek je příliš dlouhý a bude zbytečně dlouho zamykat všechny řádky. Místo toho rozdělte příkaz po 1000 nebo 10’000 řádkách a provádějte příkazy postupně (i za cenu toho, že samotný proces bude trvat dlouho, ale alespoň nezablokuje ostatní části aplikace).
- Vždy se snažte v transakcích držet stejné pořadí vkládání a aktualizace dat. Např. pokud při registraci vkládáte řádek do tabulky
usera následně douser_last_login, zatímco při potvrzení emailu (DOI) nejprve aktualizujeteuser_last_logina následně teprve zapíšete DOI stav do tabulkyuser, máte velkou šanci na vytvoření deadlocku (v této konkrétní situaci asi ne, protože uživatel se bude současně registrovat a potvrzoval email, ale jako příklad je do nejvíce názorné). - Pokud nemůžete dlouhou transakci rozdělit kvůli konzistenci dat, řešte situaci jinak a bez transakce – např. při registraci zapiště uživatele s příznakem
registration_pending, pak proveďte vše potřebné (např. ověření na serveru 3. strany) a následně buď uživatele zbavte příznaku a nebo ho (při chybě) smažte. - Uvědomte si, že indexy se nepoužívají jen pro
SELECTy, ale jsou potřeba i proUPDATEaDELETEdotazy, které nepoužívají primární klíč (např.DELETE FROM statistics WHERE created < '2023-01-01';by měl mít index na sloupci `created(proč se dozvíte v článku). - Přečtěte si celý článek a začněte konečně o transakcích uvažovat správně a brát v úvahu zámky a izolaci. (Protože tohle se zkráceně nedá říct a abyste to mohli správně používat, musíte problematiku pochopit.)
Jaké zámky InnoDB používá
Ve výchozím nastavení InnoDB nepoužívá zámky pro jednoduché SELECTy (Tedy SELECT ... FROM ... WHERE ...; Pokud ale SELECT potřebuje přečíst řádek, na kterém již nějaký zámek je, tak musí počkat, dokud neskončí transakce, která zámek vlastní.
X-Lock (Výhradní zámek)
Nejčastějším zámkem je X-lock (exlusive lock, neboli česky výhradní zámek), který danému příkazu nebo transakci umožňuje daný řádek měnit. Pokud transakce vlastní X-lock, žádný jiný příkaz nebo transakce (ve výchozím nastavení) nemůže s řádkem nic provést (ani ho přečíst).
Důležité je uvědomit si, že v MySQL je každý dotaz transakcí (i když není uvnitř explicitní transakce) a každá transakce (opět ve výchozím nastavení) drží všechny své zámky až do svého ukončení (dokončení dotazu, pokud není v transakci, resp. u transakce do commitu nebo rollbacku).
Například příkaz UPDATE user SET inactive = 1 WHERE last_login BETWEEN '2022-01-01' AND '2023-01-01'; zamkne všechny uživatele přihlášené v roce 2022 a všechny příkazy, které budou chtít takové uživatele přečíst musí počkat, než se aktualizuje celý rok.
X-lock se vytváří automaticky před začátkem příkazů UPDATE a DELETE. Ručně ho můžete vytvořit zápisem SELECT ... FROM ... WHERE ... FOR UPDATE; . Tento příkaz se používá v případě, že vybíráte řádky, které budete následně aktualizovat nebo mazat a potřebujete zajistit, že je mezi tím nikdo nepoužije k něčemu jinému – prostě tím říkáte, že daný řádek v dané podobě již neexistuje a je potřeba počkat na jeho novou podobu.
Je ale důležité si uvědomit, že pokud mezi SELECT ... FOR UPDATE a následným COMMIT budete provádět delší výpočty, bude řádek celou dobu zamčený a není možné z něj ani číst data!
Gap lock (zámek na více řádek)
Příkaz INSERT nevytváří X-Lock (protože řádek, který bude vkládat, ještě neexistuje), ale místo něj vytváří Gap lock, který zamkne mezeru, do které INSERT hodlá řádek vložit. Gap lock může existovat i na řádcích, které ještě neexistují a zabránit tak vložení řádků, které mohou kolidovat.
Předpoklad Gap zámku je ten, že pokud transakce vkládá jeden řádek, je pravděpodobné, že bude později vkládat i další řádky a tak si rezervuje právo vložit všechny řádky nejdnou.
Problém s GAP zámkem je ten, že pokud jedna transakce vloží řádek na konec tabulky (např. nově zaregistrovaný uživatel) a následně vkládá data do jiných tabulek (log, statistiky, apod.), ostatní transakce (a INSERTy bez transakce) musí čekat na její skončení, i když logicky vzato to není potřeba, protože původní transakce již nehodlá vložit další řádek do dané tabulky.
GAP zámek může vzniknout nejen na konci tabulky, ale i v mezeře mezi řádky, na řádcích, které ještě neexistují. Například pokud se uživatel s ID 123 přihlásí k odběru novinek a vy to zapisujete do referenčí tabulky (Primární klíč je ID uživatele), tak se zamkne mezera na všechny řádky, které okole ID 123 neexistují. Pokud je tedy předchozí přihlášený uživatel 105 a následující přihlášený má ID až 168, příkaz INSERT zamkce mezeru 106 až 167. Pokud se současně pokusí k novinkám přihlásit i uživatel s id 128, bude muset počkat až skončí transakce uživatele 123.
Gap lock nevzniká na tabulce samotné, ale znemožňuje použití indexů, které daná sloupec obsahují. Kvůli tomu zabraňuje použití příkazů SELECT, které i jen vybírají řádky z daného rozsahu, nebo jen zkoušejí, jestli nějaké řádky existují.
-- příklad
-- uživatelé, kteří existují před transakcí
INSERT user VALUES (105, ...), (168, ...);
-- Transakce 1
INSERT user VALUES (123, ...), ...;
-- Transakce 2
SELECT COUNT(1) FROM user WHERE id BETWEEN 140 AND 150;
-- druhá transakce musí počkat na dokončení té první, protože rozsah 140 až 150 je v zamčené mezeře
Next-key lock
Zámek Next-key je kombinace X-lock na řádku a Gap lock na mezeře. Vzniká při použití UPDATE a DELETE, které nepoužívají konkrétní hodnotu (např. WHERE id = 123 nebo phone_number IS NOT NULL), ale používají nějaký rozsah hodnot (např. DELETE FROM statistics WHERE created < '2020-01-01' AND processed = 1;). V takovém případě příkaz zamkne všechny řádky výběru pomocí X-lock a také všechny mezery mezi nimi pomocí Gap lock (příkaz z příkladu zamkne všechny řádky, které byly vytvořeny před rokem 2020 a také všechny existující či budoucí řádky obsahující processed == 1, čímž znemožní provedení UPDATE statistics SET processed = 1 WHERE ...).
Zámek na celou tabulku
InnoDB nemá přímo zámek na celou tabulku (příkaz LOCK TABLES se provádí na úrovni MySQL bez zásahu InnoDB), ale Gap a Next-Key zámky mohou zamknout všechny řádky tabulky pokud použijete nešikovný příkaz.
V případě INSERTU, jak jsme si řekli, se zamykají mezery kolem ID, které vkládáte. Pokud jedním příkazem vkládáte více řádek, zamkne se více mezer a pokud to zahrnuje všechny mezery mezi existujícími řádky, zamkne se celá tabulka.
V případě UPDATE a DELETE se celá tabulka zamkne v případě, že použijete podmínku, kterou nelze vyhodnotit pomocí indexu. Jak jsme si řekli, Gap zámek (a tedy i Next-Key zámek, který je kombinace X-lock a Gap lock) vzniká na indexu konkrétních sloupců. Pokud ale MySQL nenajde index, pomocí kterého by mohla podmínku vyhodnotit, musí místo indexu použít samotnou tabulku, což znamená, že se Gap lock vytvoří na všech řádcích tabulky (a v podstatě to odpovídá Table zámku, i když technicky v InnoDB takévý typ zámku neexistuje).
UPDATE statistics SET processed = 1 WHERE state = "DONE";
-- pokud existuje index na sloupci state, zamknou se jen řádky obsahující "DONE" hodnotu
-- pokud takový index neexistuje, zamknou se všechny řádky, protože MySQL neví, které řádky budou podmínce odpovídat
S-lock (sdílený zámek)
S-lock je zvláštní forma zámku, který se sám nevytvoří, ale může ho vytvořit programátor v případě, že potřebuje zajistit, že se řádky během transakce nezmění, ale zároveň nechce blokovat ostatní SELECTy.
Nejjednodušeji vytvoříte S-lock příkazem SELECT ... FROM ... WHERE ... FOR SHARE; (další způsoby viz dále).
Sdílený zámek se jmenuje sdílený, protože zámek na stejný řádek může vlastnit více transakcí, které se navzájem neblokují. Pokud ale alespoň jedna transakce vlastní sdílený zámek, není možné na stejném řádku vytvořit jiný zámek (X-lock, Table lock, apod. – technicky i Gap lock, ale ten vzniká jen na mezeře neexistujících řádku).
Úskalí sdíleného zámku je v tom, že nezamyká jen řádky, které odpovídají výběru, ale i řádky, které výběru odpovídají jen částečně, nebo řádky, které bylo potřeba přeskočit (pokud hledáte bez použití indexu).
SELECT * FROM user WHERE created > '2023-01-01' FOR SHARE;
SELECT * FROM user WHERE last_login > '2024-01-01' FOR SHARE;
-- pokud existuje index na created a jiný index na last_login:
-- zamkne všechny uživatele vytvořené v roce 2023, 2024, atd.
-- současně zamkne všechny uživatele vytvořené dříve ale přihlášené v roce 2024 (a dalších)
-- pokud neexistuje zámek na last_login:
-- zamkne i uživatele přihlášené PŘED rokem 2024, protože nemůže vědět, kteří uživatelé budou odpovídat podmínce
-- pokud se dnes přihlásí uživatel 123, který byl přihlášený i včera, bude muset počkat, až výše uvedená transakce skončí, protože nebude možné provést:
UPDATE user SET last_login = NOW() WHERE id = 123;
-- ... protože buď je zamčená celá tabulka (neexistuje index), nebo je zamčený index last_login na rozsahu 2024 až nekonečno a není možno ho aktualizovat
Izolace
Všechny SQL transakční databáze musejí podporovat izolaci transakcí. MySQL podporuje všechny, které jsou definované v zakladním SQL jazyce. Níže uvedený popis ale platí jen pro InnoDB, protože na ostatní úložiště funguje jinak (např. MyISAM nemůže zamknou jednotlivé řádky).
Izolace je spojená s transakcemi, ale pro MySQL je každý příkaz transakcí (pokud nevíte proč, nastudujte si něco o autocommit), takže se izolace aplikuje i v případě, že provádíte SELECT nebo UPDATE bez transakce.
REPEATABLE READ (čtení beze změn)
Toto je výchozí režim, takže funguje přesně tak, jak pravděpodobně chápete celou MySQL a jen jste dosud netušili, že jde vlastně jen o jeden z režimů, které MySQL podporuje.
Tedy před tím, než otevřete libovolnou tabulku, MySQL vytvoří v paměti kopii dat (nebo provádí COW? nevím jistě) a následně provádí čtení a zápis do těchto dat místo do samotné tabulky. Díky tomu nemůže dojít k tomu, že by během transakce dotaz na stejný řádek (resp. stejnou podmínku) vrátil jiná data (resp. jiný počet řádek).
Tím vám MySQL zajišťuje, že když provedete něco jako FOR ($i = 0, ...) { SELECT ... LIMIT 100 OFFSET 100 * $i) ..., tak řádky půjdou konzistentně za sebou a nemusíte řešit to, že mezi tím mohli nějaké řádky zmizet nebo se objevit nové (a další čtení pak buď vrátí stejáný řádek znovu a nebo nějaký řádek přeskočí).
Nevýhoda tohohle přístupu je v tom, že při zápisu (do InnoDB) přichází na řadu všechny výše uvedené typy zámků (X-lock, Gap lock, …) a blokují tak další čtení a úpravu dat a to i na řádcích, které se ve skutečnosti nezměnily (a InnoDB jen musela ověřit, jestli splňují podmínku nebo ne).
Tedy sice máte zaručená data, ale při jejich změně musíte čekat, až se propíší (COMMIT).
Z toho důvodu je tento režim izolace nejpomalejší a nejvíce náchylný k tomu, vytvořir deadlock, pokud ho nepoužíváte správně (a nebo ho používáte správně a jen máte smůlu na souběžné operace).
READ COMMITTED (čtení schválených změn)
Tento režim (jméno je s dvěma M a dvěma T!) je nejvíce doporučení-hodný, ale jen v případě, že chápete, jaká jsou jeho rizika a v čem se liší od REPEATABLE READ.
Výhoda tohoto režimu izolace je v tom, při zápisu nevytváří GAP zámky (např. INSERT jedné řádky nezamkne vkládání dalších řádek) a může mnohem dříve odemknou X-lock zámky řádek, které se nijak nezměnily (např. UPDATE user SET active = 1 WHERE active = 1; žádné zámky nedrží).
Díky tomu je tento režim mnohem rychlejší než REPEATABLE READ a má mnohem menší šanci na vytvoření (náhodného) Deadlocku. Nicméně pořád může dojít k deadlocku v případě, že výslovně (i když nevědomky) vytvoříte dvě transakce, které provádí kolizní změny data.
Nevýhoda je v tom, že přicházíte o konzistentnost dat REPEATABLE READ při čtení, protože se nevytváří žádná kopie dat a změněné řádky se do výpisu zahrnou hned, jak je schválíte (COMMIT).
Nevýhodu při zápisu ale vynahrazuje to, že InnoDB může uvolnit X-lock zámky na řádcích, které neprošli změnou a tudíž nepotřebují schvalovat. Pokud tedy provádíte změnu, která teoreticky může změnit velké množství řádek, ale prakticky jich změní jen pár (nebo žádný), tak je tento režim ideální, protože nebude blokovat ostatní transakce nepotřebnými zámky. Zároveň to znamená další zrychlení díky tomu, že při zápisu nemusí InnoDB ukládat data do dočasné tabulky a propsat je až při schválení, ale může je zapsat přímo do cílové tabulky (a mimo si pamatovat jen původní data pro případ rollbacku).
Tento režim je také ideální v případě, že během transakce používáte více SELECTů, ale na různých (navzájem nezávislých) řádcích nebo tabulkách, takže vám nevadí, že následný SELECT vrátí čerstvější data než ten předchozí.
Stejně tak je výhodný, pokud používáte UPDATE s nejednoznačnými podmínkami, které teoreticky pokrývají velké množství řádek ale prakticky upraví jen malé množství dat (tzn menší množství zámků a menší množství dat pro rollback).
READ UNCOMMITED (aneb zámky jsou na nic)
Tento režim je hodně nebezpečný a měli byste ho používat jen pokud plně chápete jeho důsledky. V angličtině se přezdívá dirty read, protože si nikdy nemůžete být jisti, co vám přečtení dat vrátí a zda to bude dávat smysl (s ohledem na ostatní data).
Při zápisu fungují READ UNCOMMITED a READ COMMITED stejně. Tedy zamykají jen řádky, které byly změněny a řádek změněný jednou transakcí nemůže být změněn jinou transakcí.
Při čtení ale dochází k tomu, že se nerespektují vůbec žádné zámky (X-lock – jiné v tomto režimu nejsou (Gap lock) nebo nemají smysl (S-lock čtení neblokuje)), což znamená, že vám SELECT může vrátit klidně data, která budou následně smazána (rollback) nebo zatím neprošli plnou změnou (např. při JOINu dvou tabulek, můžete dostat mix, kde jedna tabulka již byla změněna a druhá ještě ne).
Tento režim čtení není úplně bezcenný, ale musíte dávat velký pozor, co čtete a dobře přemýšlet nad tím, jestli vám DB může vrátit nesmyslná data, resp. jestli jste schopni nesmyslná data rozpoznat a opravit (resp. je např. ignorovat a přeskočit jejich zpracování).
Praktické využití má v případě, že potřebujete v jedné transakci zapsat tabulky v jednom pořadí a v další transakci je updatujete v opačném pořadí:
-- transakce A
UPDATE t1 SET ... WHERE id = $id1;
UPDATE t2 SET ... WHERE t1_id = $id1;
UPDATE t1 SET ... WHERE id = $id2;
UPDATE t2 SET ... WHERE t1_id = $id2;
--transakce B
UPDATE t1 SET ... WHERE id IN (select t1_id from t2 WHERE t1_id IN [$id2, $id1]);
V tomto příkladu dojde k tomu, že první příkaz nejprve zamkne záznam v první tabulce a následně i záznam v druhé tabulce a pak to samé opakuje pro další ID. Pokud se mezi prvním a druhým ID spustí transakce B, přečte záznam $id2 z t2 (zatím není zamčený), zamkne ho v t1 a následně hledá záznam $id1, ale ten je již zamčený transakcí A. Následně transakce A přejde k updatu záznamu $id2, který je ale zamčený transakcí B a vznikne deadlock. Pokud ale transakci B změníte na READ UNCOMMITTED, nebude čekat na zámky transakce A a žádný deadlock nevznikne. Poznámka: problém spočívá v tom, že MySQL příkaz z transakce B optimalizuje tím, že neprovede nejprve SELECT (všech řádek) a pak až UPDATE, ale střídavě SELECTuje jeden řádek, který okamžitě UPDATuje.
SYNCHRONIZABLE
Tento režim je ve výchozím nastavení kombinací READ COMMITED pro čtení (tedy čtení dat bez kopie) a REPEATABLE READ pro zápis (tedy vytváření všech druhů zámku. Zdá se tedy být skvělou kombinací preferující rychlost a bezpečnost.
Problém je, že to platí jen v případě, že funkce autocommit je zapnutá (a to i v případě, že jste uvnitř transakce, kde se autocommit nepoužívá).
Pokud totiž dojde k vypnutí autocommitu, čtení se změní na lakomného chlápka, který na všechny přečtené řádky vkládá S-lock zámky a blokuje tak jejich změnu (trochu techničtěji všechny SELECTy budou automaticky SELECT ... FOR SHARE). Pokud se do tohoto režimu dostanete mimo transakci, znamená to trvalé (do ukončení skriptu) zablokování všech řádek, o které jste třeba jen zavadili.
Změna režimu izolace
Režim izolace se v angličtině jmenuje isolation level a mění se dlouhý příkazem:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Podstatné je, že režim izolace musíte změnit PŘED tím, než spustíte transakci, aby MySQL věděla, co bude pro vytvoření transakce potřebovat (např. potřebuje pamět pro kopii dat?)
SET TRANSACTION ISOLATION LEVEL SYNCHRONIZABLE;
SET autocommit = 1; -- vynucení režimu synchronizace
START TRANSACTION;
-- ... operace s daty
COMMIT; -- uvolní zámky
Pokud použijete SET TRANSACTION a následně nezavoláte START TRANSACTION bude daný režim platit jen pro jeden následující dotaz a pak se vrátí do předchozího režimu.
Režim pro celé připojení nebo pro všechny připojení můžete nastavit pomocí SET SESSION TRANSACTION (platí pro nově vytvořené transakce nebo dotazy bez transakce) nebo SET GLOBAL TRANSACTION (pozor, neovlivní již existující připojení – musít se odpojit a pak znovu připojit, nebo režim nastavit jak globální tak i pro session).
Transakce se snapshotem
MySQL podporuje pro InnoDB zápis START TRANSACTION WITH CONSISTENT SNAPSHOT, což by mohlo naznačovat, že byste mohli vytvořit snapshot třeba pro READ COMMITED, ale opak je pravdou a snapshot je podporován pouze když použijete REPEATABLE READ (v jiném režimu způsobí chybu).
Důvod, proč explicitně začít transakci se snapshotem, není v dokumentaci úplně přesně popsán, ale nejspíše může být ten, že pokud nejprve do tabulky zapíšete data a následně je přečtete, InnoDB vytvoří snapshot až před prvním SELECT dotazem, zatímco při explicitním vynucení ho vytvoří před updatem (a pokud update trvá dlouho, může to způsobit nekonzistenci dat).
-- normální stav
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE user ...; //vytvoří úložiště pro změněná data
SELECT * FROM user WHERE ...; //vytvoří snapshot pro data po změně
COMMIT;
-- vynucený snapshot
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
UPDATE user ...; //vytvoří snapshop i úložiště pro změněná data
SELECT * FROM user WHERE ...; //čte z existujícího snapshotu
COMMIT;
Transakce jen pro čtení
Poznámka: text této kapitoly berte s rezervou, protože dokumentace MySQL se v tomhle tématu rozchází a co skutečně platí může záležet na vaší verzi MySQL nebo MariaDB.
Ve výchozím stavu jsou transakce (a dotazy mimo transakce) považovány za dotazy umožňující čtení i zápis. S ohledem na to také MySQL optimalizuje to, jak s dotazy a transakcemi zachází. (Podle jiných zdrojů InnoDB považuje transakci za read-only až to prvního zápisu?!)
Pokud dopředu víte, že nějaká sekvence nebude měnit data v databázi a bude jen číst data (ale například potřebuje použít REPEATABLE READ pro konzistenci dat), můžete danou transakci označit za „pouze pro čtení“ a MySQL podle toho zoptimalizuje to, jaké nástroje bude pro transakci používat.
START TRANSACTION READ ONLY;
SELECT ...;
SELECT ...;
COMMIT;
Během read-only transakce můžete číst libovolná data a dokonce můžete vytvářet dočasné tabulky a zapisovat do nich data. Nemůžete ale vyžadovat S-lock zámky. Na konci byste VŽDY měli provést COMMIT (nebo ROLLBACK v případě chyby) stejně jako to děláte u ostatních transakcí (i když read-only transakce nemůže data měnit, COMMIT zruší případné zámky a uvolní další zdroje, které MySQL pro transakci potřebovala).
Trochu problém je, že optimalizace, které se provádějí na úrovni MySQL a InnoDB jsou také v této úrovni, takže se nedá přímo říct, co přesně se vlastně stane a dost to záleží na verzi DB a dalších nastavení. Jediné, co se dá říct jistě je, že v read-only režimu MySQL nevytváří TransactionId, které je potřeba pro získání zámku (a proto read-only transakce nemůže vytvářed ani sdílené zámky pro čtení).
Nicméně pokud chcete MySQL používat na profesionální úrovni, měli byste být schopni rozpoznat read-only transakci a správně ji začít a ukončit.
V závislosti na verzi MySQL může být lepší dělat read-only transakce i v případě, že provádíte jen jeden SELECT, který by technicky vzato transakci vůbec nepotřeboval. V tomto případě ale nezapomeňte, že byste transakci měli ukončit před tím, než začnete data zpracovávat.
START TRANSACTION READ ONLY;
SELECT COUNT(1) FROM user;
COMMIT; -- pouze jeden příkaz, ale explicitně provedený v read-only režimu (co to znamená netuším, ale údajně je to tak lepší ;-) )
Pokud naopak chcete dopředu deklarovat, že transakce bude potřebovat měnit data, můžete to provést stejným příkazem (s opačnou hodnotou):
START TRANSACTION READ WRITE;
UPDATE user SET last_login = NOW() WHERE id = 123;
COMMIT; -- změna dat ve vynuceném read/write režimu
Závěr
Jak tedy shrnout výše uvedené poznatky a jak minimalizovat šanci na deadlock (nebo obecně zpomalení DB kvůli zámkům)?
- Snažte se transakce optimalizovat (viz body v úvodním TL;DR) a vždy přemýšlejte o správném izolačním režimu pro ně.
- Přemýšlejte, zda nemůže transakce se správným režimem zlepšit rychlost aplikace i v místě, kde byste o transakci normálně neuvažovali.
- Nezapomínejte vytvářet vhodné indexy pro UPDATE a DELETE dotazy (a samozřejmě ani pro SELECTy).