MySQL "Deadlock found when trying to get lock" (kļūda 1213): kā lasīt žurnālu un novērst atkārtošanos

"Deadlock found when trying to get lock; try restarting transaction" ir MySQL kļūda 1213. Tā rodas, kad divām vai vairākām InnoDB transakcijām katrai ir slēdzene, kas vajadzīga otrai. InnoDB pamana šo ciklu, izvēlas upuri, atritina to un ļauj otrai transakcijai pabeigt darbu. Atsevišķs deadlock nav avārija. Simtiem gadījumu stundā vai tie paši divi vaicājumi, kas saduras katru dienu, parasti norāda uz labojamu problēmu tajā, kā lietotne strādā ar datiem.

Ko kļūda pasaka

Pilns kļūdas teksts izskatās šādi:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Saskaņā ar MySQL servera kļūdu sarakstu kļūdas nosaukums ir ER_LOCK_DEADLOCK un SQLSTATE ir 40001, standarta serializācijas kļūmes klase. Trīs fakti no dokumentācijas nosaka visu pārējo:

• Visa transakcija ir zudusi. InnoDB kļūdu apstrādes lapā teikts, ka deadlock gadījumā InnoDB atritina visu transakciju, un ieteikts atkārtot visu transakciju. Tas atšķiras no slēdzenes gaidīšanas taimauta (kļūda 1205), kas pēc noklusējuma atritina tikai to vaicājumu, kuram iestājās taimauts.
• InnoDB izvēlas mazus upurus. Deadlock noteikšanas lapā teikts, ka InnoDB cenšas atritināt mazas transakcijas, un izmēru mēra pēc ievietoto, atjaunināto vai dzēsto rindu skaita.
• Noteikšana pēc noklusējuma ir ieslēgta. innodb_deadlock_detect noklusējuma vērtība ir ON, tāpēc kļūda atgriežas gandrīz uzreiz, nevis pēc innodb_lock_wait_timeout sekundēm.

Ja redzat 1205, nevis 1213, tā ir cita problēma: viena transakcija vienkārši pārāk ilgi gaida otru. To aplūkojam rakstā par "Lock wait timeout exceeded".

Klasiskais scenārijs: tās pašas rindas, pretēja secība

Dokumentācija apraksta biežāko cēloni: transakcijas bloķē rindas vairākās tabulās vai vairākas rindas vienā tabulā, bet pretējā secībā. Minimāla versija:

-- Sesija A                            -- Sesija B
UPDATE accounts SET ... WHERE id = 1;   UPDATE accounts SET ... WHERE id = 2;
UPDATE accounts SET ... WHERE id = 2;   UPDATE accounts SET ... WHERE id = 1;
--        gaida B                       --   deadlock: viena no tām saņem 1213

Katrai sesijai pieder rinda, kas otrai vajadzīga nākamā. Neviena nevar turpināt, tāpēc InnoDB ir jāpārtrauc cikls. Reālā kodā tie reti ir divi burtiski UPDATE. Biežāk viens koda ceļš atjaunina pasūtījumu un pēc tam noliktavas rindu, bet cits koda ceļš, ko gadu vēlāk uzrakstījis kāds cits, atjaunina noliktavu un pēc tam pasūtījumu.

Dokumentācijā arī norādīts, ka deadlock var rasties transakcijās, kas ievieto vai dzēš tikai vienu rindu, jo šīs operācijas uzliek slēdzenes vairākiem šīs rindas indeksu ierakstiem. "Mēs taču aiztiekam tikai vienu rindu" nav pierādījums, ka deadlock nav iespējams.

1. solis: izlasiet LATEST DETECTED DEADLOCK

SHOW ENGINE INNODB STATUS (vajadzīga PROCESS privilēģija) satur sadaļu ar pēdējo deadlock. Deadlock piemērs dokumentācijā parāda tās struktūru:

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
*** (1) HOLDS THE LOCK(S):
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
*** (2) HOLDS THE LOCK(S):
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
*** WE ROLL BACK TRANSACTION (2)

Katrai transakcijai redzams vaicājums, ko tā izpildīja, slēdzenes, kas tai piederēja, un slēdzene, ko tā gaidīja, un pēc tam, kuru transakciju InnoDB atritināja. Lasot pievērsiet uzmanību:

