PostgreSQL "deadlock detected": ko tas nozīmē un kā to novērst

Agrāk vai vēlāk katra noslogota lietotne to ieraksta žurnālā: ERROR: deadlock detected. PostgreSQL strupceļš (deadlock) nav datubāzes kļūda un nav pazīme, ka serveris ir pārslogots. Tas nozīmē, ka divas (vai vairākas) transakcijas katra turēja slēdzeni, kas bija vajadzīga otrai, un neviena nekad nevarētu pabeigt darbu. PostgreSQL to pamanīja un vienu no tām atcēla. Labā ziņa: strupceļus gandrīz vienmēr izraisa daži modeļi lietotnes kodā, un, tos zinot, tos var novērst.

Kas patiesībā notiek

PostgreSQL dokumentācijā izmantots klasisks piemērs. Transakcija A atjaunina kontu 11111. Transakcija B atjaunina kontu 22222 un tad mēģina atjaunināt 11111, tāpēc gaida A. Tagad A mēģina atjaunināt 22222 un gaida B. Katra gaida otru. Bez iejaukšanās tās gaidītu mūžīgi.

PostgreSQL to nepārbauda katrā slēdzenes gaidīšanas reizē, jo pārbaude ir dārga. Sesija, kas slēdzeni gaidījusi ilgāk par deadlock_timeout (noklusējums 1 sekunde), palaiž strupceļa pārbaudi. Ja tā atrod ciklu, tā pārtrauc vienu no transakcijām ar SQLSTATE 40P01 (deadlock_detected), un pārējās turpina. Kura transakcija tiks atcelta, dokumentācijas vārdiem, ir "grūti paredzams, un uz to nevajadzētu paļauties".

Divas praktiskas sekas: atceltā transakcija tiek pilnībā atritināta, tāpēc lietotnei ar to jātiek galā; un katrs strupceļš iesaistītajām sesijām maksā vismaz sekundi gaidīšanas, pirms tas tiek atrisināts.

Kā lasīt žurnāla ziņojumu

Kļūdai servera žurnālā pievienota informācija, kas precīzi parāda, kurš ko gaidīja. Tā izskatās aptuveni šādi:

ERROR:  deadlock detected
DETAIL:  Process 4121 waits for ShareLock on transaction 9182736; blocked by process 4188.
        Process 4188 waits for ShareLock on transaction 9182730; blocked by process 4121.
        Process 4121: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222
        Process 4188: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 11111
HINT:  See server log for query details.
CONTEXT:  while updating tuple (12,7) in relation "accounts"

Svarīgākais ir abi priekšraksti un relācijas nosaukums. "ShareLock on transaction" ir veids, kā PostgreSQL gaida citas transakcijas turētu rindas slēdzeni, tātad šis ir rindu līmeņa strupceļš starp diviem UPDATE. Atcerieties, ka žurnāls rāda tikai katras transakcijas pēdējo priekšrakstu. Slēdzeni, kas radīja problēmu, parasti paņēma kāds agrāks priekšraksts tajā pašā transakcijā, tāpēc pilnu secību bieži var redzēt tikai lietotnes kodā, ne žurnālā.

Modeļi, kas slēpjas aiz lielākās daļas strupceļu

1. Tās pašas rindas, atšķirīga secība

Šis ir dokumentācijas piemērs, un tas ir krietni biežākais. Divi koda ceļi atjaunina vienu un to pašu rindu kopu, bet atšķirīgā secībā: pārskaitījums no A uz B un no B uz A, vai pakešuzdevums, kas iet cauri pasūtījumiem pēc datuma, kamēr tīmekļa lietotne tos atjaunina pēc klienta.

Risinājums: vienmēr slēdziet rindas vienādā secībā. Ja transakcija skars vairākas rindas, vispirms tās sakārtojiet (piemēram, pēc primārās atslēgas) un atjauniniet šajā secībā. Pakešu atjauninājumiem pievienojiet ORDER BY priekšrakstam SELECT ... FOR UPDATE, kas paņem slēdzenes jau sākumā.

2. Slēdzenes pastiprināšana transakcijas laikā

Transakcija nolasa rindu ar SELECT ... FOR SHARE un vēlāk to atjaunina. Divas transakcijas to dara vienlaikus: abas saņem koplietojamo slēdzeni, un pēc tam abas gaida, kad otra to atbrīvos, lai varētu atjaunināt. Šeit tieši der dokumentācijas padoms: pirmajai slēdzenei, kas tiek paņemta objektam, jābūt "visstingrākajā režīmā, kāds būs vajadzīgs".

