About
home
Tmax OpenSQL
home
šŸ’Ž

6. PostgreSQLģ˜ Concurrency Control

ģž‘ģ„±ģž
JBH
SHC

Concurrency Control

Concurrency Controlģ€ DBMSģ—ģ„œ ķŠ¹ģ • ė°ģ“ķ„°ģ— ėŒ€ķ•œ ė™ģ‹œ ģ ‘ź·¼ģ„ ģ œģ–“ķ•˜ėŠ” ź²ƒģ„ ģ˜ėÆøķ•©ė‹ˆė‹¤. Concurrency Controlģ€ Multi-version Concurrency Control (MVCC), Strict Two-phase Locking (S2PL), Optimistic Concurrency Control (OCC) ė“± ģ—¬ėŸ¬ ė°©ģ‹ģœ¼ė”œ ģ“ė£Øģ–“ģ§‘ė‹ˆė‹¤. PostgreSQLģ€ MVCC ė°©ģ‹ģ„ ķ™œģš©ķ•˜ģ—¬ Concurrency Controlģ„ ģˆ˜ķ–‰ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤.
MVCCėŠ” ź° ė°ģ“ķ„°ė„¼ ź° ģ‹œģ ģ— ė”°ė¼ ģ—¬ėŸ¬ ė²„ģ „ģœ¼ė”œ ź“€ė¦¬ķ•˜ėŠ” ė°©ģ‹ģž…ė‹ˆė‹¤. ė”°ė¼ģ„œ, ģ‚¬ģš©ģžģ—ź²Œ ė³“ģ—¬ģ§€ėŠ” ė°ģ“ķ„°ėŠ” 1ź°œė”œ ė³“ģ—¬ģ§€ģ§€ė§Œ ģ‹¤ģ œė”œ ķ•“ė‹¹ ė°ģ“ķ„°ėŠ” ģ—¬ėŸ¬ ė²„ģ „ģ˜ ė°ģ“ķ„°(ė ˆģ½”ė“œ)ė”œ ź“€ė¦¬ė˜ź³  ģžˆģŠµė‹ˆė‹¤.
ģ“ė²ˆ ģž„ģ—ģ„œėŠ” PostgreSQLģ—ģ„œ MVCCė„¼ źµ¬ķ˜„ķ•˜ėŠ” ė°©ė²•ģ„ ģ„¤ėŖ…ķ•˜ź³  ģ“ė„¼ źø°ė°˜ģœ¼ė”œ Isolation Level, Lost Update ė“± DBMSģ—ģ„œ ģ •ģ˜ķ•˜ėŠ” Concurrency ģ“ģŠˆė“¤ģ“ ģ‹¤ģ œė”œ ģ–“ė–»ź²Œ ė‹¤ė£Øģ–“ģ§€ź³  ģžˆėŠ”ģ§€ ķ™•ģøķ•“ė³¼ ź²ƒģž…ė‹ˆė‹¤.
Reference. Bernstein, Philip A., and Nathan Goodman. "Concurrency control in distributed database systems."Ā ACM Computing Surveys (CSUR) 13.2 (1981): 185-221.

Transaction ID

ėŒ€ė‹¤ģˆ˜ģ˜ ė°ģ“ķ„°ė² ģ“ģŠ¤ėŠ” ķŠøėžœģž­ģ…˜ģ„ ź“€ė¦¬ķ•˜ź³  ģžˆź³  ģ“ė„¼ ģœ„ķ•˜ģ—¬ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė¼ėŠ” ź°œė…ģ„ ģ‚¬ģš©ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤. PostgreSQLė„ ģ˜ˆģ™ø ģ—†ģ“ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė„¼ ģ‚¬ģš©ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤. ė³ø ė¬øė‹Øģ—ģ„œėŠ” PostgreSQLģ—ģ„œ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė„¼ ź“€ė¦¬ķ•˜ėŠ” źµ¬ģ”°ģ— ėŒ€ķ•˜ģ—¬ ģ„¤ėŖ…ķ•˜ź² ģŠµė‹ˆė‹¤.
PostgreSQLģ€ 32-bit unsigned integer(1 ~ 2^32 -1)ė”œ ķ‘œķ˜„ė˜ģ–“ ģ•½ 42ģ–µ ź°œģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė„¼ ź°€ģ§ˆ ģˆ˜ ģžˆź³  1ė¶€ķ„° ģ‹œģž‘ķ•˜ģ—¬ ģƒˆė”œģš“ ķŠøėžœģž­ģ…˜ģ“ ģ‹œģž‘ė  ė•Œ ģ¦ź°€ķ•©ė‹ˆė‹¤(0ģ€ invalid tx_idė”œ ģ˜ˆģ•½ė˜ģ–“ ģžˆģŠµė‹ˆė‹¤). PostgreSQLģ€ ģ“ė„¼ ģ›ķ˜•(Circular) ķ˜•ķƒœė”œ ź“€ė¦¬ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤.
ėˆˆģ¹˜ź°€ ė¹ ė„“ė‹¤ė©“ ģ“ źµ¬ģ”°ģ—ģ„œ ė‹¤ģŒź³¼ ź°™ģ€ ģ˜ė¬øģ ģ„ ź°€ģ§ˆ ģˆ˜ ģžˆģŠµė‹ˆė‹¤. ā€œķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė„¼ ė‹¤ ģ“°ź³  ķ•œė°”ķ€“ ėŒė©“ ģ–“ė–”ķ•˜ģ§€?ā€ ģ“ ź°œė…ģ€ Vacuumģ—ģ„œ ģ„¤ėŖ…ķ•  ź²ƒ ģž…ė‹ˆė‹¤. ź·øė•Œź¹Œģ§€ ģ“ ģ˜ė¬øģ„ ė§ˆģŒģ— ķ’ˆź³  ģžˆźøø ė°”ėžė‹ˆė‹¤.

Tuple

