PostgreSQL "idle in transaction": kā atrast, apturēt un novērst iestrēgušas sesijas

PostgreSQL idle in transaction ir sesijas stāvoklis, kad tā ir sākusi transakciju, kaut ko izpildījusi un tad pārtraukusi sūtīt vaicājumus, ne apstiprinot (commit), ne atsaucot (rollback) izmaiņas. Tā neizmanto CPU un procesu sarakstā izskatās nekaitīga. Patiesībā tā var stundām turēt slēdzenes, likt divu sekunžu migrācijai iesaldēt visu lietotni un neļaut VACUUM izņemt mirušās rindas. Šis ceļvedis parāda, kā šīs sesijas atrast, apturēt tās, nepasliktinot situāciju, un iestatīt ierobežojumus, kas neļauj tām atgriezties.

Ko patiesībā nozīmē "idle in transaction"

pg_stat_activity dokumentācija to definē kā servera procesu, kas "ir transakcijā, bet pašlaik neizpilda vaicājumu". Ir arī tuvs radinieks – idle in transaction (aborted): tā pati situācija, tikai viens no transakcijas vaicājumiem beidzās ar kļūdu. Kamēr klients nenosūta ROLLBACK, katra nākamā komanda šajā transakcijā beidzas ar kļūdu current transaction is aborted, un transakcija paliek atvērta.

No atvērtas transakcijas, ko neviens neizmanto, izriet divu veidu kaitējums:

• Slēdzenes paliek aizņemtas. Rindu slēdzenes no UPDATE vai SELECT ... FOR UPDATE un tabulu līmeņa slēdzenes no jebkura vaicājuma tiek turētas līdz transakcijas beigām. Citas sesijas, kurām vajadzīga konfliktējoša slēdzene, vienkārši gaida.
• VACUUM nevar iztīrīt. Konfigurācijas dokumentācijā tas teikts tieši: pat ja nozīmīgas slēdzenes netiek turētas, "atvērta transakcija neļauj ar VACUUM izņemt nesen mirušas rindas, kas var būt redzamas tikai šai transakcijai", tāpēc ilgstoša dīkstāve veicina tabulu uzpūšanos (bloat). Ja esat lasījuši mūsu ceļvedi par autovacuum un bloat, šī ir "xmin horizonta" problēma.

No kurienes šīs sesijas rodas

Gandrīz vienmēr vaina ir lietotnē, nevis PostgreSQL. Tipiski scenāriji:

• Draiveri, kas transakcijas atver netieši. psycopg 3 dokumentācijā norādīts, ka pēc noklusējuma "pat vienkāršs SELECT sāks transakciju", un, ja programma nekad neveic commit, sesija "paliks idle in transaction". Parasti cieš ilgi strādājoši skripti.
• Darbs transakcijas vidū. Kods atver transakciju, nolasa rindu un tad, pirms ierakstīt rezultātu, izsauc lēnu HTTP API, sūta e-pastu vai gaida ziņojumu rindu.
• Kļūdu apstrāde, kas izlaiž rollback. Izņēmums tiek noķerts un ierakstīts žurnālā, bet savienojums atgriežas pūlā ar joprojām atvērtu transakciju. Tas ir tipisks idle in transaction (aborted) avots.
• Cilvēki. Kāds produkcijā psql ievada BEGIN;, izpilda update, lai kaut ko pārbaudītu, un aiziet pusdienās.

1. solis: atrodiet tās

Sāciet ar abu dīkstāves stāvokļu sesiju sarakstu, vecākā transakcija pirmā:

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start   AS xact_age,
       now() - state_change AS idle_for,
       backend_xmin,
       left(query, 120)     AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

Kā lasīt kolonnas:

• xact_age parāda, cik ilgi transakcija ir atvērta; idle_for – cik ilgs laiks pagājis kopš sesijas pēdējās stāvokļa maiņas. Transakcija, kas atvērta trīs stundas un pēdējās divas ir dīkstāvē, nav lēns vaicājums, bet aizmirsts.
• query ir pēdējais vaicājums, ko sesija izpildīja, nevis tas, kas izpildās tagad. Tomēr tas ir labākais pavediens uz to, kura koda daļa atstāja transakciju atvērtu.
• application_name, usename un client_addr pasaka, ar kura servisa īpašniekiem runāt. application_name iestatīšana katrā savienojuma virknē atmaksājas tieši šajā brīdī.
• Ja backend_xmin ir aizpildīts, sesija aiztur tīrīšanas horizontu.

2. solis: pārbaudiet, vai tā kaut ko bloķē

Dīkstāvē esoša transakcija ir kļūda; dīkstāvē esoša transakcija, aiz kuras rindā stāv citas sesijas, ir incidents. pg_blocking_pids() atgriež sesijas, kas neļauj konkrētam procesam iegūt slēdzeni:

SELECT w.pid               AS waiting_pid,
       left(w.query, 80)   AS waiting_query,
       b.pid               AS blocker_pid,
       b.state             AS blocker_state,
       now() - b.xact_start AS blocker_xact_age
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid)
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock';

Dokumentācijā par šo funkciju ir svarīga piezīme: process tiek uzskatīts par bloķējošu ne tikai tad, ja tas tur konfliktējošu slēdzeni, bet arī tad, ja tas pats to gaida un ir priekšā rindā. Tieši tā dīkstāves transakcija pārvēršas pārtraukumā. Migrācija izpilda ALTER TABLE, kam vajadzīga ekskluzīva slēdzene, un tā gaida aiz dīkstāves sesijas; pēc tam parastie vaicājumi šai tabulai nostājas rindā aiz gaidošā ALTER TABLE. Risinājums ir tā pati sesija ķēdes sākumā. Dokumentācija arī brīdina, ka ļoti bieža funkcijas izsaukšana zināmā mērā ietekmē veiktspēju, tāpēc lietojiet to diagnostikai, nevis aptaujai katru sekundi.

