mysql 多并发控制_mysql中多版本并发控制

为了解决写数据时，不可以对数据进行读的问题。mysql引入的多版本并发控制(MVCC)

提innodb中对于数据表，系统会在表的上增加3列系统列，分别是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。我们可以从代码中看到:

dict_table_add_system_columns(

/*==========================*/

dict_table_t* table, /*!< in/out: table */

mem_heap_t* heap) /*!< in: temporary heap */

{

ut_ad(table);

ut_ad(table->n_def == (table->n_cols - table->get_n_sys_cols()));

ut_ad(table->magic_n == DICT_TABLE_MAGIC_N);

ut_ad(!table->cached);

/* NOTE: the system columns MUST be added in the following order

(so that they can be indexed by the numerical value of DATA_ROW_ID,

etc.) and as the last columns of the table memory object.

The clustered index will not always physically contain all system

columns.

Intrinsic table don't need DB_ROLL_PTR as UNDO logging is turned off

for these tables. */

dict_mem_table_add_col(table, heap, "DB_ROW_ID", DATA_SYS,

DATA_ROW_ID | DATA_NOT_NULL,

DATA_ROW_ID_LEN);

#if (DATA_ITT_N_SYS_COLS != 2)

#error "DATA_ITT_N_SYS_COLS != 2"

#endif

#if DATA_ROW_ID != 0

#error "DATA_ROW_ID != 0"

#endif

dict_mem_table_add_col(table, heap, "DB_TRX_ID", DATA_SYS,

DATA_TRX_ID | DATA_NOT_NULL,

DATA_TRX_ID_LEN);

#if DATA_TRX_ID != 1

#error "DATA_TRX_ID != 1"

#endif

if (!table->is_intrinsic()) {

dict_mem_table_add_col(table, heap, "DB_ROLL_PTR", DATA_SYS,

DATA_ROLL_PTR | DATA_NOT_NULL,

DATA_ROLL_PTR_LEN);

#if DATA_ROLL_PTR != 2

#error "DATA_ROLL_PTR != 2"

#endif

/* This check reminds that if a new system column is added to

the program, it should be dealt with here */

#if DATA_N_SYS_COLS != 3

#error "DATA_N_SYS_COLS != 3"

#endif

}

}

情况一：步骤T1T21begin

2select * from t

3begin

4update t set age=18 where id = 1

5select * from t –> return ?

步骤1：事务T1获取一个TRX_ID 如：2

步聚2: 到表中查DB_TRX_ID大于2且DB_ROLL_PTR不为空的数据+查DB_TRX_ID小于2的数据(保留疑问)

步骤3：事务T2获取一个TRX_ID 如：3

步骤4：

copy一行数据。

把原id那一行数据的DB_ROLL_PTR 变更成3

新的一行DB_TRX_ID=3

步骤5：

age分别是12、24

情况二：

|步骤| T1 | T2|

| —- | ————- |:————-:|

|1| | begin|

|2| | update t set age=18 where id = 1|

|3| begin| |

|4| select * from t retun ??? | |

返回：

age分别是12、24

Undo log