ź“€ź³„ķ˜• ė°ģ“ķ„°ė² ģ“ģŠ¤ė„¼ ģµœģ“ˆė”œ ź³ ģ•ˆķ•œ ģ—ė“œź±° F. ģ»¤ė“œėŠ” Relationģ€ ķŠœķ”Œ(Tuple)ė“¤ģ˜ ģ§‘ķ•©ģ“ė¼ź³  ģ •ģ˜ķ•˜ģ˜€ģŠµė‹ˆė‹¤. ģ¼ė°˜ģ ģø ė°ģ“ķ„°ė² ģ“ģŠ¤ģ—ģ„œėŠ” ģ£¼ė”œ Relationģ€ ķ…Œģ“ėø”(Table), ķŠœķ”Œģ€ ķ–‰(Row)ģ˜ ķ˜•ķƒœė”œ ķ‘œķ˜„ė©ė‹ˆė‹¤. ź·øė¦¬ź³  ķŒŒģ¼ ģ‹œģŠ¤ķ…œģ˜ ź“€ģ ģ—ģ„œėŠ” Relationģ€ ķŒŒģ¼, ķŠœķ”Œģ€ ź·ø ģ•ˆģ— źø°ė”ėœ ė ˆģ½”ė“œ(Record)ė“¤ģ— ėŒ€ģ‘ķ•©ė‹ˆė‹¤.
ķ›„ģˆ ķ•  ė‚“ģš©ģ„ ģ›ķ™œķ•˜ź²Œ ģ“ķ•“ķ•˜źø° ģœ„ķ•“ģ„œėŠ” ģš°ė¦¬ź°€ ģ›ė” ģ ģø ģ •ģ˜ė„¼ ķ•˜ėŠ” ģ“ģœ ģ— ėŒ€ķ•“ ģ“ķ•“ķ•˜ź³  ė„˜ģ–“ź°ˆ ķ•„ģš”ź°€ ģžˆģŠµė‹ˆė‹¤. ģ™œėƒķ•˜ė©“ ģš°ė¦¬ź°€ ė‹¤ė£° Concurrency Controlģ€ ģš°ė¦¬ź°€ ģµģˆ™ķ•œ ź°œė…ģø ķ…Œģ“ėø”ź³¼ ķ–‰ź³¼ ź°™ģ€ ė°ģ“ķ„°ė² ģ“ģŠ¤ ź“€ģ ģ“ ģ•„ė‹Œ ķŒŒģ¼ ģ‹œģŠ¤ķ…œģ˜ ź“€ģ ģ—ģ„œ ģ“ė£Øģ–“ģ§€źø° ė•Œė¬øģž…ė‹ˆė‹¤. (ģœ„ ź°œė…ģ€ ķ•„ģˆ˜ģ ģœ¼ė”œ ģ“ķ•“ģ•¼ķ•˜ėŠ” ķ›„ģˆ ķ•  ė‚“ģš©ė“¤ģ˜ źø°ė³ø ģ „ģ œģž…ė‹ˆė‹¤. ģ“ ė¬øģž„ģ“ ģ“ķ•“ė˜ģ§€ ģ•ŠėŠ”ė‹¤ė©“ Concurrency Control ķ•­ėŖ©ģ„ ė‹¤ģ‹œ ķ•œė²ˆ ģ½ėŠ” ź²ƒģ„ ģ¶”ģ²œķ•©ė‹ˆė‹¤.)
Reference. Codd, Edgar F. "A relational model of data for large shared data banks."Ā Communications of the ACM 13.6 (1970): 377-387.
PostgreSQLģ—ģ„œėŠ” Relationģ€ ķ…Œģ“ėø”, ģøė±ģŠ¤, ģ‹œķ€€ģŠ¤ ė“±ģ„ ķ¬ķ•Øķ•˜ėŠ” ź°œė…ģœ¼ė”œ ģ •ģ˜ķ•©ė‹ˆė‹¤. ė”°ė¼ģ„œ, ķŠœķ”Œ ģ—­ģ‹œ ķ•“ė‹¹ ķŠœķ”Œģ“ ķ¬ķ•Øėœ Relationģ— ė”°ė¼ Heap Tuple, Index Tuple ė“±ģœ¼ė”œ ė‚˜ė‰˜ģ–“ģ§‘ė‹ˆė‹¤. PostgreSQLģ—ģ„œ ķŠœķ”Œģ€ ė°ģ“ķ„°ģ˜ ė ˆģ½”ė“œė„¼ ģ˜ėÆøķ•˜ė©°, ė°ģ“ķ„°ģ— ė³€ź²½(INSERT, UPDATE, DELETE)ģ“ ė°œģƒķ•  ė•Œ ģƒˆė”œ ģƒģ„±ė˜ź±°ė‚˜ ė‚“ģš©ģ“ ė³€ź²½ė©ė‹ˆė‹¤.
ė³øė¬øģ—ģ„œėŠ” ķ…Œģ“ėø”ģ„ źø°ģ¤€ģœ¼ė”œ Concurrency Controlģ— ėŒ€ķ•œ ģ„¤ėŖ…ģ„ ģ§„ķ–‰ķ•  ź²ƒģ“ė©°, ģ“ģ— ė”°ė¼ ķž™ ķŠœķ”Œģ„ źø°ģ¤€ģœ¼ė”œ ģ„¤ėŖ…ģ„ ģ§„ķ–‰ķ•˜ź² ģŠµė‹ˆė‹¤.
PostgreSQL ķž™ ķŠœķ”Œģ„ ķ‘œķ˜„ķ•˜ėŠ” źµ¬ģ”°ģ²“ģø HeapTupleDataėŠ” ė‹¤ģŒź³¼ ź°™ģ“ ģ •ģ˜ė˜ģ–“ ģžˆģŠµė‹ˆė‹¤. ģ“ė²ˆ ģž„ģ—ģ„œėŠ” ķ›„ģˆ ķ•  ė‚“ģš©ģ„ ģ“ķ•“ķ•˜ėŠ”ė° ķ•„ģš”ķ•œ ź°’ģ— ėŒ€ķ•“ģ„œė§Œ ģ„øė¶€ģ ģø ģ„¤ėŖ…ģ„ ģ§„ķ–‰ķ•˜ź² ģŠµė‹ˆė‹¤.
HeapTupleData Struct
ģ“ģ¤‘ ģš°ė¦¬ėŠ” ė‹¤ģŒ 4ź°€ģ§€ ź°’ģ„ ģ‚¬ģš©ķ•˜ģ—¬ Concurrency Controlģ„ ģ„¤ėŖ…ķ•  ź²ƒģž…ė‹ˆė‹¤.
ā€¢
t_xmin: ķŠœķ”Œģ“ INSERT ė  ė•Œģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””
ā€¢
t_xmax: ķŠœķ”Œģ“ DELETE ė˜ź±°ė‚˜ LOCKING ė ė•Œģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””
ā€¢
t_cid: ķŠœķ”Œģ˜ INSERT, DELETEģ— ėŒ€ķ•œ ķŠøėžœģž­ģ…˜ ė‚“ COMMAND ID (0ė¶€ķ„° ģ¦ź°€ķ•©ė‹ˆė‹¤.)
ā€¢
t_ctid: ķ˜„ģž¬ ķŠœķ”Œ(ģžźø° ģžģ‹ )ģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė”” ķ˜¹ģ€ ģƒˆė”œģš“ ķŠœķ”Œ(ė‹¤ģŒ ė ˆģ½”ė“œ)ģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””

Operation

ė³ø ė¬øė‹Øģ—ģ„œėŠ” ź° ģæ¼ė¦¬ź°€ ģˆ˜ķ–‰ė  ė•Œ ķŠœķ”Œģ“ ģ–“ė–»ź²Œ ģ¶”ź°€ė˜ź³  ė°”ė€ŒėŠ”ģ§€ģ— ėŒ€ķ•œ ģ„¤ėŖ…ģ„ ģ§„ķ–‰ķ•˜ź² ģŠµė‹ˆė‹¤. ģ¼ė‹Ø ģæ¼ė¦¬ģ˜ INSERT, UPDATE, DELETEģ™€ ķŠœķ”Œģ— ėŒ€ķ•œ INSERT, DELETEė„¼ źµ¬ė¶„ķ•“ģ„œ ģ“ķ•“ķ•“ģ•¼ ķ•©ė‹ˆė‹¤. ė³ø ė¬øė‹Øģ—ģ„œėŠ” ģ“ė„¼ ģœ„ķ•“ ķŠœķ”Œģ— ėŒ€ķ•œ INSERT, DELETEė„¼ ź°ź° T_INSERT, T_DELETEė”œ ėŖ…ģ‹œķ•˜ź² ģŠµė‹ˆė‹¤.
ģŠ¤ķ¬ģ¼ėŸ¬

Insert

