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
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 "$*%
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
Active Transaction ID • In progress or not started • It’s invisible • Inactive Transaction ID • committed or aborted • It’s visible if committed "DUJWF*OBDUJWF5SBOTBDUJPO*%
• 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.
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/
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/
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
• 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/
Uncommited transacftions • If transactions is commited, system will remove transaction id from trx_sys list. USY@TZTUSBOTBDUJPOTMJTU ref: https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
lukahuang
axbom
iamctodd
michaelherold
nmsamuel
kimpetersen
lfama
bluesmoon
apolaine
akosma
jnunemaker
katiecoart
reverentgeek