Risinājums: ja plānojat rindu atjaunināt, slēdziet to ar SELECT ... FOR UPDATE (vai FOR NO KEY UPDATE, ja nemaināt atslēgas kolonnas) jau no sākuma.

3. Aizmirstās ārējās atslēgas

Ievietojot vai atjauninot rindu, kas atsaucas uz citu tabulu, tiek paņemta koplietojama slēdzene uz norādīto vecākrindu, lai pārbaudes laikā vecāku nevarētu izdzēst vai mainīt tā atslēgu. Daudzi bērnrindu ievietojumi plus vecākrindas atjauninājums citā secībā var nonākt strupceļā, lai gan lietotnes kodā slēdzenes vispār nav minētas. Tipisks gadījums: pasūtījuma rindu ievietošana un pēc tam pasūtījuma kopsummas atjaunināšana divās vienlaicīgās transakcijās vienam un tam pašam pasūtījumam.

Risinājums: vispirms slēdziet vecāku (atjauniniet pasūtījuma rindu, tad ievietojiet pozīcijas) un turiet šīs transakcijas īsas.

4. Lieli pakešu atjauninājumi blakus tiešsaistes slodzei

Viens UPDATE, kas skar 200 000 rindu, paņem rindu slēdzenes pa vienai tādā secībā, kādā plāns tās apmeklē. Tikmēr lietotne atjaunina atsevišķas rindas no tās pašas kopas. Jo ilgāk darbojas pakete, jo vairāk iespēju ciklam.

Risinājums: apstrādājiet paketes nelielās daļās, noteiktā secībā, katru savā īsā transakcijā. Tas palīdz arī replikācijai un vacuum.

5. Tiešas tabulu slēdzenes un DDL

Kods, kas izpilda LOCK TABLE vairākām tabulām, vai migrācijas, kas maina tabulas citā secībā, nekā tām piekļūst lietotne, var nonākt strupceļā tabulu līmenī. Shēmas izmaiņas bieži rada nepatīkamas slēdzeņu rindas pat bez strupceļa; drošus veidus, kā tās veikt, aprakstām rakstā PostgreSQL migrācijas bez dīkstāves.

Strupceļu un slēdzeņu gaidīšanas monitorings

• Skaitiet tos. Skatā pg_stat_database katrai datubāzei ir kolonna deadlocks. Attēlojiet biežumu grafikā: pēkšņs lēciens tūlīt pēc izvietošanas norāda tieši uz izmaiņām.
• Ieslēdziet log_lock_waits. Pēc noklusējuma tas ir off (žurnalēšanas iestatījumi). Kad tas ieslēgts, PostgreSQL žurnālā ieraksta katru slēdzenes gaidīšanu, kas ilgāka par deadlock_timeout. Tas parāda "gandrīz strupceļus" un garas slēdzeņu rindas, kas pasliktina latentumu pat tad, ja nekas netiek atcelts.
• Redziet, kurš kuru bloķē tieši tagad:

SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS xact_age, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

• Aizsargājieties pret ilgu gaidīšanu. lock_timeout sesijai vai lomai liek priekšrakstam padoties, nevis bezgalīgi gaidīt aiz slēdzenes; tas ir īpaši noderīgi migrācijām. idle_in_transaction_session_timeout neļauj aizmirstām transakcijām stundām turēt slēdzenes.

Par pašu deadlock_timeout: dokumentācija 1 sekundi sauc par "droši vien mazāko vērtību, ko praksē vēlētos", un iesaka to palielināt ļoti noslogotos serveros (slēdzeņu pārvaldības iestatījumi). Samazināšana strupceļus nenovērš, tā tikai ātrāk par tiem ziņo uz biežāku pārbaužu rēķina.

Īss kontrolsaraksts, kad parādās strupceļi

1. Paņemiet no žurnāla priekšrakstus un relāciju nosaukumus.
2. Atrodiet kodā transakcijas, kas tos izpilda, un uzskaitiet visus priekšrakstus katrā no tām pēc kārtas.
3. Meklējiet atšķirīgu rindu secību, koplietojamu slēdzeni, kam seko ekskluzīva, ārējās atslēgas un pakešuzdevumus, kas skar tās pašas rindas.
4. Izlabojiet secību vai slēdzenes režīmu; saīsiniet transakcijas.
5. Datu piekļuves slānī pievienojiet atkārtojumu kļūdai 40P01.
6. Vērojiet skaitītāju deadlocks, lai apstiprinātu labojumu.

Slēgšanas problēmas bieži slēpjas aiz frāzes "datubāze ir lēna". Ja jūsu lēnie vaicājumi patiesībā gaida, nevis strādā, mūsu PostgreSQL lēno vaicājumu optimizācijas kontrolsaraksts parāda, kā to atšķirt.