ė‹¤ģŒ ģ½”ė“œģ™€ ķ‘œėŠ” ź°ź° ķ•˜ė‚˜ģ˜ INSERTė„¼ ķ¬ķ•Øķ•˜ėŠ” ķŠøėžœģž­ģ…˜ź³¼ ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ“ ģ™„ė£Œėœ ķ›„ģ˜ ķŠœķ”Œģ˜ ģƒķƒœģž…ė‹ˆė‹¤.
# txid = 100 BEGIN; INSERT INTO example_table VALUES('A'); -- COMMAND 0 COMMIT;
SQL
ė³µģ‚¬
1
100
0
0
(0, 1)
ā€˜Aā€™
COMMAND 0ģ€ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ź°€ 100ģ¼ ė•Œ ģˆ˜ķ–‰ė˜ģ–“ 1ė²ˆ ķŠœķ”Œģ“ T_INSERT ė˜ģ—ˆģœ¼ėƀė”œ t_xmin = 100ģž…ė‹ˆė‹¤.
ģœ„ ģ‹œė‚˜ė¦¬ģ˜¤ģ—ģ„œ 1ė²ˆ ķŠœķ”Œģ€ T_DELETE ė˜ģ§€ ģ•Šģ•˜ģœ¼ėƀė”œ t_xmax = 0 ģž…ė‹ˆė‹¤.
ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ—ģ„œ 1ė²ˆ ķŠœķ”Œģ€ ģ²«ė²ˆģ§ø COMMANDģ“ė‹¤. Command IDėŠ” 0ė¶€ķ„° ģ‹œģž‘ķ•˜ėƀė”œ t_cid = 0ģž…ė‹ˆė‹¤.
ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ—ģ„œ ģƒˆė”œ ģƒģ„±ėœ ā€˜Aā€™ė„¼ ė‹“ź³  ģžˆėŠ” ROWėŠ” ģœ„ ģ‹œė‚˜ė¦¬ģ˜¤ģ—ģ„œ ė³€ź²½ėœ ģ ģ“ ģ—†ģœ¼ėƀė”œ 1ė²ˆ ķŠœķ”Œģ˜ t_ctidėŠ” ģžźø° ģžģ‹ ģ„ ģ§€ģ¹­ķ•˜ź²Œ ė©ė‹ˆė‹¤. (0, 1)ģ˜ ģ˜ėÆøėŠ” 0ė²ˆ ķŽ˜ģ“ģ§€ģ˜ Offset = 1ģ— ķ•“ė‹¹ ķŠœķ”Œģ“ ģœ„ģ¹˜ķ•œė‹¤ėŠ” ģ˜ėÆøģž…ė‹ˆė‹¤.

Update

# txid = 105 BEGIN; UPDATE example_table SET data = 'B' WHERE ...; -- COMMAND 0 UPDATE example_table SET data = 'C' WHERE ...; -- COMMAND 1 COMMIT;
SQL
ė³µģ‚¬
ė‹¤ģŒ ķ‘œėŠ” COMMAND 0ģ“ ģˆ˜ķ–‰ėœ ģ“ķ›„ģ˜ ķŠœķ”Œģ˜ ģƒķƒœģž…ė‹ˆė‹¤.
no
t_xmin
t_xmax
t_cid
t_ctid
data
1
100
105
0
(0, 2)
ā€˜Aā€™
2
105
0
0
(0, 2)
ā€˜Bā€™
ģƒģˆ ķ•˜ģ˜€ė˜ INSERT ģ˜ˆģ‹œģ—ģ„œ ģ“ģ–“ģ§„ ģ˜ˆģ‹œģž…ė‹ˆė‹¤. 1ė²ˆ ķŠœķ”Œģ€ INSERT ģ˜ˆģ‹œģ—ģ„œ T_INSERTėœ ķŠœķ”Œģž…ė‹ˆė‹¤. COMMAND 0 ģˆ˜ķ–‰ ģ“ķ›„, 1ė²ˆ ķŠœķ”Œģ—ėŠ” ė‹¤ģŒź³¼ ź°™ģ€ ė³€ķ™”ź°€ ė°œģƒķ•©ė‹ˆė‹¤.
t_xminģ˜ ź°’ģ€ T_INSERT ģ‹œģ ģ˜ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė„¼ ģ˜ėÆøķ•˜ėƀė”œ ė°”ė€Œģ§€ ģ•ŠģŠµė‹ˆė‹¤. t_xmaxģ˜ ź²½ģš°, 2ė²ˆ ķŠœķ”Œģ“ T_INSERT ė˜ė©“ģ„œ T_DELETEėœ ģƒķ™©ģ“ėƀė”œ t_xmaxėŠ” T_DELETEź°€ ģ“ė£Øģ–“ģ§„ ģ‹œģ  ģ¦‰, COMMAND 0ģ“ ģˆ˜ķ–‰ėœ ģ‹œģ ģø 105ė”œ ź°±ģ‹ ė©ė‹ˆė‹¤. t_cidėŠ” ķ•“ė‹¹ ķŠœķ”Œģ„ ė°œģƒģ‹œķ‚Ø COMMAND IDģ“ėƀė”œ ė°”ė€Œģ§€ ģ•ŠģŠµė‹ˆė‹¤. t_ctidėŠ” 1ė²ˆ ķŠœķ”Œģ“ T_DELETEė˜ź³  ģƒˆė”œ ģƒģ„±ėœ ė ˆģ½”ė“œģø 2ė²ˆ ķŠœķ”Œģ„ ź°€ė¦¬ķ‚¤ź²Œ ė©ė‹ˆė‹¤. ė”°ė¼ģ„œ, ķ•“ė‹¹ ź°’ģ€ 2ė²ˆ ķŠœķ”Œģ˜ ģœ„ģ¹˜ė”œ ź°±ģ‹ ė©ė‹ˆė‹¤.
1ė²ˆ ķŠœķ”Œģ˜ ź²½ģš°, ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ź°€ 105ģø ķŠøėžœģž­ģ…˜ ė‚“ģ—ģ„œ ģˆ˜ķ–‰ė˜ģ—ˆģœ¼ėƀė”œ t_xmin = 105 ģž…ė‹ˆė‹¤. ģœ„ ķŠøėžœģž­ģ…˜ģ˜ ģ²«ė²ˆģ§ø COMMAND ģ“ėƀė”œ COMMAND IDėŠ” 0 ģž…ė‹ˆė‹¤. ė”°ė¼ģ„œ, t_cid = 0 ģ“ ė©ė‹ˆė‹¤. ģ“ģ œ ė§‰ T_INSERTėœ ķŠœķ”Œģ“ėƀė”œ t_xmax = 0 ģž…ė‹ˆė‹¤. ė‹¤ģŒ ķŠœķ”Œģ“ ģ—†ģœ¼ėƀė”œ t_ctidėŠ” ģžźø° ģžģ‹ ģ„ ź°€ė¦¬ķ‚¤ź²Œ ė©ė‹ˆė‹¤.
ė‹¤ģŒ ķ‘œėŠ” COMMAND 1ģ“ ģˆ˜ķ–‰ėœ ģ“ķ›„ģ˜ ķŠœķ”Œ ģƒķƒœģž…ė‹ˆė‹¤.
no
t_xmin
t_xmax
t_cid
t_ctid
data
1
100
105
0
(0, 2)
ā€˜Aā€™
2
105
105
0
(1, 1)
ā€˜Bā€™
3
105
0
1
(1, 1)
ā€˜Cā€™
COMMAND 0, COMMAND 1 ėŖØė‘ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ź°€ 105 ģ¼ ė•Œ ģˆ˜ķ–‰ė˜ģ–“ ķ•“ė‹¹ ķŠœķ”Œģ“ T_INSERT ė˜ģ—ˆģœ¼ėƀė”œ t_xminģ€ 2, 3ėŖØė‘ 105 ģž…ė‹ˆė‹¤.
2ė²ˆ ķŠœķ”Œģ˜ ź²½ģš°, COMMAND 1ģ“ ģˆ˜ķ–‰ė˜ė©“ģ„œ T_DELETE ė˜ģ—ˆģŠµė‹ˆė‹¤. ķ•“ė‹¹ ģ‹œģ  ģ—­ģ‹œ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ź°€ 105ģ¼ ė•Œģ“ėƀė”œ 2ė²ˆ ķŠœķ”Œģ˜ t_xmaxėŠ” 105 ź°€ ė©ė‹ˆė‹¤. 3ė²ˆ ķŠœķ”Œģ˜ ź²½ģš°, ģƒˆė”œ T_INSERT ė˜ģ—ˆģœ¼ėƀė”œ t_xmaxėŠ” 0 ģž…ė‹ˆė‹¤.
ķ•œ ķŠøėžœģž­ģ…˜ ė‚“ģ—ģ„œ Command IDėŠ” 0ė¶€ķ„° ģ¦ź°€ķ•©ė‹ˆė‹¤. ģœ„ ģ˜ˆģ‹œģ—ģ„œ COMMAND 0, COMMAND 1ė”œ ź° ģæ¼ė¦¬ė„¼ ģ§€ģ¹­ķ•œ ģ“ģœ ģž…ė‹ˆė‹¤. 2ė²ˆ ķŠœķ”Œģ€ COMMAND 0 ģ— ėŒ€ķ•œ ķŠœķ”Œģ“ėƀė”œ t_cidėŠ” 0 ģž…ė‹ˆė‹¤. ź°™ģ€ ģ›ė¦¬ė”œ 3ė²ˆ ķŠœķ”Œģ˜ t_cid = 1 ģž…ė‹ˆė‹¤.
ė°ģ“ķ„°ģ— ėŒ€ķ•“ 2ė²ˆ ķŠœķ”Œģ“ T_DELETEė˜ź³  3ė²ˆ ķŠœķ”Œģ“ T_INSERT ė˜ģ—ˆģŠµė‹ˆė‹¤. 2ė²ˆ ķŠœķ”Œģ˜ ė‹¤ģŒ ź°’ģ€ 3ė²ˆ ķŠœķ”Œģ“ėƀė”œ 2ė²ˆ ķŠœķ”Œģ˜ t_ctid = (1, 1)ģ“ ė˜ź³  3ė²ˆ ķŠœķ”Œģ€ ė‹¤ģŒ ķŠœķ”Œģ“ ģ—†ģ–“ ģžźø° ģžģ‹ ģ„ ź°€ė¦¬ķ‚¤ėƀė”œ t_ctid = (1, 1) ģ“ ė©ė‹ˆė‹¤. ģ—¬źø°ģ„œ t_ctidģ˜ ģ²«ė²ˆģ§ø ź°’ģ€ ķŽ˜ģ“ģ§€ Numberė„¼ ģ§€ģ¹­ķ•˜ź³  ė‘ė²ˆģ§ø ź°’ģ€ ķŽ˜ģ“ģ§€ ė‚“ģ—ģ„œ ķ•“ė‹¹ ķŠœķ”Œģ˜ Offsetģ„ ģ˜ėÆøķ•©ė‹ˆė‹¤. ė”°ė¼ģ„œ, ģœ„ ģ˜ˆģ‹œģ—ģ„œėŠ” COMMAND 1 ģˆ˜ķ–‰ ź²°ź³¼ ģƒˆė”œģš“ ķŽ˜ģ“ģ§€ź°€ ģƒģ„±ė˜ģ—ˆź³  3ė²ˆ ķŠœķ”Œģ€ ģƒˆė”œģš“ ķŽ˜ģ“ģ§€ģ— ģ¶”ź°€ė˜ģ—ˆģŒģ„ ģ˜ėÆøķ•©ė‹ˆė‹¤.

