MYSQL innodb中的只读事物以及事物id的分配方式

点击(此处)折叠或打开

1. trx->read_only =
   
2.         (trx->api_trx && !trx->read_write)
   
3.         || (!trx->ddl && !trx->internal
   
4.          && thd_trx_is_read_only(trx->mysql_thd))
   
5.         || srv_read_only_mode; //此处获取事物当前是否是只读属性，可以看到他和我们的read_only参数设置事物是ddl事物是否是内部事物有关
   
6. 
   
7.     if (!trx->auto_commit) { //是否自动提交否则需要设置will_Lock属性如果时候只读事物未TURE，如果是DML事物为flase
   
8.      //这里的auto_commit属性和我们平时设置的参数感觉不是一回事
   
9.         ++trx->will_lock;
   
10.     } else if (trx->will_lock == 0) {
    
11.         trx->read_only = true; //如果不需要will_lock属性它肯定是只读事物
    
12.     }
    
13. //以上也就说明了只读事物不需要锁结构因为 trx->will_lock = 0(false)
    
14.     /* We tend to over assert and that complicates the code somewhat.
    
15.     e.g., the transaction state can be set earlier but we are forced to
    
16.     set it under the protection of the trx_sys_t::mutex because some
    
17.     trx list assertions are triggered unnecessarily. */
    
18. 
    
19.     /* By default all transactions are in the read-only list unless they
    
20.     are non-locking auto-commit read only transactions or background
    
21.     (internal) transactions. Note: Transactions marked explicitly as
    
22.     read only can write to temporary tables, we put those on the RO
    
23.     list too. */
    
24.     //当然如果是非只读事物 我们需要开始分配undo rollback segment了 以及undo segment了
    
25.     //并且trx->mysql_thd == 0 表示是否是MYSQL线程建立的innodb事物
    
26.     //是否是读写事物这个是由调用者传入只读事物为false，DML事物为true，这里的读写和前面
    
27.     //trx->read_only有区别如果是只读事物建立临时表也是读写事物
    
28.     //是否是DDL事物 DDL也需要分配undo rollback segment了 以及undo segment
    
29.     if (!trx->read_only
    
30.      && (trx->mysql_thd == 0 || read_write || trx->ddl)) {
    
31. 
    
32.         trx->rsegs.m_redo.rseg = trx_assign_rseg_low(
    
33.             srv_undo_logs, srv_undo_tablespaces,
    
34.             TRX_RSEG_TYPE_REDO);
    
35. 
    
36.         /* Temporary rseg is assigned only if the transaction
    
37.         updates a temporary table */
    
38. 
    
39.         trx_sys_mutex_enter();
    
40. 
    
41.         trx_assign_id_for_rw(trx);//分配事物号
    
42.         /*
    
43.      (gdb) p trx_sys->max_trx_id
    
44.         $21 = 328707
    
45.      */
    
46. 
    
47.         trx_sys_rw_trx_add(trx); //将入集合
    
48. 
    
49.         ut_ad(trx->rsegs.m_redo.rseg != 0
    
50.          || srv_read_only_mode
    
51.          || srv_force_recovery >= SRV_FORCE_NO_TRX_UNDO);
    
52. 
    
53.         UT_LIST_ADD_FIRST(trx_sys->rw_trx_list, trx); //将事物放入rw_trx_list
    
54. 
    
55.         ut_d(trx->in_rw_trx_list = true);
    
56. #ifdef UNIV_DEBUG
    
57.         if (trx->id > trx_sys->rw_max_trx_id) {
    
58.             trx_sys->rw_max_trx_id = trx->id;
    
59.         }
    
60. #endif /* UNIV_DEBUG */
    
61. 
    
62.         trx->state = TRX_STATE_ACTIVE; //更改事物的状态为ACTIVE
    
63. 
    
64.         ut_ad(trx_sys_validate_trx_list());
    
65. 
    
66.         trx_sys_mutex_exit();
    
67. 
    
68.     } else {
    
69.         trx->id = 0; //任然没有分配事物ID给只读事物
    
70. 
    
71.         if (!trx_is_autocommit_non_locking(trx)) { //#define trx_is_autocommit_non_locking(t)     ((t)->auto_commit && (t)->will_lock == 0)
    
72. 
    
73.             /* If this is a read-only transaction that is writing
    
74.             to a temporary table then it needs a transaction id
    
75.             to write to the temporary table. */
    
76.             //如果是只读事物并且写入了临时表需要额外操作
    
77. 
    
78.             if (read_write) {
    
79. 
    
80.                 trx_sys_mutex_enter();
    
81. 
    
82.                 ut_ad(!srv_read_only_mode);
    
83. 
    
84.                 trx_assign_id_for_rw(trx);
    
85. 
    
86.                 trx_sys->rw_trx_set.insert(
    
87.                     TrxTrack(trx->id, trx));
    
88. 
    
89.                 trx_sys_mutex_exit();
    
90.             }
    
91. 
    
92.             trx->state = TRX_STATE_ACTIVE;
    
93. 
    
94.         } else {
    
95.             ut_ad(!read_write);
    
96.             trx->state = TRX_STATE_ACTIVE;
    
97.         }
    
98.     }
    
99. 
    
100.     if (trx->mysql_thd != NULL) {
     
101.         trx->start_time = thd_start_time_in_secs(trx->mysql_thd); //开始计时这是系统时间LINUX系统调用time()
     
102.     } else {
     
103.         trx->start_time = ut_time();
     
104.     }

点击(此处)折叠或打开

1. UNIV_INLINE
   
2. trx_id_t
   
