Saka, takatarisa nyaya dzine chekuita nazvo insulation, ndokudzokera kumashure kuronga data pamwero wakaderera. Uye pakupedzisira takasvika kune inonyanya kunakidza chikamu - iyo tambo shanduro.

Header

Sezvatakambotaura, mutsara wega wega unogona kuvapo panguva imwe chete mune akati wandei mudura re database. Imwe vhezheni inofanira kusiyaniswa neimwe nzira.Nechinangwa ichi, vhezheni imwe neimwe ine mamakisi maviri anotara "nguva" yechiito cheshanduro ino (xmin uye xmax). Mune makotesheni - nekuti haisi nguva seyakadaro inoshandiswa, asi yakakosha kaunda inowedzera. Uye kaunda iyi ndiyo nhamba yekutengeserana.

(Senguva dzose, chokwadi chakanyanya kuoma: nhamba yekutengeserana haigoni kuwedzera nguva dzose nekuda kwehuwandu hushoma hwehuwandu hwekaunda. Asi tichatarisa izvi zvakadzama kana tasvika pakutonhora.)

Kana mutsara wagadzirwa, xmin inoiswa kune nhamba yekutengeserana iyo yakapa INSERT command, uye xmax inosiiwa isina chinhu.

Kana mutsara wadzimwa, iyo xmax kukosha kweiyo yazvino vhezheni inomisikidzwa nenhamba yekutengeserana kwaita DELETE.

Kana mutsara uchinge wagadziridzwa ne UPDATE command, maviri ma operation anonyatso itwa: DELETE uye INSERT. Iyo yazvino vhezheni yemutsetse inoisa xmax yakaenzana nenhamba yekutengeserana kwaita UPDATE. Imwe shanduro itsva yetambo imwechete inobva yagadzirwa; kukosha kwayo kwexmin kunopindirana nehukoshi hwexmax hweshanduro yapfuura.

Iyo xmin uye xmax minda inosanganisirwa mumutsara wevhezheni musoro. Pamusoro peminda iyi, musoro une zvimwe, semuenzaniso:

• infomask nhevedzano yemabits inotsanangura zvimiro zveiyi vhezheni. Kune akawanda kwazvo; Tichafunga zvishoma nezvishoma izvo zvikuru.
• ctid chinongedzo kune inotevera, nyowani vhezheni yemutsetse mumwe chete. Kune itsva, yazvino vhezheni yetambo, iyo cid inoreva iyi vhezheni pachayo. Nhamba ine chimiro (x,y), apo x ndiyo nhamba yepeji, y ndiyo nhamba yenhamba muhurongwa.
• null bitmap - Inomaka iwo makoramu eshanduro yakapihwa ine null kukosha (NULL). NULL haisi imwe yeakajairika data mhando kukosha, saka hunhu hunofanirwa kuchengetwa zvakasiyana.

Nekuda kweizvozvo, iyo musoro wakakura kwazvo - angangoita 23 bytes kune yega yega vhezheni yemutsetse, uye kazhinji yakawanda nekuda kweiyo NULL bitmap. Kana tafura iri "yakamanikana" (kureva kuti, ine makoramu mashoma), iyo yepamusoro inogona kutora yakawanda kupfuura iyo inobatsira ruzivo.

pinza

Ngatitarisei zvakanyanya kuti tambo dzepasi-pamwero dzinoitwa sei, kutanga nekuisa.

Nezviedzo, ngatigadzire tafura nyowani ine makoramu maviri uye index pane imwe yacho:

=> CREATE TABLE t(
  id serial,
  s text
);
=> CREATE INDEX ON t(s);

Ngatiisei mutsara mumwe mushure mekutanga kutengeserana.

=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');

Heino nhamba yedu yekutengeserana yazvino:

=> SELECT txid_current();

 txid_current 
--------------
         3664
(1 row)

Ngatitarisei zviri papeji. Iyo heap_page_items basa rekuwedzera peji rekuongorora rinokutendera iwe kuti uwane ruzivo nezve anongedza uye mitsara shanduro:

=> SELECT * FROM heap_page_items(get_raw_page('t',0)) gx