Delete

# txid = 111 BEGIN; DELETE FROM example_table WHERE ...; -- COMMAND 0 COMMIT;
SQL
ė³µģ‚¬
no
t_xmin
t_xmax
t_cid
t_ctid
data
1
100
105
0
(0, 2)
ā€˜Aā€™
2
105
105
0
(1, 1)
ā€˜Bā€™
3
105
111
1
(1, 1)
ā€˜Cā€™
PostgreSQLģ€ DELETEź°€ ė°œģƒķ•˜ģ˜€ģ„ ė•Œ, ģ‹¤ģ œė”œ ķŠœķ”Œģ„ ģ‚­ģ œķ•˜ģ§€ ģ•Šź³  źø°ģ”“ģ˜ ķŠœķ”Œ(ė§ˆģ§€ė§‰ ė ˆģ½”ė“œ)ģ˜ t_xmax ź°’ė§Œģ„ ķ˜„ģž¬ ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ė”œ ź°±ģ‹ ķ•©ė‹ˆė‹¤. ģœ„ģ˜ ģ˜ˆģ‹œė„¼ ė³“ė©“ źø°ģ”“ģ— ģ”“ģž¬ķ–ˆė˜ 3ė²ˆ ķŠœķ”Œģ˜ t_xmax ź°’ė§Œģ„ 111ė”œ ź°±ģ‹ ķ•˜ģ˜€ģŒģ„ ķ™•ģøķ•  ģˆ˜ ģžˆģŠµė‹ˆė‹¤.
ģœ„ģ˜ ģŠ¤ķ¬ģ¼ėŸ¬ ķ•­ėŖ©ģ„ ģ½ģ§€ ģ•Šģ•˜ė‹¤ė©“ ģœ„ė”œ ģ˜¬ė¼ź°€ ģ½ģ–“ė³“ė©“ģ„œ ģ •ė¦¬ķ•“ė³“źøø ė°”ėžė‹ˆė‹¤.

Commit Log

PostgreSQLģ€ ź° ķŠøėžœģž­ģ…˜ģ˜ ģƒķƒœė„¼ Commit Logģ— źø°ė”ķ•©ė‹ˆė‹¤. ClogėŠ” ķŠøėžœģž­ģ…˜ ģˆ˜ķ–‰ ģ¤‘, ź³µģœ  ė©”ėŖØė¦¬ģ— ķ• ė‹¹ė˜ģ–“ ģ‚¬ģš©ė©ė‹ˆė‹¤.
PostgreSQLģ—ģ„œėŠ” ķŠøėžœģž­ģ…˜ģ˜ ģƒķƒœė„¼ IN_PROGRESS, COMMITTED, ABORTED, SUB_COMMITTEDė”œ źµ¬ė¶„ķ•©ė‹ˆė‹¤. ė³ø ė¬øģ„œģ—ģ„œėŠ” Sub-Transactionģ˜ ģƒķƒœė„¼ ė‚˜ķƒ€ė‚“ėŠ” SUB_COMMITTED ģƒķƒœģ— ėŒ€ķ•“ģ„œėŠ” ģ„¤ėŖ…ķ•˜ģ§€ ģ•Šź² ģŠµė‹ˆė‹¤.
IN_PROGRESSėŠ” ķ˜„ģž¬ ģˆ˜ķ–‰ģ¤‘ģø ķŠøėžœģž­ģ…˜ģž…ė‹ˆė‹¤. ģ—¬źø°ģ„œ ģƒģ„±ėœ ķŠœķ”Œģ€ ģžźø° ģžģ‹ ģ—ź²ŒėŠ” ė³“ģ“ģ§€ė§Œ ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ—ėŠ” ė³“ģ“ģ§€ ģ•Šģ•„ģ•¼ķ•©ė‹ˆė‹¤. COMMITTEDėŠ” ģ»¤ė°‹ģ“ ģ™„ė£Œėœ ķŠøėžœģž­ģ…˜ģž…ė‹ˆė‹¤. ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ€ ģ“ėÆø ģ¢…ė£Œė˜ģ—ˆź³  ģ—¬źø°ģ„œ ģƒģ„±ėœ ķŠœķ”Œģ€ ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ—ė„ ė³“ģ—¬ģ•¼ ķ•©ė‹ˆė‹¤. ABORTEDėŠ” ģ¤‘ė‹Øėœ ķŠøėžœģž­ģ…˜ģ“ė‹¤. ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ€ ģ¢…ė£Œė˜ģ—ˆģœ¼ė‚˜, ģ¤‘ė‹Øė˜ģ—ˆģœ¼ėƀė”œ ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ— ė³“ģ—¬ģ„œėŠ” ģ•ˆė©ė‹ˆė‹¤.

Transaction Snapshot