Plašāk par to, kā veidojas slēdzeņu ķēdes, skatiet mūsu rakstā par PostgreSQL "deadlock detected".

3. solis: apturiet to droši

PostgreSQL ir divas signālu funkcijas, un atšķirība šeit ir būtiska:

• pg_cancel_backend(pid) atceļ sesijas pašreizējo vaicājumu. Idle in transaction sesija neko neizpilda, tāpēc šis nav rīks tās transakcijas pārtraukšanai.
• pg_terminate_backend(pid, timeout) pārtrauc visu sesiju. Tās atvērtā transakcija tiek atsaukta, un slēdzenes atbrīvotas. Ar pozitīvu timeout milisekundēs funkcija gaida, līdz process patiešām beidzies, un, ja tā nav, atgriež false ar brīdinājumu.

Pirms pārtraukšanas izlemiet, vai darbs šajā transakcijā ir svarīgs. Ja tā pieder pakešuzdevumam, kas raksta jau stundu, pārtraucot šī stunda tiek izmesta, un uzdevums var vienkārši sākties no jauna. Ja iespējams, runājiet ar īpašnieku. Ja produkcija ir bloķēta, vispirms pārtrauciet ķēdes sākumu un pārbaudiet vēlreiz; reti vajag pārtraukt visu.

Tīrīšanas vaicājums acīmredzami aizmirstām sesijām izskatās šādi:

SELECT pid, pg_terminate_backend(pid, 5000)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND backend_type = 'client backend'
  AND now() - state_change > interval '30 minutes';

Pēc noklusējuma šīs funkcijas var izsaukt tikai superlietotāji; sesijas lomas dalībnieki un lomas ar pg_signal_backend var sūtīt signālus sesijām, kas nepieder superlietotājiem.

4. solis: novērsiet to ar taimautiem

idle_in_transaction_session_timeout pārtrauc jebkuru sesiju, kas atvērtā transakcijā dīkstāvē pavadījusi ilgāk par norādīto laiku. Tas pastāv kopš PostgreSQL 9.6 un pēc noklusējuma ir izslēgts (0). Vērtības bez mērvienības ir milisekundes. Pārtrauktā sesija beidzas ar SQLSTATE 25P03; dīkstāvē esošs klients to parasti pamana tikai tad, kad nākamreiz mēģina izmantot savienojumu, tāpēc pārliecinieties, ka lietotne to apstrādā kā "pieslēgties no jauna un atkārtot", nevis avarē.

PostgreSQL 17 pievienoja transaction_timeout, kas ierobežo transakcijas kopējo ilgumu – aktīvas vai dīkstāvē – un pārtrauc sesiju, ja tas pārsniegts (SQLSTATE 25P04). Ja tas ir īsāks par idle_in_transaction_session_timeout vai statement_timeout vai vienāds ar tiem, garākais taimauts tiek ignorēts. Dokumentācija neiesaka transaction_timeout un statement_timeout iestatīt globāli postgresql.conf, jo tie ietekmētu katru sesiju, arī apkopes darbus. Uz sagatavotajām (prepared) transakcijām transaction_timeout neattiecas.

Iestatot ierobežojumus konkrētai lomai vai datubāzei, tie paliek mērķtiecīgi:

-- konkrētai lietotnes lomai (attiecas uz jaunām sesijām)
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

-- vai visai datubāzei
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '10min';

-- PostgreSQL 17+: ierobežo visu transakciju, aktīvu vai dīkstāvē
ALTER ROLE report_user SET transaction_timeout = '2h';

Ir arī idle_session_timeout (PostgreSQL 14+), taču tas paredzēts sesijām, kas dīkstāvē ārpus transakcijas. Dokumentācija norāda, ka šādas sesijas nerada lielas izmaksas, un brīdina to nelietot savienojumiem caur pūlu, kas var slikti reaģēt uz negaidītu atvienošanu.

Ja izmantojat PgBouncer

Transakciju pūla režīmā klients, kas ir idle in transaction, tur sev piesaistītu servera savienojumu, tāpēc daži šādi klienti var iztukšot nelielu pūlu. PgBouncer ir savs idle_transaction_timeout: klients, kas stāvoklī "idle in transaction" paliek ilgāk par norādīto sekunžu skaitu, tiek atvienots. Pēc noklusējuma tas ir izslēgts. Iestatījums pūlā aizsargā pašu pūlu, bet PostgreSQL iestatījums – slēdzenes un VACUUM; ir saprātīgi izmantot abus. Mūsu PgBouncer ceļvedī sīkāk aplūkota pūla izmēra izvēle.

Ko uzraudzīt

• Vecākās transakcijas vecumu stāvoklī idle in transaction vai idle in transaction (aborted) no pg_stat_activity. Brīdiniet pie sliekšņa, kas atbilst jūsu slodzei, piemēram, dažām minūtēm OLTP lietotnei.
• Sesiju skaitu, kas gaida slēdzenes (wait_event_type = 'Lock'). Pēkšņam lēcienam parasti ir viens bloķētājs.
• Kļūdas ar SQLSTATE 25P03 un 25P04 lietotņu žurnālos pēc taimautu ieslēgšanas. Tās parāda, kuri koda ceļi joprojām atstāj transakcijas atvērtas.