MySQL锁分析和监控

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

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

大多数时候我们通过执行show engine innodb status来查看和监控数据库的锁信息,其实还有更简单的方式,MySQL将事务和锁信息记录在了information_schema数据库中,我们只需要查询即可。

涉及的表主要有三个表:

我们通过实例分析来说明如何监控事务和锁,首先开启事务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_TRX\G

***************************1. row ***************************

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

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在事务T2执行过程中我们来监控锁信息,首先来查询INNODB_LOCK_WAITS数据表,可以看到上面T1,T2两个事务已经产生了锁等待。

mysql>SELECT * FROM INNODB_LOCK_WAITS\G

***************************1. row ***************************

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_LOCKS\G

***************************1. row ***************************

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_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的数值,说明有锁等待现象,然后点击小圆点,即可以定位到相关锁信息,是不是更方便了。

最后给大家留个问题,上面可以看到T1阻塞了T2,为啥T2会等待id=10这条数据持有的PK锁?请大家想一想,欢迎留言。

又是一个周末,又是一个深夜。持续原创进行中,欢迎关注。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180908G1Q84O00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券