ķŠøėžœģž­ģ…˜ ģŠ¤ėƒ…ģƒ·ģ€ ķŠ¹ģ • ģ‹œģ ģ˜ ź° ķŠøėžœģž­ģ…˜ģ— ėŒ€ķ•œ Active ģ—¬ė¶€ė„¼ źø°ė”ķ•œė‹¤. PostgreSQLģ—ģ„œėŠ” ķŠøėžœģž­ģ…˜ģ“ IN_PROGRESS ģƒķƒœģ“ź±°ė‚˜ ģ•„ģ§ ģ‹œģž‘ķ•˜ģ§€ ģ•Šģ€ ķŠøėžœģž­ģ…˜ģ„ Activeė¼ź³  ģ •ģ˜ķ•©ė‹ˆė‹¤.
ķŠøėžœģž­ģ…˜ ģŠ¤ėƒ…ģƒ·ģ€ txid_current_snapshot()ģ„ ķ†µķ•“ ģ”°ķšŒķ•  ģˆ˜ ģžˆģŠµė‹ˆė‹¤. ķŠøėžœģž­ģ…˜ ģŠ¤ėƒ…ģƒ·ģ€ ė‹¤ģŒź³¼ ź°™ģ“ ķ‘œķ˜„ė©ė‹ˆė‹¤.
SELECT txid_current_snapshot(); -- xmin:xmax:xip_list -- 100:108:100,101,106
SQL
ė³µģ‚¬
ķŠøėžœģž­ģ…˜ ģŠ¤ėƒ…ģƒ·ģ˜ źµ¬ģ„± ģš”ģ†ŒėŠ” ė‹¤ģŒź³¼ ź°™ģŠµė‹ˆė‹¤.
ā€¢
xmin: ķ™œģ„± ģƒķƒœģø ķŠøėžœģž­ģ…˜ ģ¤‘ ź°€ģž„ ģ˜¤ėž˜ėœ(ķŠøėžœģž­ģ…˜ ģ•„ģ“ė””ź°€ ė‚®ģ€) ķŠøėžœģž­ģ…˜ģ˜ ģ•„ģ“ė””
ā€¢
xmax: ģ•„ģ§ ķ• ė‹¹ė˜ģ§€ ģ•Šģ€ ķŠøėžœģž­ģ…˜ ģ¤‘ ź°€ģž„ ģ˜¤ėž˜ėœ(ģ²«ė²ˆģ§ø) ķŠøėžœģž­ģ…˜ģ˜ ģ•„ģ“ė””
ā€¢
xip_list: ģŠ¤ėƒ…ģƒ· ģ‹œģ ģ˜ ķ™œģ„±ėœ ķŠøėžœģž­ģ…˜ģ˜ ģ•„ģ“ė””ģ˜ ė¦¬ģŠ¤ķŠø

Visibility

PostgreSQLėŠ” ķŠøėžœģž­ģ…˜ ź²©ė¦¬(Isolation)ė„¼ ģœ„ķ•“ ķŠ¹ģ • ķŠøėžœģž­ģ…˜ ģˆ˜ķ–‰ ģ‹œ, ģ–“ė–¤ ķŠœķ”Œģ“ ė³“ģ—¬ģ§€ź³  ģ•ˆ ė³“ģ—¬ģ§ˆģ§€ė„¼ ź²°ģ •ķ•“ģ•¼ķ•©ė‹ˆė‹¤. ģ“ėŠ” Visibility(ź°€ģ‹œģ„±)ė¼ėŠ” ź°œė…ģœ¼ė”œ źµ¬ķ˜„ė©ė‹ˆė‹¤. VisibilityėŠ” ź° ķŠøėžœģž­ģ…˜ģ—ģ„œ ķŠ¹ģ • ķŠœķ”Œģ˜ ź°€ģ‹œģ„±ģ„ ģ˜ėÆøķ•˜ė©° Visibility Ruleģ— ģ˜ķ•“ ź²°ģ •ė©ė‹ˆė‹¤. ė‚“ė¶€ģ ģœ¼ė”œėŠ” ģˆ˜ ė§Žģ€ ź·œģ¹™ģ“ ģ”“ģž¬ķ•˜ģ§€ė§Œ ė³ø ė¬øģ„œģ—ģ„œėŠ” ė³“ķŽøģ ģø ė‹¤ģŒ 11ź°€ģ§€ ź·œģ¹™ģ— ėŒ€ķ•“ģ„œė§Œ ģ„¤ėŖ…ķ•˜ź² ģŠµė‹ˆė‹¤.

ź·øėƒ„ ė“…ģ‹œė‹¤.

Rule #
status (t_xmin)
status (t_xmax)
value (t_xmin)
value (t_xmax)
snapshot (t_xmin)
snapshot (t_xmax)
visibility
1
ABORTED
X
2
IN_PROGRESS
CURRENT
ZERO
O
3
IN_PROGRESS
CURRENT
NON-ZERO
X
4
IN_PROGRESS
OTHER
X
5
COMMITTED
ACTIVE
X
6
COMMITTED
ABORTED
O
7
COMMITTED
ZERO
O
8
COMMITTED
IN_PROGRESS
CURRENT
X
9
COMMITTED
IN_PROGRESS
NON-ZERO
O
10
COMMITTED
COMMITTED
ACTIVE
O
11
COMMITTED
COMMITTED
IN-ACTIVE
X

ģˆ²ģ„ ė“…ģ‹œė‹¤.

ā€¢
Rule 1 : ķŠœķ”Œģ„ ģƒģ„±ķ•œ(T_INSERT) ķŠøėžœģž­ģ…˜(t_xmin)ģ€ ABORTEDė˜ģ—ˆģŠµė‹ˆė‹¤. ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ ģžģ²“ź°€ ģ¤‘ė‹Øė˜ģ—ˆė‹¤ėŠ” ģ˜ėÆøģ“ėƀė”œ All or Nothingģ˜ Nothingģž…ė‹ˆė‹¤. ė”°ė¼ģ„œ, ķ•“ė‹¹ ķŠœķ”Œģ€ ė³“ģ—¬ģ§€ģ§€ ģ•ŠģŠµė‹ˆė‹¤.
ā€¢
Rule 2,3,4 : ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ“ ģ•„ģ§ IN_PROGRESS ģƒķƒœģž…ė‹ˆė‹¤. ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ“ ģ»¤ė°‹ė˜ģ§€ ģ•Šģ•˜ė‹¤ėŠ” ģ˜ėÆøģ“ėƀė”œ ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ—ģ„œė§Œ ė³“ģ—¬ģ§‘ė‹ˆė‹¤. ė˜ķ•œ ķ•“ė‹¹ ķŠœķ”Œģ“ UPDATE, DELETEģ— ģ˜ķ•“ T_DELETE ė˜ģ—ˆģ„ ė•Œģ—ėŠ” ź°™ģ€ ķŠøėžœģž­ģ…˜ģ—ģ„œė„ ė³“ģ—¬ģ§€ģ§€ ģ•ŠģŠµė‹ˆė‹¤.
ā€¢
Rule 5,6,7,8,9,10,11 : ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ“ COMMITTED ģƒķƒœģž…ė‹ˆė‹¤. ģ¦‰, ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ“ ģ»¤ė°‹ ėœ ģ“ķ›„ģ— ģƒģ„±ėœ ķŠøėžœģž­ģ…˜ģ—ģ„œėŠ” ķ•“ė‹¹ ķŠœķ”Œģ“ ė³“ģ—¬ģ øģ•¼ķ•©ė‹ˆė‹¤. ė‹Ø, ģœ„ģ™€ ė§ˆģ°¬ź°€ģ§€ė”œ ė˜ķ•œ ķ•“ė‹¹ ķŠœķ”Œģ“ UPDATE, DELETEģ— ģ˜ķ•“ T_DELETE ė˜ģ—ˆģ„ ė•Œģ—ėŠ” ė³“ģ—¬ģ§€ģ§€ ģ•ŠģŠµė‹ˆė‹¤.

ė‚˜ė¬“ė„¼ ė“…ģ‹œė‹¤.

