行锁升级表锁如何避免？表锁后如何排查？

一、那些场景会造成行锁升级表锁

说明：

InnoDB引擎3种行锁算法（Record Lock、Gap Lock、Next-key Lock）都是锁定的索引。

当触发X锁（写锁）的where条件 无索引 或 索引失效 时，查询的方式就变成全表扫描，也就是扫描所有的聚集索引记录。

为什么要把不匹配的记录也加锁呢？

这里针对的是默认的事务隔离级别：可重复读(RR)，因为要解决 不可重复读 和 幻读问，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其他事务修改（不可重复读问题） 或 间隙被其他事务插入记录（幻读问题），从而导致数据不一致，所以MYSQL的解决方案就是 把所有扫描过的索引记录和间隙都锁上，这时也就出现了我们看到的锁表。

无索引：

例如下面的sql，remark 列不是索引列，如果按照remark列更新就是无索引更新

UPDATE user_info SET user_name = 'keke' WHERE remark = 'keke1'

索引失效：

explain验证：

索引失效的场景很多，我们可以用explain验证：

explain 返回的 key 不是你期望的索引，而是primary；

explain 返回的 type 是 index 或 all

MYSQL成本计算分析认为全表扫描成本更低时：

同样的SQL，传入的参数不同，explain 的结果也不同，有时会走索引、有时索引又会失效！

这里的原因是：更具传入的入参不同、导致 结果集不同。再正式扫描前、MYSQL会进行成本计算。计算走那个索引快，就算命中了索引，但是发现还不如全表扫描快，那么也会不走索引，而是进行全表扫描，这时就会导致索引失效！

关于成本计算：它是先计算不同索引的I/O成本 和 CPU成本，然后进行对比，那个成本低就选择那个执行！

二、如何避免：

1.禁止 WHERE 条件使用无索引列进行 更新/删除

2.尽可能使用聚集索引进行更新/删除

3.如果确实需要用到 非聚集索引进行更新/删除时：

使用explain检测索引是否会失效

避免对索引列进行 类型转换、函数、运算符等会造成升级的操作！

尽可能减少检索条件范围，范围越大就越可能被MySQL成本计算太高，从而导致索引失效！

4.尽可能控制事务的大小、减少锁定的时间

5.使用读已提交（RC）事务隔离级别

读已提交（RC）事务隔离级别，由于没有间隙锁（Gap Lock），所以它的加锁规则相对简单，都是针对匹配索引记录加的Record Lock、因为不用解决 不可重复读 和 幻读 问题，所以也就不存在 锁表了

三、锁表应该如何分析排查

查看InnoDB_row_lock%相关变量

show status like 'innodb_row_lock%';

字段	说明
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	等待总时长: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	等待平均时长: 每次等待所花平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits	等待总次数: 系统启动后到现在总共等待的次数

四、查看 INFORMATION_SCHEMA系统库

可以通过 INFORMATION_SCHEMA系统库提供的：查看事务、锁、锁等待的 数据表 来分析

系统表	说明
INNODB_TRX	查看事务
INNODB_LOCKS	查看锁
INNODB_LOCK_WAITS	查看锁等待
PROCESSLIST	查看连接情况

INNODB_TRX：

下面对 innodb_trx 表的每个字段进行解释：

字段	说明
trx_id	事务ID。只读事务和非锁事务是不会创建id的
trx_state	事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING
trx_started	事务开始时间
trx_requested_lock_id	事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息
trx_wait_started	事务开始等待的时间
trx_weight	事务的权重。代表修改的行数和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
trx_mysql_thread_id	事务线程 ID，可以和 PROCESSLIST 表 JOIN
trx_query	事务正在执行的 SQL 语句。
trx_operation_state	事务当前操作状态
trx_tables_in_use	当前事务执行的 SQL 中使用的表的个数
trx_tables_locked	当前执行 SQL 的行锁数量。因为只是行锁，不是表锁，表仍然可以被多个事务读和写
trx_lock_structs	事务保留的锁数量。
trx_lock_memory_bytes	事务锁住的内存大小，单位为 BYTES
trx_rows_locked	事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行
trx_rows_modified	事务更改的行数。
trx_concurrency_tickets	该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
trx_isolation_level	当前事务的隔离级别
trx_unique_checks	是否打开唯一性检查的标识
trx_foreign_key_checks	是否打开外键检查的标识
trx_last_foreign_key_error	最后一次的外键错误信息
trx_adaptive_hash_latched	自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8
trx_adaptive_hash_timeout	是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0

INNODB_LOCKS：

只有发生阻塞才会有数据，可以查看上锁的详细信息

字段	说明
lock_id	锁 ID
lock_trx_id	拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode	锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type	锁的类型
lock_table	被锁定的或者包含锁定记录的表的名称。
lock_index	当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec	当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

INNODB_LOCK_WAITS：

只有发生锁等待才有数据，可以通过此表找出阻塞的事务ID 和 锁ID

字段	说明
requesting_trx_id	请求的事务ID
requested_lock_id	事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id	阻塞的事务ID
blocking_lock_id	某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

PROCESSLIST：

可以查看连接情况，通过这张表我们可以看到事务所在的主机

字段	说明
ID	线程ID, 可以JOIN INNODB_TRX.trx_requested_lock_id
USER	连接用户
HOST	连接主机 ip:port
DB	连接的数据库

五、如何kill某个事务：

通过对上面的表进行查询, 当我们发现某个事务阻塞了很多事务, 并且执行时间很长时, 我们可以手动中止它。

只需要找到INNODB_TRX 表的trx_mysql_thread_id字段，然后调用kill命令即可：

kill {INNODB_TRX.trx_mysql_thread_id}

学习自：【MySQL】说透锁机制（三）行锁升表锁如何避免? 锁表了如何排查?_为什么断开连接避免锁表-CSDN博客