-[ RECORD 1 ]-------------------
lp          | 1
lp_off      | 8160
lp_flags    | 1
lp_len      | 32
t_xmin      | 3664
t_xmax      | 0
t_field3    | 0
t_ctid      | (0,1)
t_infomask2 | 2
t_infomask  | 2050
t_hoff      | 24
t_bits      | 
t_oid       | 
t_data      | x0100000009464f4f

Ziva kuti izwi murwi muPostgreSQL rinoreva matafura. Uku ndiko kumwe kushandiswa kunoshamisa kwezwi rokuti - murwi unozivikanwa data structure, iyo isina chinhu chakafanana netafura. Pano shoko racho rinoshandiswa mupfungwa yokuti “chinhu chiri chose chinokandwa pamwe chete,” mukupesana namaindekisi akaodha.

Basa racho rinoratidza data "sezvazviri", mune chimiro chakaoma kunzwisisa. Kuti tizvione, tinosiya chikamu cheruzivo chete uye tochidudzira:

=> SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin as xmin,
       t_xmax as xmax,
       (t_infomask & 256) > 0  AS xmin_commited,
       (t_infomask & 512) > 0  AS xmin_aborted,
       (t_infomask & 1024) > 0 AS xmax_commited,
       (t_infomask & 2048) > 0 AS xmax_aborted,
       t_ctid
FROM heap_page_items(get_raw_page('t',0)) gx

-[ RECORD 1 ]-+-------
ctid          | (0,1)
state         | normal
xmin          | 3664
xmax          | 0
xmin_commited | f
xmin_aborted  | f
xmax_commited | f
xmax_aborted  | t
t_ctid        | (0,1)

Hezvino zvatakaita:

• Yakawedzera zero kunhamba yeindekisi kuti iite senge t_ctid: (nhamba yepeji, nhamba yenhamba).
• Deciphered mamiriro eiyo lp_flags pointer. Heino "zvakajairika" - izvi zvinoreva kuti chinongedzo chinoreva vhezheni yetambo. Tichatarisa zvimwe zvinoreva gare gare.
• Pamabhiti ese eruzivo, maviri maviri chete akaonekwa kusvika parizvino. Iwo xmin_committed uye xmin_aborted bits anoratidza kana transaction nhamba xmin yaitwa (yakabviswa). Mabhiti maviri akafanana anoreva nhamba yekutengeserana xmax.

Chii chatinoona? Paunoisa mutsara, nhamba yenhamba 1 inobuda papeji yetafura, ichinongedza kune yekutanga uye yega shanduro yemutsara.

Muiyo tambo vhezheni, iyo xmin munda izere neiyo ikozvino transaction nhamba. Iko kutengeserana kuchiri kushanda, saka ese ari maviri xmin_committed uye xmin_aborted bits haana kusetwa.

The row version ctid field inoreva mutsara mumwe chete. Izvi zvinoreva kuti shanduro itsva haipo.

Iyo xmax munda yakazadzwa nedummy nhamba 0 nekuti iyi vhezheni yemutsetse haina kudzimwa uye iripo. Transactions haizo tarise nhamba iyi nekuti xmax_aborted bit yakaiswa.

Ngatitorei rimwe danho rekuvandudza kuverenga nekuwedzera mabhiti eruzivo kunhamba dzekutengesa. Uye ngatiite basa, sezvo isu tichada chikumbiro kanopfuura kamwe chete:

=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256) > 0 THEN ' (c)'
         WHEN (t_infomask & 512) > 0 THEN ' (a)'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' (c)'
         WHEN (t_infomask & 2048) > 0 THEN ' (a)'
         ELSE ''
       END AS xmax,
       t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE SQL;

Mune iyi fomu, zviri kujeka zvakanyanya zviri kuitika mumusoro weiyo mutsara vhezheni:

=> SELECT * FROM heap_page('t',0);

 ctid  | state  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)

Zvakafanana, asi zvakanyanya kutsanangurwa zvishoma, ruzivo runogona kuwanikwa kubva patafura pachayo, uchishandisa pseudo-columns xmin uye xmax:

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3664 |    0 |  1 | FOO
(1 row)

Kugadziriswa

Kana kutengeserana kuchipedzwa zvakabudirira, unofanirwa kuyeuka chimiro chayo - cherechedza kuti chakazvipira. Kuti uite izvi, chimiro chinonzi XACT chinoshandiswa (uye isati yasvika vhezheni gumi yainzi CLOG (commit log) uye zita iri richiri kuwanikwa munzvimbo dzakasiyana).

