关于只读事务的一些疑惑

语句如下:
select  t_r.shop_id,t_r.dayid,s_n.terms ,
count(1)  ,max(s_n.record_create_time)
from voc_sit_db.base_tm_trade_rate t_r force index(idx_base_tm_trade_rate_dayid)
inner join voc_sit_db.comment_terms s_n on s_n.id = t_r.id
where t_r.dayid >= '201710801'
and t_r.dayid < '20180201'
group by t_r.shop_id,t_r.dayid,s_n.terms
从information_shema.innodb_trx里看  事物号符合预期很大,但是为什么trx_is_read_only为0 不是1呢。按叶老师以前说的 只读事务号都很大,但是官档上说trx_is_read_only为1为只读事务。有些疑惑。。。。
*************************** 2. row ***************************
                    trx_id: 421300275306400
                 trx_state: RUNNING
               trx_started: 2018-01-24 16:28:38
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 52690
                 trx_query: select  t_r.shop_id,t_r.dayid,s_n.terms ,
count(1)  ,max(s_n.record_create_time)
from voc_sit_db.base_tm_trade_rate t_r force index(idx_base_tm_trade_rate_dayid)
inner join voc_sit_db.comment_terms s_n on s_n.id = t_r.id
where t_r.dayid >= '201710801'
and t_r.dayid < '20180201'
group by t_r.shop_id,t_r.dayid,s_n.terms
       trx_operation_state: NULL
         trx_tables_in_use: 3
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
已邀请:

arron

赞同来自:

我测试了一下,trx_is_read_only这个字段只对显式声明的只读事务有效
mysql >select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
 
1. 未显式申明只读事务
mysql >begin; select * from t1_1 where id=1 lock in share mode;
Query OK, 0 rows affected (0.00 sec)
 
mysql >select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421460321504792
                 trx_state: RUNNING
               trx_started: 2018-01-24 17:42:52
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 13
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
 
可以看到此时trx_id很大,且trx_is_read_only=0
 
2. 显式申明只读事务
mysql >start transaction read only;
mysql >select * from t1_1 where id=1 lock in share mode;
 
mysql >select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421460321504792
                 trx_state: RUNNING
               trx_started: 2018-01-24 17:45:26
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 13
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
 
可以看到此时trx_id很大,且trx_is_read_only=1

要回复问题请先登录注册