mysql执行语句问题跟踪

Posted on | In

1.查看事物隔离级别

1
2
3
4
5
6
7
mysql> show session variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

2.设置事物隔离级别

1
2
3
mysql> set session transaction isolation level repeatable read;

mysql> set session autocommit=0;

3.开启事物

1
mysql> start transaction;

4.提交事物

1
mysql> commit;

5.查看死锁记录

1
mysql> show engine innodb status;

6.事物相关查询

MySQL支持的四种事务隔离级别：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE

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

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

修改事务隔离级别参数

1
2
SET GLOBAL tx_isolation='REPEATABLE-READ'; 
SET SESSION tx_isolation='SERIALIZABLE';

查看事务锁持有的锁

1
2
3
4
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

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

1
2
3
4
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;

查看未提交事务

1
2
3
4
5
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
WHERE t.trx_state='RUNNING' AND p.COMMAND='Sleep' 
ORDER BY T.trx_started ASC;

查看阻塞事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT P2.`ID` BlockProcessID,
p2.`HOST` Blockedhost,p2.`USER` BlockedUser,  
r.trx_id BlockedTrxId,r.trx_mysql_thread_id BlockedThreadId,      
TIMESTAMPDIFF(SECOND,      
r.trx_wait_started,      
CURRENT_TIMESTAMP) WaitTime,      
r.trx_query BlockedQuery,      
l.lock_table BlockedTable,    
m.`lock_mode` BlockedLockMode,  
m.`lock_type` BlockedLockType,  
m.`lock_index` BlockedLockIndex,  
m.`lock_space` BlockedLockSpace,  
m.lock_page BlockedLockPage,  
m.lock_rec BlockedLockRec,  
m.lock_data BlockedLockData,
p.`ID` BlockingProcessID,  
p.`HOST` blocking_host,   
p.`USER` blocking_user,  
b.trx_id BlockingTrxid,      
b.trx_mysql_thread_id BlockingThreadId,  
b.trx_query BlockingQuery,  
l.`lock_mode` BlockingLockMode,  
l.`lock_type` BlockingLockType,  
l.`lock_index` BlockingLockIndex,  
l.`lock_space` BlockingLockSpace,  
l.lock_page BlockingLockPage,  
l.lock_rec BlockingLockRec,  
l.lock_data BlockingLockData,           
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx               
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 WaitTime DESC ;