MySQL Transaction--事务相关查询

MySQL支持的四种事务隔离级别

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

 

查看全局事务隔离级别和会话事务隔离级别

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';
SHOW SESSION VARIABLES LIKE 'tx_isolation';
SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation; 

 

修改事务隔离级别参数

SET GLOBAL tx_isolation='REPEATABLE-READ'; 
SET GLOBAL transaction_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';
SET GLOBAL transaction_isolation='REPEATABLE-READ';

tx_isolation和transaction_isolation两者等价相同，修改任意一个即可。

 

修改已开启事务的隔离级别

在事务中修改事务隔离级别，需要使用使用SET TRANSACTION ISOLATION LEVEL 命令来修改事务隔离级别。

语法格式为：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

主要修改时必须使用SESSION或GLOBAL进行修饰，SET TRANSACTION without GLOBAL or SESSION is not permitted while there is an active transaction，否者会报“ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress”

修改Demo

START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

查看事务锁持有的锁

SELECT  
r.trx_id waiting_trx_id ,
r.trx_query waiting_query ,
b.trx_id blocking_trx_id ,
b.trx_query blocking_query ,
b.trx_mysql_thread_id blocking_thread ,
b.trx_started ,
b.trx_wait_started
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id 

 

查看当前事务使用的事务隔离级别

SELECT 
p.ID,
P.USER,
P.HOST,
p.DB,
P.TIME,
T.trx_started,
T.trx_isolation_level,
T.trx_tables_locked,
T.trx_rows_locked,
t.trx_state,
p.COMMAND AS process_state
FROM `information_schema`.`INNODB_TRX` t
INNER JOIN `information_schema`.`PROCESSLIST` p
ON t.trx_mysql_thread_id=p.id;

 

查看未提交事务

## 查看未提交的事务 ##
SELECT 
p.ID,
p.USER,
p.HOST,
p.DB,
p.TIME,
t.trx_started,
TIMESTAMPDIFF(second,t.trx_started,now()) as trx_seconds,
t.trx_isolation_level,
t.trx_tables_locked,
t.trx_rows_locked,
t.trx_state,
p.COMMAND AS process_state
FROM `information_schema`.`INNODB_TRX` t
INNER JOIN `information_schema`.`PROCESSLIST` p
ON t.trx_mysql_thread_id=p.id
WHERE t.trx_state='RUNNING'
AND p.COMMAND='Sleep' 
ORDER BY t.trx_started ASC;

查看阻塞事务

SELECT
p2.`ID` as blocked_process_id,
p2.`COMMAND` as blocked_process_state,
p2.`HOST` as blocked_host,
p2.`USER` as blocked_user,
r.trx_id as bloecked_trx_id,
r.trx_state as blocked_trx_state,
r.trx_started as blocked_trx_start_time,
TIMESTAMPDIFF(SECOND,r.trx_started,CURRENT_TIMESTAMP) as blocked_trx_start_seconds,
TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) as blocked_trx_wait_seconds,
r.trx_query as blocked_query,
r.trx_tables_locked as blocked_trx_tables_locked,
r.trx_rows_locked as blocked_trx_row_locked,
r.trx_rows_modified as blocked_trx_rows_modified,
concat('index: ',l.lock_table,'.',m.`lock_index` , ', lock_mode: ',m.`lock_type`,',lock_mode: ',m.`lock_mode`) as blocked_lock_info,
m.lock_data blocked_lock_data,
p.`ID` as blocking_process_id,
p.`COMMAND` as blocking_process_state,
p.`HOST` as blocking_host,
p.`USER` as blocking_user,
b.trx_id as blocking_trx_id,
b.trx_state as blocking_trx_state,
b.trx_started as blocking_trx_start_time,
TIMESTAMPDIFF(SECOND,b.trx_started,CURRENT_TIMESTAMP) as blocking_trx_start_seconds,
b.trx_query blocking_query,
b.trx_tables_locked as blocking_trx_tables_locked,
b.trx_rows_locked as blocking_trx_row_locked,
b.trx_rows_modified as blocking_trx_rows_modified,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) blocking_thread_idle_seconds,
CONCAT('kill ',p.`ID`,';') kill_sql
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r
ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l
ON w.blocking_lock_id = l.lock_id
AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m
ON m.`lock_id`=w.`requested_lock_id`
AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema.PROCESSLIST p
ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema.PROCESSLIST p2
ON p2.ID = r.trx_mysql_thread_id
ORDER BY blocked_trx_wait_seconds DESC ;