XACT haisi system catalog tafura; aya ndiwo mafaera ari muPGDATA/pg_xact dhairekitori. Ivo vane mabheti maviri ega ega kutengeserana: akazvipira uye akabviswa - senge mumutsara wevhezheni musoro. Ruzivo urwu rwakakamurwa kuita mafaera akati wandei kuti tive nyore; isu tichadzokera kumagazini ino kana tafunga kutonhora. Uye kushanda nemafaira aya kunoitwa peji nepeji, sevamwe vese.

Saka, kana kutengeserana kuchiitwa muXACT, iyo yakazvipira bhiti inogadzirirwa kutengeserana uku. Uye izvi ndizvo zvese zvinoitika panguva yekuita (kunyangwe isu tisiri kutaura nezve pre-rekodha log parizvino).

Kana imwe kutengeserana ikawana peji yetafura yatichangobva kutarisa, ichafanira kupindura mibvunzo yakati wandei.

1. Iko xmin transaction yapera here? Kana zvisina kudaro, saka shanduro yakagadzirwa yetambo haifaniri kuonekwa.
   Cheki iyi inoitwa nekutarisa chimwe chimiro, chiri mune yakagovaniswa ndangariro yemuenzaniso uye inonzi ProcArray. Iyo ine rondedzero yeese anoshanda maitiro, uye kune imwe neimwe nhamba yeazvino (inoshanda) kutengeserana inoratidzwa.
2. Kana yapera, saka sei - nekuita kana kukanzura? Kana ikanzurwa, saka iyo mutsara vhezheni haifanirwe kuoneka zvakare.
   Izvi ndizvo chaizvo zvinoitirwa XACT. Asi, kunyangwe iwo mapeji ekupedzisira eXACT akachengetwa mumabuffers muRAM, zvichiri kudhura kutarisa XACT nguva dzese. Naizvozvo, kana mamiriro ekutengeserana atemwa, anonyorerwa kune xmin_committed uye xmin_aborted bits yetambo vhezheni. Kana imwe yeaya mabheti akaiswa, ipapo mamiriro ekutengeserana xmin anoonekwa seanozivikanwa uye iyo inotevera kutengeserana haifanirwe kuwana XACT.

Sei mabits aya asina kugadzikwa netransaction pachayo kuita kupinza? Kana kuiswa kwaitika, dhizaini haisati yaziva kuti ichabudirira here. Uye panguva yekuzvipira, hazvisisiri pachena kuti ndeapi mitsara yakashandurwa mapeji. Panogona kunge paine akawanda emapeji akadaro, uye kuabata nemusoro hakubatsiri. Pamusoro pezvo, mamwe mapeji anogona kudzingwa kubva kune buffer cache kuenda kudiski; kuvaverenga zvakare kuti uchinje mabits kwaizononotsa kuzvipira zvakanyanya.

Iyo yakaderera pane yekuchengetedza ndeyekuti mushure mekuchinja, chero kutengeserana (kunyangwe imwe ichiita nyore kuverenga - SELECT) inogona kutanga kushandura mapeji edata mubuffer cache.

Saka, ngatigadzirise shanduko.

=> COMMIT;

Hapana chakachinja pane peji (asi isu tinoziva kuti mamiriro ekutengeserana akatorekodhwa muXACT):

=> SELECT * FROM heap_page('t',0);

 ctid  | state  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)

Ikozvino kutengeserana kunowana peji kutanga kuchafanirwa kuona iyo xmin transaction mamiriro uye kuinyora kune iyo ruzivo mabhiti:

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | FOO
(1 row)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3664 (c) | 0 (a) | (0,1)
(1 row)

Delete

Kana mutsara wadzimwa, nhamba yezvino kudzima transaction inonyorerwa kundima yexmax yeshanduro yazvino, uye xmax_aborted bit inobviswa.

Ziva kuti kukosha kweseti ye xmax inoenderana neiyo inoshanda transaction inoita sekiyi yekiyi. Kana imwe kutengeserana ichida kugadzirisa kana kudzima mutsara uyu, zvinomanikidzwa kumirira transaction xmax kuti ipedze. Tichazotaura zvakawanda nezve blocking gare gare. Parizvino, isu tinongoona kuti nhamba yemitsara yekiyi haina muganho. Ivo havatore nzvimbo mu RAM uye system performance haina kutambura nenhamba yavo. Ichokwadi, "kureba" kutengeserana kune zvimwe zvipingamupinyi, asi zvakawanda pane izvozvo gare gare.

