MySQL "Lock wait timeout exceeded" (kļūda 1205): atrodiet bloķētāju un novērsiet to

MySQL "Lock wait timeout exceeded" ir kļūda, ko saņemat, kad vaicājums pārāk ilgi gaidījis rindu, ko tur cita transakcija. Ziņojums ir ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, un tajā dotais padoms ir tikai daļēji pareizs. Transakcijas atkārtošana var izdoties. Bet, ja kaut kas sēž uz šīm rindām, atkārtojums gaidīs vēl 50 sekundes un izgāzīsies tieši tāpat. Noderīgais jautājums nav "kā likt kļūdai pazust", bet "kurš tur bloķējumu un kāpēc tik ilgi?"

Kas patiesībā notika

InnoDB bloķē rindas, ko transakcija maina, un rindas, kas nolasītas ar SELECT ... FOR UPDATE vai FOR SHARE. Šie bloķējumi paliek, līdz transakcija tiek apstiprināta vai atritināta. Citai transakcijai, kas grib rakstīt to pašu rindu, jāgaida. innodb_lock_wait_timeout nosaka, cik ilgi: pēc noklusējuma 50 sekundes. Pēc tam gaidošais vaicājums saņem kļūdu 1205.

Trīs detaļas bieži pārsteidz:

• Atritināts tiek tikai vaicājums, nevis transakcija. Pēc noklusējuma InnoDB atceļ tikai to vaicājumu, kuram iestājās taimauts. Viss, ko transakcija paveica pirms tam, joprojām ir tur, neapstiprināts, un tās bloķējumi joprojām tiek turēti. Ja jūsu kods noķer kļūdu un turpina darbu vai veic commit, datubāzē var nonākt puse transakcijas. Iestatījums innodb_rollback_on_timeout to maina, lai tiktu atritināta visa transakcija, taču to var iestatīt tikai servera startēšanas brīdī.
• Tas nav strupceļš (deadlock). Strupceļā divas transakcijas gaida viena otru, un neviena nevar pabeigt. InnoDB to konstatē uzreiz un vienu atritina ar kļūdu 1213. Bloķējuma gaidīšanas taimauts nozīmē, ka viena transakcija vienkārši pārāk ilgi gaidīja aiz citas. Bloķētājs, iespējams, galu galā pabeigtu darbu, tikai ne laikā. PostgreSQL strupceļus aplūkojam mūsu ceļvedī par "deadlock detected".
• Tas attiecas tikai uz rindu bloķējumiem. Metadatu bloķējumu gaidīšanu, piemēram, vaicājumu, kas iestrēdzis aiz ALTER TABLE, kontrolē cits mainīgais, lock_wait_timeout, kura noklusējums ir viens gads.

1. solis: atrodiet bloķētāju, kamēr tas notiek

sys shēmā ir skats, kas radīts tieši tam. sys.innodb_lock_waits parāda katru gaidošo transakciju, ko tā gaida un kura sesija to bloķē:

SELECT wait_age_secs, locked_table_name, locked_index,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query, sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

Kolonna blocking_pid ir bloķējošās sesijas procesu saraksta ID, un sql_kill_blocking_connection satur gatavu KILL komandu tai. Pirms to izpildāt, paskatieties uz blocking_query. Ja tas ir NULL, bloķējošā sesija šobrīd neko neizpilda. Tas ir biežākais gadījums un svarīgākā norāde: transakcija, kas sākās, mainīja vai bloķēja dažas rindas un tad apklusa bez commit.

Ja vairākas sesijas bloķē viena otru ķēdē, meklējiet to, kas ir ķēdes sākumā: tā bloķē citas, bet pati neko negaida. Sesiju pārtraukšana ķēdes vidū nepalīdz.

2. solis: apskatiet vecākās atvērtās transakcijas

Pat ja šobrīd nekas negaida, ilgi atvērtas transakcijas ir vieta, no kurienes nāks nākamais taimauts. information_schema.innodb_trx uzskaita katru darbojošos InnoDB transakciju:

SELECT trx_mysql_thread_id AS pid, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

Transakcija, kas ir minūtes vai stundas veca un kurai trx_query ir NULL, gandrīz vienmēr nozīmē lietotnes kodu, kas atvēra transakciju un nekad to neaizvēra. Tipiski vaininieki:

