Ngakho-ke, siye sahlanganisa izindaba ezihlobene , futhi wenza ukuhlehla mayelana Futhi ekugcineni, sifinyelele engxenyeni ethakazelisa kakhulu - izinguqulo zezintambo.
unhlokweni
Njengoba sesixoxile, umugqa ngamunye kusizindalwazi ungaba khona ezinguqulweni eziningi ngesikhathi esisodwa. Inguqulo eyodwa kufanele ihlukaniswe kwenye ngandlela thize. Kulokhu, inguqulo ngayinye inomaka ababili abachaza "isikhathi" sokufaneleka kwayo (i-xmin ne-xmax). Isizathu sokusebenzisa izimpawu zokucaphuna ingoba akusona isikhathi ngokwaso esisetshenziswayo, kodwa isibali esikhethekile sokwenyusa. Lesi sibali inombolo yokwenziwayo.
(Njengenjwayelo, izinto zixaka kakhulu: inombolo yokwenziwe ayikwazi ukukhuphuka ngokuqhubekayo ngenxa yokujula okulinganiselwe kwekhawunta. Kodwa sizohlanganisa le mininingwane ngokuningiliziwe uma sifika ekuqandeni.)
Uma umugqa udaliwe, i-xmin isethwe ku-ID yomsebenzi okhiphe umyalo othi INSERT, futhi i-xmax ishiywa ingenalutho.
Uma umugqa ususwa, inani elingu-xmax lenguqulo yamanje limakwa ngenombolo yokwenziwe yomsebenzi owenze okuthi SUSA.
Uma umugqa ulungiswa ngomyalo othi UPDATE, imisebenzi emibili iyenziwa ngempela: SUSA futhi FAKA. Inguqulo yamanje yomugqa isethelwe kunani elingu-xmax elilingana ne-ID yokwenziwe yomsebenzi owenze UKUBUYISA. Inguqulo entsha yomugqa ofanayo ibe isidalwa; inani layo le-xmin lifana nevelu engu-xmax yenguqulo yangaphambilini.
Izinkambu ze-xmin kanye ne-xmax zifakiwe kusihloko senguqulo yomugqa. Ngaphezu kwalezi zinkambu, unhlokweni uqukethe nezinye, njenge:
- i-infomask isethi yamabhithi achaza izici zenguqulo enikeziwe. Kukhona ezimbalwa zazo; sizohlanganisa eziyinhloko kancane kancane.
- I-ctid iyireferensi yenguqulo elandelayo, entsha yeyunithi yezinhlamvu efanayo. Enguqulweni entsha, yamanje yeyunithi yezinhlamvu, i-ctid ibhekisela kuleyo nguqulo ngokwayo. Inombolo inefomu (x,y), lapho u-x eyinombolo yekhasi futhi u-y eyinkomba yesikhombi ohlwini.
- NULL bitmap - imaka lawo makholomu enguqulo enikeziwe equkethe inani elingu-NULL. I-NULL ayilona inani elivamile lohlobo lwedatha, ngakho ifulegi kufanele ligcinwe ngokuhlukana.
Ngenxa yalokho, unhlokweni sikhulu impela—okungenani amabhayithi angu-23 inguqulo yomugqa ngamunye, futhi ngokuvamile ngaphezulu ngenxa ye-bitmap engu-NULL. Uma ithebula "lincane" (okusho ukuthi liqukethe amakholomu ambalwa), i-overhead ingase ibe ngaphezu kolwazi oluwusizo.
Faka
Ake sibhekisise ukuthi ukusebenza kwentambo kwenziwa kanjani ezingeni eliphansi, siqale ngokufaka.
Ukuze uthole izivivinyo, masidale ithebula elisha elinamakholomu amabili kanye nenkomba kweyodwa yawo:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Masifake umugqa owodwa, njengoba sesiqale umsebenzi.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nansi inombolo yethu yamanje yokwenza:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Ake sibheke okuqukethwe kwekhasi. Umsebenzi we-heap_page_items wesandiso sokuhlola ikhasi ukuvumela ukuthi uthole ulwazi mayelana nezikhombisi nezinguqulo zemigqa:
=> 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 ukuthi igama elithi inqwaba ku-PostgreSQL libhekise kumathebula. Lokhu okunye ukusetshenziswa okungajwayelekile kwegama-inqwaba eyaziwa kakhulu , okungahlangene netafula. Lapha, leli gama lisetshenziswe ngomqondo wokuthi "konke kuphonswa ndawonye," ngokuphambene nezinkomba ezihleliwe.
Umsebenzi ubonisa idatha "njengoba injalo," ngefomethi okunzima ukuyiqonda. Ukwenza umqondo wayo, sizoshiya kuphela olunye ulwazi futhi siluhlukanise:
=> 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)
Nakhu esikwenzile:
- Kwengezwe uziro enombolweni yesikhombi ukuze ibukeke ifana nethi t_ctid: (inombolo yekhasi, inombolo yesikhombi).
- Sicacise isimo sesikhombi se-lp_flags. Lapha, "kujwayelekile," okusho ukuthi isikhombisi sikhomba enguqulweni yeyunithi yezinhlamvu. Sizobheka amanye amanani kamuva.
- Kuzo zonke izingcezu zolwazi, ngamapheya amabili kuphela ahlonziwe kuze kube manje. Amabhithi e-xmin_committed kanye ne-xmin_aborted abonisa ukuthi inombolo yokwenziwe i-xmin yenziwe (noma ichithiwe). Amabhithi amabili afanayo abhekisela kunombolo yokwenziwe xmax.
Sibonani? Lapho ufaka umugqa, iphoyinti elinenombolo 1 livela ekhasini lethebula, likhomba uhlobo lokuqala nokuphela komugqa.
Enguqulweni yomugqa, inkambu ye-xmin igcwaliswa ngenombolo yamanje yokwenziwe. Okwenziwayo kusasebenza, ngakho kokubili amabhithi e-xmin_committed kanye ne-xmin_aborted awasethiwe.
Inkambu ye-ctid yenguqulo yomugqa ibhekisela kumugqa ofanayo, okusho ukuthi ayikho inguqulo entsha ekhona.
Inkambu ye-xmax igcwaliswe ngenombolo ye-dummy 0 ngoba le nguqulo yomugqa ayikasuswa futhi isengamanje. Okwenziwayo kuzoziba le nombolo ngoba i-xmax_aborted bit isethiwe.
Ake sithathe esinye isinyathelo sokuthuthukisa ukufundeka ngokungeza izingcezu zedatha ezinombolweni zokwenziwe. Futhi masidale umsebenzi, njengoba sizodinga lo mbuzo futhi:
=> 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;
Kuleli fomu, kucace kakhudlwana ukuthi kwenzekani kunhlokweni yeyunithi yezinhlamvu:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Okufanayo, kodwa okunemininingwane encane kakhulu, ulwazi lungatholwa kuthebula ngokwalo kusetshenziswa amakholomu mbumbulu we-xmin kanye ne-xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Ukulungisa
Lapho okwenziwayo kuqedwa ngempumelelo, isimo sakhona sidinga ukukhunjulwa—kumakwe njengokuzibophezele. Lokhu kufezwa kusetshenziswa isakhiwo esibizwa nge-XACT (ngaphambi kwenguqulo 10, yayibizwa nge-CLOG (umbhalo wokuzibophezela), futhi leli gama lisengatholakala ezindaweni ezihlukahlukene).
I-XACT ayilona ithebula lekhathalogi yesistimu; kungamafayela kuhla lwemibhalo lwe-PGDATA/pg_xact. Aqukethe amabhithi amabili okwenziwayo ngakunye: ukuzibophezela nokuhoxisiwe—njengakusihloko senguqulo yomugqa. Lolu lwazi luhlukaniswa lwaba amafayela amaningi ukuze kube lula kuphela; sizobuyela kulolu daba uma sixoxa ngamaqhwa. Lawa mafayela afinyelelwa ikhasi nekhasi, njengawo wonke amanye amafayela.
Ngakho-ke, lapho okwenziwayo kwenziwa, ibhithi "esizibophezele" isethwe ku-XACT yalokho kuthenga. Futhi yilokho kuphela okwenzekayo lapho kwenziwa ukuthengiselana (yize singakaxoxi ngelogi yokubhala kusengaphambili).
Uma noma yikuphi okunye okwenziwayo kufinyelela ikhasi lethebula esisanda kulibuka, kuzomele liphendule imibuzo embalwa.
- Ingabe umsebenzi we-xmin usuqedile? Uma kungenjalo, inguqulo yomugqa edaliwe akufanele ibonakale.
Lokhu kuhlola kwenziwa ngokuhlola esinye isakhiwo esitholakala kumemori eyabiwe yesibonelo, ebizwa ngokuthi i-ProcArray. Iqukethe uhlu lwazo zonke izinqubo ezisebenzayo, ngayinye enenombolo yayo yamanje (esebenzayo) yokwenziwayo. - Uma iphelile, kanjani-ngokuzibophezela noma ngokubuyisela emuva? Uma ngokuhlehlisa, inguqulo yomugqa akufanele futhi ibonakale.
Lokhu yikho kanye i-XACT eyenzelwe yona. Nokho, nakuba amakhasi okugcina e-XACT egcinwe kumabhafa e-RAM, ukuhlola i-XACT isikhathi ngasinye kuyabiza. Ngakho-ke, uma isimo sokwenziwe sesinqunyiwe, kubhalwa ku-xmin_committed kanye ne-xmin_aborted bits yenguqulo yomugqa. Uma enye yalezi zingcezu isethiwe, isimo sokwenziwa kwe-xmin sithathwa saziwa, futhi umsebenzi olandelayo ngeke usadinga ukufinyelela ku-XACT.
Kungani lezi zingcezu zingasethwa wumsebenzi owenza okufakwayo? Uma okufakwayo kwenzeka, okwenziwayo akukaziwa ukuthi kuzoqedwa ngempumelelo yini. Futhi ngesikhathi sokuzinikela, akusacaci ukuthi yimiphi imigqa lapho amakhasi alungiswa khona. Angase abe maningi amakhasi anjalo, futhi ukuwagcina akunakwenzeka. Ngaphezu kwalokho, amanye amakhasi kungenzeka akhishiwe kunqolobane yokulondoloza ayiswa kudiski; ukuwafunda kabusha ukuze ushintshe izingcezu kuzonciphisa kakhulu ukuzibophezela.
Ububi balokhu kulondoloza ukuthi ngemva kwezinguquko, noma yikuphi okwenziwayo (ngisho noyedwa owenza ukufunda okulula - KHETHA) angase aqale ukushintsha amakhasi edatha kunqolobane yebhafa.
Ngakho-ke, masirekhode ushintsho.
=> COMMIT;
Akukho okushintshile ekhasini (kodwa siyazi ukuthi isimo sokwenziwe sesibhalelwe i-XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Manje okwenziwayo okufinyelele kuqala ekhasini kuzofanele kunqume isimo sokwenziwa kwe-xmin futhi sikubhale kumabhithi edatha:
=> 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)
Susa
Uma umugqa ususwa, inkambu ye-xmax yenguqulo yamanje ibhalwa enombolweni yamanje yokususa umsebenzi, bese i-xmax_aborted bit isethwe kabusha.
Qaphela ukuthi inani le-xmax elibekwe okwenziwayo okusebenzayo lisebenza njengesihluthulelo somugqa. Uma omunye umsebenzi uzama ukubuyekeza noma ukususa lo mugqa, kuzophoqeleka ukuthi kulinde ukuthi umsebenzi we-xmax uqedele. Sizoxoxa ngokukhiya ngokuningiliziwe ngokuhamba kwesikhathi. Okwamanje, ake siqaphele ukuthi inombolo yokukhiya imigqa ayinamkhawulo. Abayithathi i-RAM, futhi ukusebenza kwesistimu akuthintwa inombolo yabo. Kodwa-ke, ukuthengiselana okude kunezinye ezimbi, kodwa sizoxoxa ngalokho kamuva.
Asisuse ulayini.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Siyabona ukuthi inombolo yokwenziwayo ibhalelwe kunkambu ye-xmax, kodwa izingcezu zolwazi azisethiwe:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Khansela
Ukuhlehlisa izinguquko kusebenza ngendlela efanayo nasekuzinikeleni, ngaphandle kokuthi ibhithi enqanyuliwe isethwe ku-XACT ngomsebenzi. Ukuhlehlisa kwenziwa ngokushesha njengokuzibophezela. Nakuba umyalo ubizwa ngokuthi i-ROLLBACK, awubuyiseli emuva noma yiziphi izinguquko: yonke into okwenziwayo eshintshiwe emakhasini wedatha ihlala ingashintshile.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Uma ikhasi selifinyelelwe, isimo sizohlolwa futhi i-xmax_aborted hint bit izosethwa enguqulweni yomugqa. Inombolo ye-xmax ngokwayo ihlala ekhasini, kodwa akekho osazoyibheka.
=> 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)
Vuselela
Isibuyekezo sisebenza sengathi inguqulo yamanje yomugqa iqale yasuswa kwase kufakwa entsha.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Umbuzo ubuyisela umugqa owodwa (inguqulo entsha):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Kodwa ekhasini sibona zombili izinguqulo:
=> 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)
Inguqulo esusiwe imakwe ngenombolo yamanje yokwenziwe endaweni ye-xmax. Leli nani libhalwe phezu ngoba umsebenzi wangaphambilini ukhanseliwe. I-xmax_aborted bit isuliwe ngoba isimo sokwenziwe asikaziwa.
Inguqulo yokuqala yomugqa manje ibhekisela kowesibili (inkambu t_ctid) njengentsha kakhudlwana.
Isikhombi sesibili kanye nomugqa wesibili kuvela ekhasini lenkomba, kubhekiselwa kunguqulo yesibili ekhasini lethebula.
Njengokususa, inani le-xmax enguqulweni yokuqala yomugqa lisebenza njengenkomba yokuthi umugqa ukhiyiwe.
Awu, asiqedele okwenziwayo.
=> COMMIT;
Izinkomba
Kuze kube manje, sikhulume kuphela ngamakhasi ethebula. Kodwa kwenzekani ngaphakathi kwezinkomba?
Ulwazi emakhasini ezinkomba luyahlukahluka kuye ngohlobo oluthile lwenkomba. Ngisho nohlobo olulodwa lwenkomba lungaba nezinhlobo ezahlukene zamakhasi. Isibonelo, i-B-tree inekhasi lemethadatha namakhasi "avamile".
Nokho, ikhasi ngokuvamile liqukethe izinkomba eziningi zemigqa kanye nemigqa ngokwayo (njengekhasi lethebula). Ukwengeza, isikhala sigodliwe ekupheleni kwekhasi ngedatha ekhethekile.
Imigqa ezinkombeni ingase futhi ibe nezakhiwo ezihluke kakhulu kuye ngohlobo lwenkomba. Isibonelo, esihlahleni esingu-B, imigqa ehlobene namakhasi eqabunga iqukethe inani lokhiye wenkomba kanye nereferensi (ctid) yomugqa wethebula ohambelanayo. Ngokuvamile, inkomba ingahlelwa ngendlela ehluke kakhulu.
Iphuzu elibaluleke kakhulu ukuthi izinkomba zanoma yiluphi uhlobo azinazo izinguqulo zamarowu. Noma, kunalokho, umugqa ngamunye ungabhekwa njengomelwe inguqulo eyodwa ncamashi. Ngamanye amazwi, azikho izinkambu ze-xmin ne-xmax kunhlokweni yomugqa wenkomba. Izinkomba zenkomba zingabhekwa njengokukhomba kuzo zonke izinguqulo zomugqa wethebula—ngakho ukuphela kwendlela yokunquma ukuthi iyiphi inguqulo umsebenzi ozoyibona iwukubheka ithebula. (Njengenjwayelo, leli akulona iqiniso lonke. Kwezinye izimo, imephu ebonakalayo ingathuthukisa inqubo, kodwa sizoxoxa ngalokhu ngokuningiliziwe ngokuhamba kwesikhathi.)
Kulokhu, ekhasini lenkomba sithola izinkomba kuzo zombili izinguqulo, zombili ezamanje nezindala:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Ukuthengiselana okubonakalayo
Empeleni, i-PostgreSQL isebenzisa ukulungiselelwa okuyivumela ukuthi "igcine" izinombolo zokwenziwayo.
Uma okwenziwayo kufundwa idatha kuphela, akuthinti ukubonakala kwezinguqulo zomugqa. Ngakho-ke, i-backend kuqala yabela okwenziwayo i-virtual ID (virtual xid). Le ID iqukethe i-ID yenqubo kanye nenombolo elandelanayo.
Ukukhipha le nombolo akudingi ukuvumelanisa phakathi kwazo zonke izinqubo ngakho-ke kuyashesha kakhulu. Sizoxoxa ngesinye isizathu sokusebenzisa izinombolo ezibonakalayo uma sixoxa ngokuqandisa.
Izinombolo ezibonakalayo azinakwa nganoma iyiphi indlela kuzifinyezo zedatha.
Ngamaphoyinti ahlukahlukene ngokuhamba kwesikhathi, uhlelo lungaqukatha ukuthengiselana okubonakalayo okunezinombolo esezisetshenzisiwe kakade, futhi lokhu kuvamile. Kodwa-ke, inombolo enjalo ayikwazi ukubhalwa emakhasini wedatha, ngoba ngesikhathi esilandelayo lapho ikhasi lifinyelelwa khona, lingase libe yize.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Uma umsebenzi uqala ukushintsha idatha, unikezwa inombolo yangempela, eyingqayizivele yokwenziwe.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Okwenziwe kusidleke
Londoloza amaphuzu
Ku-SQL kuchaziwe gcina amaphuzu (ama-savepoints), akuvumela ukuthi uhlehlise ingxenye yomsebenzi ngaphandle kokukuhoxisa ngokuphelele. Nokho, lokhu akungeni esikimini esingenhla, njengoba okwenziwayo kunesimo esisodwa sazo zonke izinguquko zakhona, futhi ayikho idatha ebonakalayo ebuyiselwa emuva.
Ukuze wenze lokhu kusebenze, umsebenzi one-savepoint uhlukaniswa ube izingxenye ezimbalwa ezihlukene. ukuthengiselana okufakiwe (i-subtransaction), isimo sayo esingalawulwa ngokwehlukana.
I-subtransactions inenombolo yayo yokwenziwe (iphezulu kunenombolo yokwenziwe eyinhloko). Isimo se-subtransactions sirekhodwa ku-XACT njengenjwayelo, kodwa isimo sazo sokugcina sincike esimweni somsebenzi oyinhloko: uma kukhanseliwe, konke ukuthengiselana kuyakhanselwa.
Ulwazi lokwenza isidleke lugcinwa kumafayela kunkomba ye-PGDATA/pg_subtrans. Lawa mafayela afinyelelwa ngamabhafa kumemori eyabiwe yesibonelo, ahlelwe ngendlela efanayo namabhafa e-XACT.
Ungaphambanisi ukuthengiselana okufakwe esidlekeni nokuthengiselana okuzenzakalelayo. Ukuthengiselana okuzenzakalelayo kuzimele ngokuphelele komunye nomunye, kuyilapho okwenziwa esidlekeni kuhlukile. Ukuthengiselana okuzenzakalelayo akukho ku-PostgreSQL ejwayelekile, futhi mhlawumbe okuhle kakhulu: akuvamile ukudingeka, futhi ukuba khona kwabo kwamanye ama-DBMS kumema ukuhlukumeza, okuholela ekusetshenzisweni kabi okusabalele.
Masisule ithebula, siqale umsebenzi bese sifaka umugqa:
=> 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)
Manje ake simise iphoyinti lokulondoloza bese sifaka omunye umugqa.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Qaphela ukuthi umsebenzi we-txid_current() ubuyisela inombolo yokwenziwayo okuyinhloko, hhayi evalelwe.
=> 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 emuva endaweni yokulondoloza bese sifaka umugqa 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)
Ekhasini siyaqhubeka nokubona umugqa owengezwe ngokuhlehliswa okwenziwe esidlekeni.
Sirekhoda izinguquko.
=> 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)
Manje sekubonakala ngokucacile ukuthi okwenziwayo ngakunye kunesimo sakhona.
Qaphela ukuthi okwenziwayo okufakiwe akukwazi ukusetshenziswa ngokusobala ku-SQL; okusho ukuthi, okwenziwayo okusha akukwazi ukuqaliswa ngaphandle kokuqedela okwamanje. Le nqubo isetshenziswa ngokungagunci lapho usebenzisa ama-savepoints, nalapho uphatha okuhlukile kwe-PL/pgSQL kanye nenani lamanye, izimo ezingavamile.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Amaphutha kanye ne-atomicity yokusebenza
Kwenzekani uma kwenzeka iphutha phakathi nokusebenza? Ngokwesibonelo:
=> 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 iphutha. Okwenziwayo manje sekubhekwa njengokunqanyuliwe futhi akukho okunye ukusebenza okuvunyelwe:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Futhi noma uzama ukwenza izinguquko, i-PostgreSQL izobika ukukhanselwa:
=> COMMIT;
ROLLBACK
Kungani singakwazi ukuqhubeka nomsebenzi ngemva kokwehluleka? Inkinga ukuthi iphutha belingenzeka ngendlela ebingasivumela ukuthi sifinyelele kuphela ezinye zezinguquko—i-athomu hhayi nje yokwenziwayo, kodwa isisebenzisi ngokwaso ngabe sephulwe. Njengesibonelo sethu, lapho opharetha ekwazile ukubuyekeza umugqa owodwa ngaphambi kwephutha:
=> 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)
Kufanele kuqashelwe ukuthi i-psql inemodi evumela ukuthi ukuthengiselana kuqhubeke ngemva kokwehluleka, njengokungathi izenzo zesitatimende esiyiphutha zihlehliswa emuva.
=> 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;
Njengoba ungase uqagele, kule modi, i-psql empeleni ibeka indawo yokugcina engacacile ngaphambi komyalo ngamunye, futhi uma kwenzeka ukwehluleka, iqala ukuhlehlisa kuyo. Le modi ayisetshenziswa ngokuzenzakalelayo, njengoba ukusetha izindawo zokulondoloza (ngisho nangaphandle kokuhlehlisa) kuletha phezulu okubalulekile.
Source: www.habr.com