ā€¢
Rule 1ģ€ ā€œStatus(t_xmin) = ABORTEDā€ ģž…ė‹ˆė‹¤. ģ¦‰, ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ“ ģ¤‘ė‹Øė˜ģ—ˆė‹¤ėŠ” ģ˜ėÆøģž…ė‹ˆė‹¤. ķŠøėžœģž­ģ…˜ģ€ ģ›ģžģ„±ģ— ģ˜ķ•“ All or Nothingģž…ė‹ˆė‹¤. ģ¤‘ė‹Øėœ ķŠøėžœģž­ģ…˜ģ˜ ķŠœķ”Œģ€ ė°˜ģ˜ė˜ģ§€ ģ•Šģ•„ģ•¼ķ•˜ėƀė”œ ķ˜„ģž¬ ķŠøėžœģž­ģ…˜ģ—ģ„œ ķ•“ė‹¹ ķŠœķ”Œģ€ ė³¼ ģˆ˜ ģ—†ģ–“ģ•¼ķ•©ė‹ˆė‹¤. ģ¦‰, Invisible ģž…ė‹ˆė‹¤.
ā€¢
Rule 2ėŠ” ā€œStatus(t_xmin) = IN_PROGRESSā€. ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ“ ģ•„ģ§ ģˆ˜ķ–‰ ģ¤‘ģ“ė¼ėŠ” ģ˜ėÆøģž…ė‹ˆė‹¤. ģ¦‰, ģ•„ģ§ ģ»¤ė°‹ė˜ģ§€ė„ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ—ģ„œė§Œ ė³¼ ģˆ˜ ģžˆģ–“ģ•¼ķ•©ė‹ˆė‹¤. ā€œt_xmin = current_txidā€ ģ¦‰, ķ•“ė‹¹ ķŠœķ”Œģ€ ķ˜„ģž¬ ķŠøėžœģž­ģ…˜ģ—ģ„œ ģƒģ„±ė˜ģ—ˆė‹¤ėŠ” ģ˜ėÆøģž…ė‹ˆė‹¤. ė§ˆģ§€ė§‰ģœ¼ė”œ T_DELETEė˜ģ—ˆėŠ”ģ§€ė§Œ ģ‚“ķŽ“ė³“ź² ģŠµė‹ˆė‹¤. t_xmax = 0 ģž…ė‹ˆė‹¤. ģš°ė¦¬ėŠ” ģƒģˆ ķ•œ Operation ė¬øė‹Øģ—ģ„œ t_xmaxź°€ 0ģ¼ ź²½ģš°ģ—ėŠ” T_DELETEė˜ģ§€ ģ•Šģ•˜ė‹¤ėŠ” ģ‚¬ģ‹¤ģ„ ģ–øźø‰ ķ•˜ģ˜€ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 2ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Visibleģž…ė‹ˆė‹¤.
ā€¢
Rule 3ėŠ” Rule 2ģ™€ ė‹¬ė¦¬ t_xmax ā‰  0 ģ“ėƀė”œ ķ•“ė‹¹ ķŠœķ”Œģ€ ģ“ėÆø T_DELETE ė˜ģ—ˆģŒģ„ ģ•Œ ģˆ˜ ģžˆģŠµė‹ˆė‹¤. UPDATEė”œ ģøķ•“ ė‹¤ģŒ ė²„ģ „ģ˜ ķŠœķ”Œģ“ ģ”“ģž¬ķ•˜ź±°ė‚˜ ģ‚­ģ œė˜ģ—ˆė‹¤ėŠ” ź²ƒģ„ ģ˜ėÆøķ•©ė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 3ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Invisibleģž…ė‹ˆė‹¤.
ā€¢
Rule 4ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ ģ•„ģ§ IN_PROGRESS ģƒķƒœģ“ė‹ˆ ģ»¤ė°‹ė˜ģ§€ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ģ¦‰, ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ—ģ„œėŠ” ė³“ģ—¬ģ§€ģ§€ ģ•Šģ•„ģ•¼ķ•©ė‹ˆė‹¤. ā€œt_xmin ā‰  current_txidā€ģ— ģ˜ķ•“ ķ•“ė‹¹ ķŠœķ”Œģ€ ķ˜„ģž¬ ķŠøėžœģž­ģ…˜ź³¼ ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ—ģ„œ ģƒģ„±ė˜ģ—ˆģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 4ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Invisibleģž…ė‹ˆė‹¤.
ā€¢
Rule 5ėŠ” ģ”°źøˆ ģ“ģƒķ•©ė‹ˆė‹¤. ģœ„ģ—ģ„œ ė¶„ėŖ… IN_PROGRESS ģ“ź±°ė‚˜ ķ• ė‹¹ė˜ģ§€ ģ•Šģ€ ķŠøėžœģž­ģ…˜ģ“ Activeė¼ź³  ķ–ˆėŠ”ė°, Rule 5ėŠ” COMMITTED ģƒķƒœģž„ģ—ė„ Activeė¼ź³  ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤. ģ“ėŠ” ź°„ėžµķ•˜ź²Œ ģ„¤ėŖ…ķ•˜ģžė©“ ė‹¤ė„ø Isolation Level(ķ›„ģˆ ķ•˜ź² ģŠµė‹ˆė‹¤)ģ„ ź°€ģ§„ ģ„øģ…˜ģ—ģ„œ ėŒ€ģƒ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ“ ģ»¤ė°‹ė˜ģ§€ ģ•Šģ€ ģƒķƒœģ—ģ„œ ģŠ¤ėƒ…ģƒ· ģƒģ„±ķ•˜ģ—¬ ė°œģƒķ•œ ģ¼ģž…ė‹ˆė‹¤. ź²°ė” ģ ģœ¼ė”œ PostgreSQLģ€ Activeė„¼ ģš°ģ„ ģœ¼ė”œ ķ•˜ģ—¬ ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ„ IN_PROGRESSė”œ ź°„ģ£¼ķ•©ė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 5ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Invisibleģž…ė‹ˆė‹¤.
ā€¢
Rule 6ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ€ ā€œIf Status(t_xmin) = COMMITTEDā€ ģ¦‰, ģ»¤ė°‹ė˜ģ—ˆģŠµė‹ˆė‹¤. ź·øė¦¬ź³ , ā€œStatus(t_xmax) = ABORTEDā€ ķ•“ģ œķ•˜ė©“ T_DELETEė„¼ ģˆ˜ķ–‰ķ•œ ķŠøėžœģž­ģ…˜ģ“ Abort ė˜ģ—ˆģŠµė‹ˆė‹¤. ģ¦‰, ķ•“ė‹¹ ķŠœķ”Œģ€ ģƒģ„± ģ“ķ›„ T_DELETEė„¼ ķ–ˆģ§€ė§Œ ķ•“ė‹¹ ė™ģž‘ģ“ ģ·Øģ†Œė˜ģ—ˆģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 6ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Visibleģž…ė‹ˆė‹¤.
ā€¢
Rule 7ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ€ ā€œIf Status(t_xmin) = COMMITTEDā€ ģ¦‰, ģ»¤ė°‹ė˜ģ—ˆģŠµė‹ˆė‹¤. ź·øė¦¬ź³ , t_xmaxź°€ 0ģ“ėƀė”œ ģƒģ„± ģ“ķ›„ T_DELETE ė˜ģ§€ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ģ¦‰, ķ•“ė‹¹ ķŠœķ”Œģ€ ģƒģ„± ģ“ķ›„ T_DELETEķ•˜ģ§€ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 7ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Visibleģž…ė‹ˆė‹¤.
ā€¢
Rule 8ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ ā€œIf Status(t_xmin) = COMMITTED āˆ§ Status(t_xmax) = IN_PROGRESSā€ ģ¦‰, ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ€ ģ»¤ė°‹ė˜ģ—ˆź³ , ķ•“ė‹¹ ķŠœķ”Œģ„ UPDATE ė˜ėŠ” DELETEė„¼ ķ†µķ•“ T_DELETEķ•œ ķŠøėžœģž­ģ…˜ģ€ ģ•„ģ§ ģ»¤ė°‹ė˜ģ§€ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, ķ•“ė‹¹ ķŠœķ”Œģ€T_DELETEė„¼ ģˆ˜ķ–‰ķ•œ ķŠøėžœģž­ģ…˜ģ—ģ„œė§Œ ė³“ģ—¬ģ§€ģ§€ ģ•Šź³ (ģ•„ģ§ T_DELETEź°€ COMMITģ“ ģ•ˆė˜ģ—ˆģœ¼ėƀė”œ), ė‹¤ė„ø ķŠøėžœģž­ģ…˜ģ—ģ„œėŠ” ė³“ģ—¬ģ øģ•¼ķ•©ė‹ˆė‹¤(ķ•“ė‹¹ ķŠœķ”Œģ„ ģƒģ„±ķ•œ T_INSERTėŠ” COMMITė˜ģ—ˆģœ¼ėƀė”œ). Rule 7ģ€ ā€œt_xmax = current_txidā€, ģ¦‰ ķ˜„ģž¬ ķŠøėžœģž­ģ…˜ģ“ T_DELETEė„¼ ģˆ˜ķ–‰ķ•˜ģ˜€ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 7ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Invisibleģž…ė‹ˆė‹¤.
ā€¢
Rule 9ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Rule 7ź³¼ ė‹¬ė¦¬ ā€œt_xmax ā‰  current_txid AND t_xmax ā‰  0ā€ ģ¦‰, T_DELETEė„¼ ģˆ˜ķ–‰ķ•œ ķŠøėžœģž­ģ…˜ģ“ ģ•„ė‹™ė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 8ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ„ Visibleģž…ė‹ˆė‹¤.
ā€¢
Rule 10ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ ā€œIf Status(t_xmin) = COMMITTEDā€ ģ¦‰, ģƒģ„±ķ•œ ķŠøėžœģž­ģ…˜ģ€ ģ»¤ė°‹ė˜ģ—ˆģŠµė‹ˆė‹¤. ź·øė¦¬ź³ , ā€œStatus(t_xmax) = COMMITTED āˆ§ Snapshot(t_xmax) = activeā€ ķ•“ė‹¹ ķŠœķ”Œģ„ T_DELETEķ•œ ķŠøėžœģž­ģ…˜ģ€ ģ»¤ė°‹ė˜ģ—ˆģœ¼ė‚˜ ģ•„ģ§ Activeģž…ė‹ˆė‹¤. ģš°ė¦¬ėŠ” Rule 5ģ—ģ„œ ģ”°źøˆ ģ“ģƒķ•œ ģƒķ™©ģ— ėŒ€ķ•“ ģ“ėÆø ģ„¤ėŖ…ķ•˜ģ˜€ģŠµė‹ˆė‹¤. Active ģƒķƒœź°€ ģš°ģ„ ė˜ėƀė”œ IN_PROGRESSė”œ ź°„ģ£¼ķ•˜ėƀė”œ ģ•„ģ§ T_DELETEź°€ ė°˜ģ˜ė˜ģ§€ ģ•Šģ•˜ģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 9ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Visible ģž…ė‹ˆė‹¤.
ā€¢
Rule 11ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Rule 9ģ™€ ė‹¬ė¦¬ ā€œSnapshot(t_xmax) ā‰  activeā€ ģž…ė‹ˆė‹¤. ė”°ė¼ģ„œ, T_DELETEź°€ ė°˜ģ˜ė˜ģ—ˆģŠµė‹ˆė‹¤. ė”°ė¼ģ„œ, Rule 10ģ— ķ•“ė‹¹ķ•˜ėŠ” ķŠœķ”Œģ€ Invisible ģž…ė‹ˆė‹¤.

