对于Mysql中锁的理解

Mysql数据库中锁的概念非常多，这次我就来分享一下我对于Mysql中锁的理解
首先我们先明确一下锁的概念：
锁是计算机协调进程或线程并发访问某一资源的机制，在数据库中要保证数据并发访问的一致性、有效性。锁冲突也是影响数据库并发性能的一个重要因素。
我们再来看两个名词：

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。（在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做）
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。（在修改数据之前先锁定，再修改的方式被称之为悲观并发控制）

我们肯定听说过，Mysql中有表锁和行锁之分，那么表锁和行锁到底是什么呢？

• 表级锁：开销小，加锁快；不会产生死锁；锁定粒度大，发生锁冲突的概率最高；并发度最低
• 行级锁：开销大，加锁慢；会产生死锁；锁定粒度小，发生锁冲突的概率最低；并发度最高

• 页面锁：开销和加锁时间界于表级锁和行级锁之间；会产生死锁；锁定粒度界于表解锁与行级锁之间；并发度一般

  来看看MyISAM的表锁，MyISAM的表锁分为共享读锁（Table Read Lock）和独占写锁（Table Write Lock）

  

  MyISAM的表锁.png

  我们通过SQL的形式分别来看看这两种锁到底是怎么运作的

  首先是共享读锁：
  在同一个session中

-- 加读锁
LOCK TABLE my_account READ
-- 查询自己的这一张表不会报错
SELECT * FROM my_account
-- 加了读锁，在同一个session中对表进行修改会报错
UPDATE my_account SET mOney= money - 50 WHERE id = 1
-- 对另外的表进行写操作（报错）
UPDATE test SET c1 = 1 WHERE id = 1
-- 对另外的表进行读操作（报错）
SELECT * FROM test
-- 使用别名查询同一张表也会报错（此处只能查my_account）
SELECT a.* FROM my_account a
-- 若一定要使用该别名则在加锁时就设置别名
LOCK TABLE my_account AS a READ
-- 释放锁
UNLOCK TABLES

在另一个session

-- 在另一个session中对表进行修改会等待锁释放，锁释放后就执行
UPDATE my_account SET mOney= money - 50 WHERE id = 1
-- 在另一个session中对另一个表进行操作不会报错，正常执行

然后是独占写锁
在同一个session中

-- 加写锁
LOCK TABLE my_account WRITE
-- 对同一张表进行操作不会报错
INSERT INTO my_account VALUES(3,3,'wangwu',0)
DELETE FROM my_account WHERE id = 3
SELECT * FROM my_account
-- 对其他表进行查询(报错)
SELECT * FROM test 
-- 对其他表进行修改(报错)
INSERT INTO test VALUES(10,10,10)
-- 释放锁
UNLOCK TABLES

在另一个session

-- 在另一张表中对该表进行操作需要等待写锁释放
SELECT * FROM my_account
INSERT INTO my_account VALUES(3,3,'wangwu',0)
DELETE FROM my_account WHERE id = 3

总结一下：

• 对于MyISAM表的读锁，不会阻塞当前用户对表的读请求，但对写请求会报错
• 对于MyISAM表的读锁，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
• 一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的日志，但对其他表进行访问和修改都会报错
• 对于MyISAM表的写锁，会阻塞其他用户对同一表的读写操作
• 对于MyISAM表的写锁，当前用户可以对本表进行读写操作，但对其他表进行操作会报错

再来看看Inoodb的行锁

• 共享锁：又称读锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许进行写操作。允许其他事务给这几行上读锁，但不允许上写锁。
• 排它锁：又称写锁，当一个事务对某几行上写锁时，不允许其他事务写，但允许读，更不允许其他事务给这几行上任何锁。

共享锁

BEGIN
-- 上一个行读锁
SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE 
-- 提交事务
COMMIT

-- 对于被锁的这一行的写操作要等待读锁的释放
UPDATE test SET c1 = '1' WHERE id = 1
-- 读操作正常执行
SELECT * FROM test WHERE id = 1

排它锁

BEGIN
-- 上一个行写锁
SELECT * FROM test WHERE id = 1 FOR UPDATE

-- 等待写锁的释放
UPDATE test SET c1 = '1' WHERE id = 1
-- 正常执行
UPDATE test SET c1 = '1' WHERE id = 2
-- 读操作正常执行
SELECT * FROM test WHERE id = 1

总结一下：

• 共享锁：又称读锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许进行写操作。允许其他事务给这几行上读锁，但不允许上写锁。
• 排它锁：又称写锁，当一个事务对某几行上写锁时，不允许其他事务写，但允许读，更不允许其他事务给这几行上任何锁。

那么什么情况下行锁会升级成表锁？

• 如果没有索引，更新数据（update、delete）时会锁住整张表。
  InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁;否则，InnoDB使用表锁 在不通过索引(主 键)条件查询的时候，InnoDB是表锁而不是行锁。

• 事务隔离级别为：串行化时，读写数据都会锁住整张表。（一次只能一个人操作表）

Mysql中还有间隙锁，什么是间隙锁，什么方式会产生间隙锁？
概念：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。

假设有以下表my_account：（其中id为PK，id和name为联合索引）

间隙锁1.png

BEGIN
SELECT id,name FROM my_account WHERE number > 0 AND number 

所有数据都会命中，而因为命中了索引，加的是行锁。而此时就会产生间隙锁（对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)**
在另一个session

BEGIN
INSERT INTO my_account VALUES(5,5,'test',0)

插入id=5的数据，是间隙，被上了间隙锁，因此需要等待上一个事务结束以后才会执行