A Tour of PostgreSQL Concurrency Control

Transcript

1. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 "
   5063
   0'
   1045(3&42-
   $0/$633&/$:
   $0/530- Luka Huang@COSCUP 2019
2. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 8IZ
   UIJT
   UPQJD
3. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
4. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 .7$$ 5VQMF )05 7BDVVN
   1BHF
5. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 "
   5063
   0'
   1045(3&42-
   $0/$633&/$:
   $0/530-
6. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • ACID and Isolation levels • How MVCC works in PostgresQL • How tuples changed during CRUD • How snapshot isolation works • How MVCC works in MySQL • MySQL vs PosgreSQL 0VUMJOF
7. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 Concurrency Control and ACID
8. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019
9. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Atomicity • Consistency • Isolation • Durability "$*%
10. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Atomicity • A transaction is inseperable – ”all or nothing” • Consistency • Isolation • Durability "$*%
11. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Atomicity • Consistency • A transaction shall bring the database from one consistent state to another consistent state, even if its not necessarily consistent during the transaction. • Isolation • Durability "$*%
12. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Atomicity • Consistency • Isolation • Transactions are not affected by changes done by concurrent transactions • Durability "$*%
13. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Atomicity • Consistency • Isolation • Durability • When a transaction is COMMITED, the changes are permanent, even after a crash "$*%
14. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019
15. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $PODVSSFODZ
    $POUSPM
    5FDIOJRVFT
16. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $PODVSSFODZ
    $POUSPM
    5FDIOJRVFT • Locking e.g. 2PL (Two-Phase Locking) • Optimistic Concurrency Control • MVCC (Multi-version Concurrency Control)
17. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $PODVSSFODZ
    $POUSPM
    5FDIOJRVFT • Locking e.g. 2PL (Two-Phase Locking) • Optimistic Concurrency Control • MVCC (Multi-version Concurrency Control)
18. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $PODVSSFODZ
    $POUSPM
    5FDIOJRVFT • Locking e.g. 2PL (Two-Phase Locking) • Optimistic Concurrency Control • MVCC (Multi-version Concurrency Control)
19. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Locking e.g. 2PL (Two-Phase Locking) • Optimistic Concurrency Control • MVCC (Multi-version Concurrency Control) $PODVSSFODZ
    $POUSPM
    5FDIOJRVFT
20. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 4OBQTIPU
    *TPMBUJPO
    4*
21. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • A type of MVCC • PostgreSQL • every writing operation create a new version of data • retain old version of data. • use visibility check rules to decide which data to show • MySQL、Oracle • Implement by rollback segments 4*
    4OBQTIPU
    *TPMBUJPO
22. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 Isolation levels
23. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 -PSFN
    JQTVN
    ref: https://www.microsoft.com/en-us/research/wp-content/uploads/
24. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref: https://www.microsoft.com/en-us/research/wp-content/uploads/
25. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • P1 (Dirty Read) • P2 (Non-repeatable or Fuzzy Read) • P3 (Phantom Read) 
    QIFOPNFOB
    PG
    *TPMBUJPO
26. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1
    %JSUZ
    3FBE ref: https://medium.com/getamis/database-transaction-isolation-
27. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1
    /POSFQFBUBCMF
    3FBE ref: https://medium.com/getamis/database-transaction-isolation-
28. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1
    1IBOUPN
    3FBE ref: https://medium.com/getamis/database-transaction-isolation-
29. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Read Uncommitted • Read Committed • Repeatable Read • Serializable *TPMBUJPO
    -FWFMT
    
30. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 *TPMBUJPO
    MFWFMT
    WT
    SFBE
    QIFOPNFOB
    
31. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 3FBE
    $PNNJUUFE
    
32. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 3FQFBUBCMF
    3FBE
    
    'V[[Z
    3FBE
33. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 4FSJBMJ[BCMF
    
34. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 Table, Page and Tuple
35. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 5BCMF
    %BUB QBHF QBHF QBHF QBHF QBHF QBHF QBHF 5BCMF
    EBUB
    BOE
    1BHF
    8kb
36. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 5BCMF
    EBUB
    BOE
    1BHF
    
37. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 5BCMF
    EBUB
    BOE
    1BHF
    
38. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 5BCMF
    EBUB
    BOE
    1BHF
    
39. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Sequential Scan • B-Tree Index scan "DDFTT
    NFUIPET
40. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 4FRVFOUJBM
    4DBO
41. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 #5SFF
    *OEFY
    4DBO ref: https://www.pgcon.org/2016/schedule/attachments/423_Btree
42. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 Concurrency Control
43. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref: 1PTUHSF42-
    4PVSDF
    $PEF
44. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref: 1PTUHSF42-
    4PVSDF
    $PEF
45. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 )FBQ5VQMF'JFMET src/include/access/htup_details.h
46. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 )FBQ5VQMF)FBEFS%BUB
47. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1BHF
    BOE
    5VQMF
48. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • t_xmin - 保存插入此 tuple 的 txid。 • t_xmax - 更新或刪除 tuple 的 txid。如果 沒更新、刪除的話就設為 0 • t_cid (command_id) - ⽬前執⾏了多少命 令。 • t_ctid - ⾃⼰所在的 tuple (tid)。 'JFMET
    JO
    )FBQ
    5VQMF
49. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 CREATE TABLE p_table (data text) ; INSERT INTO p_table ( data ) VALUES ( 'Jack' ) ; INSERT INTO p_table ( data ) VALUES ( 'Peter' ) ; INSERT INTO p_table ( data ) VALUES ( 'Luka' ) ; SELECT * FROM p_table; )FBQ
    5VQMF
50. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 (FU
    UVQMF
    JOGPSNBUJPO SELECT lp as tuple, t_xmin, t_xmax, t_ fi eld3 as t_cid, t_cti d FROM heap_page_items(get_raw_page(p_table', 0));
51. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 */4&35
52. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 INSERT INTO p_table ( data ) VALUES ( 'Apple' ) ; *OTFSU
53. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 BEGIN ; INSERT INTO p_table ( data ) VALUES ( 'Apple' ) ; SELECT txid_current() ; END ; *OTFSU
54. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 BEGIN ; INSERT INTO p_table ( data ) VALUES ( 'Apple' ) ; SELECT txid_current(); => txid: 62 7 END ; *OTFSU
55. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 61%"5&
56. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 #&'03&
57. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 6QEBUF
    UPDATE p_table SET data = ‘Cake';
58. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 6QEBUF
    UPDATE p_table SET data = ‘Cake';
59. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 UPDATE p_table SET data = 'Cheese'; 6QEBUF
    BHBJO
60. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 UPDATE p_table SET data = 'Cheese'; 6QEBUF
    BHBJO
61. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 %&-&5&
62. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 %FMFUF DELETE FROM p_table;
63. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 %FMFUF
64. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 53"/4"$5*0/
    4/"14)05
    
65. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Active Transaction ID • In progress or not started • It’s invisible • Inactive Transaction ID • committed or aborted • It’s visible if committed "DUJWF
    
    *OBDUJWF
    5SBOTBDUJPO
    *%
66. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $VSSFOU
    4OBQTIPU xmin:xmax:xip_list
67. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 4OBQTIPU • xmin • if txid < xmin, transaction is inactive • xmax • if txid >= xmax transaction is active. • xip_list • transactions that transaction id in xip_list are active.
68. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 &YBNQMF ref: http://www.interdb.jp/pg/
69. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 &YBNQMF ref: http://www.interdb.jp/pg/
70. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 7*4*#*-*5:
    $)&$,
    
71. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 7*4*#*-*5:
    $)&$,
    36-&4
    
72. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019
73. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • ABORTED • IN PROGRESS • COMMITTED U@YNJO
74. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 /* t_xmin status = ABORTED */ Rule 1: IF t_xmin status is 'ABORTED' THE N RETURN 'Invisible' END IF "#035&% ref: http://www.interdb.jp/pg/
75. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 /* t_xmin status = IN_PROGRESS */ IF t_xmin status is 'IN_PROGRESS' THE N IF t_xmin = current_txid THE N Rule 2: IF t_xmax = INVALID THE N RETURN 'Visible' Rule 3: ELSE /* this tuple has been deleted or updated by the current transaction itself. */ RETURN 'Invisible' END I F Rule 4: ELSE /* t_xmin ≠ current_txid */ RETURN 'Invisible' END I F END IF */@130(3&44 ref: http://www.interdb.jp/pg/
76. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 /* t_xmin status = COMMITTED */ IF t_xmin status is 'COMMITTED' THE N Rule 5: IF t_xmin is active in the obtained transaction snapshot THE N RETURN 'Invisible' Rule 6: ELSE IF t_xmax = INVALID OR status of t_xmax is 'ABORTED' THE N RETURN 'Visible' ELSE IF t_xmax status is 'IN_PROGRESS' THE N Rule 7: IF t_xmax = current_txid THE N RETURN 'Invisible' Rule 8: ELSE /* t_xmax ≠ current_txid */ RETURN 'Visible' END I F ELSE IF t_xmax status is 'COMMITTED' THE N Rule 9: IF t_xmax is active in the obtained transaction snapshot THE N RETURN 'Visible' Rule 10: ELS E RETURN 'Invisible' END I F $0..*55&%
    ref: http://www.interdb.jp/pg/
77. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 &YBNQMF ref: http://www.interdb.jp/pg/
78. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Rule6(Tuple_1) 㱺 Status(t_xmin:199) = COMMITTED ∧ t_xmax = INVALID 㱺 Visible 5 ref: http://www.interdb.jp/pg/ ref: http://www.interdb.jp/pg/
79. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Rule7(Tuple_1): Status(t_xmin:199) = COMMITTED ∧ Status(t_xmax:200) = IN_PROGRESS ∧ t_xmax:200 = current_txid:200 㱺 Invisible 5
     ref: http://www.interdb.jp/pg/ ref: http://www.interdb.jp/pg/
80. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Rule2(Tuple_2): Status(t_xmin:200) = IN_PROGRESS ∧ t_xmin:200 = current_txid:200 ∧ t_xmax = INVAILD 㱺 Visible 5
     ref: http://www.interdb.jp/pg/
81. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 )05
    )&"1
    0/-:
    561-&4
82. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 8JUIPVU
    )05 ref: http://www.interdb.jp/pg/
83. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 8JUI
    )05 ref: http://www.interdb.jp/pg/
84. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 )05
    JT
    OPU
    XPSL  ref: http://www.interdb.jp/pg/
85. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 )05
    JT
    OPU
    XPSL  ref: http://www.interdb.jp/pg/
86. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 MVCC in MySQ L
87. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Storage Engine - InnoDB only • Isolation level • READ COMMITTED • REPEATABLE READ .7$$
    JO
    .Z42-
88. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • DB_TRX_ID • DB_ROLL_PTR .7$$
    JO
    .Z42-
    *OOP%# ref: https://zhuanlan.zhihu.com/p/40208895
89. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • DB_TRX_ID • The last transaction that update this record • DB_ROLL_PTR .7$$
    JO
    .Z42-
    *OOP%# ref: https://zhuanlan.zhihu.com/p/40208895
90. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • DB_TRX_ID • The last transaction that update this record • DB_ROLL_PTR • rollback segment (undo log) .7$$
    JO
    .Z42-
    *OOP%# ref: https://zhuanlan.zhihu.com/p/40208895
91. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 6OEP
    -PH
    • Automaticity • Copy data to another place before updating data . • The place is called undo log. ref: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
92. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 3PMMCBDL
    4FHNFOU ref: http://leviathan.vip/2019/02/14/InnoDB%E7%9A%84%E4%BA%8B%E5%8A%A1%E5%88%86%E6%9E%90- undo slot rollback segment undo log tablespace
93. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1. rseg_id - get rollback tablespace id 2. get data from undo log 4UFQT
    PG
    3PMM
    1PJOUFS /** Decodes a roll pointer. */ inline void trx_undo_decode_roll_ptr ( roll_ptr_t roll_ptr, /*!< in: roll pointer */ ibool *is_insert, /*!< out: TRUE if insert undo log */ ulint *rseg_id, /*!< out: rollback segment id */ page_no_t *page_no, /*!< out: page number */ ulint *offset) /*!< out: offset of the und o entry within page */ { ut_ad(roll_ptr < (1ULL << 56)) ; *offset = (ulint)roll_ptr & 0xFFFF ; roll_ptr >>= 16 ; *page_no = (ulint)roll_ptr & 0xFFFFFFFF ; roll_ptr >>= 32 ; *rseg_id = (ulint)roll_ptr & 0x7F ; roll_ptr >>= 7 ; *is_insert = (ibool)roll_ptr; /* TRUE==1 */ }
94. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • Uncommited transacftions • If transactions is commited, system will remove transaction id from trx_sys list. USY@TZT
    
    USBOTBDUJPOT
    MJTU ref: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
95. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 • low_trx_id • up_trx_id • trx_ids 3FBE
    WJFX ref: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
96. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/ .7$$
    JO
    .Z42-
97. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 1PTUHSF42-
    WT
    
 .Z42-
98. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
99. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019
100. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
101. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
102. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
103. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
104. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
105. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
106. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 ref:
107. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 $0/$-64*0/
108. A Tour of PostgreSQL Concurrency Control, Luka Huang@COSCUP 2019 5)"/,4
     '03
     -*45&/*/(