Isolation Level

Isolation Levelģ€ ķŠøėžœģž­ģ…˜ģ˜ ź²©ė¦¬ ģˆ˜ģ¤€ģœ¼ė”œ ANSI SQL-92ģ—ģ„œ ė°œģƒķ•˜ėŠ” ķ˜„ģƒģ— ė”°ė¼ ģ •ģ˜ķ•œ ź°œė…ģž…ė‹ˆė‹¤.
ANSI SQL-92 MS, ANSI] defines Isolation Levels in terms of phenomena.
Reference. Berenson, Hal, et al. "A critique of ANSI SQL isolation levels."Ā ACM SIGMOD Record Ā 24.2 (1995): 1-10.
SQL-92ģ—ģ„œėŠ” ė‹¤ģŒ 3ź°€ģ§€ ķ˜„ģƒģ„ źø°ė°˜ģœ¼ė”œ ķŠøėžœģž­ģ…˜ ź²©ė¦¬ ģˆ˜ģ¤€ģ„ ģ •ģ˜ķ•˜ģ˜€ģŠµė‹ˆė‹¤.
ā€¢
Dirty Read 1. ķŠøėžœģž­ģ…˜ T1ģ“ ķŠ¹ģ • ė°ģ“ķ„° Aė„¼ Bė”œ ģˆ˜ģ • 2. ķŠøėžœģž­ģ…˜ T2ź°€ ė³€ź²½ėœ ė°ģ“ķ„° Bė„¼ ģ”°ķšŒ 3. ķŠøėžœģž­ģ…˜ T1ģ“ ė”¤ė°±(Rollback)ģ„ ģˆ˜ķ–‰ ā‡’ ė”¤ė°±ģ“ ģˆ˜ķ–‰ė˜ģ–“ ģ»¤ė°‹ėœ ģ ģ“ ģ—†ģ–“ ģ”“ģž¬ķ•œ ģ ģ“ ģ—†ėŠ” ė°ģ“ķ„° Bė„¼ T2ź°€ ģ”°ķšŒ
ā€¢
Non-repeatable Read 1. ķŠøėžœģž­ģ…˜ T1ģ“ ķŠ¹ģ • ė°ģ“ķ„° Aė„¼ ģ”°ķšŒ 2. ķŠøėžœģž­ģ…˜ T2ź°€ ė°ģ“ķ„° Aė„¼ Bė”œ ģˆ˜ģ •(ė˜ėŠ” ģ‚­ģ œ)ķ•˜ź³  ģ»¤ė°‹ 3. ķŠøėžœģž­ģ…˜ T1ģ“ ķ•“ė‹¹ ė°ģ“ķ„°ė„¼ ė‹¤ģ‹œ ģ”°ķšŒķ•˜ė©“ B(ė˜ėŠ” ķ•“ė‹¹ ė°ģ“ķ„°ź°€ ģ—†ė‹¤ėŠ” ź²°ź³¼)ģ„ ģ”°ķšŒ ā‡’ ź°™ģ€ ķŠøėžœģž­ģ…˜ T1ģ—ģ„œ ź°™ģ€ ė°ģ“ķ„°ģ— ėŒ€ķ•“ ė‹¤ė„ø ź²°ź³¼ė„¼ ģ”°ķšŒ
ā€¢
Phantom Read 1. ķŠøėžœģž­ģ…˜ T1ģ“ ķŠ¹ģ • ģæ¼ė¦¬ė”œ READķ•˜ģ—¬ SET1ģ„ ź²°ź³¼ė”œ ģ”°ķšŒ 2. ķŠøėžœģž­ģ…˜ T2ź°€ ķ•“ė‹¹ ģæ¼ė¦¬ ģ”°ź±“ģ„ ė§Œģ”±ķ•˜ėŠ” ė°ģ“ķ„° Aė„¼ ģ¶”ź°€ 3. ķŠøėžœģž­ģ…˜ T1ģ“ ķ•“ė‹¹ ģæ¼ė¦¬ė”œ ė‹¤ģ‹œ READķ•˜ģ—¬ Aź°€ ķ¬ķ•Øėœ SET2ė„¼ ź²°ź³¼ ģ”°ķšŒ ā‡’ ź°™ģ€ ķŠøėžœģž­ģ…˜ T1ģ—ģ„œ ź°™ģ€ ģæ¼ė¦¬ė”œ ģ”°ķšŒķ•œ ź²°ź³¼ģø SET1ź³¼ SET2ź°€ ė¶ˆģ¼ģ¹˜
SQL-92ģ—ģ„œėŠ” ģœ„ģ—ģ„œ ģ–øźø‰ķ•œ 3ź°€ģ§€ ķ˜„ģƒģ˜ ė°œģƒ ģ—¬ė¶€ė„¼ źø°ģ¤€ģœ¼ė”œ 4ė‹Øź³„ģ˜ ķŠøėžœģž­ģ…˜ ź²©ė¦¬ ģˆ˜ģ¤€ģ„ ģ •ģ˜ķ•˜ģ˜€ģŠµė‹ˆė‹¤. ź° ė‹Øź³„ėŠ” ė‹¤ģŒź³¼ ź°™ģŠµė‹ˆė‹¤.
Isolation Level
Dirty Read
Non-repeatable Read
Phantom Read
Read uncommitted
Possible
Possible
Possible
Read committed
Not possible
Possible
Possible
Repeatable read
Not possible
Not possible
Possible
Serializable
Not possible
Not possible
Not possible
* ģœ„ģ˜ ģ •ģ˜ėŠ” ķŠøėžœģž­ģ…˜ ź²©ė¦¬ ģˆ˜ģ¤€ģ˜ ź°œė…ģ— ėŒ€ķ•œ ģ“ķ•“ė„¼ ģœ„ķ•“ ģ›ģ „ģ„ ģøģš©ķ•œ ź²ƒģœ¼ė”œ ź° ė²¤ė” ė³„ė”œ ģ •ģ˜ķ•˜ėŠ” ķ˜„ģƒź³¼ ķŠøėžœģž­ģ…˜ ź²©ė¦¬ ģˆ˜ģ¤€ģ€ ģƒģ“ķ•˜ėƀė”œ ģ°øź³  ė°”ėžė‹ˆė‹¤.
PostgreSQLģ—ģ„œ ģ •ģ˜ķ•˜ėŠ” ķŠøėžœģž­ģ…˜ ź²©ė¦¬ ģˆ˜ģ¤€ģ€ ė‹¤ģŒ ķ‘œģ™€ ź°™ģŠµė‹ˆė‹¤.
Isolation Level
Dirty Read
Non-repeatable Read
Phantom Read
Serialization Anomaly
Read uncommitted
Allowed, but not in PG
Possible
Possible
Possible
Read committed
Not possible
Possible
Possible
Possible
Repeatable read
Not possible
Not possible
Allowed, but not in PG
Possible
Serializable
Not possible
Not possible
Not possible
Not possible
PostgreSQLģ—ģ„œėŠ” ķŠøėžœģž­ģ…˜ T1ź³¼ T2ź°€ ģžˆė‹¤ź³  ķ–ˆģ„ ė•Œ, Visibility Rule 5ģ— ģ˜ķ•“ T2ģ—ģ„œ ģ”°ķšŒķ•œ ģŠ¤ėƒ…ģƒ·ģ—ģ„œ ė³“ģ—¬ģ§€ėŠ” T1ģ˜ Active ģ—¬ė¶€ź°€ T1ģ˜ ķŠøėžœģž­ģ…˜ ģƒķƒœģ— ģš°ģ„ ķ•©ė‹ˆė‹¤. ģ¦‰, ķŠøėžœģž­ģ…˜ ģƒķƒœź°€ COMMITTEDė¼ ķ•˜ė”ė¼ė„ ģ”°ķšŒķ•œ ģŠ¤ėƒ…ģƒ·ģ“ Activeķ•˜ė‹¤ė©“ IN_PROGRESSė”œ ź°„ģ£¼ķ•©ė‹ˆė‹¤. ķ•“ė‹¹ ź·œģ¹™ģ— ģ˜ķ•“ PostgreSQLģ€ Read uncommittedģ˜ Dirty Read, Repeatable readģ˜ Phantom Read ķ˜„ģƒģ“ ė°œģƒķ•˜ģ§€ ģ•ŠģŠµė‹ˆė‹¤.