3. trx_get_id_for_print(
   
4.     const trx_t*    trx)
   
5. {
   
6.     /* Readonly and transactions whose intentions are unknown (whether
   
7.     they will eventually do a WRITE) don't have trx_t::id assigned (it is
   
8.     0 for those transactions). Transaction IDs in
   
9.     innodb_trx.trx_id,
   
10.     innodb_locks.lock_id,
    
11.     innodb_locks.lock_trx_id,
    
12.     innodb_lock_waits.requesting_trx_id,
    
13.     innodb_lock_waits.blocking_trx_id should match because those tables
    
14.     could be used in an SQL JOIN on those columns. Also trx_t::id is
    
15.     printed by SHOW ENGINE INNODB STATUS, and in logs, so we must have the
    
16.     same value printed everywhere consistently. */
    
17.   
    
18.     /* DATA_TRX_ID_LEN is the storage size in bytes. */
    
19.     static const trx_id_t    max_trx_id
    
20.         = (1ULL << (DATA_TRX_ID_LEN * CHAR_BIT)) - 1;
    
21. 
    
22.     ut_ad(trx->id <= max_trx_id);
    
23. 
    
24.     return(trx->id != 0
    
25.      ? trx->id
    
26.      : reinterpret_cast<trx_id_t>(trx) | (max_trx_id + 1));
    
27. }

点击(此处)折叠或打开

1. void
   
2. trx_assign_id_for_rw(trx_t* trx)
   
3. {
   
4.     ut_ad(mutex_own(&trx_sys->mutex));
   
5. 
   
6.     trx->id = trx->preallocated_id
   
7.         ? trx->preallocated_id : trx_sys_get_new_trx_id();
   
8.     //先判断是否是这个事物分配过事物ID，因为从事物池中拿出来
   
9.     //很可能以前用过，那么就不需要再次分配了，否则新分配
   
10. 
    
11.     if (trx->preallocated_id) { //如果是以前使用过的不一定是最大需要加入到vertor中间
    
12.         // Maintain ordering in rw_trx_ids
    
13.         trx_sys->rw_trx_ids.insert(
    
14.             std::upper_bound(trx_sys->rw_trx_ids.begin(),
    
15.                      trx_sys->rw_trx_ids.end(),
    
16.                      trx->id), trx->id);
    
17.     } else {
    
18.         // The id is known to be greatest 新分配的肯定是最大 如果是最大加到某位即可
    
19.         trx_sys->rw_trx_ids.push_back(trx->id);
    
20.     }
    
21. }

点击(此处)折叠或打开

1. trx_sys_get_new_trx_id()
   
2. /*====================*/
   
3. {
   
4.     ut_ad(trx_sys_mutex_own());
   
5. 
   
6.     /* VERY important: after the database is started, max_trx_id value is
   
7.     divisible by TRX_SYS_TRX_ID_WRITE_MARGIN, and the following if
   
8.     will evaluate to TRUE when this function is first time called,
   
9.     and the value for trx id will be written to disk-based
   
10.     Thus trx id values will not overlap when the database is
    
11.     repeatedly */
    
12. 
    
13.     if (!(trx_sys->max_trx_id % TRX_SYS_TRX_ID_WRITE_MARGIN)) {
    
14. 
    
15.         trx_sys_flush_max_trx_id(); //TRX_SYS_TRX_ID_WRITE_MARGIN为256 如果trx_sys->max_trx_id达到256的整数倍需要刷盘
    
16.          //到TRX_SYS_TRX_ID_STORE中.
    
17.     }
    
18. 
    
19.     return(trx_sys->max_trx_id++);//然后自身+1返回
    
20. }

点击(此处)折叠或打开

1. 下面是我修改后show engine innodb的输出
   
2. 
   
3. LIST OF TRANSACTIONS FOR EACH SESSION(1)(CHANGE BY GAOPENG ALL mysql_trx_list and rw_trx_list):
   
4. (MYSQL)---TRANSACTION 422212177402680, ACTIVE 3 sec fetching rows
   
5. mysql tables in use 1, locked 0
   
6. 0 lock struct(s), heap size 1160, 0 row lock(s), RO TRX
   
7. MySQL thread id 7, OS thread handle 140737153619712, query id 411 localhost root Sending data
   
8. select * from test.tuser

点击(此处)折叠或打开

1. mysql> select * from information_schema.innodb_trx /G
   
2. *************************** 1. row ***************************
   
3.                     trx_id: 422212177402680
   
4.                  trx_state: RUNNING
   
5.                trx_started: 2017-07-19 16:52:53
   
6.      trx_requested_lock_id: NULL
   
7.           trx_wait_started: NULL
   
8.                 trx_weight: 0
   
9.        trx_mysql_thread_id: 7
   
10.                  trx_query: select * from test.tuser
    
11.        trx_operation_state: fetching rows
    
12.          trx_tables_in_use: 1
    
13.          trx_tables_locked: 0
    
14.           trx_lock_structs: 0
    
15.      trx_lock_memory_bytes: 1160
    
16.            trx_rows_locked: 0
    
17.          trx_rows_modified: 0
    
18.    trx_concurrency_tickets: 0
    
19.        trx_isolation_level: REPEATABLE READ
    
20.          trx_unique_checks: 1
    
21.     trx_foreign_key_checks: 1
    
22. trx_last_foreign_key_error: NULL
    
23.  trx_adaptive_hash_latched: 0
    
24.  trx_adaptive_hash_timeout: 0
    
25.           trx_is_read_only: 1
    
26. trx_autocommit_non_locking: 1