在数据库中，除了传统计算资源（CPU、RAM、IO）的争夺外，数据也是众多用户共享的资源。如何保证并发访问数据的一致性和有效性是所有数据库必须解决的问题，而锁冲突也是影响数据库并发访问性能的重要因素。从这个角度来说，锁对于数据库来说尤为重要，也更加复杂。本文将通过实验介绍如何排查MySQL8.0版本的锁问题和查找被阻塞的SQL语句。实验中MySQL版本为8.0.26，隔离级别为RR。1、MySQL8.0版本锁检查核心表信息_schema.innodb_trx##运行事务信息。sys.innodb_lock_waits##等待锁的关联事务信息。performance_schema.threads##SQL线程及线程号、进程号、OS线程号等信息2.行锁监控语句及排错步骤#确认是否有锁在等待：showstatuslike'innodb_row_lock%';从information_schema.innodb_trx中选择*；#查询锁等待详情select*fromsys.innodb_lock_waits;---->blocking_pid(锁源连接线程)#通过连接线程ID查找SQL线程语句select*fromperformance_schema.threads;#通过SQL线程查找SQL语句select*fromperformance_schema.events_statements_history;3.测试验证mysql>useworld;mysql>showtables;+----------------+|Tables_in_world|+------------------+|城市||国家||countrylanguage|+----------------+3rowsinset(0.00sec)3.1分别打开两个窗口(session1,session2)s1:#addexclusivelockmysql>begin;mysql>select*fromworld.citywhereid=1forupdate;s2:#addexclusivelockmysql>begin;mysql>updatecitysetname='girl'whereid=1;执行后处于tamping状态，默认50秒超时回滚。ERROR1205(HY000):超出锁定等待超时；尝试重新启动transactionmysql>setinnodb_lock_wait_timeout=5000;##锁等待超时参数，这里设置为5000方便测试。mysql>updatecitysetname='girl'whereid=1;3.2另开一个窗口s3查看锁状态mysql>useinformation_schema;mysql>从innodb_trx\G中选择trx_id、trx_state、trx_started、trx_tables_locked、trx_rows_locked******************************1.行***************************trx_id:8995##第二条语句事务刚刚运行IDtrx_state:LOCKWAIT##进入锁等待状态trx_started:2022-12-2316:00:42trx_tables_locked:1##locked1tabletrx_rows_locked:2##locked2rows*****************************2.row******************************trx_id:8994##刚刚运行trx_state的第一条语句的事务ID：RUNNING##获得锁的状态trx_started:2022-12-2315:59:41trx_tables_locked:1trx_rows_locked:12rowsinset(0.00sec)mysql>select*fromsys.innodb_lock_waits\G**************************1.行******************************等待开始：2022-12-2316:01:57等待年龄：00:00:52等待年龄秒：52:8995waiting_trx_started:2022-12-2316:00:42waiting_trx_age:00:02:07waiting_trx_rows_locked:2waiting_trx_rows_modified:0waiting_pid:33waiting_query:updatecitysetname='girl'whereid=1waiting_lock_id:140190433:12659446:2:140190349859736waiting_lock_mode:X,REC_NOT_GAPblocking_trx_id:8994##阻塞者事务IDblocking_pid:32##阻塞者进程ID,showprocesslist可查;blocking_query:NULLblocking_lock_id:140190433226752:16:6：2：2：140190349865536blocking_lock_mode：x，rec_not_gapblocking_trx_started：2022-12-2315:59:41blocking_trx_age：00:03:03:03:08set(0.00sec)3.3查看进程ID为32的进程，但无法显示当前执行的SQL语句mysql>showprocesslist;+----+-----------------+--------------+----------------+--------+-------+------------------------+--------------------------------------+|Id|User|Host|db|Command|Time|State|Info|+----+---------------+----------------+-------------------+--------+--------+------------------------+-------------------------------------------+|5|事件调度器|本地主机|空|守护进程|27235|等待空队列|无效的||29|根|本地主机：43074|信息架构|查询|0|初始化|显示进程列表||32|根|本地主机：43080|世界|睡觉|248||空||查询|112|更新|更新城市集name='girl'whereid=1|+----+----------------+-----------------+----------------+--------+--------+--------------------+------------------------------------------+4rowsinset(0.00sec)3.4查看进程ID为32的进程对应的SQL线程IDmysql>selectthread_id,processlist_idfromperformance_schema.threadswhereprocesslist_id=32;+-----------+----------------+|thread_id|processlist_id|+------------+--------------+|75|32|+------------+-----------------+1rowinset(0.00sec)3.5根据线程ID75，找到真正执行的SQL语句mysql>;从performance_schema.events_statements_history中选择thread_id,sql_textwherethread_id=75\G******************************1.行***************************thread_id：75sql_text：NULL****************************2.行***************************thread_id：75sql_text：NULL***************************3.行***************************thread_id：75sql_text:NULL******************************4.行***************************thread_id：75sql_text：显示表***************************5.行***************************thread_id：75sql_text：设置自动提交=0****************************6.行***************************thread_id:75sql_text:begin****************************7.行***************************thread_id:75sql_text:select*fromworld.citywhereid=1forupdate******************************8.行***************************thread_id：75sql_text：NULL****************************9.行***************************thread_id：75sql_文本：显示数据库*********************************10.行*****************************thread_id:75sql_text:showtables10rowsinset(0.00sec)findselect*fromworld.citywhereid=1forupdate语句，确认后，如果没有问题,可以kill3.6processing锁源SQL对应的连接线程。kill32;3.7通过设置时间回滚申请锁的事务，回滚等待的事务，解决锁冲突。设置innodb_lock_wait_timeout=500；#设置时间回滚申请锁的事务。4、innodb_lock_wait_timeout参数innodb_lock_wait_timeout是指一个事务等待获取资源的最长等待时间。如果超过这个时间还没有分配资源，则返回申请失败；该参数的时间单位为秒，最小可设置为1s，最大可设置为1073741824秒（34年），默认安装值为50s。当锁等待超过设定的时间，会报如下错误；ERROR1205(HY000):超出锁定等待超时；tryrestart事务参数支持session和global作用域，支持动态修改，可以通过两种方式修改；通过语句修改setinnodb_lock_wait_timeout=50；设置全局innodb_lock_wait_timeout=50；注意global的修改对当前线程无效，只有在建立新连接时修改参数文件/etc/my.cnfinnodb_lock_wait_timeout=50
