PostgreSQL "could not serialize access due to concurrent update": kāpēc tas notiek un ko darīt

PostgreSQL "could not serialize access due to concurrent update" nozīmē, ka jūsu transakcija mēģināja mainīt rindu, kuru cita transakcija izmainīja un apstiprināja pēc tam, kad jūsējā jau bija paņēmusi savu momentuzņēmumu (snapshot). PostgreSQL var vai nu ļaut jums pārrakstīt izmaiņas, kuras jūs nekad neredzējāt, vai pārtraukt jūsu transakciju. Repeatable Read un Serializable līmenī tas to pārtrauc. Risinājums gandrīz nekad nav panākt, lai kļūda pazūd. Jāpanāk, lai lietotne pareizi atkārto transakciju un sadursmes būtu retas.

Ziņojumi un to kopīgais

Jūs redzēsiet kādu no šiem, parasti ORM ietērptu savā izņēmuma tipā:

ERROR:  could not serialize access due to concurrent update

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Ir arī radniecīgs ziņojums could not serialize access due to concurrent delete rindai, kas tika izdzēsta jums zem kājām. Visiem tiem ir SQLSTATE 40001 (serialization_failure). Tieši pēc šī koda, nevis pēc ziņojuma teksta, jāvadās atkārtošanas loģikai.

Kad parādās katrs no tiem

• Read Committed (noklusējums). Šeit šo kļūdu nesaņemsiet. Ja divas transakcijas atjaunina vienu rindu, otrā gaida; kad pirmā apstiprina, otrā vēlreiz pārbauda savu WHERE nosacījumu pret jauno rindas versiju un piemēro izmaiņas tai. Ja lietotne pēkšņi redz 40001, kaut kas ir nomainījis izolācijas līmeni: ietvara noklusējums, ALTER ROLE ... SET default_transaction_isolation vai tiešs BEGIN ISOLATION LEVEL.
• Repeatable Read. Transakcija redz vienu momentuzņēmumu, sākot ar pirmo vaicājumu. Dokumentācijā teikts, ka tā nevar mainīt vai bloķēt rindas, kuras citas transakcijas izmainījušas pēc tās sākuma. Tāpēc UPDATE, DELETE, SELECT ... FOR UPDATE vai FOR SHARE šādai rindai izraisa "concurrent update" (vai "concurrent delete"). Kļūda rodas tikai tad, ja otra transakcija tika apstiprināta. Ja tā tika atcelta, jūsējā turpinās.
• Serializable. Viss iepriekš minētais plus read/write atkarību pārbaude (Serializable Snapshot Isolation). PostgreSQL ar predikātu slēdzenēm seko, ko katra transakcija lasīja. Kad lasīšanas un rakstīšanas kombinācija varētu dot rezultātu, kādu nedotu neviena secīga izpilde, tas atceļ vienu no dalībniekiem. "Reason code" laukā DETAIL norāda, kurā brīdī tas notika. Tas noder PostgreSQL izstrādātājiem un reti maina to, kas jums jādara.

Minimāls Repeatable Read piemērs:

-- sesija A                                 -- sesija B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM items WHERE id = 7;   -- 10
                                            UPDATE items SET stock = 9 WHERE id = 7;
                                            -- autocommit, pabeigts
UPDATE items SET stock = stock - 1 WHERE id = 7;
-- ERROR:  could not serialize access due to concurrent update

1. solis: atkārtojiet visu transakciju, pareizi

Dokumentācija to saka tieši: lietotnēm, kas izmanto šos līmeņus, jābūt gatavām atkārtot. Atkārtošanas noteikumi ir īsi:

1. Atkārtojiet visu transakciju no BEGIN, ieskaitot lietotnes loģiku, kas izlēma, ko rakstīt. Ja atkārtojat tikai neizdevušos UPDATE ar vērtībām, kas aprēķinātas no vecā momentuzņēmuma, jūs atjaunojat tieši to zaudēto atjauninājumu kļūdu, no kuras šī kļūda jūs sargāja.
2. Vadieties pēc SQLSTATE. 40001 var atkārtot bez nosacījumiem. Arī 40P01 (deadlock, skatiet mūsu deadlock detected ceļvedi) ir vērts atkārtot. Unikalitātes pārkāpumus (23505) un izslēgšanas ierobežojumu pārkāpumus (23P01) drīkst atkārtot tikai uzmanīgāk, jo tās var būt pastāvīgas kļūdas.
3. Pievienojiet limitu un pauzi. Dokumentācija brīdina, ka lielas konkurences apstākļos var būt vajadzīgi daudzi mēģinājumi. Ierobežojiet mēģinājumu skaitu, pievienojiet nejaušinātu pauzi (backoff) un reģistrējiet katru atkārtojumu žurnālā, lai redzētu biežumu.
4. Neveiciet ārējas darbības transakcijas iekšienē. E-pasts, maksājumu API izsaukums vai ziņojums, kas nosūtīts pirms commit, atkārtosies ar katru mēģinājumu. Pārnesiet šīs darbības uz laiku pēc commit vai izmantojiet outbox tabulu.

2. solis: noskaidrojiet, kāpēc sadursmes ir biežas

