大家都知道,锁是可以阻碍读写的,锁越多,数据库并发性能就越差,所以我们要随时准备监测锁的问题.
当然根本问题还是要开发要控制好锁粒度,把where条件写好,这样才能减少锁的影响.
另外,死锁其实不可怕,因为mysql内部发现死锁会发生回滚操作,所以不会真的"死"了,可怕的是锁等待,并发太高下一大堆语句等着某个语句释放锁,这才是严重阻碍效率的情况.
当然了,也不是说死锁的问题不重要,如果出现大量的死锁,那显然就是业务逻辑的问题了,需要和开发人员好好谈谈,不是随便谁谁能解决的事情.
查看事务隔离级别
首先,我们知道mysql有四种隔离级别,不同隔离级别,锁的等级也不一样,所以,查看锁的信息之前一定要先看当前的隔离级别是什么.
|
1
2
3
4
5
6
7
|
mysql>
select
@@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row
in
set
(0.00 sec)
|
当前是RR的隔离级别
当然,除了改配置文件,你是能在线改的,下面是改成RC级别,注意[ global | session ]区别:
|
1
2
3
|
mysql>
set
global transaction isolation level READ COMMITTED;
或者
mysql>
set
global tx_isolation=
'read-committed'
;
|
查看锁信息
在mysql里面有两个记录数据库性能的库information_schema和performance_schema,是专门记录mysql其他库的事务ID、锁信息、锁等待时间、缓存使用情况、sql执行信息等等信息,现在我们只看我们需要的信息。而performance_schema在5.7之前是默认不开启的，因为会占用性能，而在5.7之后是默认开的。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
#查看innodb事务ID,会显示是什么操作和一些常规信息,例如是否在运行running,还是等待锁.
SELECT * FROM information_schema.INNODB_TRX\G
#查看当前innodb的锁的信息,会显示是什么锁类型,属于那个事务ID，通常只显示行锁
SELECT * FROM information_schema.innodb_locks\G
#查看innodb锁的等待时间,和等待的是那的是那个事务ID的锁
select
* from information_schema.innodb_lock_waits\G
#查看metadata lock信息,即元数据锁,这种锁会让任何语句都不能操作这个表,包括表结构
select
* from information_schema.processlist where state =
'Waiting for table metadata lock'
;
#上面这些语句要是查不到数据,那就证明当前没有响应的锁信息,查到了那就是有了.
#要想查询历史数据来统计性能,可以用下面这两个语句来计算,计算方法可以另外查查
show status like
'%lock%'
;
show variables like
'%timeout%'
;
#查看表锁，上面那些sql未必会显示出来
show OPEN TABLES where In_use > 0;
#handles锁，可以理解为要执行写操作的表锁，所以会排除其他所有sql的读写
select
* from performance_schema.table_handles where OWNER_THREAD_ID > 0;
#查看那个线程加表锁
select
pth.* from performance_schema.table_handles as pt
join
performance_schema.threads as pth on pt.OWNER_THREAD_ID=pth.thread_id where pt.OWNER_THREAD_ID > 0 \G
#当前正在干活的线程在执行什么语句以及相关的信息，也就是确认那些sql在锁数据
select
* from performance_schema.events_statements_current\G
#查看当前会话信息，有时候可以分析下会话的操作
select
* from sys.session\G
|
查看死锁信息
注意,查询死锁的信息的语句,记录的是最后一条的死锁信息,之前的是没记录下来的,
查询当前死锁信息的命令:
|
1
|
show engine innodb status\G
|
如果有DEADLOCK标识,也就是说出现过死锁,如果没有,也就是没出现过.
这个界面只会记录最后一个死锁,不是只记录一个,所以你能看到的死锁语句只有一条而且是最近出现的一条,这个要注意一下.
除非你在配置文件my.cnf里面加入下面参数,那就会将死锁记录到mysql的报错文件中，例如默认的mysql.err里面
|
1
|
innodb_print_all_deadlocks = 1
|
也正如开头说的，InnoDB中死锁会自动被检测出，并选择代价较小的事务进行回滚以打破死锁。事务完全回滚后其保持的锁被全部释放，若是仅有单条SQL由于错误发生了回滚则语句保持的锁可能不会被释放，因为InnoDB中不保存哪条语句持有哪些锁的信息。若事务中的select调用了存储函数，函数中的SQL执行失败，则该语句被回滚。
因此,死锁并不危险但频繁出现就有问题了。应用中应做好出现死锁导致事务回滚后的后续处理逻辑,频繁出现就应该和开发沟通去修改业务逻辑避免死锁。
查找锁相关的语句的线程号
很多时我们说假如有个锁等待实在太长,已经不想去执行他,但是他严重阻碍了后面重要语句的执行,那怎么办呢?虽然说能kill掉语句,但是又不确定那条跟他有关系,所以就很头痛,这个时候可以用下面这条语句
select `r`.`trx_wait_started` AS `wait_started`,timediff(now(),`r`.`trx_wait_started`) AS `wait_age`,timestampdiff(SECOND,`r`.`trx_wait_started`,now()) AS `wait_age_secs`,`rl`.`lock_table` AS `locked_table`,`rl`.`lock_index` AS `locked_index`,`rl`.`lock_type` AS `locked_type`,`r`.`trx_id` AS `waiting_trx_id`,`r`.`trx_started` AS `waiting_trx_started`,timediff(now(),`r`.`trx_started`) AS `waiting_trx_age`,`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,`r`.`trx_mysql_thread_id` AS `waiting_pid`, `r`.`trx_query` AS `waiting_query`,`rl`.`lock_id` AS `waiting_lock_id`,`rl`.`lock_mode` AS `waiting_lock_mode`,`b`.`trx_id` AS `blocking_trx_id`,`b`.`trx_mysql_thread_id` AS `blocking_pid`, `b`.`trx_query` AS `blocking_query`,`bl`.`lock_id` AS `blocking_lock_id`,`bl`.`lock_mode` AS `blocking_lock_mode`,`b`.`trx_started` AS `blocking_trx_started`,timediff(now(),`b`.`trx_started`) AS `blocking_trx_age`,`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,concat('KILL QUERY ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_query`,concat('KILL ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection` from ((((`information_schema`.`innodb_lock_waits` `w` join `information_schema`.`innodb_trx` `b` on((`b`.`trx_id` = `w`.`blocking_trx_id`))) join `information_schema`.`innodb_trx` `r` on((`r`.`trx_id` = `w`.`requesting_trx_id`))) join `information_schema`.`innodb_locks` `bl` on((`bl`.`lock_id` = `w`.`blocking_lock_id`))) join `information_schema`.`innodb_locks` `rl` on((`rl`.`lock_id` = `w`.`requested_lock_id`))) order by `r`.`trx_wait_started`\G
这条语句会计算出当前锁的关联信息,如果你不想那个锁继续执行,那就执行最后显示的kill命令就可以了.
附录一些information_schema 表结构的信息
|
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
46
47
48
49
50
51
52
53
54
55
56
|
information_schema > desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |
#锁ID
| lock_trx_id | varchar(18) | NO | | | |
#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |
#锁模式
| lock_type | varchar(32) | NO | | | |
#锁类型
| lock_table | varchar(1024) | NO | | | |
#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |
#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |
#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |
#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |
#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |
#被锁的数据
+-------------+---------------------+------+-----+---------+-------+
10 rows
in
set
(0.00 sec)
information_schema > desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
#请求锁的事务ID
| requested_lock_id | varchar(81) | NO | | | |
#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |
#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |
#当前拥有锁的锁ID
+-------------------+-------------+------+-----+---------+-------+
4 rows
in
set
(0.00 sec)
information_schema > desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |
#事务ID
| trx_state | varchar(13) | NO | | | |
#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |
#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |
#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |
#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |
#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |
#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |
#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |
#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
#
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows
in
set
(0.01 sec)
|
XA事务锁
严格来说，XA事务分为内部XA和外部XA，我们常说的XA事务其实指的是外部XA事务，内部XA一般只涉及binlog提交，和外部XA没关联。
外部xa事务是分布式事务的意思，可以实现不同数据库同步查询和修改数据的目的，mysql很久之前就支持XA事务了，不过仅支持innodb引擎。在5.7之前性能都非常差，并难以保证数据一致性，所以几乎没人用，在5.7较后的版本中，mysql修复了XA事务的bug，数据一致性得到了很大得提高，所以就可以提上使用的日情了。但是，性能还是很差。。。
所谓不同数据库的同步，可以同是mysql，也可以是mysql+sql server或mysql+oracle。不过无论你是什么组合都好，都必须要有一个XA事务控制器，用来统筹XA事务的锁和提交、回滚，可以是用开源的，也可以是你自己写的（技术足够NB）。
而我们用的环境是mysql+sql server外加spring could的XA事务控制器，怎么安装我就不多说了，这篇文章也不是重点介绍这个方面，重点来说XA事务锁的问题。
好了，回归正题，XA事务锁是个让人很头痛的问题，因为动不动就是来个表锁，为什么说性能很差，也正是因为并发大的时候会出现很多表锁，而表锁的粒度太大，直接就影响了并发。
而且XA的bug虽然修复了，但是机制的缺陷还是存在，mysql没有记录外部xa日志，在5.7也只是prepare和commit的时候才记录binlog，粒度还不够。xa事务在你控制器断掉了之后，事务节点会一直死等你控制器起来。如果节点自己有日志可以判断事务是要回滚还是提交就自己做，他自己没办法判断就要等控制器，一直等的结果可能就是挂死一个表的锁，如果是读锁就阻碍alter，如果是写锁就完全拒绝别的表操作，最终导致服务不能使用，这是坑点。
说了那么多，是怎么查XA事务锁呢，上面那些语句是查询一般sql的锁，XA事务的锁，是无能为力的，也就只有查到一些事务在跑，但是没有任何信息，你也不能判断他是否XA事务，因为XA事务有专门的命令提供：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
#启动XA事务
XA {START|BEGIN} xid [JOIN|RESUME]
#完成XA事务
XA END xid [SUSPEND [FOR MIGRATE]]
#PREPARE这个XA事务
XA PREPARE xid
#提交这个XA事务
XA COMMIT xid [ONE PHASE]
#回滚这个XA事务
XA ROLLBACK xid
#查看正在运行的XA事务
XA RECOVER [CONVERT XID]
|
来看看实际例子：
|
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
46
47
48
49
|
#一般情况下，你用查锁的语句是查不到的，因为他不是mysql的锁，而是XA的锁
mysql> SELECT * FROM information_schema.innodb_locks\G
Empty
set
, 1 warning (0.00 sec)
#但是你去查事务的话，倒是能查到出来，但是没任何信息，意义有点比较虚
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 148834655
trx_state: RUNNING
trx_started: 2017-09-28 22:58:19
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 9
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 2
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 7
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
#但是并不是语句有问题，而是根本没用对
#XA就是要用XA的语句
mysql> xa recover;
+------------+--------------+--------------+-------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data |
+------------+--------------+--------------+-------------------------------------------------------+
| 1096044365 | 32 | 21 | 192.168.0.3.tm150617699467628158192.168.0.3.tm1123145 |
+------------+--------------+--------------+-------------------------------------------------------+
1 row
in
set
(0.00 sec)
#连续查几次，这个XA事务还在，基本上可以肯定这个XA事务是挂起的了，怎么办？
#那就回滚吧，注意逗号的位置
mysql> XA ROLLBACK
'192.168.0.3.tm150617699467628158'
,
'192.168.0.3.tm1123145'
,1096044365;
Query OK, 0 rows affected (0.00 sec)
mysql> xa recover;
Empty
set
(0.00 sec)
#彻底清空了
#再去尝试查这个事务，就不存在了
mysql> SELECT * FROM information_schema.INNODB_TRX\G
Empty
set
(0.00 sec)
|
这个时候，锁没了，系统又恢复正常了。一个正常的XA事务，虽然是表锁，但是执行时间一般不会很长，所以长时间挂起的并不多，这类一般就建议回滚，因为你也不知道究竟提交后会影响什么数据，或者说你根本不知道能不能提交。
本文转自arthur376 51CTO博客，原文链接：http://blog.51cto.com/arthur376/1773050，如需转载请自行联系原作者
