Markaa, waxaanu tixgelinay arrimo la xidhiidha , oo dib u gurasho sameeyay . Ugu dambeyntiina waxaan gaadhnay qaybta ugu xiisaha badan - noocyada xargaha.
Madaxa
Sidaan horey u sheegnay, saf kastaa wuxuu isku mar ka jiri karaa noocyo badan oo kaydka ah. Mid ka mid ah nooca waa in si uun looga soocaa mid kale: Ujeedadaas awgeed, nuqul kastaa wuxuu leeyahay laba calaamadood oo go'aaminaya "waqtiga" ficilka noocaan (xmin iyo xmax). In xigashooyin - sababtoo ah ma aha waqti sida in la isticmaalo, laakiin counter gaar ah sii kordhaya. Oo counter-kani waa lambarka wax kala iibsiga.
(Sida caadiga ah, xaqiiqadu way ka sii adag tahay: lambarka macaamilku ma kordhin karo mar walba sababtoo ah awoodda xaddidan ee miiska. Laakiin waxaan si faahfaahsan u eegi doonaa faahfaahintan marka aan helno qaboojinta.)
Marka saf la sameeyo, xmin waxaa lagu dhejiyaa lambarka wax kala iibsiga ee soo saaray amarka INSERT, xmaxna waa bannaan yahay.
Marka safka la tirtiro, qiimaha xmax ee nooca hadda jira waxaa lagu calaamadiyaa tirada macaamilka ee sameeyay DELETE.
Marka saf lagu hagaajiyo amarka UPDATE, laba hawlgal ayaa dhab ahaantii la sameeyaa: Tirtir iyo Geli. Nooca hadda ee safku wuxuu dhigayaa xmax la mid ah tirada wax kala iibsiga ee sameeyay UPDATE. Nooc cusub oo isla xadhig ah ayaa markaa la abuurayaa; qiimihiisu xmin waxa uu ku beegan yahay xmax qiimaha noocii hore.
Goobaha xmin iyo xmax waxay ku jiraan madaxa nooca safka ah. Marka laga soo tago meelahan, madaxa ayaa ka kooban kuwa kale, tusaale ahaan:
- infomask waa taxane taxane ah oo qeexaya sifooyinka noocaan. Aad bay u badan yihiin; Waxaan si tartiib tartiib ah u tixgelin doonaa kuwa ugu muhiimsan.
- ctid waa isku xirka kan xiga, nooca cusub ee isla xariiqda. Kan ugu cusub, nooca ugu badan ee xadhigga, ctid waxa loola jeedaa noocan laftiisa. Nambarku wuxuu leeyahay foomka (x,y), halkaasoo x uu yahay lambarka bogga, y waa lambarka tusaha ee shaxanka.
- null bitmap - Waxay calaamadisaa tiirarka nooca la bixiyay ee ka kooban qiimo aan jirin (NULL). NULL maaha mid ka mid ah qiyamka nooca xogta ee caadiga ah, markaa sifada waa in si gaar ah loo kaydiyaa.
Natiijo ahaan, madaxa ayaa aad u weyn - ugu yaraan 23 bytes nooc kasta oo khadka ah, iyo badiyaa in ka badan sababtoo ah NULL bitmapka. Haddii miisku "cidhiidhi yahay" (taas oo ah, ka kooban dhowr tiirar), dusha sare waxa laga yaabaa inay qaadato wax ka badan macluumaadka waxtarka leh.
gelin
Aan si dhow u eegno sida hawlgallada xargaha hoose loo fuliyo, laga bilaabo gelinta.
Tijaabooyinka, aynu abuurno miis cusub oo leh laba tiir iyo tusmayn mid ka mid ah:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Aan gelino hal saf ka dib marka aan bilowno wax kala iibsiga.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Waa kan lambarkayaga wax kala iibsiga ee hadda:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Aynu eegno waxa ku jira bogga. Shaqada heap_page_items ee kordhinta hubinta bogga waxay kuu ogolaanaysaa inaad hesho macluumaadka ku saabsan tilmaamayaasha iyo noocyada safka:
=> 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
Ogsoonow in ereyga tuulan ee PostgreSQL uu tixraacayo miisaska. Tani waa adeegsi kale oo yaab leh oo ereyga ah - tuulmo waa la yaqaan , taas oo aan waxba la wadaagin miiska. Halkan ereyga waxaa loo adeegsaday macnaha "wax walba waa la isku tuuray," oo ka soo horjeeda tusmooyinka la amray.
Shaqadu waxay muujinaysaa xogta "sida ay tahay", qaab ay adag tahay in la fahmo. Si aan u ogaano, waxaan ka tagi doonaa qayb ka mid ah macluumaadka oo aan kala saari doono:
=> 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)
Waa tan waxaan sameynay:
- Waxaa lagu daray eber lambarka tusaha si uu ugu ekaado t_ctid: (lambarka bogga, lambarka tusmada).
- La go'aamiyay xaaladda tilmaame lp_flags. Halkan waa "caadi" - tani waxay ka dhigan tahay in tilmaamuhu dhab ahaantii tixraacayo nooca xadhigga. Macnayaal kale ayaan gadaal ka eegi doonaa.
- Dhammaan qaybaha macluumaadka, laba lammaane oo keliya ayaa ilaa hadda la aqoonsaday. Xmin_committed iyo xmin_aborted bits waxay muujinayaan in nambarka wax kala iibsiga xmin la sameeyay (la soo riday). Laba xabbadood oo isku mid ah waxay tixraacaan lambarka macaamil ganacsi xmax.
Maxaan aragnaa? Markaad geliso saf, nambarka tusaha 1 ayaa ka soo bixi doona bogga miiska, isagoo tilmaamaya nooca koowaad iyo kan keliya ee safka.
Nooca xargaha, goobta xmin waxaa ka buuxa lambarka wax kala iibsiga ee hadda jira. Wax kala iibsiga weli waa firfircoon yahay, marka labada xmin_committed iyo xmin_aborted bits lama dejin.
Qaybta safka ctid waxay tilmaamaysaa isla safkii. Tani waxay ka dhigan tahay in nooc cusub aanu jirin.
Goobta xmax waxaa ka buuxsamay nambar yar oo 0 ah sababtoo ah nooca safka ah lama tirtirin oo waa hadda. Macaamilyadu ma sii fiirsan doonaan lambarkan sababtoo ah xmax_aborted yara waa la dejiyay.
Aan qaadno hal tillaabo oo kale xagga hagaajinta wax-akhriska annagoo ku daraya xoogaa macluumaad lambarrada wax kala iibsiga. Oo aynu abuurno hawl, maadaama aan u baahan doono codsiga in ka badan hal mar:
=> 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;
Qaabkan, aad ayay uga caddahay waxa ka socda madaxa nooca safka:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Si la mid ah, laakiin aad u faahfaahsan, macluumaadka waxaa laga heli karaa miiska laftiisa, iyadoo la adeegsanayo tiirarka been abuurka ah ee xmin iyo xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Diyaarinta
Haddii macaamil ganacsi si guul leh loo dhammeeyo, waxaad u baahan tahay inaad xasuusato xaaladdeeda - ogow in ay ka go'an tahay. Si taas loo sameeyo, qaab dhismeed loo yaqaan XACT ayaa la isticmaalaa (oo ka hor nooca 10 waxaa la odhan jiray CLOG (commit log) magacan wali meelo kala duwan ayaa laga heli karaa).
XACT ma aha shaxda buug-yaraha; kuwani waa faylasha ku jira hagaha PGDATA/pg_xact. Waxay leeyihiin laba xabbadood oo kala iibsi kasta: go'an iyo la soo riday - sida ku jira madaxa qaybta nooca safka ah. Macluumaadkan waxa loo qaybiyaa dhawr fayl oo keliya si ay ugu habboonaato; waxaanu ku noqon doonaa arrintan marka aan tixgelinno qaboojinta. Oo la shaqeynta faylashaas waxaa lagu fuliyaa bog-bog, sida kuwa kale oo dhan.
Marka, marka macaamil ganacsi lagu sameeyo XACT, xoogaa go'an ayaa loo dejiyay macaamilkan. Oo tanuna waa waxa dhacaya inta lagu jiro samaynta (inkasta oo aynaan weli ka hadlin diiwaanka hore ee duubista).
Marka macaamil kale uu galo bogga miiska ee aan hadda eegnay, waxay ku qasbanaan doontaa inay ka jawaabto dhowr su'aalood.
- Ma dhammaatay wax kala iibsiga xmin? Haddaysan ahayn, markaa nooca la abuuray ee xadhiggu waa inaanu muuqan.
Hubintan waxa lagu sameeyaa iyadoo la eegayo qaab dhismeed kale, kaas oo ku yaal xusuusta la wadaago ee tusaale ahaan waxaana loo yaqaan ProcArray. Waxay ka kooban tahay liiska dhammaan hababka firfircoon, mid kastana tirada macaamilka hadda (firfircoon) ayaa la tilmaamay. - Haddii la dhammaystiro, markaa sidee - adoo samaynaya ama burinaya? Haddii la tirtiro, markaas nooca safka waa inuusan sidoo kale muuqan.
Tani waa dhab ahaan waxa XACT loogu talagalay. Laakiin, inkasta oo boggaga ugu dambeeya ee XACT lagu kaydiyo kaydka RAM, haddana waa qaali in la hubiyo XACT mar kasta. Sidaa darteed, marka heerka macaamilka la go'aamiyo, waxaa loo qoraa xmin_committed iyo xmin_aborted ee nooca xargaha. Haddii mid ka mid ah jajabyadaas la dejiyo, markaa xaaladda macaamil ganacsi ee xmin waxaa loo tixgeliyaa in la yaqaan oo macaamilka soo socdaa ma heli doono XACT.
Waa maxay sababta aan loo dejin karin wax kala beddelashada lafteeda? Marka galinta dhacdo, wax kala iibsigu weli ma garanayo inuu guulaysan doono iyo in kale. Haddana ballan-qaadka, ma cadda khadadka boggaga la beddelay. Waxaa jiri kara bogag badan oo noocaas ah, xafididdoodana faa'iido ma leh. Intaa waxaa dheer, boggaga qaar ayaa laga saari karaa kaydka kaydka ilaa diskka; akhrinta mar labaad si aad u bedesho xoogaa yar ayaa si weyn hoos u dhigi doonta ballan-qaadka.
Dhinaca hoose ee kaydinta ayaa ah in isbeddelada ka dib, wax kala iibsi kasta (xitaa mid samaynaysa akhrin fudud - SELECT) wuxuu bilaabi karaa inuu beddelo boggaga xogta ee kaydka kaydka.
Haddaba, aynu hagaajino isbeddelka.
=> COMMIT;
Waxbo iskamay beddelin bogga (laakiin waxaan ognahay in heerka wax kala iibsiga uu horayba ugu diiwaan gashan yahay XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Hadda macaamilka marka hore bogga gala waa inuu go'aamiyaa heerka macaamil ganacsi ee xmin oo u qoraa macluumaadka macluumaadka:
=> 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)
Tirtir
Marka safka la tirtiro, nambarka wax kala iibsiga hadda la tirtirayo waxaa loo qoraa xmax qaybta hadda jirta, xmax_aborted bit waa la nadiifiyaa.
Ogsoonow in qiimaha go'an ee xmax ee u dhigma macaamil ganacsi oo firfircoon uu u dhaqmo sida safafka. Haddii macaamil kale uu rabo inuu cusboonaysiiyo ama tirtiro safkan, waxa lagu qasbi doonaa in la sugo xmax si uu u dhamaystiro. Wax badan oo ku saabsan xannibaadda dib ayaan uga hadli doonaa. Hadda, waxaan ogaanay in tirada safafka aan xadidneyn. Meel bannaan kuma qaataan RAM-ka iyo waxqabadka nidaamku kuma dhibtoonayo tiradooda. Run, macaamilka "dheer" waxay leeyihiin faa'iidooyin kale, laakiin wax badan oo ku saabsan taas ka dib.
Aynu tirtirno khadka.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Waxaan aragnaa in nambarka wax kala iibsiga uu ku qoran yahay goobta xmax, laakiin macluumaadka macluumaadka aan la dejin:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
baajinta
Isbeddellada ilmo iska soo rididda waxay u shaqeysaa si la mid ah samaynta, kaliya XACT qaybta la soo tuuray ayaa loo dejiyay wax kala iibsiga. Dib u laabashadu waa sida ugu dhakhsaha badan ee loo sameeyo. Inkasta oo amarka loo yaqaan ROLLBACK, isbeddellada dib looma rogin: wax kasta oo macaamilku u suurtagashay in uu beddelo boggaga xogta ayaa weli ah mid aan isbeddelin.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Marka bogga la galo, heerka waa la eegi doonaa oo xmax_aborted tilmaanta waxa lagu dejin doonaa nooca safka. Nambarka xmax laftiisa ayaa ku haray bogga, laakiin qofna ma fiirin doono.
=> 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)
Cusboonaysii
Cusboonaysiinta waxay u shaqeysaa sidii inay markii hore tirtirtay nooca hadda ee safka ka dibna gelisay mid cusub.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Weydiinta waxay soo saartaa hal xariiq (nooc cusub):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Laakiin bogga waxaan ku aragnaa labada nooc:
=> 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)
Nooca la tirtiray waxaa lagu calaamadeeyay lambarka wax kala iibsiga ee hadda ee goobta xmax. Waxaa intaa dheer, qiimahan ayaa lagu qoray kii hore, tan iyo markii la joojiyay macaamilkii hore. Xmax_aborted bit waa la nadiifiyay sababtoo ah heerka wax kala iibsiga hadda lama oga.
Nooca ugu horreeya ee xariiqa hadda wuxuu tilmaamayaa kan labaad (t_ctid field) inuu yahay kan cusub.
Tusmada labaad ayaa ka muuqata bogga tusmada, safka labaadna wuxuu tixraacayaa nooca labaad ee bogga miiska.
Sida la tirtiro, qiimaha xmax ee nooca ugu horreeya ee safka waa calaamad muujinaysa in safku xiran yahay.
Hagaag, aynu dhamaystirno wax kala iibsiga.
=> COMMIT;
Tirooyinka
Ilaa hadda waxaan ka hadalnay oo keliya bogagga miiska. Maxaa ka dhacaya gudaha tusmooyinka?
Macluumaadka ku jira boggaga tusmada aad bay u kala duwan yihiin iyadoo ku xidhan nooca tusmada gaarka ah. Iyo xitaa hal nooc oo index ah ayaa leh noocyo kala duwan oo bogag ah. Tusaale ahaan, geedka B wuxuu leeyahay bog xog badan iyo bogag "joogto ah".
Si kastaba ha ahaatee, boggu inta badan waxa uu leeyahay tilmaamo kala duwan oo safafka iyo safafka laftooda (sida bogga miiska). Intaa waxaa dheer, dhamaadka bogga waxaa jira meel loogu talagalay xogta gaarka ah.
Safafka tusmooyinka sidoo kale waxay yeelan karaan qaabab aad u kala duwan iyadoo ku xiran nooca tusmada. Tusaale ahaan, geedka B, safafka la xidhiidha boggaga caleenku waxa ay ka kooban yihiin tusmaynta qiimaha muhiimka ah iyo tixraaca (ctid) ee safka miiska u dhigma. Guud ahaan, tusmada waxaa loo qaabayn karaa si gebi ahaanba ka duwan.
Qodobka ugu muhiimsan waa in aysan jirin noocyo saf ah oo ku jira tusmooyinka nooc kasta ah. Hagaag, ama waxaan u qaadan karnaa in sadar kasta uu matalo hal nooc oo sax ah. Si kale haddii loo dhigo, ma jiraan goobo xmin iyo xmax oo ku jira madaxa safka tusaha. Waxaan u qaadan karnaa in xiriirinta tusmooyinka ay horseedaan dhammaan noocyada miiska ee safafka - si aad u ogaato nooca nooca wax kala iibsiga uu arki doono kaliya adoo eegaya miiska. (Sida had iyo jeer, tani maaha runta oo dhan. Xaaladaha qaarkood, khariidadda muuqaalku waxay hagaajin kartaa habka, laakiin waxaan si faahfaahsan u eegi doonaa tan dambe.)
Isla mar ahaantaana, bogga tusmada waxaynu ka helaynaa tilmaamayaasha labada nooc, labadaba kan hadda iyo kan hore:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Wax kala iibsiga dhabta ah
Ficil ahaan, PostgreSQL waxay isticmaashaa hagaajinta taasoo u oggolaanaysa inay "badbaadiso" lambarrada macaamilka.
Haddii wax kala iibsigu akhriyo xogta oo keliya, wax saamayn ah kuma laha aragtida noocyada safafka. Sidaa darteed, habka adeeggu wuxuu marka hore soo saaraa xid dalwaddii wax kala iibsiga. Nambarku wuxuu ka kooban yahay aqoonsiga habsocodka iyo nambarka taxanaha.
Soo saarista lambarkan uma baahna is-waafajin ka dhexeeya dhammaan hababka oo sidaas awgeed aad ayey u dhakhso badan tahay. Waxaan baran doonaa sabab kale oo loo isticmaalo nambarada farsamada marka aan ka hadalno baraf.
Nambarada dhabta ah laguma xisaabtamo sinaba marka la eego sawir-qaadista xogta.
Waqtiyo kala duwan, waxaa laga yaabaa inay si fiican u jiraan wax kala beddelasho toos ah nidaamka oo leh nambaro hore loo isticmaalay, tanina waa caadi. Laakiin lambarkan oo kale laguma qori karo boggaga xogta, sababtoo ah marka xigta ee bogga la galo waxa laga yaabaa inay lumiso dhammaan macnaha.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Haddii wax kala iibsigu bilaabo inuu beddelo xogta, waxa la siinayaa nambar wax kala beddelasho oo dhab ah oo gaar ah.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Wax kala iibsiga
Keydso dhibcaha
Lagu qeexay SQL dhibcood kaydso (savepoint), kaas oo kuu ogolaanaya inaad tirtirto qayb ka mid ah wax kala iibsiga adiga oo aan gebi ahaanba joojin. Laakiin tani kuma habboona jaantuska kore, maadaama macaamilku leeyahay xaalad isku mid ah dhammaan isbeddelladeeda, iyo muuqaal ahaan wax xog ah dib looma soo celinayo.
Si loo hirgeliyo shaqadan, wax kala beddelashada barta kaydinta ayaa loo kala qaybiyaa dhawr nooc macaamil ganacsi oo buul ah (Subtransaction), heerka kaas oo si gaar ah loo maareyn karo.
Wax kala iibsiga buulku wuxuu leeyahay nambar u gaar ah (oo ka sarreeya tirada macaamilka ugu weyn). Xaaladda macaamil ganacsi oo buul ah ayaa loo diiwaangeliyaa habka caadiga ah ee XACT, laakiin heerka ugu dambeeya wuxuu ku xiran yahay heerka macaamilka ugu weyn: haddii la tirtiro, dhammaan macaamil ganacsiyada ayaa sidoo kale la joojiyay.
Macluumaadka ku saabsan buul-galka wax kala iibsiga waxa lagu kaydiyaa faylalka ku jira buugga PGDATA/pg_subtrans. Faylasha waxaa lagu galaa meelo kaydiyayaal ku jira xusuusta la wadaago, oo loo habeeyey si la mid ah kuwa XACT baffers.
Ha ku khaldamin wax kala iibsiga saadka ah iyo wax kala beddelasho madaxbannaan. Wax kala iibsigu sina uma xidhna midba midka kale, laakiin wax kala iibsiga buulku wuu sameeyaa. Ma jiraan wax macaamilo madaxbannaan oo ku jira PostgreSQL ee caadiga ah, iyo, laga yaabee, sida ugu fiican: waxaa loo baahan yahay aad iyo aad dhif u ah, iyo joogitaankooda DBMS-yada kale waxay kiciyaan xadgudub, kaas oo qof kastaa uu markaas ku dhaco.
Aynu nadiifinno miiska, billowno wax kala iibsiga oo aan gelino safka:
=> 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)
Hadda aan dhigno meel kaydin oo aan gelinno khad kale.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Ogsoonow in shaqada txid_current() ay soo celiso nambarka wax kala iibsiga ee ugu muhiimsan, ma aha nambarka wax kala iibsiga ee buulkiisa leh.
=> 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)
Aynu dib ugu soo rogno barta kaydinta oo aan gelinno xariiqda saddexaad.
=> 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)
Bogga waxaan ku sii wadeynaa inaan aragno safka uu ku daray wax kala iibsiga la joojiyay.
Waxaan hagaajineynaa isbeddellada.
=> 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)
Hadda waxaad si cad u arki kartaa in macaamil ganacsi kasta oo buul ah uu leeyahay xaalad u gaar ah.
Ogsoonow in macaamil ganacsi oo buul ah aan si cad loogu isticmaali karin SQL, taas oo ah, ma bilaabi kartid macaamil cusub adigoon dhamaystirin midka hadda jira. Habkan waxa loo hawlgeliyay si aan toos ahayn marka la isticmaalayo kaydka, iyo sidoo kale marka la maaraynayo PL/pgSQL ka reeban iyo tiro kale oo kiisas qalaad.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Khaladaadka iyo antamiga hawlgallada
Maxaa dhacaya haddii uu khalad dhaco marka la samaynayo qalliin? Tusaale ahaan, sida tan:
=> 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
Khalad ayaa dhacay Hadda wax kala iibsiga waxaa loo arkaa mid la soo riday oo wax hawlgal ah lama oggola:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Xitaa haddii aad isku daydo inaad sameyso isbeddelada, PostgreSQL waxay soo sheegi doontaa ilmo soo rididda:
=> COMMIT;
ROLLBACK
Waa maxay sababta wax kala gadashadu u sii socon la'dahay fashilka ka dib? Xaqiiqdu waxay tahay in qalad uu ka dhalan karo si aan u helno qayb ka mid ah isbeddellada - atomity ee xitaa macaamilka, laakiin hawlwadeenka waa lagu xadgudbi doonaa. Sida tusaalaheena, halkaas oo hawlwadeenku ku guulaystey inuu cusbooneysiiyo hal xariiq ka hor qaladka:
=> 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)
Waa in la sheegaa in psql ay leedahay hab wali u ogolaanaya wax kala iibsiga in uu sii socdo ka dib fashilka sida haddii ficilada hawlwadeenka khaldan dib loo rogay.
=> 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;
Ma adka in la qiyaaso in qaabkan, psql ay dhab ahaantii dhigayso barta kaydinta daahsoon ka hor amar kasta, iyo haddii ay dhacdo guuldarro waxay dib u soo celinaysaa. Habkan si caadi ah looma isticmaalo, maadaama dejinta dhibcaha kaydinta (xitaa iyada oo aan dib loogu laaban) ay ku lug leedahay kor u kac weyn.
Source: www.habr.com