Ngakho-ke, sicabangele izindaba ezihlobene , futhi wahlehlela emuva . Futhi ekugcineni sifinyelele engxenyeni ethakazelisa kakhulu - izinguqulo zezintambo.
unhlokweni
Njengoba sesishilo, umugqa ngamunye ungaba khona ngesikhathi esisodwa ezinguqulweni ezimbalwa kusizindalwazi. Inguqulo eyodwa kufanele ihlukaniswe ngandlela thize nenye. Ngale njongo, inguqulo ngayinye inamamaki amabili anquma “isikhathi” sesenzo sale nguqulo (xmin kanye no-xmax). Kumacaphuno - ngoba akusona isikhathi esinjalo esisetshenziswayo, kodwa ikhawunta ekhethekile ekhulayo. Futhi lesi sibali inombolo yokwenziwayo.
(Njengenjwayelo, iqiniso liyinkimbinkimbi kakhulu: inombolo yokwenziwe ayikwazi ukukhuphuka ngaso sonke isikhathi ngenxa yomthamo webhithi olinganiselwe wekhawunta. Kodwa sizobheka le mininingwane ngokuningiliziwe uma sifika ekuqandeni.)
Uma kwakhiwa umugqa, i-xmin isethwe enombolweni yokwenziwayo ekhiphe umyalo othi FAKA, futhi i-xmax ishiywa ingenalutho.
Uma umugqa ususwa, inani elingu-xmax lenguqulo yamanje limakwa ngenombolo yomsebenzi owenze okuthi SUSA.
Uma umugqa ulungiswa ngomyalo othi UPDATE, imisebenzi emibili iyenziwa ngempela: SUSA futhi FAKA. Inguqulo yamanje yomugqa ibeka u-xmax olingana nenani lomsebenzi owenze UKUBUYISA. Inguqulo entsha yeyunithi yezinhlamvu efanayo ibe isidalwa; inani layo le-xmin lihambisana nevelu engu-xmax yenguqulo yangaphambilini.
Izinkambu ze-xmin ne-xmax zifakiwe kusihloko senguqulo yomugqa. Ngaphezu kwalezi zinkambu, unhlokweni uqukethe ezinye, isibonelo:
- i-infomask uchungechunge lwamabhithi achaza izici zale nguqulo. Ziningi impela; Sizocabangela kancane kancane eziyinhloko.
- I-ctid iyisixhumanisi senguqulo elandelayo, entsha yomugqa ofanayo. Okwenguqulo entsha, yamanje kakhulu yeyunithi yezinhlamvu, i-ctid ibhekisela kule nguqulo ngokwayo. Inombolo inefomu (x,y), lapho u-x eyinombolo yekhasi, u-y uyinombolo yenkomba ohlwini.
- null bitmap - Imaka lawo makholomu enguqulo enikeziwe equkethe inani elingenalutho (NULL). I-NULL ayilona amanani ohlobo lwedatha avamile, ngakho isibaluli kufanele sigcinwe ngokuhlukene.
Ngenxa yalokho, unhlokweni sikhulu impela - okungenani amabhayithi angama-23 kunguqulo ngayinye yomugqa, futhi ngokuvamile ngaphezulu ngenxa ye-bitmap engu-NULL. Uma ithebula "lincane" (okungukuthi, liqukethe amakholomu ambalwa), i-overhead ingase ithathe okungaphezu kolwazi oluwusizo.
Faka
Ake sibhekisise ukuthi imisebenzi yezintambo ezisezingeni eliphansi yenziwa kanjani, 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 ngemva kokuqala umsebenzi.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nansi inombolo yethu yamanje yokwenza:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Ake sibheke okuqukethwe yikhasi. 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 iyaziwa , elingahlangene ngalutho netafula. Lapha leli gama lisetshenziswe ngomqondo wokuthi “konke kuphonswa ndawonye,” ngokuphambene nezinkomba ezihleliwe.
Umsebenzi ubonisa idatha “njengoba injalo”, ngefomethi okunzima ukuyiqonda. Ukuze sikuthole, sizoshiya ingxenye yolwazi kuphela bese siluchaza:
=> 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 yenkomba ukuze ibukeke ifana nethi t_ctid: (inombolo yekhasi, inombolo yenkomba).
- Icacise isimo sesikhombi se-lp_flags. Lapha "kujwayelekile" - lokhu kusho ukuthi i-pointer empeleni ibhekisela enguqulweni yentambo. Sizobheka ezinye izincazelo kamuva.
- Kuzo zonke izingcezu zolwazi, ngamapheya amabili kuphela ahlonziwe kuze kube manje. Amabhithi e-xmin_committed kanye ne-xmin_aborted abonisa ukuthi inombolo yokwenziwayo ethi xmin yenziwe yini (ichithiwe). Amabhithi amabili afanayo abhekisela kunombolo yokwenziwe xmax.
Sibonani? Uma ufaka umugqa, inombolo yenkomba 1 izovela ekhasini lethebula, ikhomba inguqulo yokuqala nokuphela komugqa.
Kunguqulo yeyunithi yezinhlamvu, inkambu ye-xmin igcwaliswa ngenombolo yamanje yokwenziwe. Okwenziwayo kusasebenza, ngakho-ke kokubili amabhithi xmin_committed kanye ne-xmin_aborted akusethiwe.
Inkambu yenguqulo yerowu ye-ctid ibhekisela kumugqa ofanayo. Lokhu kusho ukuthi inguqulo entsha ayikho.
Inkambu ye-xmax igcwaliswe ngenombolo ye-dummy 0 ngoba le nguqulo yomugqa ayisusiwe futhi ingeyamanje. Okwenziwayo ngeke kuyinake le nombolo ngoba i-xmax_aborted bit isethiwe.
Ake sithathe esinye isinyathelo sokuthuthukisa ukufundeka ngokwengeza izingcezu zolwazi ezinombolweni zokwenziwe. Futhi masidale umsebenzi, njengoba sizodinga isicelo izikhathi ezingaphezu kwesisodwa:
=> 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 yenguqulo yomugqa:
=> 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 lungatholakala kuthebula ngokwalo, kusetshenziswa amakholomu mbumbulu xmin kanye ne-xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Ukulungisa
Uma ukuthengiselana kuqediwe ngempumelelo, udinga ukukhumbula isimo sakhona - qaphela ukuthi kuzibophezele. Ukwenza lokhu, kusetshenziswa isakhiwo esibizwa nge-XACT (futhi ngaphambi kwenguqulo ye-10 sasibizwa ngokuthi i-CLOG (umbhalo wokuzibophezela) futhi leli gama lisengatholakala ezindaweni ezahlukene).
I-XACT ayilona ithebula lekhathalogi yesistimu; lawa amafayela kuhlu lwemibhalo lwe-PGDATA/pg_xact. Anamabhithi amabili okwenziwayo ngakunye: ukuzibophezela nokuhoxiswa - njengakusihloko senguqulo yomugqa. Lolu lwazi luhlukaniswe lwaba amafayela amaningana ukuze kube lula kuphela; sizobuyela kulolu daba uma sicabangela ukuqandisa. Futhi ukusebenza ngalawa mafayela kwenziwa ikhasi nekhasi, njengawo wonke amanye.
Ngakho-ke, lapho okwenziwayo kwenziwa ku-XACT, ibhithi elizibophezele lisethelwa lokhu kuthenga. Futhi lokhu yikho konke okwenzeka ngesikhathi sokuzibophezela (yize singakakhulumi ngelogi yokurekhoda ngaphambilini).
Uma okunye okwenziwayo kufinyelela ikhasi lethebula esisanda kulibuka, kuzomele liphendule imibuzo embalwa.
- Ingabe umsebenzi we-xmin usuqedile? Uma kungenjalo, inguqulo edaliwe yochungechunge akufanele ibonakale.
Lokhu kuhlola kwenziwa ngokubheka esinye isakhiwo, esitholakala kumemori eyabiwe yesibonelo futhi esibizwa nge-ProcArray. Iqukethe uhlu lwazo zonke izinqubo ezisebenzayo, futhi ngayinye ngayinye inombolo yokwenziwayo kwayo kwamanje (okusebenzayo) ikhonjisiwe. - Uma kuqediwe, kanjani-ke - ngokuzibophezela noma ngokukhansela? Uma ikhanseliwe, inguqulo yomugqa akufanele ibonakale nayo.
Lokhu yikho kanye i-XACT eyenzelwe yona. Kodwa, nakuba amakhasi okugcina e-XACT egcinwa kumabhafa ku-RAM, kusabiza ukuhlola i-XACT njalo. Ngakho-ke, uma isimo sokwenziwe sesinqunyiwe, sibhalwa ku-xmin_committed kanye ne-xmin_aborted bits yenguqulo yeyunithi yezinhlamvu. Uma okukodwa kwalawa mabhithi kusethiwe, isimo somsebenzi we-xmin sithathwa saziwa futhi okwenziwayo okulandelayo ngeke kudingeke ukuthi kufinyelele ku-XACT.
Kungani lezi zingcezu zingasethwa yi-transaction ngokwayo eyenza ukufaka? Uma okufakiwe kwenzeka, okwenziwayo akukaziwa ukuthi kuzophumelela yini. Futhi ngesikhathi sokuzibophezela, akusacaci ukuthi yimiphi imigqa lapho amakhasi ashintshwa khona. Angase abe maningi amakhasi anjalo, futhi ukuwakhumbula akusizi ngalutho. Ngaphezu kwalokho, amanye amakhasi angakhishwa kunqolobane ye-buffer aye kudiski; ukuwafunda futhi ukuze ushintshe izingcezu kunganciphisa ukuzibophezela kakhulu.
Okubi kokonga ukuthi ngemva kwezinguquko, noma yikuphi ukuthengiselana (ngisho noyedwa owenza ukufunda okulula - KHETHA) angaqala ukushintsha amakhasi edatha kunqolobane yebhafa.
Ngakho-ke, masilungise ushintsho.
=> COMMIT;
Akukho okushintshile ekhasini (kodwa siyazi ukuthi isimo somsebenzi sesivele sirekhodiwe ku-XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Manje umsebenzi ofinyelela ikhasi kuqala kuzomele unqume isimo sokwenziwa kwe-xmin bese usibhala kumabhithi wolwazi:
=> 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, inombolo yamanje yokususa umsebenzi ibhalwa kunkambu ye-xmax yenguqulo yamanje, futhi i-xmax_aborted bit iyasulwa.
Qaphela ukuthi inani elimisiwe le-xmax elihambisana nomsebenzi osebenzayo lisebenza njengokhiye womugqa. Uma okunye okwenziwayo kufuna ukubuyekeza noma ukususa lo mugqa, kuzophoqeleka ukuthi kulinde ukuthi umsebenzi u-xmax uqedele. Sizokhuluma kabanzi ngokuvimba kamuva. Okwamanje, siphawula nje ukuthi inani lokukhiya amarowu alinamkhawulo. Abathathi isikhala ku-RAM futhi ukusebenza kwesistimu akuhlushwa inombolo yabo. Yiqiniso, ukuthengiselana “okude” kunezinye izinkinga, kodwa okuningi ngalokho kamuva.
Asisuse ulayini.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Siyabona ukuthi inombolo yokwenziwayo ibhaliwe 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
Ukuhoxisa izinguquko kusebenza ngokufanayo nasekuzinikeleni, kuphela ku-XACT ibhithi elihoxisiwe lisethelwe umsebenzi. Ukuhlehlisa kushesha njengokuzibophezela. Nakuba umyalo ubizwa nge-ROLLBACK, izinguquko azihlehliswa emuva: yonke into ekwazile ukuyishintsha 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 ozoyibheka.
=> 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 siqale sasusa inguqulo yamanje yomugqa sabe sesifaka entsha.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Umbuzo ukhiqiza 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. Ngaphezu kwalokho, leli nani libhalwe phezu kwendala, njengoba okwenziwa ngaphambilini kukhanseliwe. Futhi i-xmax_aborted bit isuliwe ngoba isimo somsebenzi wamanje asikaziwa.
Inguqulo yokuqala yomugqa manje ibhekisela kweyesibili (inkambu ye-t_ctid) njengentsha.
Inkomba yesibili ivela ekhasini lenkomba futhi umugqa wesibili ubhekisela enguqulweni yesibili ekhasini lethebula.
Njengokususwa, inani le-xmax enguqulweni yokuqala yomugqa liyinkomba yokuthi umugqa ukhiyiwe.
Awu, asiqedele okwenziwayo.
=> COMMIT;
Izinkomba
Kuze kube manje sikhulume kuphela ngamakhasi etafula. Kwenzekani ngaphakathi kwezinkomba?
Ulwazi olusemakhasini ezinkomba luyahluka kakhulu kuye ngohlobo oluthile lwenkomba. Futhi ngisho nohlobo olulodwa lwenkomba lunezinhlobo ezahlukene zamakhasi. Isibonelo, i-B-tree inekhasi lemethadatha namakhasi "avamile".
Kodwa-ke, leli khasi livamise ukuba nezikhombisi-ndlela zemigqa kanye nemigqa ngokwayo (njengekhasi lethebula). Ngaphezu kwalokho, ekugcineni kwekhasi kukhona isikhala sedatha ekhethekile.
Imigqa ezinkombeni ingase futhi ibe nezakhiwo ezihluke kakhulu kuye ngohlobo lwenkomba. Isibonelo, esihlahleni esingu-B, imigqa ehlobene namakhasi eqabunga iqukethe inani lokhiye wokukhomba kanye nereferensi (ctid) yomugqa wethebula ohambelanayo. Ngokuvamile, inkomba ingahlelwa ngendlela ehluke ngokuphelele.
Iphuzu elibaluleke kakhulu ukuthi azikho izinguqulo zemigqa ezikhombeni zanoma yiluphi uhlobo. Hhayi-ke, noma singacabanga ukuthi umugqa ngamunye umelwe inguqulo eyodwa ncamashi. Ngamanye amazwi, azikho izinkambu ze-xmin ne-xmax kunhlokweni yomugqa wenkomba. Singacabanga ukuthi izixhumanisi ezisuka kunkomba ziholela kuzo zonke izinguqulo zethebula zemigqa - ukuze ukwazi ukuthola ukuthi iyiphi inguqulo umsebenzi ozoyibona kuphela ngokubheka ithebula. (Njengenjwayelo, leli akulona iqiniso lonke. Kwezinye izimo, ukubonakala kwemephu kungathuthukisa inqubo, kodwa sizokubheka lokhu ngokuningiliziwe ngokuhamba kwesikhathi.)
Ngasikhathi sinye, 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, akunawo umthelela ekubonakaleni kwezinguqulo zomugqa. Ngakho-ke, inqubo yesevisi iqala ngokukhipha i-xid ebonakalayo kumsebenzi. Inombolo iqukethe i-ID yenqubo kanye nenombolo yokulandelana.
Ukukhipha le nombolo akudingi ukuvumelanisa phakathi kwazo zonke izinqubo ngakho-ke kuyashesha kakhulu. Sizojwayelana nesinye isizathu sokusebenzisa izinombolo ezibonakalayo lapho sikhuluma ngokuqandisa.
Izinombolo ezibonakalayo azinakwa nganoma iyiphi indlela kuzifinyezo zedatha.
Emaphuzwini ahlukene ngesikhathi, kungase kube khona ukuthengiselana okubonakalayo ohlelweni olunezinombolo esezisetshenzisiwe kakade, futhi lokhu kuvamile. Kodwa inombolo enjalo ayikwazi ukubhalwa emakhasini edatha, ngoba ngesikhathi esilandelayo ikhasi lifinyelelwa lingase lilahlekelwe yiyo yonke incazelo.
=> 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;
Ukuthengiselana Okufakiwe
Londoloza amaphuzu
Ichazwe ku-SQL gcina amaphuzu (i-savepoint), ekuvumela ukuthi ukhansele ingxenye yentengiselwano ngaphandle kokuyiphazamisa ngokuphelele. Kodwa lokhu akungeni emdwebeni ongenhla, njengoba okwenziwayo kunesimo esifanayo sazo zonke izinguquko zakho, futhi ngokomzimba akukho datha ebuyiselwa emuva.
Ukuze wenze lokhu kusebenze, umsebenzi one-savepoint uhlukaniswa ngokwehlukana okuningana ukuthengiselana okufakiwe (i-subtransaction), isimo esingaphathwa ngokwehlukana.
Imisebenzi efakwe kusidleke inenombolo yayo (iphezulu kunenombolo yokwenziwayo okuyinhloko). Isimo sokuthengiselana okufakwe esidlekeni sirekhodwa ngendlela evamile ku-XACT, kodwa isimo sokugcina sincike esimweni somsebenzi oyinhloko: uma kukhanseliwe, khona-ke yonke imisebenzi esesidlekeni nayo iyakhanselwa.
Ulwazi mayelana nokwenza isidleke kugcinwe kumafayela kuhla lwemibhalo lwe-PGDATA/pg_subtrans. Amafayela afinyelelwa ngamabhafa kumemori eyabiwe yesibonelo, ehlelwe ngendlela efanayo namabhafa e-XACT.
Ungaphambanisi ukuthengiselana okufakwe esidlekeni nokuthengiselana okuzenzakalelayo. Ukuthengiselana okuzenzakalelayo akuncikile komunye nomunye nganoma iyiphi indlela, kodwa ukuthengiselana okufakwe esidlekeni kuyakwenza. Akukho ukuthengiselana okuzimele ku-PostgreSQL ejwayelekile, futhi, mhlawumbe, okungcono kakhulu: adingeka kakhulu, kuyaqabukela, futhi ukuba khona kwawo kwamanye ama-DBMS kubangela ukuhlukunyezwa, lapho wonke umuntu ehlupheka khona.
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 sibeke 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 eyinhloko, hhayi inombolo yokwenziwe efakwe esidlekeni.
=> 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 okwenziwayo okukhanseliwe.
Silungisa 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 ungabona ngokucacile ukuthi okwenziwayo ngakunye kunesimo sakhona.
Qaphela ukuthi okwenziwa kusidleke akukwazi ukusetshenziswa ngokusobala ku-SQL, okusho ukuthi, awukwazi ukuqala umsebenzi omusha ngaphandle kokuqeda owamanje. Le nqubo yenziwa yasebenza ngokusobala lapho usebenzisa ama-savepoints, kanye nalapho uphatha okuhlukile kwe-PL/pgSQL kanye nenani lamanye, izimo ezingavamile kakhulu.
=> 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 ngenkathi kwenziwa umsebenzi? Ngokwesibonelo, kanje:
=> 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. Manje okwenziwayo kuthathwa njengokunqanyuliwe futhi akukho misebenzi evunyelwe kukho:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Futhi noma uzama ukwenza izinguquko, i-PostgreSQL izobika ngokukhipha isisu:
=> COMMIT;
ROLLBACK
Kungani umsebenzi ungaqhubeki ngemva kokwehluleka? Iqiniso liwukuthi iphutha lingavela ngendlela yokuthi singathola ukufinyelela engxenyeni yezinguquko - i-atomicity yokungakwenzi ngisho umsebenzi, kodwa u-opharetha uzophulwa. 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)
Kumele kushiwo ukuthi i-psql inemodi esavumela ukuthi ukuthengiselana kuqhubeke ngemva kokwehluleka njengokungathi izenzo zomqhubi onephutha zibuyiselwe 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;
Akunzima ukuqagela ukuthi kule modi, i-psql empeleni ibeka iphuzu lokulondoloza elingacacile ngaphambi komyalo ngamunye, futhi uma kwenzeka ukwehluleka iqalisa ukuhlehlisa kuyo. Le modi ayisetshenziswa ngokuzenzakalelayo, njengoba ukusetha izindawo zokulondoloza (ngisho nangaphandle kokuhlehla kuzo) kuhilela phezulu okubalulekile.
Source: www.habr.com