MySql中乐观锁、共享锁、悲观锁、排它锁、行锁、表锁

本文链接：https://blog.youkuaiyun.com/qq630326347/article/details/107800438

乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

举例：

下单操作包括3步骤：

1.查询出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};

除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可能自行搜索"hiberate 乐观锁"试试看。

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享(shared lock)和排他锁(exclusive lock)

InnoDB 实现了标准的行级锁，主要分为两类：共享锁和排他锁。

共享锁（s）允许事务获取锁来读取某行记录。
排他锁（x）允许事务获得锁来更新或者删除某行记录。

如果事务T1获得某记录(r)的一个共享锁（s），那么就r记录来说，来自其他事务（T2）的请求会按照下面两种情况被处理：

T2发出了对r记录的s锁请求：立即获得s锁，这样T1, T2就都获得了s锁。
T2发出了对r记录的x锁请求：无法获取，需要等待。

如果T1事务获取了对r记录的x排他锁，那么来自其他事务（T2）的请求，无论是s锁请求还是x锁请求，都无法立即获取，而是等待T1释放了对r记录的x锁后才能获取。

注意：更新和删除操作系统加的排他锁，这个无法更改，比如：

UPDATE employee SET money = money + 5000 WHERE `name`='1001';加的是排他锁，而不能手动加锁，如：

UPDATE employee SET money = money + 5000 WHERE `name`='1001' lock in share mode;

UPDATE employee SET money = money + 5000 WHERE `name`='1001' FOR UPDATE;

是错误的写法。

查询操作可以加共享锁、记录锁、间隙锁，不加则表示不使用锁，比如：

SELECT * FROM employee WHERE `name`='1001'; #无锁

SELECT * FROM employee WHERE `name`='1001' FOR UPDATE; # 记录锁

SELECT * FROM employee WHERE `id` betwen 1 and 10 FOR UPDATE; # 间隙锁

SELECT * FROM employee WHERE `name`='1001' lock in share mode; #共享锁

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明：

1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。

记录锁

记录锁是指加在索引记录上的锁。比如，select c1 from t where c1 = 10 for update；这条语句可以防止其他事务插入、更新、或者删除t.c1字段为10的行。

记录永远只锁住索引记录，哪怕一个表没有定义任何索引。在这种情况下，InnoDB会创建一个隐藏的簇索引并且用它来进行记录加锁。具体方法参考簇索引和第二索引;

记录锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。

同时查询语句必须为精准匹配（=，in），不能为 >、<、like等，否则也会退化成表锁。

间隙锁（Gap Locks）

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

除了手动加锁外，在执行完某些 SQL 后，InnoDB 也会自动加间隙锁。

临键锁（Next-Key Locks）

Next-Key （NK锁）可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

死锁的发生及解决

当事务锁定多个表中的行（通过诸如UPDATE或的语句SELECT ... FOR UPDATE）但顺序相反时，可能会发生死锁。

为了减少死锁的可能性，请使用事务而不是LOCK TABLES语句；
保持用于插入或更新数据的事务足够小，以使其长时间不保持打开状态；
当不同的事务更新多个表或大范围的行时，SELECT ... FOR UPDATE在每个事务中使用相同的操作顺序；
在SELECT ... FOR UPDATE和 UPDATE ... WHERE 语句中使用的列上创建索引，避免出现表锁。

死锁的可能性不受隔离级别的影响，因为隔离级别更改了读取操作的行为，而死锁则是由于写入操作而发生的。

启用死锁检测（默认设置）并且发生死锁后，将InnoDB检测条件并回滚事务之一。如果使用innodb_deadlock_detect 配置选项禁用了死锁检测，则在死锁的情况下InnoDB依靠该 innodb_lock_wait_timeout设置回滚事务。因此，即使您的应用程序逻辑正确，您仍必须处理必须重试事务的情况。要查看InnoDB用户事务中的最后一个死锁，请使用 SHOW ENGINE INNODB STATUS命令。如果频繁出现死锁，说明事务结构或应用程序错误处理存在问题，请使用 innodb_print_all_deadlocks 启用此设置可将有关所有死锁的信息打印到 mysqld错误日志中。

锁类型的兼容性汇总表

	X（排他锁）	IX（意向排他锁）	S（共享锁）	IS（意向共享锁）
X（排他锁）	冲突	冲突	冲突	冲突
IX（意向排他锁）	冲突	兼容	冲突	兼容
S（共享锁）	冲突	冲突	兼容	兼容
IS（意向共享锁）	冲突	兼容	兼容	兼容

两锁冲突代表事务在已经获取了其中某个锁后无法再获取另外一个锁，兼容表示可以同时获得多个锁。

注意记录锁、间隙锁及临键锁和其他锁也是冲突的，不能兼容，所以记录锁、间隙锁及临键锁是一种特殊的排他锁，因为他锁定的是索引，而update和delete语句是排他锁，但是其内部不是锁定的索引，所以不会出现表锁的情况。经过实际测试确实是这样，这个和网上很多文章说的有差异，需要特别注意。

MySQL隔离级别

一、事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

二、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否