• Diviem vaicājumiem. Atrodiet tos kodā. Jautājums gandrīz vienmēr ir: "kāpēc šie divi koda ceļi bloķē tās pašas rindas dažādā secībā?"
• Indeksa nosaukumam katrā slēdzenes rindā. Slēdzene uz PRIMARY ir gaidāma; slēdzene uz negaidīta indeksa vai liels slēdzeņu skaits vaicājumam, kam vajadzētu aiztikt vienu rindu, bieži nozīmē, ka vaicājums skenē vairāk, nekā vajag.
• Slēdzenes režīmam. InnoDB izmanto koplietošanas (S) un ekskluzīvās (X) slēdzenes, kā arī ieraksta, spraugas (gap) un next-key variantus. Gap slēdzene izvadē pie REPEATABLE READ nozīmē, ka vaicājums bloķēja diapazonu, nevis tikai esošās rindas.

Āķis: šī sadaļa rāda tikai pēdējo deadlock. Noslogotā serverī tā tiek pārrakstīta, pirms paspējat paskatīties.

2. solis: reģistrējiet katru deadlock un skaitiet tos

Ieslēdziet innodb_print_all_deadlocks. Tā noklusējuma vērtība ir OFF, to var mainīt darbības laikā, un saskaņā ar InnoDB parametru aprakstu tā ieraksta kļūdu žurnālā informāciju par visiem deadlock gadījumiem InnoDB lietotāju transakcijās:

SET GLOBAL innodb_print_all_deadlocks = ON;
SELECT @@log_error;

Dokumentācija iesaka to izslēgt, kad atkļūdošana pabeigta. Dažas komandas to atstāj ieslēgtu pastāvīgi, jo ieraksti parādās tikai tad, kad deadlock notiek. Izlemiet pēc tā, cik strauji jau aug jūsu kļūdu žurnāls. Atcerieties, ka SET GLOBAL pēc restarta zūd, ja iestatījumu neieliekat arī konfigurācijas failā vai neizmantojat SET PERSIST.

Tendencei InnoDB uztur skaitītāju:

SELECT name, status, count
FROM information_schema.INNODB_METRICS
WHERE name = 'lock_deadlocks';

Dokumentācijas INNODB_METRICS sarakstā lock_deadlocks ir norādīts kā pēc noklusējuma ieslēgts. Ja jūsu serverī status rāda disabled, ieslēdziet to ar innodb_monitor_enable. Eksportējiet skaitītāju uz monitoringu un brīdiniet par ātrumu, nevis par atsevišķiem gadījumiem. Pēkšņs lēciens pēc izlaidiena tieši norāda uz izmaiņu, kas to izraisīja.

Lai redzētu, kam slēdzenes pieder un kas tās gaida tieši tagad, performance_schema.data_locks un data_lock_waits rāda līdzīgu slēdzeņu informāciju tabulas veidā, ieskaitot slēdzenes režīmu, statusu un bloķētajām atslēgu vērtībām.

3. solis: biežākie cēloņi un to labojumi

Nekonsekventa bloķēšanas secība

Dokumentācija iesaka izmaiņas vairākās tabulās vai vairākās rindās katru reizi veikt vienā un tajā pašā secībā, lai transakcijas veidotu rindu, nevis ciklu. Praktiski: vienmēr atjauniniet vecāku pirms bērna; atjauninot rindu kopu, vispirms sakārtojiet atslēgas; katru izmaiņu veidu turiet vienā funkcijā vai saglabātajā procedūrā, nevis atkārtojiet līdzīgas UPDATE secības dažādās vietās.

Trūkstoši vai slikti indeksi

Šis daudzus pārsteidz. Bloķēšanas dokumentācijā teikts, ka bloķējošs lasījums, UPDATE vai DELETE parasti uzliek ieraksta slēdzenes katram indeksa ierakstam, ko tas skenē, neatkarīgi no tā, vai WHERE nosacījums rindu vēlāk izslēdz. Ja piemērota indeksa nav un MySQL skenē visu tabulu, tiek bloķēta katra tabulas rinda. UPDATE ... WHERE status = 'new' AND customer_id = ? bez indeksa uz customer_id var bloķēt daudz vairāk nekā viena klienta rindas un sadurties ar visu. Pārbaudiet deadlock vaicājumus ar EXPLAIN; lēno vaicājumu žurnāls bieži rāda tos pašus vaicājumus kā lēnus.

Vienas atslēgas vienlaicīga ievietošana

Dokumentācijā aprakstīts gadījums ar trim sesijām. Kad INSERT sastopas ar dublētu atslēgu, InnoDB uzliek koplietošanas slēdzeni dublētajam indeksa ierakstam. Ja sesija, kurai pieder ekskluzīvā slēdzene, pēc tam atritina transakciju, visas gaidošās sesijas saņem koplietošanas slēdzenes un visām vajag ekskluzīvās:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

