MySQL表锁、行锁、排它锁及共享锁的使用详解 MySQL表锁、行锁、排它锁及共享

前言事务隔离级别的实现原理：简单来说就是各种锁机制和MVCC多版本并发控制
我们学习知识的时候，需要了解知识点出现的原因，什么情况下能用到这个知识
我们说到事务，就得说到事务的ACID特性，说到隔离性的时候，事务要能够允许并发执行，并发执行为了同时保证数据的安全性，一致性和并发的效率，就需要设置事务的隔离级别
一、事务隔离机制的选择

如果我们完全不管，使用未提交读的事务隔离机制，任由这些线程并发操作数据库，那就会出现脏读（读取了未commit的数据）、不可重复读（两次查询值不同）、幻读（两次查询数据量不同）等问题，数据的安全性最低，优点是并发效率非常高，一般不会使用
如果我们串行化（靠锁实现），通过锁给所有的事务都排个序，虽然数据的安全性提高了，并发的效率就太低了，一般也不会使用
所以我们一般用的是已提交读、可重复读这两个隔离级别，平衡了数据的安全性，一致性以及并发的效率，是由MVCC多版本并发控制实现的（MVCC是已提交读和可重复读的原理，锁是串行化的原理）

二、表级锁&行级锁表级锁：对整张表加锁。开销小（因为不用去找表的某一行的记录进行加锁，要修改这张表，直接申请加这张表的锁），加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低
行级锁：对某行记录加锁。开销大（需要找到表中相应的记录，有搜表搜索引的过程），加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高

文章图片

InnoDB存储引擎支持事务处理，表支持行级锁定，并发能力更好

InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味者只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁
由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行
即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率高，此时会放弃使用索引，因此也不会
使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引

三、排它锁（Exclusive）和共享锁（Shared）排它锁，又称为X锁，写锁
共享锁，又称为S锁，读锁
读读（SS）之间是可以兼容的，但是读写（SX、SX）之间，写写（XX）之间是互斥的
对事务加X和S锁之间有以下的关系：

一个事务对数据对象A加了 S 锁，可以对A进行读取操作但不能进行update操作，加锁期间其它事务能对A加S锁但不能加 X 锁
一个事务对数据对象A加了 X 锁，就可以对A进行读取和更新，加锁期间其它事务不能对A加任何锁

显示加锁：select … lock in share mode强制获取共享锁，select … for update获取排它锁
1. 测试不同事务之间排它锁和共享锁的兼容性
我们先查看表的SQL以及内容

文章图片

查看隔离级别：

文章图片

首先开启一个事务，给id=7的数据加上排它锁

文章图片

在用另一个客户端开启事务

文章图片

我们用另一个事务的服务线程给id=7的数据加上排它锁，阻塞了

文章图片

我们尝试给id=7的数据加上共享锁，还是阻塞了
总结：不同事务之间对于数据的锁，只有SS锁可以共存，XX、SX、XS都不能共存
2. 测试行锁加在索引项上

其实行锁是加在索引树上的

文章图片

用表的无索引字段作为过滤条件

文章图片

事务2现在同样想获取这条记录的排它锁，可想而知地失败了；那现在事务2获取chenwei的记录的排它锁，试试能不能成功

文章图片

InnoDB是支持行锁的，刚才以主键id为过滤条件时，事务1和事务2获取不同行的锁是可以成功的。然而现在我们发现获取name为chenwei的排它锁也获取不到了，这是为什么？我们解释一下：
InnoDB的行锁是通过给索引项加锁来实现的，而不是给表的行记录加锁实现的
而我们用name作为过滤条件没有用到索引，自然就不会使用行锁，而是使用表锁。这就意味着只有通过索引检索数据，InnoDB才使用行级锁，否则InnoDB都将使用表锁!!!
我们给name字段加上索引

文章图片

文章图片

我们发现，给name加上索引后，两个事务可以获取到不同行的排它锁（for update），再一次证明了InnoDB的行锁是加在索引项上的

文章图片

因为现在name走的是索引，通过zhangsan在辅助索引树上找到它所在行记录的id是7，然后到主键索引树上，获取对应行记录的排他锁（个人猜测应该是辅助索引树和主键索引树相应的记录都加了锁）
四、串行化隔离级别测试
（所有的事务都使用排它锁或共享锁，不需要用户手动加锁）
设置串行化隔离级别

文章图片

两个事务可以同时获取共享锁（SS共存）

文章图片

现在让事务2插入数据

文章图片

此时由于insert需要加排它锁，但由于事务1已经对整张表添加了共享锁，事务2无法再对表成功加锁（SX不共存）
rollback一下

文章图片

因为我们给name加上了索引，以上的select相当于给name为zhangsan的数据加上了行共享锁
事务2 update

文章图片

事务2不能update，因为此时已经被事务1的共享锁锁住了整个表
事务2在辅助索引树上找zhangsan，找到对应的主键值，然后去主键索引树找到相应的记录，但是发现这行记录已经被共享锁锁住了，事务2可以获取共享锁，但是不能获取排他锁

文章图片

我们用主键索引id试试能不能update

文章图片

依然阻塞住了，虽然我们where后面的字段现在使用的id而不是name，但是name也是通过辅助索引树找到对应的主键，再到主键索引树上找相应的记录，而主键索引树上的记录加了锁（个人猜想应该是辅助索引树和主键索引树对应的数据都加了锁）
我们update id=8的数据，成功了。因为我们select的时候，只是给id=7的数据加上了行锁，我们操作id=8的数据当然可以成功

文章图片

有索引，则使用行锁；没有索引，则使用表锁。
表级锁还是行级锁说的是锁的粒度，共享锁和排他锁说的是锁的性质，不管是表锁还是行锁，都有共享锁和排他锁的区分
总结 【MySQL表锁、行锁、排它锁及共享锁的使用详解】到此这篇关于MySQL表锁、行锁、排它锁及共享锁使用的文章就介绍到这了,更多相关MySQL表锁、行锁、排它锁和共享锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！