【MySQL中的锁】

原文链接：https://blog.csdn.net/soonfly/article/details/70238902

首先从思想上无论在java还是mysql都有这乐观锁和悲观锁的概念，数据库可以分为悲观锁（共享锁和排他锁），本文就从这里入手

[1] 为什么要加锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

[2] 不同引擎所支持的锁？

它们都支持表级锁，BDB采用页面锁，InnoDB采用的是行级锁。

MyISAM和MEMORY存储引擎：采用的是表级锁（table-level locking）；

BDB存储引擎：采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎：既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MySQL的表级锁有两种模式**：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）**。

[3] 行锁：悲观锁

InnoDB实现了以下两种类型的悲观锁。

• X锁（Exclusive Lock）—排他锁或独占锁：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁.它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止.在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁.

  注意：排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是不加锁的就没办法控制了.

排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁，而并非不能读

在select命令中使用独占锁的SQL语句为：select … for update;

• S锁（Shared Lock）—共享锁：若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁.这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改.

如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select … lock in share mode

InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

[4] 意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

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

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
事务可以通过以下语句显式给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE。
用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

[5] 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

[5] MVCC：乐观锁

https://www.jianshu.com/p/56fa361e0d94

mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。 什么是多版本并发控制呢 ？

MVCC：multiversion concurrency control。其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。 而每一个事务在启动的时候，都有一个唯一的递增的版本号。

1、在插入操作时 ： 记录的创建版本号就是事务版本号。比如我插入一条记录, 事务id 假设是1 ，那么记录如下：

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;

3、删除操作的时候，就把事务版本号作为删除版本号。比如

delete from table where id=1;

4、查询操作：
从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1. 删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的。
2. 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前。

创建版本号<=当前事务版本号<删除版本号

1. 这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是： 通过版本号来减少锁的争用。
2. 另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用MVCC
   read-uncommited由于是读到未提交的，所以不存在版本的问题
3. 而serializable 则会对所有读取的行加锁。

[6] 什么时候会加锁？

在数据库增删改查四种操作中**，insert、delete和update都是会加排它锁**(Exclusive Locks)的，select不会加锁，只有显式声明才会加锁:

• select: 即最常用的查询，是不加任何锁的
• select … lock in share mode: 会加共享锁(Shared Locks)
• select … for update: 会加排它锁