Yog li, peb tau txiav txim siab txog cov teeb meem ntsig txog , thiab ua ib tug retreat txog . Thiab thaum kawg peb tau mus rau qhov nthuav tshaj plaws - txoj hlua versions.
Header
Raws li peb tau hais lawm, txhua kab tuaj yeem muaj nyob rau hauv ntau lub versions hauv cov ntaub ntawv. Rau lub hom phiaj no, txhua lub version muaj ob lub cim uas txiav txim siab "lub sij hawm" ntawm qhov kev ua ntawm no version (xmin thiab xmax). Nyob rau hauv quotes - vim hais tias nws tsis yog lub sij hawm raws li xws li uas yog siv, tab sis ib tug tshwj xeeb nce txee. Thiab lub txee no yog tus lej pauv.
(Raws li ib txwm, qhov kev muaj tiag yog qhov nyuaj dua: tus lej hloov pauv tsis tuaj yeem nce txhua lub sijhawm vim muaj peev xwm tsawg ntawm lub txee. Tab sis peb yuav saib cov ntsiab lus kom ntxaws thaum peb mus rau khov.)
Thaum ib kab raug tsim, xmin tau teeb tsa rau tus lej hloov pauv uas tau tshaj tawm INSERT hais kom ua, thiab xmax yog sab laug.
Thaum ib kab raug muab tshem tawm, tus nqi xmax ntawm cov ntawv tam sim no raug cim nrog tus lej ntawm kev hloov pauv uas tau ua qhov DELETE.
Thaum ib kab hloov kho los ntawm UPDATE cov lus txib, ob txoj haujlwm tau ua tiav: DELETE thiab INSERT. Cov kab ntawv tam sim no tau teeb tsa xmax sib npaug rau tus lej ntawm kev hloov pauv uas tau ua UPDATE. Ib tug tshiab version ntawm tib txoj hlua yog ces tsim; nws tus nqi xmin coincides nrog rau xmax tus nqi ntawm lub dhau los version.
Cov xmin thiab xmax teb muaj nyob rau hauv kab version header. Ntxiv nrog rau cov teb no, lub header muaj lwm tus, piv txwv li:
- infomask yog ib tug series ntawm cov khoom uas txhais cov khoom ntawm no version. Muaj ntau heev ntawm lawv; Peb mam li maj mam xav txog cov tseem ceeb.
- ctid yog qhov txuas mus rau qhov txuas ntxiv, tshiab dua ntawm tib kab. Rau qhov tshiab tshaj plaws, feem ntau tam sim no version ntawm ib txoj hlua, ctid hais txog qhov no nws tus kheej. Tus lej muaj daim ntawv (x, y), qhov twg x yog nplooj ntawv tus lej, y yog tus lej ntsuas hauv qhov array.
- null bitmap - Kos cov kab ntawm qhov muab version uas muaj tus nqi null (NULL). NULL tsis yog ib qho ntawm cov ntaub ntawv ib txwm muaj txiaj ntsig, yog li tus cwj pwm yuav tsum tau muab cais tawm.
Yog li ntawd, lub header yog loj heev - tsawg kawg yog 23 bytes rau txhua version ntawm kab, thiab feem ntau yog vim NULL bitmap. Yog tias lub rooj yog "qis" (uas yog, muaj ob peb kab), cov nyiaj siv ua haujlwm yuav siv ntau dua li cov ntaub ntawv tseem ceeb.
ntxig
Cia peb ua tib zoo saib seb yuav ua li cas cov hlua qis qis ua haujlwm, pib nrog kev ntxig.
Rau kev sim, cia peb tsim ib lub rooj tshiab nrog ob kab thiab qhov ntsuas ntawm ib qho ntawm lawv:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Cia peb ntxig ib kab tom qab pib ua lag luam.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Nov yog peb tus lej hloov pauv tam sim no:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Cia peb saib cov ntsiab lus ntawm nplooj ntawv. Lub heap_page_items muaj nuj nqi ntawm nplooj ntawv tshuaj xyuas txuas ntxiv tso cai rau koj kom tau txais cov ntaub ntawv hais txog tus taw tes thiab kab ntawv:
=> 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
Nco ntsoov tias lo lus heap hauv PostgreSQL hais txog cov ntxhuav. Qhov no yog lwm qhov txawv txav ntawm lub sij hawm - ib tug heap paub , uas tsis muaj dab tsi sib xws nrog lub rooj. Ntawm no lo lus yog siv nyob rau hauv lub siab ntawm "txhua yam yog muab pov tseg ua ke," as opposed to order indexes.
Lub luag haujlwm qhia cov ntaub ntawv "raws li yog", hauv hom ntawv uas nyuaj rau kev nkag siab. Txhawm rau txheeb xyuas nws, peb tsuas yog tso ib feem ntawm cov ntaub ntawv thiab txiav txim siab nws:
=> 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)
Nov yog qhov peb tau ua:
- Ntxiv xoom rau tus lej ntsuas kom nws zoo ib yam li t_ctid: (nplooj tus lej, tus lej ntsuas).
- Deciphered lub xeev ntawm tus lp_flags pointer. Ntawm no nws yog "ib txwm" - qhov no txhais tau hais tias tus pointer tiag tiag yog hais txog cov qauv ntawm txoj hlua. Peb mam li saib lwm lub ntsiab lus tom qab.
- Ntawm tag nrho cov ntaub ntawv me me, tsuas yog ob khub tau txheeb xyuas txog tam sim no. Cov xmin_committed thiab xmin_aborted bits qhia seb tus lej xmin puas tau cog lus (tso tseg). Ob qhov zoo sib xws yog xa mus rau tus lej hloov pauv xmax.
Peb pom dab tsi? Thaum koj ntxig ib kab, qhov ntsuas tus lej 1 yuav tshwm sim hauv nplooj ntawv, taw qhia rau thawj thiab tsuas yog version ntawm kab.
Hauv txoj hlua version, xmin teb tau sau nrog tus lej hloov pauv tam sim no. Kev lag luam tseem ua haujlwm, yog li ob qho tib si xmin_committed thiab xmin_aborted khoom tsis tau teeb tsa.
Kab version ctid teb hais txog tib kab. Qhov no txhais tau hais tias tsis muaj ib tug tshiab version.
Xmax teb tau sau nrog tus lej lej 0 vim tias cov kab ntawv no tsis tau muab tshem tawm thiab tam sim no. Kev lag luam yuav tsis them nyiaj rau tus lej no vim tias xmax_aborted ntsis tau teeb tsa.
Cia peb ua ib kauj ruam ntxiv rau kev txhim kho kev nyeem tau los ntawm kev ntxiv cov ntaub ntawv rau cov lej hloov pauv. Thiab cia peb tsim ib txoj haujlwm, vim peb yuav xav tau qhov kev thov ntau dua ib zaug:
=> 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;
Nyob rau hauv daim ntawv no, nws yog ntau clearer dab tsi tshwm sim nyob rau hauv lub header ntawm kab version:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Zoo sib xws, tab sis tsis tshua meej, cov ntaub ntawv tuaj yeem tau txais los ntawm lub rooj nws tus kheej, siv pseudo-kem xmin thiab xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Kev kho
Yog tias kev lag luam ua tiav tiav, koj yuav tsum nco ntsoov nws cov xwm txheej - nco ntsoov tias nws tau cog lus. Txhawm rau ua qhov no, ib qho qauv hu ua XACT yog siv (thiab ua ntej version 10 nws tau hu ua CLOG (cog log) thiab lub npe no tseem tuaj yeem pom nyob rau ntau qhov chaw).
XACT tsis yog qhov system catalog rooj; cov no yog cov ntaub ntawv hauv PGDATA/pg_xact directory. Lawv muaj ob qhov me me rau txhua qhov kev sib pauv: cog lus thiab rho tawm - ib yam li hauv kab ntawv kab lus. Cov ntaub ntawv no tau muab faib ua ob peb cov ntaub ntawv nkaus xwb kom yooj yim; peb yuav rov qab mus rau qhov teeb meem no thaum peb txiav txim siab khov. Thiab ua hauj lwm nrog cov ntaub ntawv no yog nqa tawm nplooj ntawv los ntawm nplooj ntawv, zoo li nrog rau tag nrho lwm tus.
Yog li, thaum kev sib pauv tau cog lus hauv XACT, qhov kev cog lus me ntsis yog teem rau qhov kev hloov pauv no. Thiab qhov no yog txhua yam uas tshwm sim thaum lub sij hawm cog lus (txawm tias peb tsis tau tham txog cov ntaub ntawv teev cia ua ntej).
Thaum lwm qhov kev lag luam nkag mus rau nplooj ntawv peb nyuam qhuav saib, nws yuav tau teb ob peb nqe lus nug.
- Xmin puas tau ua tiav? Yog tsis yog, ces tus tsim version ntawm txoj hlua yuav tsum tsis pom.
Qhov kev kuaj xyuas no yog ua los ntawm kev saib lwm tus qauv, uas nyob rau hauv kev sib koom nco txog qhov piv txwv thiab hu ua ProcArray. Nws muaj cov npe ntawm tag nrho cov txheej txheem nquag, thiab rau txhua tus tus lej ntawm nws qhov kev hloov pauv tam sim no (kwv yees) tau qhia. - Yog tias ua tiav, ces yuav ua li cas - los ntawm kev cog lus lossis tshem tawm? Yog tias muab tso tseg, ces kab version yuav tsum tsis pom ib qho.
Qhov no yog qhov XACT yog rau. Tab sis, txawm hais tias nplooj ntawv kawg ntawm XACT tau khaws cia hauv buffers hauv RAM, nws tseem kim los xyuas XACT txhua zaus. Yog li ntawd, thaum qhov kev hloov pauv tau txiav txim siab, nws tau sau rau xmin_committed thiab xmin_aborted me ntsis ntawm txoj hlua version. Yog tias ib qho ntawm cov khoom no tau teeb tsa, ces lub xeev ntawm kev hloov pauv xmin raug txiav txim siab paub thiab kev hloov pauv tom ntej yuav tsis tau nkag mus rau XACT.
Vim li cas cov khoom no tsis tau teeb tsa los ntawm kev hloov pauv nws tus kheej ua qhov ntxig? Thaum muaj qhov ntxig tshwm sim, qhov kev hloov pauv tseem tsis tau paub tias nws yuav ua tiav. Thiab thaum lub sijhawm cog lus, nws tsis paub meej tias kab ntawv twg tau hloov pauv lawm. Tej zaum yuav muaj ntau nplooj ntawv zoo li no, thiab nco ntsoov lawv tsis muaj txiaj ntsig. Tsis tas li ntawd, qee cov nplooj ntawv tuaj yeem raug tshem tawm ntawm qhov tsis muaj cache rau disk; nyeem lawv dua los hloov cov khoom yuav qeeb qhov kev cog lus loj.
Qhov tsis zoo ntawm kev txuag nyiaj yog tias tom qab hloov pauv, txhua qhov kev hloov pauv (txawm tias ib qho ua tau yooj yim nyeem - SELECT) tuaj yeem pib hloov cov nplooj ntawv cov ntaub ntawv hauv qhov tsis muaj cache.
Yog li, cia peb kho qhov kev hloov pauv.
=> COMMIT;
Tsis muaj dab tsi tau hloov pauv ntawm nplooj ntawv (tab sis peb paub tias cov xwm txheej pauv tau raug kaw hauv XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Tam sim no qhov kev hloov pauv uas nkag mus rau nplooj ntawv thawj zaug yuav tau txiav txim siab txog xmin kev hloov pauv thiab sau rau cov ntaub ntawv cov khoom:
=> 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)
Rho tawm
Thaum ib kab raug tshem tawm, tus lej ntawm kev rho tawm tam sim no tau sau rau xmax teb ntawm qhov tam sim no version, thiab xmax_aborted ntsis yog tshem tawm.
Nco ntsoov tias qhov teeb tsa tus nqi ntawm xmax sib raug rau cov khoom lag luam nquag ua raws li kab xauv. Yog tias lwm qhov kev hloov pauv xav hloov kho lossis tshem tawm kab no, nws yuav raug yuam kom tos kev hloov pauv xmax kom tiav. Peb yuav tham ntxiv txog kev thaiv tom qab. Txog tam sim no, peb tsuas yog nco ntsoov tias tus lej ntawm kab xauv tsis txwv. Lawv tsis siv qhov chaw hauv RAM thiab kev ua haujlwm ntawm lub cev tsis raug kev txom nyem los ntawm lawv tus lej. Muaj tseeb, "ntev" kev lag luam muaj lwm yam tsis zoo, tab sis ntxiv rau tom qab ntawd.
Wb rho tawm kab.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Peb pom tias tus lej hloov pauv tau sau rau hauv xmax teb, tab sis cov ntaub ntawv tsis raug teeb tsa:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
tshem tawm
Kev rho tawm cov kev hloov pauv ua haujlwm zoo ib yam li kev cog lus, tsuas yog hauv XACT qhov rho tawm me ntsis yog teem rau kev sib pauv. Undoing yog sai npaum li kev cog lus. Txawm hais tias cov lus txib hu ua ROLLBACK, cov kev hloov pauv tsis rov qab: txhua yam kev hloov pauv hloov pauv hauv cov nplooj ntawv cov ntaub ntawv tseem tsis hloov pauv.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Thaum nplooj ntawv nkag mus, cov xwm txheej yuav raug kuaj thiab xmax_aborted hint ntsis yuav raug teeb tsa rau kab ntawv. Tus lej xmax nws tus kheej tseem nyob ntawm nplooj ntawv, tab sis tsis muaj leej twg yuav saib nws.
=> 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)
Hloov tshiab
Qhov hloov tshiab ua haujlwm zoo li nws thawj zaug tshem tawm cov kab ntawv tam sim no thiab tom qab ntawd ntxig ib qho tshiab.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Cov lus nug tsim ib kab (tshiab version):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Tab sis ntawm nplooj ntawv peb pom ob lub versions:
=> 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)
Cov deleted version yog cim nrog tus lej pauv tam sim no hauv xmax teb. Ntxiv mus, tus nqi no yog sau tshaj qhov qub, txij li qhov kev sib pauv yav dhau los raug muab tso tseg. Thiab lub xmax_aborted ntsis yog tshem tawm vim hais tias cov xwm txheej ntawm kev pauv tam sim no tseem tsis tau paub.
Thawj version ntawm kab tam sim no hais txog qhov thib ob (t_ctid teb) ua tus tshiab dua.
Ib qhov ntsuas thib ob tshwm hauv nplooj ntawv ntsuas thiab kab thib ob hais txog qhov thib ob version hauv nplooj ntawv.
Ib yam li nrog kev tshem tawm, tus nqi xmax hauv thawj kab ntawm kab yog qhov qhia tias kab raug kaw.
Zoo, cia ua kom tiav qhov kev sib pauv.
=> COMMIT;
Indexes
Txog tam sim no peb tsuas yog tham txog cov nplooj ntawv. Yuav ua li cas tshwm sim hauv cov indexes?
Cov ntaub ntawv nyob rau hauv cov nplooj ntawv Performance index sib txawv heev nyob ntawm seb hom index. Thiab txawm tias ib hom kev ntsuas muaj ntau hom nplooj ntawv. Piv txwv li, B-ntoo muaj nplooj ntawv metadata thiab nplooj ntawv "tsis tu ncua".
Txawm li cas los xij, nplooj ntawv feem ntau muaj cov lus taw qhia rau cov kab thiab cov kab lawv tus kheej (xws li nplooj ntawv nplooj ntawv). Tsis tas li ntawd, qhov kawg ntawm nplooj ntawv muaj qhov chaw rau cov ntaub ntawv tshwj xeeb.
Kab hauv indexes tuaj yeem muaj cov qauv sib txawv nyob ntawm seb hom kev ntsuas. Piv txwv li, rau ib tsob ntoo B, cov kab ntsig txog nplooj nplooj muaj qhov ntsuas qhov tseem ceeb thiab ib qho kev siv (ctid) rau cov kab lus sib txuas. Feem ntau, qhov Performance index tuaj yeem tsim nyob rau hauv ib txoj kev sib txawv kiag li.
Qhov tseem ceeb tshaj plaws yog tias tsis muaj kab versions hauv indexes ntawm txhua yam. Zoo, lossis peb tuaj yeem xav tias txhua kab yog sawv cev los ntawm ib qho version. Nyob rau hauv lwm yam lus, tsis muaj xmin thiab xmax teb nyob rau hauv lub index kab header. Peb tuaj yeem xav tias cov kev sib txuas los ntawm qhov Performance index coj mus rau txhua lub rooj versions ntawm cov kab - yog li koj tuaj yeem txiav txim siab seb qhov kev hloov pauv twg yuav pom tsuas yog saib ntawm lub rooj. (Raws li ib txwm, qhov no tsis yog qhov tseeb tag nrho. Qee qhov xwm txheej, daim ntawv qhia pom pom tuaj yeem txhim kho cov txheej txheem, tab sis peb yuav saib qhov no kom ntxaws ntxiv tom qab.)
Nyob rau tib lub sijhawm, hauv nplooj ntawv ntsuas peb pom cov taw qhia rau ob qho tib si, ob qho tib si tam sim no thiab qhov qub:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Kev lag luam virtual
Hauv kev xyaum, PostgreSQL siv qhov kev ua kom zoo uas tso cai rau nws "txuag" cov lej hloov pauv.
Yog tias kev hloov pauv tsuas yog nyeem cov ntaub ntawv, nws tsis cuam tshuam rau qhov pom ntawm kab versions. Yog li ntawd, cov txheej txheem kev pabcuam thawj zaug teeb tsa virtual xid rau kev hloov pauv. Tus lej suav nrog tus txheej txheem ID thiab tus lej ua ntu zus.
Tshaj tawm tus lej no tsis tas yuav synchronization ntawm txhua tus txheej txheem thiab yog li ceev heev. Peb yuav tau paub txog lwm qhov laj thawj ntawm kev siv tus lej virtual thaum peb tham txog kev khov.
Cov lej virtual tsis suav nrog hauv txhua txoj hauv kev hauv cov ntaub ntawv snapshots.
Ntawm cov ntsiab lus sib txawv hauv lub sijhawm, tej zaum yuav muaj kev lag luam virtual hauv qhov system nrog cov lej uas twb tau siv lawm, thiab qhov no yog qhov qub. Tab sis tus lej no tsis tuaj yeem sau rau hauv cov ntaub ntawv nplooj ntawv, vim tias lwm zaus nkag mus rau nplooj ntawv nws yuav poob tag nrho cov ntsiab lus.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Yog tias kev hloov pauv pib hloov cov ntaub ntawv, nws tau muab tus lej sib pauv tiag tiag.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Nested Transactions
Txuag cov ntsiab lus
Txhais hauv SQL txuag cov ntsiab lus (savepoint), uas tso cai rau koj tshem tawm ib feem ntawm kev lag luam yam tsis muaj kev cuam tshuam tag nrho. Tab sis qhov no tsis haum rau hauv daim duab saum toj no, vim tias qhov kev sib pauv tau zoo ib yam rau tag nrho nws cov kev hloov pauv, thiab lub cev tsis muaj cov ntaub ntawv rov qab.
Txhawm rau siv qhov kev ua haujlwm no, kev hloov pauv nrog lub chaw khaws nyiaj tau muab faib ua ob peb cais nested muas (subtransaction), cov xwm txheej uas tuaj yeem tswj hwm nyias.
Nested muas muaj lawv tus lej (ntau dua tus lej ntawm cov kev hloov pauv tseem ceeb). Cov xwm txheej ntawm kev ua lag luam nested tau sau tseg raws li ib txwm muaj nyob rau hauv XACT, tab sis cov xwm txheej kawg yog nyob ntawm cov xwm txheej ntawm kev lag luam tseem ceeb: yog tias nws raug tso tseg, ces tag nrho cov kev lag luam nested kuj raug tso tseg.
Cov ntaub ntawv hais txog kev ua lag luam nesting yog khaws cia hauv cov ntaub ntawv hauv PGDATA/pg_subtrans directory. Cov ntaub ntawv nkag mus los ntawm buffers hauv qhov piv txwv qhov sib koom nco, koom ua ke tib yam li XACT buffers.
Tsis txhob cuam tshuam kev lag luam nested nrog autonomous muas. Autonomous muas tsis nyob ntawm ib leeg nyob rau hauv txhua txoj kev, tab sis nested muas ua. Tsis muaj kev tswj hwm tus kheej hauv PostgreSQL li niaj zaus, thiab, tej zaum, rau qhov zoo tshaj plaws: lawv xav tau heev, tsis tshua muaj, thiab lawv lub xub ntiag nyob rau hauv lwm yam DBMSs provokes kev tsim txom, uas txhua leej txhua tus raug kev txom nyem.
Cia peb tshem lub rooj, pib ua lag luam thiab ntxig rau kab:
=> 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)
Tam sim no cia peb muab qhov chaw txuag thiab ntxig rau lwm kab.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Nco ntsoov tias txid_current() muaj nuj nqi xa rov qab tus lej lag luam tseem ceeb, tsis yog tus lej lag luam nested.
=> 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)
Cia peb rov qab mus rau qhov chaw txuag thiab ntxig rau kab thib peb.
=> 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)
Hauv nplooj ntawv peb txuas ntxiv mus saib kab ntxiv los ntawm kev tso tseg nested pauv.
Peb kho cov kev hloov.
=> 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)
Tam sim no koj tuaj yeem pom meej tias txhua qhov kev sib pauv nested muaj nws tus kheej li xwm txheej.
Nco ntsoov tias nested muas tsis tuaj yeem siv tau meej meej hauv SQL, uas yog, koj tsis tuaj yeem pib qhov kev hloov pauv tshiab yam tsis tau ua tiav qhov tam sim no. Cov txheej txheem no tau qhib implicitly thaum siv savepoints, nrog rau thaum tuav PL/pgSQL kev zam thiab nyob rau hauv ib tug xov tooj ntawm lwm yam, ntau kab txawv.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Kev ua yuam kev thiab atomicity ntawm kev ua haujlwm
Yuav ua li cas yog qhov yuam kev tshwm sim thaum ua haujlwm? Piv txwv li, zoo li no:
=> 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
Ib qho yuam kev tau tshwm sim. Tam sim no qhov kev hloov pauv tau txiav txim siab rho tawm thiab tsis muaj kev tso cai ua haujlwm hauv nws:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Thiab txawm tias koj sim ua qhov kev hloov pauv, PostgreSQL yuav qhia txog kev rho menyuam:
=> COMMIT;
ROLLBACK
Vim li cas ho tsis tuaj yeem ua lag luam txuas ntxiv tom qab ua tsis tiav? Qhov tseeb yog tias qhov kev ua yuam kev tuaj yeem tshwm sim hauv txoj hauv kev uas peb yuav nkag mus rau ib feem ntawm cov kev hloov pauv - atomicity tsis txawm tias qhov kev hloov pauv, tab sis tus neeg teb xov tooj yuav raug ua txhaum cai. Raws li nyob rau hauv peb qhov piv txwv, qhov twg tus neeg teb xov tooj tau tswj kom hloov kho ib kab ua ntej qhov yuam kev:
=> 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)
Nws yuav tsum tau hais tias psql muaj ib hom uas tseem tso cai rau kev hloov pauv mus txuas ntxiv tom qab qhov tsis ua haujlwm zoo li qhov kev ua ntawm tus neeg ua haujlwm yuam kev tau rov qab.
=> 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;
Nws tsis yooj yim rau kwv yees tias hauv hom no, psql tiag tiag tso qhov cuam tshuam tsis zoo ua ntej txhua qhov lus txib, thiab yog tias tsis ua haujlwm pib rov qab rau nws. Hom no tsis yog siv los ntawm lub neej ntawd, txij li kev teeb tsa cov ntsiab lus khaws tseg (txawm tias tsis rov qab mus rau lawv) suav nrog cov nyiaj siv ua haujlwm tseem ceeb.
Tau qhov twg los: www.hab.com