Lost Update (Write-Write Conflict)

Lost Update ķ˜„ģƒģ€ ė‘ ź°œģ˜ ķŠøėžœģž­ģ…˜ģ—ģ„œ ė™ģ‹œģ— UPDATEė„¼ ģˆ˜ķ–‰ķ•  ź²½ģš° ģ²«ė²ˆģ§ø ķŠøėžœģž­ģ…˜ģ˜ UPDATEź°€ ėˆ„ė½ė˜ėŠ” ķ˜„ģƒģœ¼ė”œ ģœ„ģ—ģ„œ ģøģš©ķ–ˆė˜ ā€œA critique of ANSI SQL isolation levelsā€ģ—ģ„œ ģ–øźø‰ķ•˜ź³  ģžˆģŠµė‹ˆė‹¤.
Reference. Berenson, Hal, et al. "A critique of ANSI SQL isolation levels."Ā ACM SIGMOD Record Ā 24.2 (1995): 1-10.
PostgreSQLģ—ģ„œėŠ” Repeatable Readģ™€ Serializable ģˆ˜ģ¤€ģ—ģ„œ Lost Update ķ˜„ģƒģ„ ė°©ģ§€ķ•˜źø° ģœ„ķ•“ ė‹¤ģŒź³¼ ź°™ģ“ ė™ģž‘ķ•©ė‹ˆė‹¤.
ā€¢
ķŠøėžœģž­ģ…˜ T2ģ—ģ„œ UPDATE ķ•  ėŒ€ģƒ ė°ģ“ķ„°ź°€ ķŠøėžœģž­ģ…˜ T1ģ— ģ˜ķ•“ UPDATEź°€ ģ§„ķ–‰ ģ¤‘ģ“ė©“ T1ģ“ ģ¢…ė£Œė  ė•Œź¹Œģ§€ źø°ė‹¤ė¦½ė‹ˆė‹¤.
ā€¢
ė§Œģ•½ ķ•“ė‹¹ ķŠøėžœģž­ģ…˜ģ“ ģ»¤ė°‹ė˜ģ—ˆė‹¤ė©“,
ā—¦
REPEATABLE READ, SERIALIZABLEģ“ė©“ T2ė„¼ Abort ķ•©ė‹ˆė‹¤.
ā—¦
READ COMMITTEDģ“ė©“ UPDATEė„¼ ģˆ˜ķ–‰ķ•©ė‹ˆė‹¤.
Repeatable Read, Serializable ģˆ˜ģ¤€ģ—ģ„œėŠ” ė‘ė²ˆģ§ø ķŠøėžœģž­ģ…˜ģ„ ģ•„ģ˜ˆ Abort ķ•“ė²„ė¦¬źø° ė•Œė¬øģ— ģ²«ė²ˆģ§ø ķŠøėžœģž­ģ…˜ģ˜ UPDATEź°€ ėˆ„ė½ė˜ėŠ” ķ˜„ģƒģ“ ė°œģƒķ•˜ģ§€ ģ•ŠģŠµė‹ˆė‹¤.
ģ§€źøˆź¹Œģ§€ PostgreSQLģ˜ Concurrency Controlģ— ź“€ķ•“ ģ•Œģ•„ė³“ģ•˜ģŠµė‹ˆė‹¤
ā€˜PostgreSQLģ˜ Vacuumā€™ė„¼ ė°”ė”œ ģ“ģ–“ģ„œ ķ™•ģøķ•“ė³“ģ„øģš”!

ģžģœ ė”­ź²Œ ėŒ“źø€ģ„ ė‚Øź²Øģ£¼ģ„øģš”