• Transakcija paliek atvērta, kamēr kods izsauc ārēju API, sūta e-pastu vai gaida lietotāju.
• Savienojumu pūla vai ORM iestatījumos izslēgts autocommit, tāpēc katrs savienojums klusi tur atvērtu transakciju.
• Kļūdas apstrādes ceļš, kas atgriež savienojumu pūlā bez atritināšanas.
• Kāda interaktīvā klienta sesija ar UPDATE, ko aizmirsa apstiprināt pirms pusdienām.

3. solis: izlemiet, ko pārtraukt

Bloķētāja pārtraukšana atbrīvo tā bloķējumus, bet arī atritina visu, ko šī transakcija paveikusi. Neaktīvai sesijai, kas aizmirsa commit, tas parasti ir pieņemami, un tas ir ātrākais ceļš ārā. Lielam pakešuzdevumam, kas rindas mainījis divdesmit minūtes, atritināšana var ilgt ilgi un visu šo laiku turēt bloķējumus. Tādā gadījumā var būt ātrāk apturēt jauna darba uzkrāšanos un ļaut pakešuzdevumam pabeigt.

Nepārtrauciet gaidošās sesijas. Tās ir upuri, nevis cēlonis. Katru no tām aizstās jauns pieprasījums, kas gaidīs uz tām pašām rindām. Un, ja gaidīšanas krājas pietiekami ātri, nākamā kļūda būs "Too many connections"; tam skatiet mūsu rīcības plānu kļūdai 1040.

Kāpēc transakcijas tur bloķējumus ilgāk, nekā domājat

Daži cēloņi ir mazāk acīmredzami nekā aizmirsts commit:

• Trūkstoši indeksi. InnoDB bloķē indeksa ierakstus, ko tas skenē, ne tikai rindas, ko maina. Kā teikts rokasgrāmatā, ja nav piemērota indeksa un MySQL jāskenē visa tabula, tiek bloķēta katra tabulas rinda, un citu lietotāju ievietošanas tiek bloķētas. UPDATE ... WHERE status = 'pending' bez indeksa uz status var bloķēt daudz vairāk nekā tās dažas rindas, ko tas maina.
• Atstarpju bloķējumi (gap locks). Noklusējuma izolācijas līmenī REPEATABLE READ diapazona nosacījumi bloķē arī atstarpes starp indeksa ierakstiem, tāpēc arī ievietošanas šajā diapazonā gaida.
• Lieli pakešu atjauninājumi. Viens UPDATE vai DELETE, kas skar miljonu rindu, tur miljonu rindu bloķējumu līdz commit. Sadaliet to pa dažiem tūkstošiem rindu ar commit pēc katras paketes.
• Karstās rindas. Skaitītāji, bilances rindas vai "pēdējā ID" rinda, ko atjaunina katrs pieprasījums. Katra transakcija stājas rindā pēc tās pašas rindas, un jebkas lēns vienā no tām aizkavē visas pārējās.

Kā panākt, lai tas notiktu reti

1. Turiet transakcijas īsas. Rokasgrāmatas padoms ir veikt pēc iespējas mazāk darba starp pirmo izmaiņu un commit. Lēno darbu, piemēram, API izsaukumus, failu operācijas un aprēķinus, veiciet pirms transakcijas atvēršanas vai pēc commit.
2. Atkārtojiet visu transakciju, nevis tikai vaicājumu. Bloķējumu gaidīšanas un strupceļi ir normāli noslogotā serverī, un lietotnei tie jāparedz. Noķeriet 1205 un 1213, skaidri atritiniet, pagaidiet īsu nejaušu intervālu un palaidiet visu transakciju vēlreiz ierobežotu reižu skaitu.
3. Indeksējiet kolonnas, pēc kurām filtrējat UPDATE, DELETE un SELECT ... FOR UPDATE. Mazāk skenētu rindu nozīmē mazāk bloķējumu.
4. Rindām izmantojiet NOWAIT vai SKIP LOCKED. Darbiniekiem, kas ņem uzdevumus no tabulas, nevajadzētu stāvēt rindā vienam aiz otra:

   START TRANSACTION;
   SELECT id FROM jobs
   WHERE status = 'queued'
   ORDER BY id
   LIMIT 10
   FOR UPDATE SKIP LOCKED;
   -- process the rows, then
   COMMIT;

5. Brīdiniet par vecām transakcijām. OLTP sistēmā transakcija, kas vecāka par dažām minūtēm, ir paziņojuma vērta ilgi pirms tā pārvēršas taimautu vētrā. Augstāk dotais innodb_trx vaicājums ir gatava pārbaude.