Don haka, mun yi la'akari da batutuwan da suka shafi , kuma yayi ja da baya game da . Kuma a ƙarshe mun kai ga mafi ban sha'awa part - da kirtani versions.
Rubuta
Kamar yadda muka fada a baya, kowane layi na iya kasancewa a lokaci guda a cikin nau'ikan bayanai da yawa a cikin bayanan. Dole ne a bambanta ɗayan sigar ko ta yaya da wani, don wannan dalili, kowace sigar tana da alamomi biyu waɗanda ke ƙayyade “lokacin” aikin wannan sigar (xmin da xmax). A cikin ƙididdiga - saboda ba lokaci ba ne kamar yadda ake amfani da shi, amma ƙididdiga ta musamman. Kuma wannan counter shine lambar ciniki.
(Kamar yadda aka saba, gaskiyar ta fi rikitarwa: lambar ma'amala ba za ta iya ƙaruwa koyaushe ba saboda ƙarancin ɗan ƙaramin ƙarfin na'urar. Amma za mu kalli waɗannan cikakkun bayanai dalla-dalla lokacin da muka isa daskarewa.)
Lokacin da aka ƙirƙiri jere, ana saita xmin zuwa lambar ciniki wacce ta ba da umarnin INSERT, kuma xmax ya bar komai.
Lokacin da aka share jere, ƙimar xmax na sigar yanzu tana da alamar adadin ma'amalar da ta yi DELETE.
Lokacin da aka gyaggyara layi ta hanyar UPDATE, ana aiwatar da ayyuka guda biyu: GAME da SA. Sigar layi na yanzu yana saita xmax daidai da adadin ma'amalar da tayi UPDATE. Sannan an ƙirƙiri sabon sigar irin wannan kirtani; ƙimar xmin sa yayi daidai da ƙimar xmax na sigar da ta gabata.
An haɗa filayen xmin da xmax a cikin jigon sigar layi. Baya ga waɗannan fagagen, taken ya ƙunshi wasu, misali:
- infomask jerin rago ne da ke ayyana kaddarorin wannan sigar. Akwai su da yawa; A hankali za mu yi la'akari da manyan.
- ctid hanyar haɗi ce zuwa na gaba, sabon sigar layi ɗaya. Don sabon abu, mafi yawan sigar kirtani na yanzu, ctid yana nufin wannan sigar kanta. Lambar tana da nau'i (x,y), inda x shine lambar shafi, y shine lambar fihirisa a cikin tsararru.
- null bitmap - Alama waɗancan ginshiƙan sigar da aka bayar waɗanda ke ɗauke da ƙimar mara amfani (NULL). NULL baya ɗaya daga cikin ƙimar nau'in bayanai na yau da kullun, don haka dole ne a adana sifa daban.
Sakamakon haka, taken yana da girma sosai - aƙalla 23 bytes ga kowane sigar layin, kuma yawanci ƙari saboda NULL bitmap. Idan tebur ya kasance "kunkuntar" (wato, ya ƙunshi ginshiƙai kaɗan), abin da ke kan gaba zai iya ɗaukar fiye da bayanan masu amfani.
saka
Bari mu kalli yadda ake gudanar da ayyukan kirtani na ƙasa, farawa da sakawa.
Don gwaje-gwaje, bari mu ƙirƙiri sabon tebur mai ginshiƙai biyu da fihirisa akan ɗayansu:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Mu saka layi daya bayan fara ciniki.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Ga lambar cinikin mu ta yanzu:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Bari mu dubi abubuwan da ke cikin shafin. Aikin heap_page_items na tsawaita binciken shafin yana ba ku damar samun bayanai game da masu nuni da juzu'in jere:
=> 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
Lura cewa kalmar heap a cikin PostgreSQL tana nufin tebur. Wannan wani bakon amfani ne na kalmar - an san tsibi , wanda ba shi da wani abu da ya dace da tebur. Anan an yi amfani da kalmar a ma'anar "an jefa kome tare," sabanin ma'anar da aka ba da umarni.
Ayyukan yana nuna bayanai "kamar yadda yake", a cikin tsarin da ke da wuyar fahimta. Don gane shi, za mu bar wani ɓangare na bayanin kawai mu gane shi:
=> 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)
Ga abin da muka yi:
- Ƙara sifili zuwa lambar fihirisar don yin kama da t_ctid: (lambar shafi, lambar fihirisa).
- An tantance yanayin ma'anar lp_flags. Anan shine "al'ada" - wannan yana nufin cewa mai nuni a zahiri yana nufin sigar kirtani. Za mu duba wasu ma'anoni daga baya.
- A cikin dukkan bayanan bayanan, nau'i-nau'i biyu ne kawai aka gano ya zuwa yanzu. Xmin_committed da xmin_aborted bits suna nuna ko lambar ciniki ta xmin ta aikata (an soke). Biyu makamantansu suna nufin lambar ciniki xmax.
Me muke gani? Lokacin da kuka saka jere, lambar fihirisa 1 za ta bayyana a cikin shafin tebur, tana nuna sigar farko kuma kawai na jere.
A cikin sigar kirtani, filin xmin yana cike da lambar ciniki ta yanzu. Har yanzu cinikin yana aiki, don haka duka xmin_committed da xmin_aborted bits ba a saita su ba.
Filin ctid sigar jere yana nufin jere iri ɗaya. Wannan yana nufin cewa babu sabon sigar.
Filin xmax yana cike da dummy number 0 saboda wannan sigar layin ba a goge ba kuma tana halin yanzu. Ma'amaloli ba za su kula da wannan lambar ba saboda an saita bit xmax_aborted.
Bari mu ɗauki ƙarin mataki ɗaya don haɓaka iya karantawa ta ƙara bayanan bayanai zuwa lambobin ciniki. Kuma bari mu ƙirƙiri aiki, tunda za mu buƙaci buƙatar fiye da sau ɗaya:
=> 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;
A cikin wannan tsari, ya fi bayyana abin da ke faruwa a cikin taken jeri:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Hakazalika, amma ba dalla-dalla ba, ana iya samun bayanai daga tebur ɗin kanta, ta amfani da ginshiƙai na xmin da xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Gyarawa
Idan an kammala ciniki cikin nasara, kuna buƙatar tunawa da matsayinsa - lura cewa an yi shi. Don yin wannan, ana amfani da wani tsari mai suna XACT (kuma kafin sigar 10 ana kiranta CLOG (commit log) kuma ana iya samun wannan suna a wurare daban-daban).
XACT ba tebur kasida ba ne; waɗannan fayiloli ne a cikin PGDATA/pg_xact directory. Suna da rago biyu don kowace ma'amala: aikatawa da zubar da ciki - kamar a cikin taken sigar layi. An raba wannan bayanin zuwa fayiloli da yawa don dacewa kawai; za mu koma kan wannan batu idan muka yi la'akari da daskarewa. Kuma aiki tare da waɗannan fayilolin ana aiwatar da su shafi zuwa shafi, kamar tare da duk sauran.
Don haka, lokacin da aka yi ciniki a cikin XACT, an saita abin da aka yi don wannan ma'amala. Kuma wannan shine duk abin da ke faruwa yayin aikatawa (ko da yake ba mu magana game da log ɗin riga-kafi tukuna).
Lokacin da wata ma'amala ta shiga shafin tebur da muke kallo kawai, dole ne ta amsa tambayoyi da yawa.
- An gama cinikin xmin? Idan ba haka ba, to, ƙirƙira sigar kirtani bai kamata a ganuwa ba.
Ana yin wannan rajistan ta hanyar kallon wani tsari, wanda ke cikin ƙwaƙwalwar ajiyar misali kuma ana kiranta ProcArray. Ya ƙunshi jerin duk matakai masu aiki, kuma ga kowane ɗayan ana nuna adadin ma'amalarsa na yanzu (aiki). - Idan an gama, to ta yaya - ta hanyar aikatawa ko sokewa? Idan an soke, to bai kamata a ga sigar jere ba.
Wannan shine ainihin abin da XACT ke nufi. Amma, kodayake shafukan ƙarshe na XACT ana adana su a cikin buffers a cikin RAM, har yanzu yana da tsada don duba XACT kowane lokaci. Don haka, da zarar an tantance matsayin ciniki, ana rubuta shi zuwa ga xmin_committed da xmin_aborted bits na sigar kirtani. Idan an saita ɗaya daga cikin waɗannan raƙuman ruwa, to ana ɗaukar yanayin ma'amalar xmin an san shi kuma ciniki na gaba ba zai sami damar shiga XACT ba.
Me ya sa ba a saita waɗannan ragi ta hanyar ciniki da kanta tana yin sakawa? Lokacin da abin sakawa ya faru, ciniki bai riga ya san ko zai yi nasara ba. Kuma a halin da ake ciki, ba a san ko wane layi aka canza ba. Wataƙila akwai da yawa irin waɗannan shafuka, kuma haddace su ba shi da fa'ida. Bugu da kari, ana iya fitar da wasu shafuka daga ma'ajiyar buffer zuwa faifai; sake karanta su don canza ɓangarorin zai rage saurin aiwatarwa sosai.
Ƙarƙashin ajiyar kuɗi shine bayan canje-canje, duk wani ciniki (ko da wanda ke yin karatu mai sauƙi - SELECT) zai iya fara canza shafukan bayanai a cikin ma'ajin buffer.
Don haka, bari mu gyara canjin.
=> COMMIT;
Babu wani abu da ya canza a shafin (amma mun san cewa an riga an rubuta matsayin ciniki a cikin XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Yanzu ma'amalar da ta fara shiga shafin dole ne ta tantance matsayin kasuwancin xmin sannan a rubuta shi zuwa ga bayanan bayanan:
=> 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)
Share
Lokacin da aka share jere, ana rubuta adadin kuɗin ciniki na yanzu zuwa filin xmax na sigar yanzu, kuma ana share bit xmax_aborted.
Lura cewa saita ƙimar xmax daidai da ma'amala mai aiki tana aiki azaman kulle jere. Idan wata ma'amala tana son sabunta ko share wannan layin, za a tilasta ta jira xmax ciniki ya kammala. Za mu yi magana game da toshewa daga baya. A yanzu, kawai mu lura cewa adadin makullin jere ba shi da iyaka. Ba sa ɗaukar sarari a cikin RAM kuma aikin tsarin baya wahala daga lambar su. Gaskiya ne, ma'amaloli na "dogon" suna da wasu rashin amfani, amma fiye da haka daga baya.
Mu share layin.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Mun ga cewa an rubuta lambar ma'amala a cikin filin xmax, amma ba a saita ragowar bayanan ba:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
sokewa
Zubar da canje-canje yana aiki daidai da aikatawa, kawai a cikin XACT an saita bit ɗin da aka zubar don ma'amala. Gyarawa yana da sauri kamar aikatawa. Ko da yake ana kiran umarnin ROLLBACK, canje-canje ba a mayar da su ba: duk abin da ma'amalar ta gudanar don canzawa a cikin shafukan bayanan ya kasance baya canzawa.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Lokacin da aka shiga shafin, za a duba matsayin kuma za a saita bit hint xmax_aborted zuwa sigar jere. Lambar xmax kanta tana kan shafin, amma ba wanda zai dube ta.
=> 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)
Sabuntawa
Sabuntawa yana aiki kamar ya fara goge nau'in layin na yanzu sannan ya saka sabo.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Tambayar ta samar da layi ɗaya (sabon siga):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Amma a shafin muna ganin nau'i biyu:
=> 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)
Sigar da aka goge ana yiwa alama da lambar ciniki ta yanzu a cikin filin xmax. Bugu da ƙari, an rubuta wannan ƙimar akan tsohuwar, tun lokacin da aka soke ciniki na baya. Kuma an share bit xmax_aborted saboda ba a san matsayin ciniki na yanzu ba.
Sigar farko ta layin yanzu tana nufin na biyu (t_ctid filin) a matsayin sabuwar.
Fihirisa na biyu ya bayyana a cikin shafin fihirisar kuma jeri na biyu yana nuni ga sigar ta biyu a cikin shafin tebur.
Kamar yadda yake tare da gogewa, ƙimar xmax a cikin sigar farko na jere nuni ne cewa an kulle layin.
To, bari mu kammala ciniki.
=> COMMIT;
Fihirisa
Ya zuwa yanzu mun yi magana ne kawai game da shafukan tebur. Me ke faruwa a cikin fihirisa?
Bayanan da ke cikin shafukan fihirisa sun bambanta sosai dangane da takamaiman nau'in fihirisar. Kuma ko da nau'in fihirisa ɗaya yana da nau'ikan shafuka daban-daban. Misali, bishiyar B-itace tana da shafin metadata da shafukan “na yau da kullun”.
Koyaya, shafin yawanci yana da jeri na nuni zuwa layuka da layuka da kansu (kamar shafin tebur). Bugu da ƙari, a ƙarshen shafin akwai sarari don bayanai na musamman.
Layukan da ke cikin fihirisa kuma na iya samun tsari daban-daban dangane da nau'in fihirisar. Misali, don itacen B, layuka masu alaƙa da shafukan ganye suna ɗauke da ƙimar maɓalli mai ƙididdigewa da maƙasudi (ctid) zuwa jeren tebur madaidaici. Gabaɗaya, ana iya tsara fihirisar ta wata hanya dabam dabam.
Mafi mahimmancin batu shine cewa babu juzu'in layi a cikin fihirisar kowane nau'i. To, ko kuma za mu iya ɗauka cewa kowane layi yana wakilta da siga ɗaya daidai. A wasu kalmomi, babu filayen xmin da xmax a cikin jigon layi na index. Zamu iya ɗauka cewa hanyoyin haɗin kai daga index suna kaiwa ga duk nau'ikan tebur na layuka - don haka zaku iya gano wane nau'in ciniki zai gani kawai ta kallon tebur. (Kamar yadda aka saba, wannan ba shine gaskiyar gaba ɗaya ba. A wasu lokuta, taswirar ganuwa na iya inganta tsarin, amma zamu duba wannan dalla-dalla daga baya.)
A lokaci guda, a cikin shafin fihirisa muna samun masu nuni ga nau'ikan biyun, na yanzu da na tsohuwar:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Ma'amaloli na zahiri
A aikace, PostgreSQL yana amfani da haɓakawa waɗanda ke ba shi damar "ajiye" lambobin ma'amala.
Idan ma'amala tana karanta bayanai kawai, ba ta da wani tasiri akan ganuwa nau'ikan jere. Don haka, tsarin sabis yana fara fitar da xid kama-da-wane zuwa ma'amala. Lambar ta ƙunshi ID ɗin tsari da lambar jeri.
Bayar da wannan lambar baya buƙatar aiki tare tsakanin duk matakai don haka yana da sauri sosai. Za mu saba da wani dalili na yin amfani da lambobin kama-da-wane lokacin da muke magana game da daskarewa.
Ba a yin la'akari da lambobi masu kama da juna ta kowace hanya a cikin hotunan bayanan.
A wurare daban-daban a cikin lokaci, ƙila za a iya samun ma'amala ta zahiri a cikin tsarin tare da lambobi waɗanda aka riga aka yi amfani da su, kuma wannan al'ada ce. Amma ba za a iya rubuta irin wannan lambar a cikin shafukan bayanai ba, saboda lokacin da aka shiga shafin na gaba zai iya rasa ma'ana.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Idan ma'amala ta fara canza bayanai, ana ba ta ainihin, lambar ciniki ta musamman.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Ma'amaloli na Gida
Ajiye maki
An bayyana a cikin SQL ajiye maki (savepoint), wanda ke ba ka damar soke wani ɓangare na ma'amala ba tare da katse shi gaba ɗaya ba. Amma wannan bai dace da zanen da ke sama ba, tunda ciniki yana da matsayi iri ɗaya don duk canje-canjensa, kuma a zahiri ba a juyar da bayanai ba.
Don aiwatar da wannan aikin, an raba ma'amala tare da wurin ajiyewa zuwa daban daban ma'amaloli masu zaman kansu (subtransaction), matsayin wanda za a iya sarrafa daban.
Ma'amaloli na gida suna da lambar kansu (mafi girma fiye da adadin babban ma'amala). Matsayin ma'amaloli na gida an rubuta su ta hanyar da aka saba a cikin XACT, amma matsayi na ƙarshe ya dogara da matsayin babban ma'amala: idan an soke shi, to, duk ma'amaloli na gida kuma an soke su.
Ana adana bayanai game da tallan ciniki a cikin fayiloli a cikin PGDATA/pg_subtrans directory. Ana samun dama ga fayiloli ta hanyar buffers a cikin misali mai raba ƙwaƙwalwar ajiya, wanda aka tsara ta hanya ɗaya da masu buffer XACT.
Kada ku rikita ma'amaloli masu zaman kansu tare da ma'amaloli masu cin gashin kansu. Ma'amaloli masu cin gashin kansu ba su dogara ga juna ta kowace hanya ba, amma ma'amaloli masu zaman kansu suna yi. Babu ma'amaloli masu zaman kansu a cikin PostgreSQL na yau da kullun, kuma, watakila, don mafi kyau: ana buƙatar su sosai, da wuya sosai, kuma kasancewarsu a cikin sauran DBMSs yana haifar da zagi, wanda kowa ke shan wahala.
Bari mu share tebur, fara ciniki kuma mu saka jere:
=> 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)
Yanzu bari mu sanya wurin ajiyewa mu saka wani layi.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Lura cewa aikin txid_current() yana dawo da babban lambar ciniki, ba lambar ciniki ba.
=> 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)
Mu koma wurin ajiyewa mu saka layi na uku.
=> 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)
A cikin shafin muna ci gaba da ganin layin da aka ƙara ta hanyar cinikin da aka soke.
Muna gyara canje-canje.
=> 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)
Yanzu za ku iya ganin cewa kowace ma'amala ta gida tana da matsayinta.
Yi la'akari da cewa ba za a iya amfani da ma'amaloli a sarari a cikin SQL ba, wato, ba za ku iya fara sabon ciniki ba tare da kammala na yanzu ba. Ana kunna wannan tsarin a fakaice lokacin amfani da wuraren ajiya, da kuma lokacin sarrafa keɓancewar PL/pgSQL kuma a cikin wasu adadin wasu lokuta masu ban mamaki.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Kurakurai da atomity na ayyuka
Me zai faru idan kuskure ya faru yayin yin aiki? Misali, kamar haka:
=> 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
An sami kuskure. Yanzu ana ganin cinikin ya ƙare kuma ba a yarda da aiki a ciki ba:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Kuma ko da kuna ƙoƙarin yin canje-canje, PostgreSQL zai ba da rahoton zubar da ciki:
=> COMMIT;
ROLLBACK
Me yasa ba za a iya ci gaba da ciniki ba bayan rashin nasara? Gaskiyar ita ce, kuskure na iya tasowa ta hanyar da za mu sami damar yin amfani da wani ɓangare na canje-canje - atomity na ba ma ma'amala ba, amma za a keta ma'aikacin. Kamar a cikin misalinmu, inda mai aiki ya sami damar sabunta layi ɗaya kafin kuskuren:
=> 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)
Dole ne a ce psql yana da yanayin da har yanzu yana ba da damar ciniki ya ci gaba bayan gazawar kamar an mayar da ayyukan ma'aikacin kuskure.
=> 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;
Ba shi da wahala a yi tsammani cewa a cikin wannan yanayin, psql a zahiri yana sanya madaidaicin wurin ajiyewa a gaban kowane umarni, kuma idan gazawar ta fara juyawa zuwa gare shi. Ba a yi amfani da wannan yanayin ta tsohuwa ba, tunda saita wuraren adanawa (ko da ba tare da mirginawa gare su ba) ya ƙunshi babban sama.
source: www.habr.com