Ngatidzime mutsara.

=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();

 txid_current 
--------------
         3665
(1 row)

Isu tinoona kuti nhamba yekutengeserana yakanyorwa mumunda we xmax, asi mabhiti eruzivo haana kuiswa:

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax | t_ctid 
-------+--------+----------+------+--------
 (0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)

kukanzura

Kubvisa shanduko kunoshanda zvakafanana nekuita, muXACT chete chidimbu chakabviswa chakagadzirirwa kutengeserana. Kubvisa kunokurumidza sekuzvipira. Kunyangwe iwo murairo unonzi ROLLBACK, shanduko hadzina kudzoserwa kumashure: zvese izvo kutengeserana kwakakwanisa kushandura mumapeji edata zvinoramba zvisina kuchinjika.

=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax | t_ctid 
-------+--------+----------+------+--------
 (0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)

Kana iyo peji yasvikwa, chimiro chinotariswa uye iyo xmax_aborted hint bit ichaiswa kune mutsara vhezheni. Iyo xmax nhamba pachayo inoramba iri papeji, asi hapana anozoitarisa.

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | FOO
(1 row)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   |   xmax   | t_ctid 
-------+--------+----------+----------+--------
 (0,1) | normal | 3664 (c) | 3665 (a) | (0,1)
(1 row)

Update

Iyo yekuvandudza inoshanda sekunge yakatanga kudzima iyo yazvino vhezheni yemutsara yobva yaisa imwe nyowani.

=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();

 txid_current 
--------------
         3666
(1 row)

Mubvunzo unoburitsa mutsara mumwe (shanduro itsva):

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | BAR
(1 row)

Asi pane peji tinoona ese mavhezheni:

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3664 (c) | 3666  | (0,2)
 (0,2) | normal | 3666     | 0 (a) | (0,2)
(2 rows)

Iyo yakadzimwa vhezheni inomakwa neiyo ikozvino transaction nhamba mundima xmax. Uyezve, kukosha uku kunonyorwa pamusoro pekare, sezvo kutengeserana kwekare kwakabviswa. Uye iyo xmax_aborted bit inocheneswa nekuti mamiriro ekutengeserana kwazvino haasati azivikanwa.

Yekutanga vhezheni yemutsetse ikozvino inoreva yechipiri (t_ctid munda) seyo nyowani.

Indekisi yechipiri inoonekwa papeji yeindekisi uye mutsara wechipiri unonongedza shanduro yechipiri papeji yetafura.

Sezvakangoita nekudzima, kukosha kwexmax muvhezheni yekutanga yemutsara chiratidzo chekuti mutsara wakakiyiwa.

Zvakanaka, ngatipedzise kutengeserana.

=> COMMIT;

Indexes

Kusvika parizvino tangotaura nezvemapeji etafura. Chii chinoitika mukati me indexes?

Mashoko ari mumapeji eindekisi anosiyana zvikuru zvichienderana nerudzi chairwo rweindex. Uye kunyange rudzi rumwe rweindex ine marudzi akasiyana emapeji. Semuenzaniso, B-muti ine metadata peji uye "nguva dzose" mapeji.

Nekudaro, iro peji rinowanzo kuve neakarongedzerwa eanongedza kumitsara uye mitsara pachayo (senge peji retafura). Mukuwedzera, pamagumo epeji pane nzvimbo ye data yakakosha.

Mitsara muma indexes inogonawo kuva nezvimiro zvakasiyana kwazvo zvichienderana nerudzi rwe index. Semuenzaniso, kune B-muti, mitsetse ine chekuita nemapeji emashizha ine indexing key value uye referensi (ctid) kune inoenderana tafura mutsara. Kazhinji, index inogona kuumbwa nenzira yakasiyana zvachose.

