Kahoo, re hlahlobile litaba tse amanang le , 'me a khutlela morao ka . 'Me qetellong re fihlile karolong e thahasellisang ka ho fetisisa - mefuta ea likhoele.
Sehlooho
Joalokaha re se re boletse, mola o mong le o mong o ka ba teng ka nako e le 'ngoe liphetolelong tse' maloa ho database. Phetolelo e 'ngoe e tlameha ho khetholloa ka tsela e itseng ho e 'ngoe.Ka morero ona, phetolelo ka 'ngoe e na le matšoao a mabeli a bontšang "nako" ea ketso ea phetolelo ena (xmin le xmax). Ka mantsoe a qotsitsoeng - hobane ha se nako e sebelisoang joalo, empa ke k'haonta e khethehileng e ntseng e eketseha. 'Me counter ena ke nomoro ea transaction.
(Joalo ka tloaelo, 'nete e rarahane le ho feta: nomoro ea transaction e ke ke ea eketseha ka linako tsohle ka lebaka la matla a fokolang a k'haontareng. Empa re tla sheba lintlha tsena ka botlalo ha re fihla ho leqhoa.)
Ha ho entsoe mola, xmin e setelloa ho nomoro ea transaction e faneng ka taelo ea INSERT, 'me xmax e sala e se na letho.
Ha mola o hlakotsoe, boleng ba xmax ba mofuta oa hajoale bo tšoauoa ka palo ea transaction e entseng DELETE.
Ha mola o fetotsoe ka taelo ea UPDATE, ho hlile ho etsoa opereishene tse peli: DELETE le INSERT. Mofuta oa hajoale oa mola o beha xmax ho lekana le palo ea khoebo e entseng UPDATE. Phetolelo e ncha ea khoele e tšoanang e ntan'o bōptjoa; boleng ba eona ba xmin bo tsamaellana le boleng ba xmax ba mofuta o fetileng.
Libaka tsa xmin le xmax li kenyellelitsoe sehloohong sa mofuta oa mola. Ntle le masimo ana, hlooho e na le tse ling, mohlala:
- infomask ke letoto la likotoana tse hlalosang thepa ea mofuta ona. Ho na le tse ngata tsa tsona; Re tla hlahloba butle-butle tse ka sehloohong.
- ctid ke sehokelo sa mofuta o latelang, o mocha oa mola o tšoanang. Bakeng sa mofuta o mocha, oa morao-rao oa khoele, ctid e bua ka mofuta ona ka boyona. Nomoro e na le sebopeho (x,y), moo x e leng nomoro ea leqephe, y ke nomoro ea index ka lethathamong.
- null bitmap - E tšoaea litšiea tseo tsa mofuta o fanoeng o nang le boleng ba lefeela (NULL). NULL ha se e 'ngoe ea litekanyetso tse tloaelehileng tsa mofuta oa data, kahoo tšobotsi e tlameha ho bolokoa ka thoko.
Ka lebaka leo, hlooho e kholo haholo - bonyane li-byte tse 23 bakeng sa mofuta o mong le o mong oa mohala, 'me hangata ho feta ka lebaka la bitmap ea NULL. Haeba tafole e "moqotetsa" (ke hore, e na le litšiea tse 'maloa), sehlooho se ka holimo se ka nka ho feta tlhahisoleseding e molemo.
kenya
A re hlahlobisiseng hore na ts'ebetso ea likhoele tse tlaase li etsoa joang, ho qala ka ho kenya.
Bakeng sa liteko, ha re theheng tafole e ncha e nang le likholomo tse peli le index ho e 'ngoe ea tsona:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Ha re kenye mola o le mong ka mor'a ho qala transaction.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nomoro ea rona ea thekiso ea hajoale ke ena:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Ha re shebeng litaba tsa leqephe lena. Mosebetsi oa heap_page_items oa katoloso ea tlhahlobo ea leqephe o u lumella ho fumana leseli mabapi le lintlha le mefuta ea mela:
=> 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
Hlokomela hore lentsoe qubu ho PostgreSQL le bolela litafole. Ena ke tšebeliso e 'ngoe e makatsang ea lentsoe - qubu e tsejoa , eo ho seng letho le tšoanang le tafole. Mona lentsoe lena le sebelisoa ka kutloisiso ea “ntho e ’ngoe le e ’ngoe e phuthetsoe hammoho,” ho fapana le li- index tse hlophisitsoeng.
Ts'ebetso e bonts'a data "joalo ka ha e le", ka mokhoa oo ho leng thata ho o utloisisa. Ho e utloisisa, re tla siea karolo feela ea tlhahisoleseling ebe re e hlalosa:
=> 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)
Mona ke seo re se entseng:
- E kentse noto ho nomoro ea index ho etsa hore e shebahale e tšoana le t_ctid: (nomoro ea leqephe, nomoro ea index).
- E hlalositse boemo ba lp_flags pointer. Mona ke "tloaelehileng" - sena se bolela hore pointer e hlile e bua ka phetolelo ea khoele. Re tla sheba litlhaloso tse ling hamorao.
- Har'a likotoana tsohle tsa tlhahisoleseling, ke lipara tse peli feela tse khethiloeng ho fihlela joale. xmin_committed and xmin_aborted bits e bonts'a hore na nomoro ea transaction xmin e entsoe (e felisitsoe). Li-bits tse peli tse tšoanang li bua ka nomoro ea transaction xmax.
Re bona eng? Ha o kenya mola, nomoro ea 1 e tla hlaha leqepheng la tafole, e supa mofuta oa pele le o le mong feela oa mola.
Mofuta oa likhoele, lebala la xmin le tlatsitsoe ka nomoro ea hajoale ea transaction. Ts'ebetso e ntse e sebetsa, ka hona, li-bits tsa xmin_committed le xmin_aborted ha lia hlophisoa.
Sebaka sa mofuta oa ctid se bua ka mola o tšoanang. Sena se bolela hore mofuta o mocha ha o eo.
Sebaka sa xmax se tlatsitsoe ka nomoro ea dummy 0 hobane mofuta ona oa mola ha o so hlakoloe ebile ke oa hajoale. Lichelete li ke ke tsa ela nomoro ena hloko hobane xmax_aborted bit e setiloe.
Ha re nke mohato o mong hape oa ho ntlafatsa ho baloa ka ho eketsa lintlha tsa lintlha ho linomoro tsa transaction. Mme ha re theheng tšebetso, hobane re tla hloka kopo ho feta hang:
=> 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;
Ka sebopeho sena, ho hlakile haholoanyane se etsahalang sehloohong sa mofuta ona:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Ka mokhoa o ts'oanang, empa o sa hlake haholoanyane, tlhahisoleseling e ka fumanoa tafoleng ka boeona, ho sebelisoa li-columns xmin le xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Ho lokisa
Haeba transaction e phethiloe ka katleho, o hloka ho hopola boemo ba eona - hlokomela hore e ikemiselitse. Ho etsa sena, ho sebelisoa sebopeho se bitsoang XACT ('me pele ho mofuta oa 10 se ne se bitsoa CLOG (log log) mme lebitso lena le ntse le ka fumaneha libakeng tse fapaneng).
XACT ha se tafole ea lethathamo la litsamaiso; tsena ke lifaele tse bukeng ea PGDATA/pg_xact. Li na le likotoana tse peli tse fanoeng bakeng sa khoebo ka 'ngoe: e ikemiselitseng le e felisitsoeng - joalo ka sehlooho sa mofuta oa mola. Tlhahisoleseding ena e arotsoe ka lifaele tse 'maloa feela bakeng sa boiketlo; re tla khutlela khatisong ena ha re nahana ka leqhoa. 'Me ho sebetsa ka lifaele tsena ho etsoa leqephe ka leqephe, joalo ka ba bang kaofela.
Kahoo, ha khoebo e etsoa ho XACT, chelete e ikemiselitseng e behiloe bakeng sa thekiso ena. 'Me sena ke sohle se etsahalang nakong ea boitlamo (leha re ntse re sa bue ka logi ea ho rekota pele).
Ha khoebo e 'ngoe e fihla leqepheng la tafole eo re sa tsoa e sheba, e tla tlameha ho araba lipotso tse' maloa.
- Na transaction ea xmin e phethiloe? Haeba ho se joalo, joale phetolelo e entsoeng ea khoele ha ea lokela ho bonahala.
Cheke ena e etsoa ka ho sheba sebopeho se seng, se leng mohopolong o arolelanoeng oa mohlala mme o bitsoa ProcArray. E na le lethathamo la lits'ebetso tsohle tse sebetsang, 'me bakeng sa e' ngoe le e 'ngoe palo ea eona ea hona joale (e sebetsang) e bontšitsoe. - Haeba e phethiloe, joale joang - ka ho itlama kapa ho hlakola? Haeba e hlakotsoe, mofuta oa mela ha oa lokela ho bonahala le ona.
Sena ke sona seo XACT e leng sona. Empa, leha maqephe a ho qetela a XACT a bolokiloe ka har'a li-buffers ho RAM, ho ntse ho bitsa chelete e ngata ho lekola XACT nako le nako. Ka hona, hang ha boemo ba transaction bo khethiloe, bo ngolloa xmin_committed le xmin_aborted bits tsa mofuta oa khoele. Haeba e 'ngoe ea li-bits tsena e behiloe, joale boemo ba transaction xmin bo nkoa bo tsejoa mme transaction e latelang e ke ke ea tlameha ho fihlella XACT.
Ke hobane'ng ha li-bits tsee li sa behoa ke transaction ka boeona e etsa ho kenya? Ha ho kenngoa, transaction ha e e-s'o tsebe hore na e tla atleha. 'Me motsotsong oa ho itlama, ha ho sa hlaka hore na ke mela efe eo maqephe a fetotsoeng ka eona. Ho ka 'na ha e-ba le maqephe a joalo a mangata,' me ho a tšoara ka hlooho ha ho thuse letho. Ho feta moo, maqephe a mang a ka ntšoa ho buffer cache ho disk; ho li bala hape ho fetola likotoana ho tla liehisa boitlamo haholo.
Bothata ba ho boloka chelete ke hore ka mor'a liphetoho, khoebo leha e le efe (esita le e 'ngoe ea ho bala habonolo - KHETHA) e ka qala ho fetola maqephe a data ka har'a cache ea buffer.
Kahoo, ha re lokiseng phetoho.
=> COMMIT;
Ha ho letho le fetotsoeng leqepheng (empa rea tseba hore boemo ba transaction bo se bo tlalehiloe ho XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Hona joale transaction e kenang leqepheng la pele e tla tlameha ho tseba boemo ba transaction ea xmin ebe e e ngolla litsing tsa tlhahisoleseling:
=> 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)
Tlosa
Ha mola o hlakotsoe, palo ea transaction ea hajoale e ngolloa sebakeng sa xmax sa mofuta oa hajoale, 'me xmax_aborted bit e tla hlakoloa.
Hlokomela hore boleng bo behiloeng ba xmax bo tsamaellanang le transaction e sebetsang e sebetsa joalo ka senotlolo sa mela. Haeba khoebo e 'ngoe e batla ho ntlafatsa kapa ho hlakola mola ona, e tla tlameha ho emela hore transaction xmax e phethehe. Re tla bua haholoanyane ka ho thibela hamorao. Hajoale, re hlokomela feela hore palo ea liloko tsa mela ha e na moeli. Ha ba nke sebaka ho RAM mme ts'ebetso ea sistimi ha e na bothata ba palo ea bona. Ke 'nete, litšebelisano "tse telele" li na le mathata a mang, empa ho feta moo hamorao.
Ha re hlakole mohala.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Rea bona hore nomoro ea transaction e ngotsoe tšimong ea xmax, empa lintlha tsa tlhahisoleseling ha lia hlophisoa:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
khanselwa
Liphetoho tsa aborting li sebetsa ka mokhoa o ts'oanang le ho itlama, ho XACT feela karolo e felisitsoeng e behiloe bakeng sa thekiso. Ho etsolla ho potlakile joalo ka ho itlama. Le hoja taelo e bitsoa ROLLBACK, liphetoho ha li khutlisetsoe morao: ntho e 'ngoe le e' ngoe eo transaction e atlehileng ho e fetola maqepheng a data e lula e sa fetohe.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Ha leqephe le fumaneha, boemo bo tla hlahlojoa 'me xmax_aborted hint bit e tla hlophisoa mofuteng oa mola. Nomoro ea xmax ka boeona e sala leqepheng, empa ha ho motho ea tla e sheba.
=> 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)
Phetoho
Ntlafatso e sebetsa joalo ka ha eka e hlakotse mofuta oa hajoale oa mola ebe e kenya e ncha.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Potso e hlahisa mola o le mong (mofuta o mocha):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Empa leqepheng lena re bona liphetolelo tse peli:
=> 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)
Mofuta o tlositsoeng o tšoailoe ka nomoro ea hajoale ea transaction sebakeng sa xmax. Ho feta moo, boleng bona bo ngotsoe holim'a ea khale, kaha khoebo e fetileng e ile ea hlakoloa. 'Me xmax_aborted bit e hlakotsoe hobane boemo ba transaction ea hajoale ha bo so tsejoe.
Mofuta oa pele oa mohala joale o bua ka ea bobeli (t_ctid field) e le e ncha.
Lenane la bobeli le hlaha leqepheng la index 'me mola oa bobeli o supa mofuta oa bobeli leqepheng la tafole.
Joalo ka ha ho hlakoloa, boleng ba xmax mofuteng oa pele oa mola ke sesupo sa hore mola o notletsoe.
Joale, ha re phetheng tšebetso.
=> COMMIT;
Li-index
Ho fihlela joale re se re buile feela ka maqephe a tafole. Ho etsahala'ng ka har'a li-index?
Boitsebiso bo maqepheng a index bo fapana haholo ho itšetlehile ka mofuta o itseng oa index. Mme le mofuta o le mong oa index o na le mefuta e fapaneng ea maqephe. Ka mohlala, sefate sa B se na le leqephe la metadata le maqephe a "kamehla".
Leha ho le joalo, hangata leqephe le na le litsupa tse ngata ho mela le mela ka boeona (joalo ka leqephe la tafole). Ho phaella moo, qetellong ea leqephe ho na le sebaka sa data e khethehileng.
Mela ka har'a li-index le eona e ka ba le libopeho tse fapaneng haholo ho latela mofuta oa index. Mohlala, bakeng sa sefate sa B, mela e amanang le maqephe a makhasi e na le boleng ba senotlolo sa indexing le referense (ctid) ho mola oa tafole o tsamaellanang. Ka kakaretso, index e ka hlophisoa ka tsela e fapaneng ka ho felletseng.
Ntlha ea bohlokoa ka ho fetisisa ke hore ha ho na liphetolelo tsa mela ho li-index tsa mofuta ofe kapa ofe. Che, kapa re ka nka hore mola o mong le o mong o emeloa ke mofuta o le mong. Ka mantsoe a mang, ha ho na xmin le xmax masimo a sehlooho sa index row. Re ka nka hore likhokahano ho tsoa ho index li lebisa ho mefuta eohle ea litafole tsa mela - kahoo o ka tseba hore na ke mofuta ofe oa transaction o tla bona feela ka ho sheba tafole. (Joalo ka kamehla, sena ha se 'nete kaofela. Maemong a mang, 'mapa oa ponahalo o ka ntlafatsa ts'ebetso, empa re tla sheba sena ka botlalo hamorao.)
Ka nako e ts'oanang, leqepheng la index re fumana lintlha tsa liphetolelo tse peli, tsa morao-rao le tsa khale:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Lits'ebetso tsa Virtual
Ha e le hantle, PostgreSQL e sebelisa lintlafatso tse e lumellang ho "boloka" linomoro tsa transaction.
Haeba transaction e bala data feela, ha e na phello ponahalong ea mefuta ea mela. Ka hona, ts'ebetso ea ts'ebeletso e qala ka ho fana ka xid ea nnete ho transaction. Nomoro e na le ID ea ts'ebetso le nomoro ea tatellano.
Ho fana ka nomoro ena ha ho hloke ho amahanngoa pakeng tsa lits'ebetso tsohle mme ka hona ho potlakile haholo. Re tla tloaelana le lebaka le leng la ho sebelisa linomoro tsa sebele ha re bua ka serame.
Linomoro tsa Virtual ha li hlokomeloe ka tsela leha e le efe ho li-snapshots tsa data.
Libakeng tse fapaneng ka nako, ho ka 'na ha e-ba le litšebelisano tsa sebele tsamaisong e nang le linomoro tse seng li sebelisitsoe,' me sena se tloaelehile. Empa palo e joalo e ke ke ea ngoloa maqepheng a data, hobane nakong e tlang ha leqephe le fumanoa le ka lahleheloa ke moelelo oohle.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Haeba transaction e qala ho fetola data, e fuoa nomoro ea sebele, e ikhethang ea transaction.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Nested Transactions
Boloka lintlha
E hlalosoa ka SQL boloka lintlha (savepoint), e u lumellang ho hlakola karolo ea transaction ntle le ho e sitisa ka botlalo. Empa sena ha se lumellane le setšoantšo se ka holimo, kaha transaction e na le boemo bo tšoanang bakeng sa liphetoho tsohle tsa eona, 'me ha ho na data e khutliselitsoeng morao.
Ho kenya ts'ebetsong ts'ebetso ena, transaction e nang le savepoint e arotsoe ka likarolo tse 'maloa ditransekshene tse entsweng (subtransaction), boemo ba eona bo ka laoloang ka thoko.
Litheko tse entsoeng ka Nested li na le nomoro ea tsona (e holimo ho palo ea transaction e kholo). Boemo ba lipehelo tsa sehlaha bo ngotsoe ka mokhoa o tloaelehileng ho XACT, empa boemo ba ho qetela bo itšetlehile ka boemo ba transaction ea mantlha: haeba e hlakotsoe, litšebelisano tsohle tse entsoeng sehlaheng le tsona li hlakotsoe.
Tlhahisoleseding e mabapi le sehlaha sa transaction e bolokwa difaeleng tsa PGDATA/pg_subtrans directory. Lifaele li fumaneha ka li-buffers mohopolong o arolelanoang oa mohlala, o hlophisitsoeng ka tsela e ts'oanang le li-buffers tsa XACT.
Se ke oa ferekanya li-transaction le li-transactions tse ikemetseng. Litšebelisano tse ikemetseng ha li itšetlehe ka tsela leha e le efe, empa litšebelisano tsa sehlaha li etsa joalo. Ha ho na litšebelisano tse ikemetseng ho PostgreSQL e tloaelehileng, 'me, mohlomong, bakeng sa se molemo ka ho fetisisa: li hlokahala haholo, ka seoelo,' me ho ba teng ha tsona ho tse ling tsa DBMS ho qholotsa tlhekefetso, eo bohle ba utloang bohloko ho eona.
Ha re hlakise tafole, re qale khoebo ebe re kenya mola:
=> 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)
Joale ha re beheng ntlha ea ho boloka ebe re kenya mola o mong.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Hlokomela hore ts'ebetso ea txid_current() e khutlisa nomoro ea mantlha ea transaction, eseng nomoro ea transaction e behiloeng.
=> 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)
Ha re khutlele sebakeng sa polokelo ebe re kenya mohala oa boraro.
=> 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)
Leqepheng re tsoela pele ho bona mola o kentsoeng ke thekiso e hlakotsoeng ea sehlaha.
Re lokisa liphetoho.
=> 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)
Hona joale u ka bona ka ho hlaka hore khoebo e 'ngoe le e' ngoe e entsoeng ka sehlaha e na le boemo ba eona.
Hlokomela hore litšebelisano tse hlophisitsoeng li ke ke tsa sebelisoa ka mokhoa o hlakileng ho SQL, ke hore, u ke ke ua qala khoebo e ncha ntle le ho tlatsa ea hajoale. Mochine ona o sebelisoa ka mokhoa o hlakileng ha o sebelisa li-savepoints, hammoho le ha o sebetsana le mekhelo ea PL/pgSQL le maemong a mang a mangata, a sa tloaelehang.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Liphoso le atomicity ea ts'ebetso
Ho etsahala'ng haeba phoso e etsahala nakong ea opereishene? Ka mohlala, joalo ka:
=> 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
Phoso e etsahetse. Hona joale transaction e nkoa e felisitsoe 'me ha ho ts'ebetso e lumelloang ho eona:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Mme leha o leka ho etsa liphetoho, PostgreSQL e tla tlaleha ho ntša mpa:
=> COMMIT;
ROLLBACK
Ke hobane'ng ha khoebo e sa khone ho tsoela pele ka mor'a ho hlōleha? 'Nete ke hore phoso e ka hlaha ka tsela ea hore re ka fumana karolo ea liphetoho - atomicity ea esita le transaction, empa opareitara e ne e tla tlōloa. Joalo ka mohlala oa rona, moo opareitara e atlehileng ho nchafatsa mohala o le mong pele ho phoso:
=> 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)
Ho tlameha ho boleloa hore psql e na le mokhoa o ntseng o lumella hore transaction e tsoele pele ka mor'a ho hlōleha joalokaha eka liketso tsa mokhanni ea fosahetseng li khutliselitsoe morao.
=> 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;
Ha ho thata ho hakanya hore ka mokhoa ona, psql ha e le hantle e beha ntlha e hlakileng ea ho boloka pele ho taelo e 'ngoe le e' ngoe, 'me haeba ho hlōleha ho qala ho khutlela ho eona. Mokhoa ona ha o sebelisoe ka ho sa feleng, kaha ho beha li-savepoints (esita le ntle le ho khutlela ho tsona) ho kenyelletsa taba ea bohlokoa.
Source: www.habr.com