Mysql锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

按照锁的机制分类

• 共享锁（读锁）：其他事物可以读，但是不能写
• 排他锁（写锁）：其他事物不能读取，也不能写。

按照锁的粒度分类

表锁（偏读）

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

行锁（偏写）

InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

MyISAM 表锁读锁演示

-- 表，注意表的存储引擎
CREATE TABLE myLock(
    id INT NOT NULL primary key auto_increment,
    `name` VARCHAR(30)
)engine myisam; 

-- 向表中插入数据

INSERT INTO myLock(name) VALUES('a');
INSERT INTO myLock(name) VALUES('b');
INSERT INTO myLock(name) VALUES('c');
INSERT INTO myLock(name) VALUES('d');
INSERT INTO myLock(name) VALUES('e');
INSERT INTO myLock(name) VALUES('f');
INSERT INTO myLock(name) VALUES('g');

常用sql语法

-- 查看所有表的锁状态
SHOW OPEN TABLES;
-- 加锁
lock table <表名> read(write),<表名> read(write);
-- 解除所有的锁
UNLOCK tables;

首先我们开启两个会话

1586768442633

首先我们在黑窗口上给myLock加一把读锁（共享锁）

然后我们在黑窗口中查找myLock表中的数据

SELECT * FROM myLock;

1586768538661

我们可以看到能读取出数据

接下来我们开一个新的窗口，直接读myLock这个表

1586768614679

我们可以看到确实是可以读本表的，也就是确实是一个共享锁

我这个数据库中还有一个其他表，这个表其实是随意了，因为不影响结果

黑窗口读取其他表

SELECT * FROM user;

1586768716569

我们可以看到，上锁的那个会话在没有释放锁之前是不能读取其他表的，那么其他会话可以么？

1586768763319

很显然，其他会话可以读取。

那如果我们还可以使用关联查询么？虽然不能读取其他表，我们投机取巧使用关联查询呢？

1586768862979

很显然也是不可用的。

如果加了表锁那么就没有办法使用关联查询了么？

我们给user表也加一个锁，在试试关联查询

1586769227445

很显然，如果想使用关联查询，那么所有被查询的表都要被加上锁

这里有个坑，加锁的时候要一步到位。

-- 第一种情况
lock table myLock read;
lock table user read;

-- 第二种情况
lock table myLock read,user read;
-- 情况是不等价的，第一种情况在第二次给user添加锁的时候会释放myLock的锁

那么其他会话可以使用关联查询么？

1586768985848

从上面可以看出可以使用关联查询。

1586769018230

1586769407029

我们可以看到，黑窗口在没有释放锁的时候，其他表在写操作会进入阻塞状态

1586769473024

当我们释放锁的时候，阻塞状态会立刻消失，从上面可以看出一共阻塞了45秒

MyISAM 表读锁总结

• 当我们使用了表读锁的时候，所有的线程都可以读，但是都不能写。
• 不是加锁的线程在写的时候会进入阻塞状态，来等待锁的释放
• 加锁的线程在不释放锁的时候是不能对没加锁的表进行查找的
• 如果当前线程使用了表读锁，而且还想进行关联查询，那么要对所有的表进行加锁

MyISAM 表读锁总结

• 一个线程开启了一个写锁，那么这个锁就是该线程独占的，其他线程对该表的操作都会处于阻塞状态
• 写一个线程开启了一个表写锁，那么这个线程不能操作其他没有加锁的表

InnoDB加锁方法：

• 意向锁是 InnoDB 自动加的， 不需用户干预。

• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
  会自动给涉及数据集加排他锁（X)；

• 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  事务可以通过以下语句显式给记录集加共享锁或排他锁：

• • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
    • 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

• 隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

• 显式锁定 ：

select ... lock in share mode //共享锁 
select ... for update //排他锁 

select for update：

在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。

select *** for update 的使用场景：为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

select lock in share mode ：in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

性能影响：
select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：

前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

InnoDB锁模式：

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

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

InnoDB行锁

首先行锁是innodb默认的锁，但是在筛选条件里面没有索引字段时就会把整个表锁住.

行锁
读锁：允许其他线程上读锁，但是不允许上写锁。

黑窗口开启一个事务修改id为1，

1586854376819

1586854385043

1586854446276

另一个窗口同样修改id为2的,结构可以修改成功，如果这两个窗口修改的是同一条数据，那么后修改的会进入阻塞状态，说明是一个行锁。

InnoDB 行锁实现方式：

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
  别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

锁退化到表锁

请注意这个表结构只有id有索引，并且吧这个表的引擎改为InnoDB

InnoDB默认的行锁可以使得操作不同行时不会产生相互影响、不会阻塞，从而很好的解决了多事务和并发的问题。但是，那得基于一个前提，即 Where 条件中使用上了索引；反之，如果没有使用上索引，则是全表扫描、全部阻塞。

首先黑窗口一开启一个事务，并且where条件使用一个非索引字段来修改

1586854674349

此时另一个窗口也修改其中的任意一条非同行数据

发现另一个窗口处于阻塞状态。

还有一个隐藏问题

如果我们的name字段也有索引那么

update myLock set id=111 where name="b"//使用到了索引
update myLock set id=111 where name=b//没有使用到索引，还是会行锁变表锁

因为涉及到了自动类型转换导致没有使用到索引

• 更新的时候没有索引或者索引失效时，InnoDB 的行锁变表锁
• 原因：Mysql 的行锁是通过索引实现的！

间隙锁（Gap Lock）是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制，

幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的

• 加锁的基本单位是（next-key lock)他是前开后闭原则

• 插叙过程中访问的对象会增加锁

• 索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁

• 索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

• 唯一索引上的范围查询会访问到不满足条件的第一个值为止

间隙的范围

根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B)。

number 1 2 3 4 5 6 6 6 11
id 1 3 5 7 9 10 11 12 23

select * from t where number=6;那么间隙锁锁定的间隙为：（5，11），所以你再想插入5到11之间的数就会被阻塞。

更需要你注意的是，当你再执行update t set number = 6 where id = 1也会被阻塞。这是为什么？你想想看，要保证每次查询number=6的数据行数不变，如果你将另外一条数据修改成了6，岂不会多了一条？所以此时不会允许任何一条数据被修改成6。