Chinonyanya kukosha ndechekuti hapana mutsara shanduro muma indexes emhando ipi zvayo. Zvakanaka, kana isu tinogona kufunga kuti mutsara wega wega unomiririrwa neshanduro imwe chete. Mune mamwe mazwi, hapana xmin uye xmax minda mune index row musoro. Isu tinogona kufungidzira kuti zvinongedzo kubva kune index zvinotungamira kune ese matafura shanduro dzemitsara - saka iwe unogona kufunga kuti ndeipi vhezheni iyo transaction ichaona chete nekutarisa patafura. (Senguva dzose, ichi hachisi chokwadi chese. Mune zvimwe zviitiko, mepu yekuona inogona kukwenenzvera maitiro, asi tichazotarisa izvi zvakadzama gare gare.)

Panguva imwecheteyo, mune index peji tinowana zvinongedzo kune ese ari maviri mavhezheni, ese arizvino uye ekare:

=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);

 itemoffset | ctid  
------------+-------
          1 | (0,2)
          2 | (0,1)
(2 rows)

Virtual transactions

Mukuita, PostgreSQL inoshandisa optimizations iyo inobvumira iyo "kuchengetedza" nhamba dzekutengesa.

Kana kutengeserana kuchingoverenga data chete, haina mhedzisiro pakuonekwa kwemitsara shanduro. Naizvozvo, iyo sevhisi maitiro anotanga kuburitsa chaiyo xid kune kutengeserana. Nhamba yacho ine ID yemaitiro uye nhamba yekutevedzana.

Kuburitsa iyi nhamba hakudi kuwiriranisa pakati pemaitiro ese uye nekudaro kunokurumidza. Tichajairana nechimwe chikonzero chekushandisa manhamba chaiwo kana tichitaura nezvechando.

Nhamba dzeVirtual hadzina kuverengerwa mune chero nzira mune data snapshots.

Panguva dzakasiyana-siyana nenguva, panogona kunge paine chaiwo madhizaini muhurongwa ane nhamba dzakatoshandiswa, uye izvi zvakajairika. Asi nhamba yakadaro haigoni kunyorwa mumapeji e data, nokuti nguva inotevera peji inowanikwa inogona kurasikirwa nezvose zvinoreva.

=> BEGIN;
=> SELECT txid_current_if_assigned();

 txid_current_if_assigned 
--------------------------
                         
(1 row)

Kana kutengeserana kukatanga kushandura data, inopiwa chaiyo, yakasarudzika nhamba yekutengeserana.

=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();

 txid_current_if_assigned 
--------------------------
                     3667
(1 row)

=> COMMIT;

Nested Transactions

Sevha mapoinzi

Inotsanangurwa muSQL chengetedza mapoinzi (savepoint), iyo inokutendera iwe kudzima chikamu chekutengeserana pasina kuchikanganisa zvachose. Asi izvi hazvikodzeri mudhayagiramu iri pamusoro, sezvo kutengeserana kuine chimiro chakafanana chekuchinja kwayo kwese, uye mumuviri hapana data yakadzoserwa kumashure.

Kuti uite basa iri, kutengeserana ne savepoint kunokamurwa kuita akati wandei akapatsanuka Nested transactions (subtransaction), mamiriro ayo anogona kutarisirwa zvakasiyana.

Nested transactions ine nhamba yavo (yakakwirira kudarika nhamba yekutengeserana kukuru). Mamiriro ekutengeserana akavharirwa anorekodhwa nenzira yakajairwa muXACT, asi chimiro chekupedzisira chinoenderana nechimiro cheiyo main transaction: kana ikanzurwa, ipapo zvese zvakavharwa kutengeserana zvinodzimwawo.

Ruzivo nezve transaction nesting inochengetwa mumafaira muPGDATA/pg_subtrans directory. Mafaira anowanikwa kuburikidza nemabhafa mune yemuenzaniso yakagovaniswa ndangariro, yakarongeka nenzira imwechete seXACT buffers.

Usapesanise matendenetsi anested neautonomous transactions. Autonomous transactions haitsamiri pane imwe neimwe nzira, asi nested transactions ndizvo. Iko hakuna kuzvimiririra kutengeserana muPostgreSQL yenguva dzose, uye, pamwe, yezvakanakisa: inodiwa zvakanyanya, kashoma, uye kuvapo kwavo mune mamwe maDBMS kunokonzera kushungurudzwa, kubva kune iyo munhu wese anobva atambura.

Ngatijekese tafura, tanga kutengeserana uye isa mutsara:

