本文主要是介绍行锁升级表锁如何避免？表锁后如何排查？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
一、那些场景会造成行锁升级表锁
说明：
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博客
这篇关于行锁升级表锁如何避免？表锁后如何排查？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