-- Sesija 1: START TRANSACTION; INSERT INTO t1 VALUES (1);
-- Sesija 2: START TRANSACTION; INSERT INTO t1 VALUES (1);   -- gaida
-- Sesija 3: START TRANSACTION; INSERT INTO t1 VALUES (1);   -- gaida
-- Sesija 1: ROLLBACK;
--   sesijām 2 un 3 abām ir koplietošanas slēdzene un abām vajag ekskluzīvo

Tas pats var notikt, ja pirmā sesija rindu izdzēš un apstiprina. Tas ir tipiski loģikai "ievieto, ja nav", ko vienlaikus darbina daudzi procesi. INSERT ... ON DUPLICATE KEY UPDATE uzvedas citādi: pie dublikāta tas ņem ekskluzīvu, nevis koplietošanas slēdzeni. Vai tas novērš jūsu konkrēto deadlock, atkarīgs no pārējās transakcijas, tāpēc pārbaudiet to ar to deadlock, ko patiešām redzat.

Garas transakcijas un bloķējoši lasījumi

Jo ilgāk transakcija tur slēdzenes, jo vairāk iespēju sadurties. Dokumentācijas padoms: turiet transakcijas mazas un īsas, apstipriniet uzreiz pēc saistītu izmaiņu kopas un nepievienojiet FOR UPDATE vai FOR SHARE vaicājumam SELECT, kas var lasīt vecāku momentuzņēmumu.

Izolācijas līmenis

InnoDB noklusējuma izolācijas līmenis ir REPEATABLE READ. Pie READ COMMITTED bloķējoši lasījumi, atjauninājumi un dzēšanas bloķē tikai indeksa ierakstus, nevis spraugas (gap bloķēšana paliek ārējo atslēgu un dublētu atslēgu pārbaudēm), un slēdzenes rindām, kas neatbilst WHERE nosacījumam, tiek atbrīvotas pēc novērtēšanas. Dokumentācijā teikts, ka tas ievērojami samazina deadlock iespējamību, bet tie joprojām var notikt. Izolācijas līmeņa maiņa maina lietotnes redzamības noteikumus, tāpēc uztveriet to kā arhitektūras lēmumu, nevis ātru labojumu. Ja binārais žurnāls ir ieslēgts, ņemiet vērā, ka saskaņā ar dokumentāciju ar READ COMMITTED tiek atbalstīta tikai rindu bāzes (row-based) binārā žurnalēšana.

4. solis: atkārtojiet pareizi

Dokumentācija ir tieša: vienmēr esiet gatavi atkārtot transakciju, kas neizdevās deadlock dēļ. Pat ar perfektu bloķēšanas secību daži gadījumi būs. Drošai atkārtošanai ir trīs īpašības:

1. Tā atkārto visu transakciju no BEGIN, ieskaitot lasījumus, kuru rezultāti tika izmantoti vēlākos vaicājumos. Ja pēc 1213 atkārto tikai neizdevušos vaicājumu, tas izpildās bez transakcijas iepriekšējām izmaiņām, jo tās jau ir atritinātas.
2. Tā ir ierobežota. Divi vai trīs mēģinājumi, pēc tam skaidra kļūda. Bezgalīga atkārtošana slēpj reālu problēmu un palielina slodzi vissliktākajā brīdī.
3. Tā pirms jauna mēģinājuma nogaida īsu nejaušu laiku, lai tās pašas divas transakcijas atkal nesadurtos vienā ritmā.

for attempt in 1..3:
    try:
        BEGIN
        ... visi transakcijas vaicājumi ...
        COMMIT
        break
    except error 1213:          # SQLSTATE 40001
        ROLLBACK                # InnoDB to jau atrituļoja; tas sakārto sesiju
        sleep(random 50-200 ms * attempt)
        continue                # atkārto VISU transakciju, nevis tikai pēdējo vaicājumu
raise pēc pēdējā mēģinājuma

Uzmanieties ar visu, kas atkārtotajā blokā notiek ārpus datubāzes: e-pastiem, HTTP izsaukumiem, rindas ziņojumiem. Tie netiek atritināti kopā ar transakciju un izpildīsies atkal katrā mēģinājumā. Pārceliet tos aiz commit.

Ja izmantojat arī PostgreSQL, mehānika tur ir līdzīga, bet diagnostika atšķiras; skatiet mūsu rakstu par PostgreSQL "deadlock detected".