InnoDB存储引擎锁监控及处理

最新推荐文章于 2023-01-06 09:15:56 发布

路～～～

最新推荐文章于 2023-01-06 09:15:56 发布

阅读量186

点赞数

本文链接：https://blog.csdn.net/cxs_123/article/details/115049241

版权

前言

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远由于MyISAM存储引擎的表级锁定。当系统的并发量较高的时候，InnoDB存储引擎的整体性能和MyISAM存储引擎相比就会有比较明显的优势了。但是，InnoDB存储引擎的行级锁定同样也有脆弱的一面，当我们使用不当的时候，可能会让InnoDB存储引擎的整体性能表现得不仅不能比MyISAM存储引擎高，甚至可能会更差。

监控锁状态

为什么要监控锁状态？因为数据库中有大量的锁等待的话，不仅会占用 CPU 大量的 “sy”、"wa"时间，还会导致大量的回滚操作（锁等待的事务会进入缓存中等待回滚）占用大量的IO。
方法一、
1、查看行级锁争用情况

如果发现MySQL运行比较卡顿，可查看行级锁得争用情况，看看是否存在比较严重得锁争用情况。

db01 [test]>show status like 'innodb_row_lock%';  //查看关于行级锁得一些信息
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |      #当前有多少锁等待
| Innodb_row_lock_time          | 52851 |
| Innodb_row_lock_time_avg      | 26425 |
| Innodb_row_lock_time_max      | 51025 |
| Innodb_row_lock_waits         | 2     |       #一共发生过多少锁等待
+-------------------------------+-------+
5 rows in set (0.01 sec)

2、查看哪个事务在等待（被阻塞了）

mysql> USE information_schema;
mysql> SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';  查看锁等待的事务
   trx_id :       #事务ID号
   trx_state :     #当前事务的状态
   trx_mysql_thread_id:   #连接层  被阻塞事务的会话线程ID(SHOW PROCESSLIST ===>Id或trx_id )
   trx_query :            #当前被阻塞的操作语句(一般是要丢给开发的)

3、查看锁源，谁锁的我

mysql> SELECT * FROM sys.innodb_lock_waits;     ## ====>被锁的和锁定它的之间关系
   locked_table :       #哪张表出现的等待 
   locked_type  :       #锁的类型（Record、Next、GAP）
   waiting_trx_id:      #等待的事务(与上个视图trx_id 对应)
   waiting_pid   :      #等待事务的连接线程号(与上个视图trx_mysql_thread_id是一回事)
   waiting_query :      #当前被阻塞的操作语句
   waiting_lock_mode:   #锁等待的类型（X、S）
   blocking_trx_id :     #锁源的事务ID 
   blocking_pid    :      #锁源的事务连接线程号  -->连接层的线程，用于接收和返回SQL语句，实际并不处理SQL语句.   发起修改的语句首先到达连接层，然后由连接层传递给SQL层进行语句处理
   sql_kill_blocking_connection:       #处理建议

4、根据锁源的 pid，找到锁源执行 SQL 语句的 SQL线程

mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=15;    #processlist_id即blocking_pid

5、找到锁源的 SQL 语句

-- 当前在执行的语句
mysql> SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;    #SQL层的thread_id是真正执行SQL语句的线程   找到SQL_TEXT，就是锁源的语句（注意和被阻塞语句的区别）
-- 执行语句的历史
mysql> SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;    #通过thread_id找到SQL_TEXT时，通过观察有可能此语句并不是真正锁源语句，也可能是SQL_TEXT显示出的语句之前就已经有语句致使了锁等待，所以需要查看产生锁等待的历史，从而找到真正的锁源。

6、得出结果，修改SQL语句

方法二、
使用 InnoDB Monitor来监控锁状态。
对于 MySQL 5.6.16+版本的数据库，可通过设置 innodb_status_output 参数来开启监控

mysql> set global innodb_status_ouput=on;     #开启innodb_status_output参数后，就会将监控状态输出到MySQL的错误日志中。每隔15秒产生一次输出。若是设置为off，则监控状态不会输出到错误日志中
mysql> show engine innodb status\G;      #若是不想打开MySQL的错误日志，可直接使用此命令查看监控结果

tip：关于死锁的监控
可在配置文件中配置 “innodb_print_all_deadlocaks =1” ，此参数会将产生死锁的信息输出到错误文件。当然也可通过"show engine innodb status\G;" 语句来查看死锁的信息

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突得表、数据行等，分析锁争用得原因。
关于InnoDB Monitor具体得使用信息可参考此篇文章：传送门