Ngoko ke, siye saqwalasela imiba enxulumene nayo , kwaye wenza ukuhlehla malunga . Kwaye ekugqibeleni sifike kweyona nxalenye inomdla - iinguqulelo zemitya.
Inhloko
Njengoko besesitshilo, umqolo ngamnye unokubakho ngaxeshanye kwiinguqulelo ezininzi kwiziko ledatha. Uguqulelo olunye kufuneka lwahlulwe ngandlelβ ithile kolunye. Kwiingcaphuno - kuba akusiyo ixesha elinjalo elisetyenziswayo, kodwa i-counter ekhethekileyo yokwandisa. Kwaye le counter yinombolo yetransekshini.
(Njengesiqhelo, ubunyani buntsokothile kakhulu: inani lentengiselwano alinakunyuka ngalo lonke ixesha ngenxa yomthamo omncinci wekhawunta. Kodwa siza kujonga ezi nkcukacha ngokweenkcukacha xa sifika ekukhenkceni.)
Xa umqolo uyiliwe, i-xmin imiselwe kwinombolo yetransekshini ekhuphe umyalelo othi INSERT, kwaye i-xmax ishiywa ingenanto.
Xa umqolo ucinyiwe, ixabiso le-xmax lenguqulelo yangoku liphawulwa ngenani letransekshini eyenze i- DELETE.
Xa umqolo uhlengahlengiswa ngumyalelo WOKUHLAZIYA, imisebenzi emibini iyenziwa ngokwenene: DELETE kwaye INSERT. Uguqulelo lwangoku lomqolo lucwangcisa uxmax olingana nenani letransekshini eyenze UHLAZIYO. Uguqulelo olutsha lomtya ofanayo luye lwenziwa; ixabiso layo le-xmin lingqamana nexabiso le-xmax loguqulelo lwangaphambili.
Imihlaba ye-xmin kunye ne-xmax ibandakanyiwe kuluhlu lwesihloko soguqulelo. Ukongeza kule mihlaba, iheader inezinye, umzekelo:
- infomask luluhlu lwamasuntswana achaza iimpawu zoluguqulelo. Zininzi kakhulu zazo; Siza kuqwalasela ngokuthe ngcembe ezona ziphambili.
- I-ctid likhonkco elilandelayo, uguqulelo olutsha lomgca omnye. Olona guqulelo lwangoku lomtya, ictid ibhekisa kolu guqulelo ngokwalo. Inani linefomu (x,y), apho ux linani lephepha, uy linani lesalathiso kuluhlu.
- null bitmap - Iphawula loo mida yoguqulelo olunikiweyo oluqulathe ixabiso elingento (NULL). I-NULL ayilolunye uhlobo lwedatha eqhelekileyo, ngoko uphawu kufuneka lugcinwe ngokwahlukeneyo.
Ngenxa yoko, iheader inkulu kakhulu - ubuncinci iibyte ezingama-23 kuguqulelo ngalunye lomgca, kwaye ngokuqhelekileyo ngaphezulu ngenxa ye-NULL bitmap. Ukuba itheyibhile "imxinwa" (oko kukuthi, iqulethe iikholamu ezimbalwa), i-overhead inokuthatha ngaphezu kolwazi oluluncedo.
Faka
Makhe sijonge ngakumbi ukuba imisebenzi yomtya ekumgangatho ophantsi yenziwa njani, siqale ngokufaka.
Kwimifuniselo, masenze itheyibhile entsha enezintlu ezimbini kunye nesalathiso kwenye yazo:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Masifake umqolo omnye emva kokuqalisa intengiselwano.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nantsi inombolo yethu yentengiselwano yangoku:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Makhe sijonge imixholo yephepha. I-heap_page_items umsebenzi wokongezwa kwekhasi lokuhlola likuvumela ukuba ufumane ulwazi malunga nezikhombisi kunye neenguqulelo zerowu:
=> 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
Qaphela ukuba igama elithi imfumba kwiPostgreSQL libhekisa kwiitafile. Olu lolunye usetyenziso olungaqhelekanga lweli gama - imfumba iyaziwa , engenanto idibanisa netafile. Apha eli gama lisetyenziswa ngengqiqo yokuba βyonke into iphoswa ndawonye,β ngokuchaseneyo nezalathisi eziodolweyo.
Umsebenzi ubonisa idatha "njengoko injalo", kwifomathi enzima ukuyiqonda. Ukuyiqonda, siya kushiya kuphela inxenye yolwazi kwaye siyicacise:
=> 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)
Nantsi into esiyenzileyo:
- Yongezwe unoziro kwinombolo yesalathiso ukuze ibukeke ngokufanayo no-t_ctid: (inombolo yephepha, inombolo yesalathiso).
- Iqoshelise imeko yesalathisi se-lp_flags. Apha "yinto eqhelekileyo" - oku kuthetha ukuba isalathisi sibhekisela kuguqulelo lomtya. Siza kujonga ezinye iintsingiselo kamva.
- Kuwo onke amasuntswana olwazi, zimbini kuphela izibini ezichongiweyo ukuza kuthi ga ngoku. I-xmin_committed kunye ne-xmin_aborted bits ibonisa ukuba inombolo yetransekshini xmin yenziwe (ilahliwe). Amasuntswana amabini afanayo abhekisa kwinani letransekshini xmax.
Sibona ntoni? Xa ufaka umqolo, inombolo yesalathiso soku-1 iya kuvela kwiphepha letafile, isalatha kuguqulelo lokuqala nokuphela komqolo.
Kwinguqulo yomtya, intsimi ye-xmin izaliswe ngenombolo yentengiselwano yangoku. Intengiselwano isasebenza, ngoko zombini i-xmin_committed kunye ne-xmin_aborted bits azimiselwanga.
Indawo yomqolo wenguqulelo yectid ibhekisa kumqolo omnye. Oku kuthetha ukuba ayikho inguqulelo entsha.
Umhlaba we-xmax uzaliswe ngenombolo ye-dummy 0 kuba olu guqulelo lomqolo alukacinywanga kwaye lwangoku. Iitransekshini aziyi kuhoya le nombolo kuba xmax_aborted bit is set.
Masithathe elinye inyathelo lokuphucula ukufundeka ngokongeza amasuntswana olwazi kwiinombolo zentengiselwano. Kwaye masenze umsebenzi, kuba siya kufuna isicelo ngaphezulu kwesinye:
=> 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;
Kule fomu, icace ngakumbi into eyenzekayo kwiheda yoguqulelo lomqolo:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Okufanayo, kodwa kuncinci kakhulu, ulwazi lunokufunyanwa kwitafile ngokwayo, kusetyenziswa i-pseudo-columns xmin kunye ne-xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Ukulungiswa
Ukuba intengiselwano igqitywe ngempumelelo, kufuneka ukhumbule isimo sayo - qaphela ukuba izinikele. Ukwenza oku, kusetyenziswa isakhiwo esibizwa ngokuba yi-XACT (kwaye phambi koguqulelo lwe-10 lwalubizwa ngokuba yi-CLOG (irekhodi lokuzibophelela) kwaye eli gama lisafumaneka kwiindawo ezahlukeneyo).
I-XACT ayiyoluhlu lwenkqubo yekhathalogu; ezi ziifayile kwiPGDATA/pg_xact directory. Banamasuntswana amabini kwintengiselwano nganye: bazibophelele kwaye balahliwe - njengakuluhlu olungasentla lwenguqulelo. Olu lwazi lwahlulahlulwe lwaziifayile ezininzi ukwenzela ukuba kube lula; Kwaye ukusebenza ngezi fayile kuqhutywa iphepha nephepha, njengazo zonke ezinye.
Ke, xa intengiselwano yenziwe kwi-XACT, i-bit ezinikeleyo isetelwe le ntengiselwano. Kwaye oku kuko konke okwenzekayo ngexesha lokuzibophelela (nangona singathethi malunga nelog yokurekhoda kwangaphambili).
Xa enye intengiselwano ifikelela kwiphepha letafile esisanda kulijonga, kuya kufuneka iphendule imibuzo emininzi.
- Ngaba intengiselwano ye-xmin igqityiwe? Ukuba akunjalo, ngoko ke inguqulelo eyenziweyo yomtya akufanele ibonakale.
Olu qwalaselo lwenziwa ngokujonga esinye isakhiwo, esibekwe kwinkumbulo ekwabelwana ngayo yomzekelo kwaye ibizwa ngokuba yiProcArray. Iqulethe uluhlu lwazo zonke iinkqubo ezisebenzayo, kwaye nganye nganye inani lentengiselwano yalo yangoku (esebenzayo) ibonisiwe. - Ukuba kugqityiwe, njani-ngokuzibophelela okanye ngokurhoxisa? Ukuba irhoxisiwe, ngoko ke uguqulelo lomqolo akufuneki lubonakale nalo.
Yile nto kanye yenzelwe yona i-XACT. Kodwa, nangona amaphepha okugqibela e-XACT egcinwe kwii-buffers kwi-RAM, kuyabiza ukuyijonga i-XACT ngalo lonke ixesha. Ngoko ke, nje ukuba imeko yentengiselwano imiselwe, ibhalwa kwi-xmin_committed kunye ne-xmin_aborted bits yoguqulelo lomtya. Ukuba enye yezi bits iseti, ke imeko yetransaction xmin ithathwa njengeyaziwa kwaye intengiselwano elandelayo ayizukufikelela kwi-XACT.
Kutheni ezi bits zingasetwa yi-transaction ngokwayo yenza i-insert? Xa ufakelo lwenzeka, intengiselwano ayikazi nokuba iya kuphumelela na. Kwaye ngeli xesha lokuzibophelela, ayisacaci ukuba yeyiphi imigca apho amaphepha atshintshiwe khona. Asenokuba maninzi amaphepha anjalo, kwaye ukuwakhumbula akuncedi nganto. Ukongeza, amanye amaphepha anokukhutshwa kwi-cache ye-buffer ukuya kwidiski; ukuwafunda kwakhona ukutshintsha amasuntswana kuya kucothisa ukuzibophelela kakhulu.
I-downside yogcino kukuba emva kweenguqu, nayiphi na intengiselwano (nokuba enye yenza ukufunda okulula - KHETHA) unokuqalisa ukutshintsha amaphepha edatha kwi-cache ye-buffer.
Ngoko ke, masilungise utshintsho.
=> COMMIT;
Akukho nto itshintshileyo kwiphepha (kodwa siyazi ukuba imeko yentengiselwano sele irekhodwe kwi-XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Ngoku intengiselwano efikelela kwiphepha kuqala kuya kufuneka imisele ubume bentengiselwano ye-xmin kwaye uyibhale kumasuntswana olwazi:
=> 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)
Ukususwa
Xa umqolo ucinyiwe, inani langoku lokucinywa kwentengiselwano libhalwa kumhlaba we-xmax woguqulelo lwangoku, kwaye xmax_aborted bit iyacinywa.
Qaphela ukuba ixabiso elibekiweyo le-xmax elihambelana netransekshini esebenzayo lisebenza njengokutshixa kumqolo. Ukuba enye intengiselwano ifuna ukuhlaziya okanye ukucima lo mqolo, kuya kunyanzeleka ukuba ilinde intengiselwano xmax igqitywe. Siza kuthetha ngakumbi malunga nokuthintela kamva. Okwangoku, siqaphela nje ukuba inani lokutshixa umqolo alinamda. Abathathi indawo kwi-RAM kwaye ukusebenza kwenkqubo akukhathazi kwinani labo. Enyanisweni, ukuthengiselana "okude" kunezinye izinto ezingalunganga, kodwa ngakumbi emva koko.
Masicime umgca.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Siyabona ukuba inombolo yetransekshini ibhalwe kwibala le-xmax, kodwa ulwazi lwesuntswana alucwangciswanga:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Cancel
Ukulahlwa kweenguqu kusebenza ngokufanayo nokuzibophelela, kuphela kwi-XACT i-bit ephuhliweyo isetelwe itransekshini. Ukuhlehlisa kukhawuleza njengokuzibophelela. Nangona umyalelo ubizwa ngokuba yi-ROLLBACK, utshintsho alubuyiselwanga umva: yonke into eyenziwa yi-transaction ekwazi ukuyitshintsha kumaphepha edatha ihlala ingatshintshi.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Xa iphepha lifikelelwe, imeko iya kukhangelwa kwaye xmax_aborted hint bit iya kumiselwa kuguqulelo lomqolo. Inani xmax ngokwalo lihlala kwiphepha, kodwa akukho mntu uya kulijonga.
=> 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)
Hlaziya
Uhlaziyo lusebenza ngokungathi luqale lucime inguqulelo yangoku yomqolo emva koko lwafaka entsha.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Umbuzo uvelisa umgca omnye (uguqulelo olutsha):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Kodwa kwiphepha sibona zombini iinguqulelo:
=> 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)
Inguqulelo ecinyiweyo iphawulwe ngenombolo yetransekshini yangoku kwibala le-xmax. Ngaphezu koko, eli xabiso libhalwe ngaphaya kwendala, ekubeni ukuthengiselana kwangaphambili kwacinywa. Kwaye xmax_aborted bit isusiwe kuba ubume bentengiselwano yangoku ayaziwa.
Inguqulelo yokuqala yomgca ngoku ibhekisa kweyesibini (t_ctid field) njengentsha.
Isalathiso sesibini sivela kwiphepha lesalathisi kwaye umqolo wesibini ubhekisela kuguqulelo lwesibini kwiphepha letafile.
Kanye njengokucima, ixabiso le-xmax kuguqulelo lokuqala lomqolo luphawu lokuba umqolo utshixiwe.
Kulungile, masigqibezele intengiselwano.
=> COMMIT;
Izalathiso
Ukuza kuthi ga ngoku sithethe kuphela ngamaphepha etafile. Kwenzeka ntoni ngaphakathi kwezalathisi?
Ulwazi olukumaphepha esalathiso luyahluka kakhulu ngokuxhomekeke kuhlobo oluthile lwesalathiso. Kwaye nolunye uhlobo lwesalathiso luneentlobo ezahlukeneyo zamaphepha. Ngokomzekelo, umthi we-B unephepha lemethadatha kunye namaphepha "aqhelekileyo".
Nangona kunjalo, iphepha lidla ngokuba noluhlu lwezalathisi kwimiqolo kunye nemigca ngokwazo (njengephepha letafile). Ukongezelela, ekupheleni kwephepha kukho indawo yedatha ekhethekileyo.
Imiqolo kwizalathisi inokuba nezakhiwo ezahlukileyo ngokuxhomekeke kuhlobo lwesalathiso. Umzekelo, kumthi we-B, imiqolo enxulumene namaphepha egqabi iqulethe ixabiso elingundoqo lesalathisi kunye nereferensi (ctid) kumqolo wetafile ohambelanayo. Ngokubanzi, isalathisi sinokuqulunqwa ngendlela eyahlukileyo ngokupheleleyo.
Eyona ngongoma ibalulekileyo kukuba akukho zinguqulelo zomqolo kwizalathiso zalo naluphi na uhlobo. Ewe, okanye sinokucinga ukuba umgca ngamnye umelwe yinguqulelo enye kanye. Ngamanye amazwi, akukho xmin kunye nexmax imihlaba kumqolo wesalathisi. Sinokucinga ukuba amakhonkco asuka kwisalathiso akhokelela kuzo zonke iinguqulelo zetheyibhile zemiqolo - ukuze ukwazi ukubona ukuba yeyiphi inguqulelo intengiselwano eya kuyibona kuphela ngokujonga itafile. (Njengesiqhelo, ayisiyiyo yonke le nyani. Kwezinye iimeko, imephu yokubonakala inokwandisa inkqubo, kodwa siza kujonga oku ngakumbi kamva.)
Kwangaxeshanye, kwiphepha lesalathiso sifumana izikhombisi kuzo zombini iinguqulelo, zombini ekhoyo kunye nendala:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Iintengiselwano ezibonakalayo
Enyanisweni, i-PostgreSQL isebenzisa ukulungiswa okukuvumela ukuba "ugcine" amanani okuthengiselana.
Ukuba intengiselwano ifunda kuphela idatha, ayinayo impembelelo ekubonakaleni kweenguqulelo zerowu. Ke ngoko, inkqubo yenkonzo kuqala ikhupha i-xid ebonakalayo kwintengiselwano. Inombolo ibandakanya i-ID yenkqubo kunye nenombolo yolandelelwano.
Ukukhupha le nombolo akufuni ungqamaniso phakathi kwazo zonke iinkqubo kwaye ngoko kukhawuleza kakhulu. Siza kuqhelana nesinye isizathu sokusebenzisa amanani abonakalayo xa sithetha ngokukhenkceza.
Amanani abonakalayo awathathelwa ngqalelo nangayiphi na indlela kwii-snapshots zedatha.
Kumanqaku ahlukeneyo ngexesha, kunokubakho utshintshiselwano lwenyani kwinkqubo enamanani asele esetyenzisiwe, kwaye oku kuqhelekile. Kodwa inani elinjalo alikwazi ukubhalwa kumaphepha edatha, kuba ixesha elizayo iphepha lifikeleleke lingalahlekelwa yiyo yonke intsingiselo.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Ukuba intengiselwano iqala ukutshintsha idatha, inikwa inombolo yokwenene, ekhethekileyo yokuthengiselana.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
IiNtengiselwano eziseNdlekeni
Gcina amanqaku
Ichazwe kwiSQL gcina amanqaku (indawo yokugcina), ekuvumela ukuba urhoxise inxalenye yentengiselwano ngaphandle kokuyiphazamisa ngokupheleleyo. Kodwa oku akungeni kulo mzobo ungentla, kuba intengiselwano inesimo esifanayo kulo lonke utshintsho, kwaye ngokwasemzimbeni akukho datha ibuyiselwe umva.
Ukuphumeza lo msebenzi, intengiselwano ene-savepoint yahlulwe ibe ziindidi ezahlukeneyo iintengiselwano ezibekwe kwindlwane (intengiselwano), imeko yayo enokulawulwa ngokwahlukeneyo.
Iitransekshini ezifakwe kwi-Nested zinenombolo yazo (phezulu kunenani lentengiselwano engundoqo). Ubume bentengiselwano efakwe kwindlwane irekhodwa ngendlela eqhelekileyo kwi-XACT, kodwa imeko yokugqibela ixhomekeke kwimeko yentengiselwano ephambili: ukuba ikhanseliwe, ke zonke iintengiselwano ezifakwe kwindlwane nazo ziyacinywa.
Ulwazi malunga nentengiselwano nesting igcinwe kwiifayile kwiPGDATA/pg_subtrans directory. Iifayile zifikelelwa ngee-buffers kwinkumbulo ekwabelwana ngayo yomzekelo, zicwangciswe ngendlela efanayo ne-XACT buffers.
Musa ukubhidanisa iintengiselwano ezibekwe kwindlwane kunye neentengiselwano ezizimeleyo. Iintengiselwano ezizimeleyo azixhomekekanga enye kwenye nangayiphi na indlela, kodwa iintengiselwano ezibekwe kwindlwane ziyayenza. Akukho zintengiselwano ezizimeleyo kwi-PostgreSQL eqhelekileyo, kwaye, mhlawumbi, okona kulungileyo: zifuneka kakhulu, kunqabile kakhulu, kwaye ubukho bazo kwezinye ii-DBMS buxhokonxa ukuxhatshazwa, apho wonke umntu uhlupheka.
Masisuse itafile, siqale intengiselwano kwaye sifake umqolo:
=> 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)
Ngoku masibeke indawo yokugcina kwaye sifake omnye umgca.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Qaphela ukuba txid_current () umsebenzi ubuyisela inani letransekshini engundoqo, hayi inani lentengiselwano eligcinwe.
=> 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)
Masibuyele umva kwindawo yokugcina kwaye sifake umgca wesithathu.
=> 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)
Kwiphepha siqhubeka nokubona umqolo wongezwe yintengiselwano ekhanseliwe.
Silungisa utshintsho.
=> 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)
Ngoku unokubona ngokucacileyo ukuba intengiselwano nganye efakwe kwindlwane inesimo sayo.
Qaphela ukuba iintengiselwano ezibekwe kwindlwane azinakusetyenziswa ngokucacileyo kwiSQL, oko kukuthi, awukwazi ukuqalisa intengiselwano entsha ngaphandle kokugqiba le yangoku. Lo matshini wenziwa wasebenza ngokungafihlisiyo xa usebenzisa iindawo zokugcina, kunye naxa uphethe izinto ezingaqhelekanga zePL/pgSQL kunye nenani lezinye iimeko ezingaqhelekanga.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Iimpazamo kunye ne-atomicity yokusebenza
Kwenzeka ntoni ukuba kwenzeke impazamo ngelixa usenza utyando? Umzekelo, njengale:
=> 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
Kwenzeke impazamo. Ngoku intengiselwano ithathwa njengengavunyelwanga kwaye akukho misebenzi ivunyelweyo kuyo:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Kwaye nokuba uzama ukwenza utshintsho, i-PostgreSQL iya kuxela ukuphunza:
=> COMMIT;
ROLLBACK
Kutheni intengiselwano ingaqhubeki emva kokusilela? Inyani yeyokuba impazamo inokuvela ngendlela yokuba siya kufumana ukufikelela kwinxalenye yotshintsho - i-atomicity yento engekhoyo, kodwa umqhubi uya kuphulwa. Njengomzekelo wethu, apho umqhubi ukwazile ukuhlaziya umgca omnye ngaphambi kwempazamo:
=> 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)
Kufuneka kuthiwe i-psql inemowudi esavumela ukuba intengiselwano iqhubeke emva kokungaphumeleli ngokungathi izenzo zomqhubi ophosakeleyo zibuyiselwe umva.
=> 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;
Akunzima ukuqikelela ukuba kule ndlela, i-psql eneneni ibeka indawo yokugcina efihlakeleyo phambi komyalelo ngamnye, kwaye kwimeko yokusilela iqalisa ukubuyisela umva kuyo. Le ndlela ayisetyenziswanga ngokungagqibekanga, kuba ukuseta iindawo zogcino (nangaphandle kokuqengqeleka umva kuzo) kubandakanya umphezulu obalulekileyo.
umthombo: www.habr.com