-- kādu izolācijas līmeni sesijas patiesībā izmanto?
SHOW default_transaction_isolation;
SELECT rolname, setconfig FROM pg_roles WHERE setconfig::text LIKE '%isolation%';

-- SERIALIZABLE gadījumā: cik predikātu slēdzeņu ir tieši tagad?
SELECT locktype, count(*) FROM pg_locks
WHERE mode = 'SIReadLock' GROUP BY locktype;

-- garas transakcijas paplašina konfliktu logu
SELECT pid, now() - xact_start AS xact_age, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;

Servera žurnāli parāda neizdevušos vaicājumu, jo log_min_error_statement noklusējums ir error. Sagrupējiet tos pēc vaicājuma teksta, un parasti atradīsiet vienu vai divus karstos punktus: skaitītāja rindu, "pēdējoreiz atjaunināts" kolonnu vecākierakstā, rindas tabulu, bilanci.

3. solis: samaziniet kļūmju biežumu

• Turiet transakcijas īsas. Jo ilgāk dzīvo momentuzņēmums, jo ar vairāk paralēliem commit tas var sadurties. Dokumentācija īpaši brīdina par savienojumiem, kas atstāti "idle in transaction"; iestatiet idle_in_transaction_session_timeout (un PostgreSQL 17+ apsveriet transaction_timeout). Rakstā par idle in transaction parādīts, kā tos atrast.
• Novērsiet karstās rindas. Viena rinda, kuru atjaunina katra transakcija, stingrā izolācijā garantē konfliktus. Sadaliet skaitītājus vairākās rindās un summējiet tās vai pievienojiet notikumus, nevis atjauniniet vienu ierakstu.
• Izmantojiet vājāko līmeni, kas joprojām ir korekts. Daudzas slodzes ir korektas Read Committed līmenī ar atomāriem vaicājumiem, piemēram, UPDATE ... SET stock = stock - 1 WHERE stock > 0. Izmantojiet Repeatable Read vai Serializable tur, kur tie vajadzīgi, nevis kā globālu noklusējumu.
• Serializable gadījumā sekojiet dokumentētajam noskaņošanas sarakstam: deklarējiet transakcijas kā READ ONLY, kad iespējams; kontrolējiet aktīvo savienojumu skaitu ar pūlu; atmetiet tiešās slēdzenes un SELECT FOR UPDATE, kas ar Serializable vairs nav vajadzīgas; dodiet priekšroku indeksu skenēšanai, jo secīga skenēšana vienmēr ņem relācijas līmeņa predikātu slēdzeni.
• Sekojiet predikātu slēdzeņu apvienošanai. Kad predikātu slēdzeņu tabulā pietrūkst vietas, PostgreSQL apvieno lapas līmeņa slēdzenes relācijas līmeņa slēdzenēs, un dokumentācijā norādīts, ka kļūmju biežums var pieaugt. Parametri ir max_pred_locks_per_transaction (noklusējums 64, nepieciešams restarts), max_pred_locks_per_relation (noklusējums -2, kas nozīmē daļu no vienas transakcijas vērtības) un max_pred_locks_per_page (noklusējums 2).

Lielās datubāzēs

• Garas atskaites un pakešdarbi. Vairāku stundu Serializable atskaite, kas skar simtiem miljonu rindu, visu šo laiku tur predikātu slēdzenes un momentuzņēmumu, un tas palielina konfliktu iespēju visiem pārējiem. Palaidiet to kā SERIALIZABLE READ ONLY DEFERRABLE: saskaņā ar dokumentāciju tā sākumā var pagaidīt, līdz iegūst drošu momentuzņēmumu, un pēc tam darbojas bez predikātu slēdzenēm un to nevar atcelt serializācijas kļūmes.
• Secīga skenēšana lielās tabulās. Serializable līmenī viena liela tabulas secīga skenēšana uzliek predikātu slēdzeni visai relācijai. Jebkurš paralēls ieraksts šajā tabulā tad var izskatīties pēc konflikta. Trūkstoši indeksi tāpēc maksā ne tikai laiku, bet arī pārtrauktas transakcijas.
• Pakešdarbi, kas atjaunina daudz rindu. Repeatable Read darbs, kas atjaunina miljoniem rindu, neizdodas, tiklīdz sastop vienu rindu, kuru dzīvā slodze izmainījusi pēc tā sākuma, un atkārtojums sākas no nulles. Lielus atjauninājumus apstrādājiet mazās, atsevišķi apstiprinātās daļās.
• Replikas. Serializable nav pieejams hot standby serverī; PostgreSQL to noraida ar "cannot use serializable mode in a hot standby" un iesaka Repeatable Read. Lietotnēm, kas sūta vienu un to pašu kodu uz primāro serveri un replikām, tas jāņem vērā.
• Mēriet reālā vienlaicīgumā. Serializācijas kļūmju biežums atkarīgs no vienlaicīguma un datu sadalījuma, tāpēc tas parādās tikai produkcijai līdzīgā slodzē uz pilna izmēra datiem. Sekojiet atkārtojumu skaitam katram galapunktam kā metrikai un brīdiniet, kad tas aug.