Chifukwa chake, takambirana zovuta zokhudzana ndi izi , ndipo anabwerera . Ndipo potsiriza tinafika ku gawo losangalatsa kwambiri - mitundu ya zingwe.
Mutu
Monga tanenera kale, mzere uliwonse ukhoza kukhalapo nthawi imodzi m'matembenuzidwe angapo mu database. Pachifukwa ichi, Baibulo lirilonse liri ndi zizindikiro ziwiri zomwe zimatsimikizira "nthawi" ya zochitika za Baibuloli (xmin ndi xmax). M'mawu - chifukwa si nthawi yomwe imagwiritsidwa ntchito, koma yowonjezera mwapadera. Ndipo kauntala iyi ndi nambala yamalonda.
(Monga mwachizolowezi, zenizeni ndizovuta kwambiri: chiwerengero cha malonda sichingachuluke nthawi zonse chifukwa cha kuchepa kwa kauntala. Koma tiwona izi mwatsatanetsatane tikafika kuzizira.)
Mzere ukapangidwa, xmin imayikidwa ku nambala yamalonda yomwe idapereka lamulo la INSERT, ndipo xmax imasiyidwa yopanda kanthu.
Mzere ukachotsedwa, mtengo wa xmax wa mtundu wapano umadziwika ndi kuchuluka kwa zomwe zidachitika DELETE.
Mzere ukasinthidwa ndi lamulo la UPDATE, ntchito ziwiri zimachitikadi: DELETE ndi INSERT. Mzere wapano wam'mizere umayika xmax yofanana ndi kuchuluka kwa ndalama zomwe zidachitika UPDATE. Mtundu watsopano wa chingwe womwewo umapangidwa; mtengo wake wa xmin umagwirizana ndi mtengo wa xmax wa mtundu wakale.
Magawo a xmin ndi xmax akuphatikizidwa pamutu wamtundu wa mzere. Kuphatikiza pa magawo awa, mutuwo uli ndi zina, mwachitsanzo:
- infomask ndi ma bits angapo omwe amafotokoza za mtundu uwu. Pali zambiri za izo; Pang'onopang'ono tidzalingalira zazikuluzo.
- ctid ndi ulalo wotsatira, mtundu watsopano wa mzere womwewo. Kwa mtundu waposachedwa kwambiri wa mzere, cid imanena za mtundu uwu womwe. Nambala ili ndi mawonekedwe (x,y), pomwe x ndi nambala yatsamba, y ndi nambala yolozera pamndandanda.
- null bitmap - Imayika zipilalazo za mtundu womwe wapatsidwa womwe uli ndi mtengo wopanda pake (NULL). NULL si imodzi mwazinthu zodziwika bwino zamtundu wa data, chifukwa chake chidziwitsocho chiyenera kusungidwa padera.
Zotsatira zake, mutuwo ndi waukulu kwambiri - osachepera 23 mabayiti pamtundu uliwonse wa mzere, ndipo nthawi zambiri chifukwa cha NULL bitmap. Ngati tebulo ndi "lopapatiza" (ndiko kuti, lili ndi mizati yochepa), mutuwo ukhoza kutenga zambiri kuposa zothandiza.
kumanga
Tiyeni tiwone mwatsatanetsatane momwe zingwe zochepetsera zimagwirira ntchito, kuyambira ndikuyika.
Poyesera, tiyeni tipange tebulo latsopano lokhala ndi magawo awiri ndi index pa imodzi mwazo:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Tiyeni tiyike mzere umodzi tikayambitsa malonda.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nayi nambala yathu yamalonda:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Tiyeni tiwone zomwe zili patsambali. Ntchito ya heap_page_items ya pageinspect extension imakupatsani mwayi wodziwa zambiri za zolozera ndi mizere:
=> 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
Dziwani kuti mawu akuti mulu mu PostgreSQL amatanthauza matebulo. Uku ndi kugwiritsiridwa ntchito kwina kwachilendo kwa mawuwa - mulu umadziwika , yomwe ilibe kanthu kofanana ndi tebulo. Pano liwulo lagwiritsiridwa ntchito mβlingaliro la βchilichonse chitaikidwa pamodzi,β mosiyana ndi ma index oikidwa.
Ntchitoyi ikuwonetsa deta "monga momwe iliri", mumtundu womwe ndi wovuta kuumvetsa. Kuti timvetsetse, tingosiya gawo limodzi lazambirizo ndikuzimasulira:
=> 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)
Nazi zomwe tinachita:
- Onjezani ziro ku nambala yolozera kuti iwoneke ngati t_ctid: (nambala yatsamba, nambala yolozera).
- Kumvetsetsa momwe cholozera cha lp_flags. Apa ndi "zabwinobwino" - izi zikutanthauza kuti cholozera chikutanthauza mtundu wa chingwe. Tiona matanthauzo ena pambuyo pake.
- Pazidziwitso zonse, awiri okha ndi awiri omwe adziwika mpaka pano. Ma bits a xmin_committed ndi xmin_aborted akuwonetsa ngati transaction number xmin yachitika (yachotsedwa). Ma bits awiri ofanana amatanthauza nambala ya transaction xmax.
Kodi tikuwona chiyani? Mukayika mzere, nambala yolozera 1 idzawonekera patsamba la tebulo, ndikulozera ku mtundu woyamba komanso wokhawo wa mzerewo.
Mu mtundu wa zingwe, gawo la xmin limadzazidwa ndi nambala yamalonda yomwe ilipo. Ntchitoyi ikugwirabe ntchito, kotero ma bits onse a xmin_committed ndi xmin_aborted sanakhazikitsidwe.
Mzere wa mtundu wa ctid umatanthauza mzere womwewo. Izi zikutanthauza kuti kulibe mtundu watsopano.
Munda wa xmax wadzazidwa ndi dummy number 0 chifukwa mtundu uwu wa mzere sunachotsedwe ndipo ulipo. Zogulitsa sizilabadira nambalayi chifukwa xmax_aborted bit yakhazikitsidwa.
Tiyeni titengepo gawo limodzi kuti tiwongolere kuwerenga powonjezera zidziwitso pa manambala amalonda. Ndipo tiyeni tipange ntchito, chifukwa tidzafunika pempho kangapo:
=> 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;
Mu mawonekedwe awa, zikuwonekeratu bwino zomwe zikuchitika pamutu wa mzerewu:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Zofananira, koma zocheperako, zambiri zitha kupezeka patebulo lomwelo, pogwiritsa ntchito pseudo-columns xmin ndi xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Kukonza
Ngati ntchitoyo yatha bwino, muyenera kukumbukira momwe ilili - dziwani kuti idaperekedwa. Kuti tichite izi, dongosolo lotchedwa XACT limagwiritsidwa ntchito (ndipo mtundu 10 usanachitike linkatchedwa CLOG (lolemba lantchito) ndipo dzinali likhoza kupezekabe m'malo osiyanasiyana).
XACT si tebulo lamakasitomala; awa ndi mafayilo omwe ali mu PGDATA/pg_xact directory. Ali ndi magawo awiri pakuchita kulikonse: odzipereka ndi kuthetsedwa - monga momwe ziliri pamutu wa mzere. Izi zagawidwa m'mafayilo angapo kuti zitheke; tibwereranso ku nkhaniyi tikaganizira zozizira. Ndipo kugwira ntchito ndi mafayilowa kumachitika tsamba ndi tsamba, monganso ena onse.
Chifukwa chake, kugulitsa kukachitika ku XACT, gawo lodzipereka limakhazikitsidwa pakugulitsa uku. Ndipo izi ndi zonse zomwe zimachitika pochita (ngakhale sitikunena za chipika chojambulira kale).
Ntchito ina ikafika patsamba lomwe tangoyang'ana, iyenera kuyankha mafunso angapo.
- Kodi ntchito ya xmin yatha? Ngati sichoncho, ndiye kuti mtundu wopangidwa wa chingwe suyenera kuwoneka.
Chekecho chimapangidwa poyang'ana mawonekedwe ena, omwe ali mu kukumbukira komwe adagawana nawo ndipo amatchedwa ProcArray. Lili ndi mndandanda wazinthu zonse zomwe zikugwira ntchito, ndipo pamtundu uliwonse chiwerengero cha zochitika zake zamakono (zogwira) zikuwonetsedwa. - Ngati anamaliza, ndiye bwanji - mwa kuchita kapena kuletsa? Ngati yathetsedwa, ndiye kuti mizereyo siyenera kuwonekanso.
Izi ndi zomwe XACT ndi yake. Koma, ngakhale masamba omaliza a XACT amasungidwa mu buffers mu RAM, ndizokwera mtengo kuyang'ana XACT nthawi iliyonse. Chifukwa chake, zikatsimikizidwa momwe ndalama zagwiritsidwira ntchito, zimalembedwa ku xmin_committed ndi xmin_aborted bits za mtundu wa chingwe. Ngati imodzi mwazinthuzi yakhazikitsidwa, ndiye kuti momwe xmin ikugwiritsidwira ntchito imadziwika ndipo zochitika zina sizidzafunika kupeza XACT.
Chifukwa chiyani ma bits awa sakukhazikitsidwa ndi transaction yokhayo yomwe ikulowetsamo? Kuyikapo kukachitika, malondawo sakudziwa ngati apambana. Ndipo panthawi yochita, sizikudziwikanso kuti ndi mizere iti yomwe masamba adasinthidwa. Pakhoza kukhala masamba ambiri oterowo, ndipo kuwaloweza nβkopanda phindu. Kuphatikiza apo, masamba ena amatha kuthamangitsidwa kuchokera ku buffer cache kupita ku disk; kuwawerenganso kuti asinthe ma bits angachedwetse kudzipereka kwambiri.
Choyipa cha ndalamazo ndikuti pambuyo pakusintha, kugulitsa kulikonse (ngakhale kuwerengera kosavuta - KUSANKHA) kumatha kusintha masamba a data mu cache ya buffer.
Kotero, tiyeni tikonze kusintha.
=> COMMIT;
Palibe chomwe chasintha patsamba (koma tikudziwa kuti zomwe zikuchitika zidalembedwa kale mu XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Tsopano ntchito yomwe imalowa patsambalo ikuyenera kudziwa momwe xmin ikugwirira ntchito ndikuzilembera kuzidziwitso:
=> 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)
Chotsani
Mzere ukachotsedwa, chiwerengero cha zomwe zachotsedwa tsopano zimalembedwa ku gawo la xmax la mtundu wamakono, ndipo xmax_aborted bit imachotsedwa.
Zindikirani kuti mtengo wa xmax womwe umagwirizana ndi zomwe zikuchitika umakhala ngati loko ya mizere. Ngati ntchito ina ikufuna kusintha kapena kuchotsa mzerewu, tidzakakamizika kudikirira kuti xmax ithe. Tidzakambirana zambiri zoletsa pambuyo pake. Pakalipano, tikungowona kuti chiwerengero cha mizere ya mizere chilibe malire. Satenga malo mu RAM ndipo magwiridwe antchito samavutika ndi nambala yawo. Zowona, kuchita "kwanthawi yayitali" kumakhala ndi zovuta zina, koma zambiri pambuyo pake.
Tiyeni tifufute mzere.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Tikuwona kuti nambala yobwereketsa yalembedwa m'munda wa xmax, koma zidziwitsozo sizinakhazikitsidwe:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Kulipira
Kusintha kochotsa kumagwiranso ntchito mofanana ndi kuchita, kokha mu XACT zomwe zidachotsedwa ndizokhazikitsidwa pazochitazo. Kuchotsa ndikofulumira ngati kudzipereka. Ngakhale lamuloli limatchedwa ROLLBACK, zosintha sizinabwezedwe m'mbuyo: chilichonse chomwe ntchitoyo idakwanitsa kusintha pamasamba a data sichisintha.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Tsambalo likafika, mawonekedwe ake adzawunikidwa ndipo xmax_aborted hint bit idzakhazikitsidwa pamzere wa mzere. Nambala ya xmax yokha imakhalabe patsamba, koma palibe amene angayang'ane.
=> 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)
Sintha
Kusinthaku kumagwira ntchito ngati kuti idachotsa kaye mtundu wamakono wa mzere ndikuyika ina.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Funso limapanga mzere umodzi (mtundu watsopano):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Koma patsamba tikuwona mitundu yonse iwiri:
=> 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)
Mtundu womwe wachotsedwa umayikidwa ndi nambala yomwe yachitika mugawo la xmax. Kuphatikiza apo, mtengowu umalembedwa pazakale, popeza zomwe zidachitika kale zidathetsedwa. Ndipo xmax_aborted pang'ono imachotsedwa chifukwa momwe zinthu ziliri pano sizikudziwika.
Mtundu woyamba wa mzerewu tsopano umatchula wachiwiri (t_ctid field) ngati watsopano.
Mlozera wachiwiri umapezeka patsamba lolozera ndipo mzere wachiwiri ukuwonetsa mtundu wachiwiri patsamba la tebulo.
Mofanana ndi kuchotsa, mtengo wa xmax mumtundu woyamba wa mzerewu ndi chizindikiro chakuti mzere watsekedwa.
Chabwino, tiyeni titsirize kugulitsa.
=> COMMIT;
Zisonyezero
Mpaka pano tangokamba za masamba a tebulo. Kodi chimachitika ndi chiyani mu index?
Zomwe zili m'masamba a index zimasiyana kwambiri malinga ndi mtundu wa index. Ndipo ngakhale mtundu umodzi wa index uli ndi mitundu yosiyanasiyana yamasamba. Mwachitsanzo, mtengo wa B uli ndi tsamba la metadata ndi masamba "okhazikika".
Komabe, tsambalo nthawi zambiri limakhala ndi zolozera pamizere ndi mizere yokha (monga tsamba la tebulo). Kuonjezera apo, kumapeto kwa tsamba pali malo a deta yapadera.
Mizere muzolozera imathanso kukhala ndi mawonekedwe osiyanasiyana kutengera mtundu wa index. Mwachitsanzo, pamtengo wa B, mizere yokhudzana ndi masamba imakhala ndi mtengo wolozera komanso zolozera (ctid) ku mzere wa tebulo. Nthawi zambiri, index imatha kupangidwa mwanjira yosiyana kwambiri.
Mfundo yofunika kwambiri ndi yakuti palibe mizere ya mizere yamtundu uliwonse. Chabwino, kapena titha kuganiza kuti mzere uliwonse ukuimiridwa ndi mtundu umodzi ndendende. Mwanjira ina, palibe magawo a xmin ndi xmax pamutu wa mzere wa index. Titha kuganiza kuti maulalo kuchokera pamndandanda amatsogolera kumitundu yonse yamizere - kotero mutha kudziwa kuti ndi mtundu wanji womwe mungawone pongoyang'ana patebulo. (Monga nthawi zonse, ichi sichowonadi chonse. Nthawi zina, mapu owonekera amatha kuwongolera bwino ntchitoyi, koma tiwona izi mwatsatanetsatane pambuyo pake.)
Nthawi yomweyo, patsamba lolozera timapeza zolozera kumitundu yonse iwiri, yapano ndi yakale:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Zochitika zenizeni
M'malo mwake, PostgreSQL imagwiritsa ntchito kukhathamiritsa komwe kumalola kuti "isunge" manambala obwera.
Ngati ntchito ikungowerenga deta, ilibe mphamvu pakuwoneka kwa mizere. Chifukwa chake, njira yautumiki imayamba kutulutsa pafupifupi xid kumalondawo. Nambalayi imakhala ndi ID ya ndondomeko ndi nambala yotsatizana.
Kupereka nambala iyi sikufuna kulunzanitsa pakati pa njira zonse motero ndikothamanga kwambiri. Tidzadziwa chifukwa china chogwiritsira ntchito manambala enieni tikakamba za kuzizira.
Nambala zenizeni sizimaganiziridwa mwanjira iliyonse muzithunzi za data.
Pamalo osiyanasiyana pakapita nthawi, pakhoza kukhala zochitika zenizeni m'dongosolo ndi manambala omwe agwiritsidwa kale ntchito, ndipo izi ndizabwinobwino. Koma chiwerengero choterocho sichikhoza kulembedwa m'masamba a deta, chifukwa nthawi yotsatira tsambalo likhoza kutaya tanthauzo lonse.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Ngati malonda ayamba kusintha deta, amapatsidwa nambala yeniyeni, yapadera.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Zochita Zokhazikika
Sungani mfundo
Kufotokozedwa mu SQL sungani mfundo (savepoint), zomwe zimakupatsani mwayi woletsa gawo linalake popanda kusokoneza kwathunthu. Koma izi sizikugwirizana ndi chithunzi pamwambapa, popeza kugulitsako kuli ndi mawonekedwe ofanana pazosintha zake zonse, ndipo palibe deta yomwe imabwezeretsedwanso.
Kuti mugwiritse ntchito izi, ntchito yokhala ndi posungira imagawidwa m'magulu angapo malonda opangidwa (subtransaction), momwe angayendetsedwe padera.
Zogulitsa zomwe zasungidwa zimakhala ndi nambala yawoyawo (yokwera kuposa kuchuluka kwa ndalama zazikulu). Mkhalidwe wa zochitika zomwe zasungidwa zimalembedwa mwanjira yanthawi zonse mu XACT, koma mawonekedwe omaliza amatengera momwe ntchitoyo ilili: ngati yathetsedwa, ndiye kuti zonse zomwe zasungidwa zimathetsedwa.
Zambiri zokhuza makonda zimasungidwa m'mafayilo mu PGDATA/pg_subtrans directory. Mafayilo amafikiridwa kudzera muzosungira mumakumbukiro omwe adagawana nawo, okonzedwa mofanana ndi ma buffers a XACT.
Osasokoneza mabizinesi okhazikika ndi zochitika zodziyimira pawokha. Zochita zodziyimira pawokha sizidalira wina ndi mnzake mwanjira ina iliyonse, koma zochitika zokhazikika zimatero. Palibe zochitika zodziyimira pawokha mu PostgreSQL nthawi zonse, ndipo, mwina, zabwino kwambiri: zimafunikira kwambiri, kawirikawiri, ndipo kupezeka kwawo mu ma DBMS ena kumayambitsa nkhanza, zomwe aliyense amavutika nazo.
Tiyeni tifufuze tebulo, tiyambe kuchitapo kanthu ndikuyika mzerewu:
=> 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)
Tsopano tiyeni tiyike posungira ndikuyika mzere wina.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Zindikirani kuti txid_current() ntchito imabweretsanso nambala yayikulu yogulitsira, osati nambala yamalonda yomwe yasungidwa.
=> 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)
Tiyeni tibwerere ku malo osungira ndikuyika mzere wachitatu.
=> 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)
Patsambali tikupitiliza kuwona mzere womwe wawonjezeredwa ndi zomwe zalepheretsedwa.
Timakonza zosintha.
=> 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)
Tsopano mutha kuwona bwino lomwe kuti malonda aliwonse omwe amakhalapo ali ndi mawonekedwe ake.
Zindikirani kuti zochitika zomwe zasungidwa sizingagwiritsidwe ntchito momveka bwino mu SQL, ndiko kuti, simungathe kuyambitsa ntchito yatsopano popanda kumaliza yomwe ilipo. Dongosololi limayendetsedwa mosamalitsa mukamagwiritsa ntchito zosungira, komanso pogwira PL/pgSQL kupatulapo ndi zina zingapo, zachilendo kwambiri.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Zolakwika ndi ma atomiki a ntchito
Kodi chimachitika ndi chiyani ngati cholakwika chichitika pochita opareshoni? Mwachitsanzo, monga chonchi:
=> 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
Zalakwika. Tsopano kugulitsako kumawonedwa kuti kwatha ndipo palibe ntchito zomwe zimaloledwa mmenemo:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Ndipo ngakhale mutayesa kusintha, PostgreSQL idzanena za kuchotsa mimba:
=> COMMIT;
ROLLBACK
Chifukwa chiyani malonda sangapitirire atalephera? Chowonadi ndi chakuti cholakwika chikhoza kubwera mwanjira yoti titha kupeza gawo lazosintha - ma atomiki osachitapo kanthu, koma wogwiritsa ntchitoyo angaphwanyidwe. Monga chitsanzo chathu, pomwe wogwiritsa ntchito adatha kusintha mzere umodzi cholakwika chisanachitike:
=> 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)
Ziyenera kunenedwa kuti psql ili ndi mawonekedwe omwe amalolabe kuti ntchitoyo ipitirire pambuyo polephera ngati kuti zochita za wolakwika zidabwezedwa.
=> 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;
Sizovuta kuganiza kuti mwanjira iyi, psql imayika malo osungira pamaso pa lamulo lililonse, ndipo ngati kulephera kumayambitsa kubwezeretsanso. Izi sizimagwiritsidwa ntchito mwachisawawa, chifukwa kukhazikitsa malo osungira (ngakhale osabwereranso kwa iwo) kumafuna kupitilira apo.
Source: www.habr.com