=> TRUNCATE TABLE t;
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
=> SELECT txid_current();

 txid_current 
--------------
         3669
(1 row)

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
(1 row)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3669 | 0 (a) | (0,1)
(1 row)

Zvino ngatiisei poindi yekuchengetedza toisa mumwe mutsetse.

=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();

 txid_current 
--------------
         3669
(1 row)

Ziva kuti txid_current() basa rinodzosa nhamba huru yekutengeserana, kwete nhamba yekutengeserana yakagara.

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3670 |    0 |  3 | XYZ
(2 rows)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3669 | 0 (a) | (0,1)
 (0,2) | normal | 3670 | 0 (a) | (0,2)
(2 rows)

Ngatitenderei tidzokere kunzvimbo yekuchengetedza toisa mutsara wechitatu.

=> ROLLBACK TO sp;
=> INSERT INTO t(s) VALUES ('BAR');
=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3671 |    0 |  4 | BAR
(2 rows)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669     | 0 (a) | (0,1)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671     | 0 (a) | (0,3)
(3 rows)

Mupeji tinoenderera mberi nekuona mutsara wakawedzerwa neyakadzimwa nested transaction.

Isu tinogadzirisa shanduko.

=> COMMIT;
=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3671 |    0 |  4 | BAR
(2 rows)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669 (c) | 0 (a) | (0,1)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(3 rows)

Iye zvino iwe unogona kuona zvakajeka kuti imwe neimwe yested transaction ine chimiro chayo.

Ziva kuti matenderedzwa ekutengesa haagone kushandiswa zvakajeka muSQL, ndiko kuti, haugone kutanga kutengeserana kutsva usina kupedzisa iripo. Iyi meshini inobatiswa zvisina tsarukano kana uchishandisa savhapoints, pamwe nekubata PL/pgSQL kusarudzika uye mune akati wandei mamwe, akawanda ekunze kesi.

=> BEGIN;

BEGIN

=> BEGIN;

WARNING:  there is already a transaction in progress
BEGIN

=> COMMIT;

COMMIT

=> COMMIT;

WARNING:  there is no transaction in progress
COMMIT

Zvikanganiso uye atomicity yekushanda

Chii chinoitika kana kukanganisa kukaitika paunenge uchiita oparesheni? Semuenzaniso, seizvi:

=> BEGIN;
=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 rows)

=> UPDATE t SET s = repeat('X', 1/(id-4));

ERROR:  division by zero

Pane chakaitika. Ikozvino kutengeserana kunoonekwa sekubviswa uye hapana mashandiro anotenderwa mairi:

=> SELECT * FROM t;

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Uye kunyangwe ukaedza kuita shanduko, PostgreSQL inoshuma kubvisa nhumbu:

=> COMMIT;

ROLLBACK

Sei kutengeserana kusingakwanisi kuenderera mberi mushure mekukundikana? Icho chokwadi ndechekuti kukanganisa kunogona kumuka nenzira yekuti isu taizowana mukana kune chikamu cheshanduko - iyo atomicity yeisina kana transaction, asi mushandisi angatyorwa. Semumuenzaniso wedu, apo mushandisi akakwanisa kugadzirisa mutsara mumwe pamberi pekukanganisa:

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669 (c) | 3672  | (0,4)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671 (c) | 0 (a) | (0,3)
 (0,4) | normal | 3672     | 0 (a) | (0,4)
(4 rows)

Zvinofanira kutaurwa kuti psql ine modhi ichiri kubvumira kuti kutengeserana kuenderere mberi mushure mekutadza sekunge zviito zveanokanganisa opareta zvakadzoserwa kumashure.

=> set ON_ERROR_ROLLBACK on
=> BEGIN;
=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 rows)

=> UPDATE t SET s = repeat('X', 1/(id-4));

ERROR:  division by zero

=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 rows)

=> COMMIT;

Hazvina kuoma kufungidzira kuti mune iyi modhi, psql inoisa isina chengetedzo poindi pamberi pemurairo wega wega, uye kana ikatadza inotanga kudzoreredza kwairi. Iyi modhi haishandiswe neyakagadzika, sezvo kuseta masavepoints (kunyangwe pasina kudzoreredza kumashure kwavari) kunosanganisira kukosha kwepamusoro.

Kuenderera mberi.

Source: www.habr.com