mysql 如何判断SQL语句触发的行锁还是表锁？
1.情景展示
我们知道，当在对表执行新增、修改、删除，或者select ...for update时，会触发数据库的锁机制；
但如何才能知道当前操作触发的是哪种锁呢？以mysql为例
2.分析
首先，我们需要了解一下mysql的锁机制：
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在mysql中，锁可以分为：行锁和表锁两种类型；
其次，需要确定默认存储引擎。
MyISAM存储引擎：只支持表锁（table-level locking）；
MEMORY存储引擎：只支持表锁（table-level locking）；
BDB存储引擎：支持页面锁，也支持表级锁，已被InnoDB取代（page-level locking）；
InnoDB存储引擎：支持行锁和表锁，默认使用行级锁（row-level locking）。
需要确定mysql当前正在使用的是存储引擎是不是InnoDB，只有InnoDB才支持行锁；
锁特点：
原理：
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！！！（下面一起来验证一下）
3.解决方案
办法：
可以来两个事务，一个修改某一行数据，先别提交，然后另一个事务去修改别的行数据，如果不阻塞那应该就是行锁，否则就是表锁。
前提：
关闭mysql的自动提交事务；
在Navicat中，要想进行锁测试，必须关掉mysql的自动提交事务机制，实现方式见文末推荐。
先来验证mysql是不是由索引触发的行锁？
第一步：验证表锁
此时，表中没有任何一个索引。
此时当前号为
更新第一行记录，但并未提交事务。
此时来看表数据是否发生变化
点击底部刷新按钮，数据并未变化；
现在，更新第二条记录，并立刻提交。
我们会发现，它一直处于执行状态，如果时间过长，当前事务被迫中止，结果就是更新失败。
我们需要重新执行更新并提交才行，这次，不能再等待这么长时间，之所以这样弄，是为了方便让大家看效果。
这次，重新执行第二条更新语句，此时第一条更新语句还没有提交：
提交第一条更新语句；
此时，隔壁也会立刻提交完毕。
此时，两条记录都已+1。
如此说来，也就证明了上面的观点的前半句：
如果where条件没有索引，触发的将是表锁。
第二步：验证行锁
因为查询条件中有字段RUSERID，所以，为了验证该表能否触发行锁，加上索引试一试。
点击左上角保存
此时，索引已经加上，还是先更新第一行记录，不提交；
接着更新第二条记录，并提交。
再来看下表数据
这个时候，我们再换一个查询条件，把RUSERID去掉（就是查询条件没有涉及到索引列）。
此时，我们还是先更新第一行记录，不提交；再更新第二条记录（提不提交无所谓，都会失败）。
这时，就证明了上面的观点的后半句：
如果where条件有索引条件项（字段加的有索引），触发的将是行锁。
还有一种情况是：联合索引。
为上面的查询条件添加联合索引
再次按照上面的方式和查询条件，执行结果如下：
如此说来，联合索引并不能触发行锁，也只能触发表锁。
4.总结
在mysql中，如果存储引擎是InnoDB，结论如下：
第一，select.. for update, insert,update,delete操作都会触发锁，只要涉及到表的改动，都会触发锁；
第二，表中有索引，并不代表触发的锁就是行锁；
当where条件中含有至少一个字段添加了索引，才会触发行锁，否则就是表锁；
除了上面的条件，还有两种特殊情况：
当表中只有联合索引，而且是根据查询条件所创建的索引（总之，就是他俩涉及的列一模一样），触发的将是表锁，而不是行锁；
当查询结果过多（具体超过全表记录的百分之多少），索引将会失效，触发全表扫描，此时，也会触发表锁。
第三，行锁并不一定就是只锁了一行，具体视情况而定。
2020-12-09
第四，经过测试，当where条件中携带表主键时，即使不走显式索引（主键本身就是一种唯一索引），触发的是行锁，而不是表锁。
2020-12-14
第五，经过测试，当执行插入语句insert into时，同样的，当插入的字段含有索引字段时，触发的是行锁；否则，触发的是表锁。
所以，在高并发需要频繁插入的时候，我们不仅可以使用批量插入语句，还可以创建索引使其触发行锁，从而来避免锁表。
5.扩展延伸
2022年2月26日20:55:46
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
MVCC（多版本并发控制）在MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读和写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。
2022年2月27日20:20:37
mysql事务的4种隔离级别
READ UCOMMITTED: 读取未提交内容
所有的事务都可以“看到”未提交事务的内容。
优点：隔离级别最低，效率最高，但不推荐使用。
缺点：因为能读到其它事务未提交的数据，所以会造成脏读、不可重复读、幻读。
READ COMMITTED: 读取提交的内容
大部分数据库的默认隔离级别，比如：Oracle。一个事务开始时，只能“看见”已提交事务所做的改变；
一个事务从开始到提交前所做的任何数据改变都是不可见的，除非已经提交；
优点：解决了脏读。
缺点：会造成不可重复读、幻读。
当A事务执行一条select语句，此时B事务对该行记录进行了修改并完成了提交，A事务再次执行查询，将会导致同样一条select语句，两次的查询结果不一致。
REPEATEABLE READ: 可重复读
mysql数据库的默认隔离级别。保证了同一个事务的多个实例进行并发读取时，会看到“同样的”数据行。
优点：解决了不可重复读的问题。
还是上面那个例子，当A事务执行一条select语句，此时B事务对该行记录进行了修改并完成了提交，A事务再次执行查询，得到的结果与第一次select结果一样。
缺点：会造成幻读。
事务A批量修改某一列的值，比如：将1改为2，此时，事务B往该表中插入此列为1的数据并完成了提交，如果A事务查看刚刚完成操作的数据，会发现还有一条列值为1的数据没有修改（其实是B事务刚刚插入的，A事务不应该读到）。
InnoDB和Falcon存储引擎可以通过MVCC（多版本并发控制）解决幻读问题。
SERIALIZABLE: 可串行化
隔离级别最高。在每个读的数据行上加锁（也就是表锁）。
优点：解决了幻读问题。
通过强制事务进行排序，使之不可能相互冲突。
缺点：可能导致大量的超时Time Out和锁竞争Lock Conttention。
如果是为了数据的稳定性，需要强制减少并发量，可以选择它。
本文来自博客园，作者：Marydon，转载请注明原文链接：https://www.cnblogs.com/Marydon20170307/p/14105005.html
