ããã§ã以äžã«é¢é£ããåé¡ãæ€èšããŸããã
ã¿ã€ãã«
ãã§ã«è¿°ã¹ãããã«ãåè¡ã¯ããŒã¿ããŒã¹å ã«è€æ°ã®ããŒãžã§ã³ã§åæã«ååšã§ããŸãã ãã®ç®çã®ããã«ãåããŒãžã§ã³ã«ã¯ããã®ããŒãžã§ã³ã®åäœã®ãæéãã決å®ãã XNUMX ã€ã®ããŒã¯ (xmin ãš xmax) ãä»ããŠããŸãã åŒçšç¬Šå - 䜿çšãããã®ã¯æéãã®ãã®ã§ã¯ãªããç¹å¥ãªå¢å ã«ãŠã³ã¿ãŒã§ããããã§ãã ãããŠããã®ã«ãŠã³ã¿ãŒããã©ã³ã¶ã¯ã·ã§ã³çªå·ã§ãã
(ãã€ãã®ããã«ãçŸå®ã¯ããã«è€éã§ããã«ãŠã³ã¿ã®ããã容éãéãããŠããããããã©ã³ã¶ã¯ã·ã§ã³æ°ã¯åžžã«å¢å ããããã§ã¯ãããŸããããã ãããããã®è©³çŽ°ã«ã€ããŠã¯ãããªãŒãºãããšãã«è©³ããèŠãŠãããŸãã)
è¡ãäœæããããšãxmin 㯠INSERT ã³ãã³ããçºè¡ãããã©ã³ã¶ã¯ã·ã§ã³çªå·ã«èšå®ãããxmax ã¯ç©ºçœã®ãŸãŸã«ãªããŸãã
è¡ãåé€ããããšãçŸåšã®ããŒãžã§ã³ã® xmax å€ã«ãDELETE ãå®è¡ãããã©ã³ã¶ã¯ã·ã§ã³ã®çªå·ãããŒã¯ãããŸãã
UPDATE ã³ãã³ãã«ãã£ãŠè¡ãå€æŽããããšãDELETE ãš INSERT ãšãã XNUMX ã€ã®æäœãå®éã«å®è¡ãããŸãã è¡ã®çŸåšã®ããŒãžã§ã³ã§ã¯ãxmax ã UPDATE ãå®è¡ãããã©ã³ã¶ã¯ã·ã§ã³ã®æ°ã«çããèšå®ãããŸãã 次ã«ãåãæååã®æ°ããããŒãžã§ã³ãäœæãããŸãã ãã® xmin å€ã¯ã以åã®ããŒãžã§ã³ã® xmax å€ãšäžèŽããŸãã
xmin ãã£ãŒã«ããš xmax ãã£ãŒã«ãã¯è¡ããŒãžã§ã³ ããããŒã«å«ãŸããŸãã ãããã®ãã£ãŒã«ãã«å ããŠãããããŒã«ã¯æ¬¡ã®ãããªä»ã®ãã£ãŒã«ããå«ãŸããŸãã
- infomask ã¯ããã®ããŒãžã§ã³ã®ããããã£ãå®çŸ©ããäžé£ã®ãããã§ãã ãããã¯ããªããããããããŸãã äž»èŠãªãã®ã«ã€ããŠã¯åŸã ã«æ€èšããŠãããŸãã
- ctid ã¯ãåãè¡ã®æ¬¡ã®æ°ããããŒãžã§ã³ãžã®ãªã³ã¯ã§ãã æååã®ææ°ã®ææ°ããŒãžã§ã³ã®å Žåãctid ã¯ãã®ããŒãžã§ã³èªäœãåç §ããŸãã æ°å€ã®åœ¢åŒã¯ (x,y) ã§ãx ã¯ããŒãžçªå·ãy ã¯é åå ã®ã€ã³ããã¯ã¹çªå·ã§ãã
- null ãããããã - null å€ (NULL) ãå«ãç¹å®ã®ããŒãžã§ã³ã®åãããŒã¯ããŸãã NULL ã¯éåžžã®ããŒã¿åå€ã® XNUMX ã€ã§ã¯ãªããããå±æ§ã¯åå¥ã«ä¿åããå¿ èŠããããŸãã
ãã®çµæãããããŒã¯éåžžã«å€§ãããªããŸããã©ã€ã³ã®ããŒãžã§ã³ããšã«å°ãªããšã 23 ãã€ããé垞㯠NULL ããããããã«ããããã«å€§ãããªããŸãã ããŒãã«ããçãã(ã€ãŸããåãã»ãšãã©ãªã) å Žåãæçšãªæ å ±ããããªãŒããŒããããå€ããå ããå¯èœæ§ããããŸãã
ã€ã³ãµãŒã
æ¿å ¥ããå§ããŠãäœã¬ãã«ã®æååæäœãã©ã®ããã«å®è¡ããããã詳ããèŠãŠã¿ãŸãããã
å®éšã®ããã«ãXNUMX ã€ã®åãšãã®ãã¡ã® XNUMX ã€ã«ã€ã³ããã¯ã¹ãå«ãæ°ããããŒãã«ãäœæããŠã¿ãŸãããã
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
ãã©ã³ã¶ã¯ã·ã§ã³ãéå§ããããXNUMXè¡ãæ¿å ¥ããŸãããã
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
çŸåšã®ååŒçªå·ã¯æ¬¡ã®ãšããã§ãã
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
ããŒãžã®å 容ãèŠãŠã¿ãŸãããã pageinspect æ¡åŒµæ©èœã® heap_page_items é¢æ°ã䜿çšãããšããã€ã³ã¿ãŒãšè¡ã®ããŒãžã§ã³ã«é¢ããæ å ±ãååŸã§ããŸãã
=> 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
PostgreSQL ã®ããŒããšããèšèã¯ããŒãã«ãæãããšã«æ³šæããŠãã ããã ããããã®çšèªã®å¥åŠãªäœ¿ãæ¹ã§ã - ããŒãã¯ç¥ãããŠããŸã
ãã®é¢æ°ã¯ãç解ãã«ãã圢åŒã§ããŒã¿ãããã®ãŸãŸã衚瀺ããŸãã ãããç解ããããã«ãæ å ±ã®äžéšã ããæ®ããŠè§£èªããŸãã
=> 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)
ç§ãã¡ããã£ãããšã¯æ¬¡ã®ãšããã§ãã
- ã€ã³ããã¯ã¹çªå·ã«ãŒããè¿œå ããŠãt_ctid: (ããŒãžçªå·ãã€ã³ããã¯ã¹çªå·) ãšåãã«èŠããããã«ããŸããã
- lp_flags ãã€ã³ã¿ã®ç¶æ ã解èªããŸããã ããã§ã¯ãéåžžãã§ããããã¯ããã€ã³ã¿ãå®éã«æååã®ããŒãžã§ã³ãåç §ããŠããããšãæå³ããŸãã ä»ã®æå³ã«ã€ããŠã¯åŸã»ã©èŠãŠãããŸãã
- ãã¹ãŠã®æ å ±ãããã®ãã¡ããããŸã§ã«ç¹å®ããããã¢ã¯ XNUMX ã€ã ãã§ãã xmin_committed ããããš xmin_aborted ãããã¯ããã©ã³ã¶ã¯ã·ã§ã³çªå· xmin ãã³ãããããã (äžæ¢ããã) ãã©ããã瀺ããŸãã XNUMX ã€ã®åæ§ã®ãããã¯ãã©ã³ã¶ã¯ã·ã§ã³çªå· xmax ãåç §ããŸãã
äœãèŠããŸããïŒ è¡ãæ¿å ¥ãããšãããŒãã« ããŒãžã«ã€ã³ããã¯ã¹çªå· 1 ã衚瀺ãããè¡ã®æåã§å¯äžã®ããŒãžã§ã³ãæããŸãã
æååããŒãžã§ã³ã§ã¯ãxmin ãã£ãŒã«ãã«çŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³çªå·ãå ¥åãããŸãã ãã©ã³ã¶ã¯ã·ã§ã³ã¯ãŸã ã¢ã¯ãã£ãã§ãããããxmin_committed ããããš xmin_aborted ãããã¯äž¡æ¹ãšãèšå®ãããŠããŸããã
è¡ããŒãžã§ã³ ctid ãã£ãŒã«ãã¯åãè¡ãåç §ããŸãã ããã¯ãæ°ããããŒãžã§ã³ãååšããªãããšãæå³ããŸãã
ãã®ããŒãžã§ã³ã®è¡ã¯åé€ãããŠãããææ°ã§ãããããxmax ãã£ãŒã«ãã«ã¯ãããŒã®æ°å€ 0 ãå ¥åãããŸãã xmax_aborted ããããèšå®ãããŠããããããã©ã³ã¶ã¯ã·ã§ã³ã¯ãã®æ°å€ã«æ³šæãæããŸããã
ãã©ã³ã¶ã¯ã·ã§ã³çªå·ã«æ å ±ããããè¿œå ããŠãå¯èªæ§ãåäžãããããã«ããäžæ©é²ããŠã¿ãŸãããã ãªã¯ãšã¹ãã¯è€æ°åå¿ èŠã«ãªããããé¢æ°ãäœæããŸãããã
=> 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;
ãã®åœ¢åŒã§ã¯ãè¡ããŒãžã§ã³ã®ããããŒã§äœãèµ·ãã£ãŠããããããæ確ã«ãªããŸãã
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
åæ§ã§ã¯ãããŸããã詳现床ã¯å€§å¹ ã«å£ããŸãããç䌌å xmin ããã³ xmax ã䜿çšããŠããŒãã«èªäœããæ å ±ãååŸã§ããŸãã
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
åºå®
ãã©ã³ã¶ã¯ã·ã§ã³ãæ£åžžã«å®äºããå Žåã¯ããã®ã¹ããŒã¿ã¹ãèŠããŠããå¿ èŠããããŸãããã©ã³ã¶ã¯ã·ã§ã³ãã³ããããããŠããããšã«æ³šæããŠãã ããã ãããè¡ãã«ã¯ãXACT ãšåŒã°ããæ§é ã䜿çšãããŸã (ããŒãžã§ã³ 10 ããåã§ã¯ãCLOG (ã³ããã ãã°) ãšåŒã°ããŠããŸããããã®ååã¯ä»ã§ãããŸããŸãªå Žæã§èŠã€ããããšãã§ããŸã)ã
XACT ã¯ã·ã¹ãã ã«ã¿ãã° ããŒãã«ã§ã¯ãããŸããã ããã㯠PGDATA/pg_xact ãã£ã¬ã¯ããªå ã®ãã¡ã€ã«ã§ãã è¡ããŒãžã§ã³ ããããŒãšåæ§ã«ããã©ã³ã¶ã¯ã·ã§ã³ããšã«ã³ããããšäžæ¢ã® XNUMX ã€ã®ãããããããŸãã ãã®æ å ±ã¯äŸ¿å®äžè€æ°ã®ãã¡ã€ã«ã«åå²ãããŠããŸãããåçµãæ€èšããéã«ãã®åé¡ã«æ»ããŸãã ãããã®ãã¡ã€ã«ã®æäœã¯ãä»ã®ãã¡ã€ã«ãšåæ§ã«ããŒãžããšã«å®è¡ãããŸãã
ãããã£ãŠãXACT ã§ãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããããšããã®ãã©ã³ã¶ã¯ã·ã§ã³ã«å¯ŸããŠã³ããããããããããèšå®ãããŸãã ã³ãããäžã«èµ·ããããšã¯ããã ãã§ã (ãã ããé²ç»åã®ãã°ã«ã€ããŠã¯ãŸã 話ããŠããŸãã)ã
å¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ãå ã»ã©ç¢ºèªããããŒãã« ããŒãžã«ã¢ã¯ã»ã¹ãããšãããã€ãã®è³ªåã«çããå¿ èŠããããŸãã
- xminãã©ã³ã¶ã¯ã·ã§ã³ã¯å®äºããŸããã? ããã§ãªãå Žåãæååã®äœæãããããŒãžã§ã³ã¯è¡šç€ºãããªãã¯ãã§ãã
ãã®ãã§ãã¯ã¯ãã€ã³ã¹ã¿ã³ã¹ã®å ±æã¡ã¢ãªã«ãã ProcArray ãšåŒã°ããå¥ã®æ§é ã調ã¹ãããšã«ãã£ãŠå®è¡ãããŸãã ããã«ã¯ããã¹ãŠã®ã¢ã¯ãã£ããªããã»ã¹ã®ãªã¹ããå«ãŸããŠãããããããã®çŸåšã® (ã¢ã¯ãã£ããª) ãã©ã³ã¶ã¯ã·ã§ã³ã®çªå·ã瀺ãããŸãã - å®äºããå Žåãã©ã®ããã«ããŠã³ããããŸãã¯ãã£ã³ã»ã«ããã®ã§ãããã? ãã£ã³ã»ã«ãããšãè¡ããŒãžã§ã³ã衚瀺ãããªããªããŸãã
ããããŸãã« XACT ã®ç®çã§ãã ãã ããXACT ã®æåŸã®ããŒãžã¯ RAM ã®ãããã¡ã«ä¿åãããŸãããXACT ãæ¯åãã§ãã¯ããã®ã¯äŸç¶ãšããŠã³ã¹ããããããŸãã ãããã£ãŠããã©ã³ã¶ã¯ã·ã§ã³ ã¹ããŒã¿ã¹ã決å®ããããšãæååããŒãžã§ã³ã® xmin_committed ããããš xmin_aborted ãããã«æžã蟌ãŸããŸãã ãããã®ãããã®ãããããèšå®ãããŠããå Žåããã©ã³ã¶ã¯ã·ã§ã³ xmin ã®ç¶æ ã¯æ¢ç¥ã§ãããšã¿ãªããã次ã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ XACT ã«ã¢ã¯ã»ã¹ããå¿ èŠããªããªããŸãã
ãããã®ããããæ¿å ¥ãè¡ããã©ã³ã¶ã¯ã·ã§ã³èªäœã«ãã£ãŠèšå®ãããªãã®ã¯ãªãã§ãã? æ¿å ¥ãçºçãããšãããã©ã³ã¶ã¯ã·ã§ã³ã¯ãããæåãããã©ããããŸã ç¥ããŸããã ãããŠãã³ãããã®æç¹ã§ã¯ãã©ã®ããŒãžã®ã©ã®è¡ãå€æŽãããã®ãã¯ãã¯ãæ確ã§ã¯ãããŸããã ãã®ãããªããŒãžã¯ããããããå¯èœæ§ããããããããæèšããããšã¯æçã§ã¯ãããŸããã ããã«ãäžéšã®ããŒãžã¯ãããã¡ ãã£ãã·ã¥ãããã£ã¹ã¯ã«è¿œãåºãããå¯èœæ§ããããŸãã ããããå€æŽããããã«ããããå床èªã¿åããšãã³ããããå€§å¹ ã«é ããªããŸãã
ç¯çŽã®æ¬ ç¹ã¯ãå€æŽåŸããããããã©ã³ã¶ã¯ã·ã§ã³ (åçŽãªèªã¿åã - SELECT ãå®è¡ãããã©ã³ã¶ã¯ã·ã§ã³ã§ãã£ãŠã) ããããã¡ãŒ ãã£ãã·ã¥å ã®ããŒã¿ ããŒãžãå€æŽãå§ããå¯èœæ§ãããããšã§ãã
ããã§ã¯ãå€æŽãä¿®æ£ããŸãããã
=> COMMIT;
ããŒãžã«ã¯äœãå€åããããŸãã (ãã ãããã©ã³ã¶ã¯ã·ã§ã³ ã¹ããŒã¿ã¹ããã§ã« XACT ã«èšé²ãããŠããããšãããããŸã)ã
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
ããã§ãæåã«ããŒãžã«ã¢ã¯ã»ã¹ãããã©ã³ã¶ã¯ã·ã§ã³ã¯ãxmin ãã©ã³ã¶ã¯ã·ã§ã³ ã¹ããŒã¿ã¹ã決å®ãããããæ å ±ãããã«æžã蟌ãå¿ èŠããããŸãã
=> 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)
åé€
è¡ãåé€ããããšãçŸåšã®åé€ãã©ã³ã¶ã¯ã·ã§ã³ã®çªå·ãçŸåšã®ããŒãžã§ã³ã® xmax ãã£ãŒã«ãã«æžã蟌ãŸããxmax_aborted ããããã¯ãªã¢ãããŸãã
ã¢ã¯ãã£ããªãã©ã³ã¶ã¯ã·ã§ã³ã«å¯Ÿå¿ãã xmax ã®èšå®å€ãè¡ããã¯ãšããŠæ©èœããããšã«æ³šæããŠãã ããã å¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ããã®è¡ãæŽæ°ãŸãã¯åé€ãããå Žåããã©ã³ã¶ã¯ã·ã§ã³ xmax ãå®äºãããŸã§åŸ æ©ããå¿ èŠããããŸãã ãããã¯ã«ã€ããŠã¯åŸã»ã©è©³ãã説æããŸãã çŸæç¹ã§ã¯ãè¡ããã¯ã®æ°ãç¡å¶éã§ããããšã«æ³šæããŠãã ããã ããã㯠RAM ã®ã¹ããŒã¹ãå æããããã®æ°ã«ãã£ãŠã·ã¹ãã ã®ããã©ãŒãã³ã¹ãäœäžããããšã¯ãããŸããã 確ãã«ããé·ãããã©ã³ã¶ã¯ã·ã§ã³ã«ã¯ä»ã«ãæ¬ ç¹ããããŸãããããã«ã€ããŠã¯åŸã»ã©èª¬æããŸãã
è¡ãåé€ããŸãããã
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
ãã©ã³ã¶ã¯ã·ã§ã³çªå·ã xmax ãã£ãŒã«ãã«æžã蟌ãŸããŠããŸãããæ å ±ããããèšå®ãããŠããªãããšãããããŸãã
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
ãã£ã³ã»ã«
å€æŽã®äžæ¢ã¯ã³ããããšåæ§ã«æ©èœããŸãããXACT ã§ã®ã¿ãäžæ¢ããããããããã©ã³ã¶ã¯ã·ã§ã³ã«èšå®ãããŸãã å ã«æ»ãã®ã¯ã³ãããããã®ãšåããããç°¡åã§ãã ãã®ã³ãã³ã㯠ROLLBACK ãšåŒã°ããŸãããå€æŽã¯ããŒã«ããã¯ãããŸããããã©ã³ã¶ã¯ã·ã§ã³ã«ãã£ãŠããŒã¿ ããŒãžå ã§å€æŽãããå 容ã¯ãã¹ãŠå€æŽãããŸããã
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
ããŒãžãã¢ã¯ã»ã¹ããããšãã¹ããŒã¿ã¹ããã§ãã¯ãããxmax_aborted ãã³ã ããããè¡ããŒãžã§ã³ã«èšå®ãããŸãã xmax çªå·èªäœã¯ããŒãžäžã«æ®ããŸããã誰ããããèŠãŸããã
=> 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)
ã¢ããããŒã
æŽæ°ã¯ãæåã«è¡ã®çŸåšã®ããŒãžã§ã³ãåé€ããŠããæ°ããããŒãžã§ã³ãæ¿å ¥ãããã®ããã«æ©èœããŸãã
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
ã¯ãšãªã«ãã次㮠XNUMX è¡ãçæãããŸã (æ°ããããŒãžã§ã³)ã
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
ãã ããããŒãžã«ã¯äž¡æ¹ã®ããŒãžã§ã³ã衚瀺ãããŸãã
=> 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)
åé€ãããããŒãžã§ã³ã«ã¯ãxmax ãã£ãŒã«ãã®çŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³çªå·ãããŒã¯ãããŸãã ããã«ãåã®ãã©ã³ã¶ã¯ã·ã§ã³ããã£ã³ã»ã«ãããããããã®å€ã¯å€ãå€ã«äžæžããããŸãã ãŸããçŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã¹ããŒã¿ã¹ããŸã äžæã§ãããããxmax_aborted ãããã¯ã¯ãªã¢ãããŸãã
è¡ã®æåã®ããŒãžã§ã³ã¯ãXNUMX çªç® (t_ctid ãã£ãŒã«ã) ãæ°ããããŒãžã§ã³ãšããŠåç §ããããã«ãªããŸããã
XNUMX çªç®ã®ã€ã³ããã¯ã¹ã¯ã€ã³ããã¯ã¹ ããŒãžã«è¡šç€ºãããXNUMX çªç®ã®è¡ã¯ããŒãã« ããŒãžã® XNUMX çªç®ã®ããŒãžã§ã³ãåç §ããŸãã
åé€ã®å Žåãšåæ§ã«ãè¡ã®æåã®ããŒãžã§ã³ã® xmax å€ã¯ãè¡ãããã¯ãããŠããããšã瀺ããŸãã
ããŠãååŒãå®äºããŸãããã
=> COMMIT;
玢åŒ
ãããŸã§ã¯ããŒãã« ããŒãžã«ã€ããŠã®ã¿èª¬æããŠããŸããã ã€ã³ããã¯ã¹ã®å éšã§ã¯äœãèµ·ãã£ãŠããã®ã§ãããã?
ã€ã³ããã¯ã¹ ããŒãžã®æ å ±ã¯ãã€ã³ããã¯ã¹ã®ç¹å®ã®çš®é¡ã«ãã£ãŠå€§ããç°ãªããŸãã ãŸããåãçš®é¡ã®ã€ã³ããã¯ã¹ã§ããããŸããŸãªçš®é¡ã®ããŒãžããããŸãã ããšãã°ãB ããªãŒã«ã¯ã¡ã¿ããŒã¿ ããŒãžãšãéåžžã®ãããŒãžããããŸãã
ãã ããããŒãžã«ã¯éåžžãè¡ãžã®ãã€ã³ã¿ãŒã®é åãšè¡èªäœãå«ãŸããŸã (è¡šããŒãžãšåæ§)ã ããã«ãããŒãžã®æåŸã«ã¯ç¹å¥ãªããŒã¿çšã®ã¹ããŒã¹ããããŸãã
ã€ã³ããã¯ã¹å ã®è¡ã¯ãã€ã³ããã¯ã¹ã®çš®é¡ã«å¿ããŠéåžžã«ç°ãªãæ§é ãæã€ããšããããŸãã ããšãã°ãB ããªãŒã®å ŽåããªãŒã ããŒãžã«é¢é£ããè¡ã«ã¯ãã€ã³ããã¯ã¹ ããŒå€ãšã察å¿ããããŒãã«è¡ãžã®åç § (ctid) ãå«ãŸããŸãã äžè¬ã«ãã€ã³ããã¯ã¹ã¯ãŸã£ããç°ãªãæ¹æ³ã§æ§é åã§ããŸãã
æãéèŠãªç¹ã¯ãã©ã®ã¿ã€ãã®ã€ã³ããã¯ã¹ã«ãè¡ããŒãžã§ã³ãååšããªããšããããšã§ãã ãããã¯ãåè¡ãæ£ç¢ºã« XNUMX ã€ã®ããŒãžã§ã³ã§è¡šããããšä»®å®ããããšãã§ããŸãã ã€ãŸããã€ã³ããã¯ã¹è¡ããããŒã«ã¯ xmin ãã£ãŒã«ããš xmax ãã£ãŒã«ãããããŸããã ã€ã³ããã¯ã¹ããã®ãªã³ã¯ã¯è¡ã®ãã¹ãŠã®ããŒãã« ããŒãžã§ã³ã«ã€ãªãããšæ³å®ã§ããŸãããã®ãããããŒãã«ãèŠãã ãã§ãã©ã³ã¶ã¯ã·ã§ã³ãã©ã®ããŒãžã§ã³ãåç §ããããææ¡ã§ããŸãã (ãã€ãã®ããã«ãããããã¹ãŠã®çå®ã§ã¯ãããŸãããå Žåã«ãã£ãŠã¯ãå¯èŠæ§ãããã«ãã£ãŠããã»ã¹ãæé©åã§ããŸãããããã«ã€ããŠã¯åŸã§è©³ãã説æããŸãã)
åæã«ãã€ã³ããã¯ã¹ ããŒãžã§ã¯ãçŸåšã®ããŒãžã§ã³ãšå€ãããŒãžã§ã³ã®äž¡æ¹ã®ããŒãžã§ã³ãžã®ãã€ã³ã¿ãŒãèŠã€ãããŸãã
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
ä»®æ³ãã©ã³ã¶ã¯ã·ã§ã³
å®éã«ã¯ãPostgreSQL ã¯ãã©ã³ã¶ã¯ã·ã§ã³æ°ããä¿åãã§ããæé©åã䜿çšããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ãããŒã¿ãèªã¿åãã ãã®å Žåãè¡ããŒãžã§ã³ã®å¯èŠæ§ã«ã¯åœ±é¿ããŸããã ãããã£ãŠããµãŒãã¹ ããã»ã¹ã¯æåã«ãã©ã³ã¶ã¯ã·ã§ã³ã«ä»®æ³ xid ãçºè¡ããŸãã ãã®çªå·ã¯ããã»ã¹ ID ãšã·ãŒã±ã³ã¹çªå·ã§æ§æãããŸãã
ãã®çªå·ã®çºè¡ã«ã¯ãã¹ãŠã®ããã»ã¹éã®åæãå¿ èŠãªããããéåžžã«é«éã§ãã ããªãŒãºã«ã€ããŠè©±ããšãã«ãä»®æ³çªå·ã䜿çšããå¥ã®çç±ã«ã€ããŠèª¬æããŸãã
ä»®æ³çªå·ã¯ããŒã¿ ã¹ãããã·ã§ããã§ã¯ãŸã£ããèæ ®ãããŸããã
ããŸããŸãªæç¹ã§ããã§ã«äœ¿çšãããŠããçªå·ãæã€ä»®æ³ãã©ã³ã¶ã¯ã·ã§ã³ãã·ã¹ãã å ã«ååšããå¯èœæ§ããããŸãããããã¯æ£åžžãªçŸè±¡ã§ãã ãã ãããã®ãããªæ°å€ãããŒã¿ ããŒãžã«æžã蟌ãããšã¯ã§ããŸããã次åãã®ããŒãžã«ã¢ã¯ã»ã¹ãããšããã¹ãŠã®æå³ã倱ãããå¯èœæ§ãããããã§ãã
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
ãã©ã³ã¶ã¯ã·ã§ã³ãããŒã¿ã®å€æŽãéå§ãããšãå®éã®äžæã®ãã©ã³ã¶ã¯ã·ã§ã³çªå·ãäžããããŸãã
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³
ãã€ã³ãã貯ãã
SQLã§å®çŸ© ã»ãŒããã€ã³ã (ã»ãŒããã€ã³ã) ã䜿çšãããšããã©ã³ã¶ã¯ã·ã§ã³ãå®å šã«äžæããããšãªãããã©ã³ã¶ã¯ã·ã§ã³ã®äžéšããã£ã³ã»ã«ã§ããŸãã ãããããã©ã³ã¶ã¯ã·ã§ã³ã¯ãã¹ãŠã®å€æŽã«å¯ŸããŠåãã¹ããŒã¿ã¹ãæã¡ãç©ççã«ããŒã¿ã¯ããŒã«ããã¯ãããªããããããã¯äžã®å³ã«ã¯åœãŠã¯ãŸããŸããã
ãã®æ©èœãå®è£ ããã«ã¯ãã»ãŒããã€ã³ãã®ãããã©ã³ã¶ã¯ã·ã§ã³ãããã€ãã®åå¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã«åå²ããŸãã ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ ïŒãµããã©ã³ã¶ã¯ã·ã§ã³ïŒã®ã¹ããŒã¿ã¹ãåå¥ã«ç®¡çã§ããŸãã
ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ã«ã¯ãç¬èªã®çªå· (ã¡ã€ã³ ãã©ã³ã¶ã¯ã·ã§ã³ã®çªå·ããã倧ããçªå·) ãä»ããŠããŸãã ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ã®ã¹ããŒã¿ã¹ã¯ XACT ã«éåžžã®æ¹æ³ã§èšé²ãããŸãããæçµçãªã¹ããŒã¿ã¹ã¯ã¡ã€ã³ ãã©ã³ã¶ã¯ã·ã§ã³ã®ã¹ããŒã¿ã¹ã«ãã£ãŠç°ãªããŸããã¡ã€ã³ ãã©ã³ã¶ã¯ã·ã§ã³ããã£ã³ã»ã«ããããšããã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ããã¹ãŠãã£ã³ã»ã«ãããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ã®ãã¹ãã«é¢ããæ å ±ã¯ãPGDATA/pg_subtrans ãã£ã¬ã¯ããªå ã®ãã¡ã€ã«ã«ä¿åãããŸãã ãã¡ã€ã«ã¯ãXACT ãããã¡ãšåãæ¹æ³ã§ç·šæãããã€ã³ã¹ã¿ã³ã¹ã®å ±æã¡ã¢ãªå ã®ãããã¡ãä»ããŠã¢ã¯ã»ã¹ãããŸãã
ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ãšèªåŸåãã©ã³ã¶ã¯ã·ã§ã³ãæ··åããªãã§ãã ããã èªåŸåãã©ã³ã¶ã¯ã·ã§ã³ã¯äºãã«äŸåããŸãããããã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ã¯äŸåããŸãã éåžžã® PostgreSQL ã«ã¯èªåŸåãã©ã³ã¶ã¯ã·ã§ã³ã¯ãããŸãããããããæè¯ã®ããšã§ããããèªåŸåãã©ã³ã¶ã¯ã·ã§ã³ãå¿ èŠã«ãªãããšã¯éåžžã«çšã§ãããä»ã® DBMS ã«ååšããããšã§æªçšãåŒãèµ·ãããã誰ãããã®è¢«å®³ã«éãããšã«ãªããŸãã
ããŒãã«ãã¯ãªã¢ãããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããŠè¡ãæ¿å ¥ããŸãããã
=> 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)
次ã«ãã»ãŒããã€ã³ããé 眮ããå¥ã®è¡ãæ¿å ¥ããŸãããã
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
txid_current() é¢æ°ã¯ããã¹ãããããã©ã³ã¶ã¯ã·ã§ã³çªå·ã§ã¯ãªããã¡ã€ã³ ãã©ã³ã¶ã¯ã·ã§ã³çªå·ãè¿ãããšã«æ³šæããŠãã ããã
=> 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)
ä¿åãã€ã³ããŸã§ããŒã«ããã¯ã㊠XNUMX è¡ç®ãæ¿å ¥ããŸãããã
=> 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)
ããŒãžã«ã¯ããã£ã³ã»ã«ããããã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ã«ãã£ãŠè¿œå ãããè¡ãåŒãç¶ã衚瀺ãããŸãã
å€æŽãä¿®æ£ããŸãã
=> 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)
ããã§ããã¹ããããåãã©ã³ã¶ã¯ã·ã§ã³ãç¬èªã®ã¹ããŒã¿ã¹ãæã£ãŠããããšãæ確ã«ããããŸãã
ãã¹ãããããã©ã³ã¶ã¯ã·ã§ã³ã¯ SQL ã§æ瀺çã«äœ¿çšã§ããªãããšã«æ³šæããŠãã ãããã€ãŸããçŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³ãå®äºããã«æ°ãããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããããšã¯ã§ããŸããã ãã®ã¡ã«ããºã ã¯ãã»ãŒããã€ã³ãã䜿çšãããšããPL/pgSQL äŸå€ãåŠçãããšããããã³ä»ã®å€ãã®ããç¹æ®ãªã±ãŒã¹ã§æé»çã«ã¢ã¯ãã£ãåãããŸãã
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
ãšã©ãŒãšæäœã®ååæ§
æäœã®å®è¡äžã«ãšã©ãŒãçºçããå Žåã¯ã©ããªããŸãã? ããšãã°ã次ã®ããã«ãªããŸãã
=> 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;
ERROR: current transaction is aborted, commands ignored until end of transaction block
å€æŽãã³ãããããããšããŠããPostgreSQL ã¯äžæ¢ãå ±åããŸãã
=> COMMIT;
ROLLBACK
é害ãçºçãããšãã©ã³ã¶ã¯ã·ã§ã³ãç¶è¡ã§ããªãã®ã¯ãªãã§ãã? å®éã«ã¯ãå€æŽã®äžéšã«ã¢ã¯ã»ã¹ã§ãããããªæ¹æ³ã§ãšã©ãŒãçºçããå¯èœæ§ããããŸããã€ãŸãããã©ã³ã¶ã¯ã·ã§ã³ã§ã¯ãªãããªãã¬ãŒã¿ãŒã®ã¢ãããã¯æ§ã«éåããããšã«ãªããŸãã ãã®äŸã®ããã«ããªãã¬ãŒã¿ãŒã¯ãšã©ãŒã® XNUMX è¡åãæŽæ°ã§ããŸããã
=> 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)
psql ã«ã¯ã倱æãããªãã¬ãŒã¿ãŒã®ã¢ã¯ã·ã§ã³ãããŒã«ããã¯ããããã®ããã«ãã©ã³ã¶ã¯ã·ã§ã³ãç¶ç¶ã§ããã¢ãŒãããããšèšããªããã°ãªããŸããã
=> 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;
ãã®ã¢ãŒãã§ã¯ãpsql ãå®éã«åã³ãã³ãã®åã«æé»çãªã»ãŒã ãã€ã³ããé 眮ãã倱æããå Žåã«ã¯ãããžã®ããŒã«ããã¯ãéå§ããããšã¯æšæž¬ã«é£ãããããŸããã ã»ãŒããã€ã³ãã®èšå® (ã»ãŒããã€ã³ãã«ããŒã«ããã¯ããªãå Žåã§ã) ã«ã¯å€å€§ãªãªãŒããŒãããã䌎ãããããã®ã¢ãŒãã¯ããã©ã«ãã§ã¯äœ¿çšãããŸããã
åºæïŒ habr.com