MySQLwwwhj8828com18088045700锁分析和监控

通常在MySQL的管理和监控中，Active Session（活动会话）是监控指标中的一个很重要的指标，通过活动会话监控，可以很清楚的了解到数据库当前是否有SQL堆积，是否处于非常繁忙的状态。那么除了活动会话之外，还有哪些指标是非常重要的呢，本文就来给大家介绍下MySQL里面另外几个重要指标，事务和锁信息，锁等待的监控。

我们知道事务和锁是数据库中最最核心的内容，有了事务和锁，才保证了数据的ACID特性，上面说到的活动会话监控，可以反映出数据库的一个健康状态，但是如果监控到事务和锁，那么会对数据库的运行状态有更加全面的认识，在数据库出现异常时也可以很快定位到一些问题。比如业务设计开发同学开启了事务但是忘了提交，或者事务提交时间过长，都会导致一些数据库的问题产生，严重时会数据库故障。下面就如何查看和监控事务、锁信息做个简单介绍。

大多数时候我们通过执行show engine innodb status来查看和监控数据库的锁信息，其实还有更简单的方式，MySQL将事务和锁信息记录在了information_schema数据库中，我们只需要查询即可。
INNODB_TRX
记录INNODB未提交事务信息
INNODB_LOCKS
记录INNODB锁信息，当出现锁等待时才有数据
INNODB_LOCK_WAITS
记录锁等待信息，关联INNODB_LOCKS查询。

我们通过实例分析来说明如何监控事务和锁，首先开启事务T1，执行update：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='xxxx' where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

然后查询INNODB_TRX表，可以看到如下信息，表示有1条事务当然没有提交，这个事务就是上面T1没有提交的事务。
mysql> use information_schema
mysql>SELECT * FROM INNODB_TRXG
1. row **

                trx_id: 36076063  （事务ID）
             trx_state: RUNNING （事务正在运行）
           trx_started: 2018-09-08 22:35:32（事务开始时间）
 trx_requested_lock_id: NULL
      trx_wait_started: NULL
            trx_weight: 3
   trx_mysql_thread_id: 882965 （MySQL线程ID）
             trx_query: NULL （执行的SQL语句）
   trx_operation_state: NULL
     trx_tables_in_use: 0
     trx_tables_locked: 0
      trx_lock_structs: 2
 trx_lock_memory_bytes: 360
       trx_rows_locked: 1 （锁定了1行索引记录）
     trx_rows_modified: 1

trx_concurrency_tickets: 0

   trx_isolation_level: READ COMMITTED （当前事务隔离级别）
     trx_unique_checks: 1 （唯一性检测，因为是UK锁）
trx_foreign_key_checks: 1 （外键检测）

trx_last_foreign_key_error:NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000

      trx_is_read_only: 0

trx_autocommit_non_locking:0
1 row inset (0.00 sec)

然后我们开启事务T2：
mysql>begin;
QueryOK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id<=4 lock in share mode;
ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在事务T2执行过程中我们来监控锁信息，首先来查询INNODB_LOCK_WAITS数据表，可以看到上面T1，T2两个事务已经产生了锁等待。
mysql>SELECT * FROM INNODB_LOCK_WAITSG
1. row **
requesting_trx_id:36076064 （T2请求的事务ID）
requested_lock_id:36076064:69:3:5 （T2请求的锁ID）
blocking_trx_id: 36076063 （T1阻塞的事务ID）
blocking_lock_id: 36076063:69:3:5 （T1阻塞的锁ID）
1 row inset (0.00 sec)

上面我们已经知道了事务T2在执行过程中被事务T1的锁阻塞住了，然后我们就可以通过查询INNODB_LOCKS查询看到的锁详细信息，具体如下所示，可以看到上面的事务T1（36076063）对t1表加了X模式的PK锁，锁类型为Record Lock,锁定了1行数据,锁定的位置为69表空间的第3个页面的第5行记录，锁定记录为10，因为是PK更新，所以这里的lock_data: 10就是id=10的这行记录的PK被加锁了。再来看事务T2（36076064），在请求id=10这个锁的时候无法获取到锁，导致了锁等待。
mysql>SELECT * FROM INNODB_LOCKSG
1. row **

lock_id: 36076064:69:3:5 （锁ID）

lock_trx_id:36076064 （事务ID）
lock_mode: S （锁模式）
lock_type: RECORD （锁类型）
lock_table: test.t1 （锁了哪个表）
lock_index: PRIMARY （锁定的索引类型）
lock_space: 69 （表空间位置）
lock_page: 3 （页位置）
lock_rec: 5 （记录位置）
lock_data: 10 （哪个数据被锁了，如果是PK，这个值就是PK值）
2. row **

lock_id: 36076063:69:3:5

lock_trx_id:36076063
lock_mode: X
lock_type: RECORD
lock_table: test.t1
lock_index: PRIMARY
lock_space: 69
lock_page: 3
lock_rec: 5
lock_data: 10
2 rowsin set (0.00 sec)

现在我们知道了如何定位和查询没有提交的事务，以及锁等待信息，只需要将上面的SQL定时采集告警即可很容易的实现事务和锁的监控了。最近我自己也写了一个demo，通过上面三个SQL监控了事务和锁的信息。

可以看到上面监控里面有大于0的数值，说明有锁等待现象，然后点击小圆点，即可以定位到相关锁信息，是不是更方便了。