mysql的锁--一篇读懂所有锁机制

目录

mysql的锁

概述：根据mysql锁的大类型可以分为

我们先来讲一下范围最大的全局锁

使用

为什么要使用全局锁？

使用全局锁进行备份的缺点

表级锁

表锁

1.共享读表锁的语法

2.排斥写表锁

元数据锁

意向锁

什么是意向锁

 怎么产生意向锁

意向锁的作用

 AUTO-INC 锁

行级锁

 记录锁

记录共享锁

记录排斥锁 

---

mysql的锁

主要规则：读读锁兼容，写写锁不兼容，读写锁不兼容

概述：根据mysql锁的大类型可以分为

• 全局锁
• 表级锁：表锁 意向锁 元数据锁 AUTO-INC锁
• 行级锁：记录锁，间隙锁，next-key锁(记录锁加间隙锁)

我们先来讲一下范围最大的全局锁

使用

只要全局锁一旦加上， 所有的增删改的操作都不能被执行，整个数据库只会处于只读状态

在一个会话里加上全局锁

flush tables with read lock

只能执行读操作，执行增删改操作会直接报错

在其他会话里

也可以执行查询操作，但是执行增删改操作会被阻塞，直到全局锁释放才会去执行当前操作

 

解锁

unlock tables

解锁之后 ，阻塞的操作就会被执行

 

 

为什么要使用全局锁？

我们需要对整个数据库的数据进行备份时 ，就需要使用全局锁。

如果在一个库中有一张订单表，商品余额表

当我们对这个数据库的数据进行备份时，先对订单表进行备份，这时一个其他的并发事务执行了购买操作，减少的订单余额，这时候再去备份的商品余额表就与之前备份的订单表数据不一致了。

使用全局锁进行备份的缺点

当我们使用全局锁时，只有查询的业务可以运行，而增删改的业务都被阻塞，这是我们十分不想看到的，那有什么方法可以解决吗，有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

这就是事务中的隔离性，就算其他事务对数据表进行了操作，数据库备份时只会对read view进行备份

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，在备份数据库之前先开启事务。这种操作只适合于可重复读级别的隔离机制，而mysql默认的innodb存储引擎就是可重复读的隔离机制。

表级锁

表锁

我们现在来讲以下表级锁的表锁 ，这是一个用来锁着一整张表的锁。

可以分为共享读表锁，排斥写表锁。

1.共享读表锁的语法

共享读表锁就是让这张表只能读，增删改操作都不可以执行

lock tables 表名 read;

演示共享读表锁

在一个会话里给person表加上共享读表锁

可以发现对这张表只能读，增删改操作都会直接报错

 在其它会话里

也可以读，但是增删改操作都会被阻塞

 释放锁

其他会话的阻塞操作也会被执行

注意：如果当前会话对person表加了共享读表锁后，也不能操作其他表的数据了，连查询都不可以，其他会话则可以

2.排斥写表锁

如果当前会话对一张表加了排斥写表锁，那么只能让当前对话能对这张表进行CRUD操作，其他会话不能查询这个表，也不能增删改

lock  table 表名 write; 

 演示

当前会话对person加了排斥写表锁，可以进行CRUD操作

其他会话，所有对这个表的操作都会被阻塞

 

我们也可以发现对整个表直接进行加排斥写锁后，其他会话都无法操作 这个表，对并发事务十分不友好，性能十分低下，所以innodb有了行锁这种更小粒度的锁。

元数据锁

我们之前讲的全局锁和表锁都是需要我们进行显示调用的，而这个元数据锁，意向锁（增删改）和行锁（增删改）是自动调用的，所以我们需要开启事务来查询加锁的情况，因为事务一旦结束，这些锁都会自动释放。这也是我们为什么一定要开启事务来查看加锁的情况，因为mysql是自动提交事务的(执行一行sql后自动提交)，然后这些自动加的锁我们就无法观察到了。

元数据锁的作用是什么-->用来解决DML语句和DDL语句的冲突，假设我们在事务a中进行了增删改操作，而事务b把表的结构给改了，这显然是不被允许的。 

元数据锁的类型-->MDL读锁（进行CRUD时的事务进行加的锁）

                        -->MDL写锁(进行修改表结构时的事务加的锁)

 这两个锁是不兼容的，在事务a中先对person表进行了CRUD操作，那么这个表会被加上MDL读锁，这时事务b想对person表进行DDL,操作表数据时，想要去给这个表加MDL写锁时，因为MDL读锁与MDL写锁不兼容，所以MDL写锁无法加上，DDL语句也无法执行，b事务就只能阻塞等待a事务提交释放MDL读锁。

但是经过我的操作，如果先在事务a修改的表结构，在事务b中可以进行CURD操作

演示

在事务a中进行CURD操作，这是这张表已经被加上的MDL读锁

 

 事务b想要去修改表结构时，会阻塞，因为MDL写锁无法加上，与MDL读锁无法兼容，会阻塞等待

事务a提交

 

表结构成功改变 

 

意向锁

意向锁进行增删改操作也是自动调用加锁的，事务结束自动解锁。

什么是意向锁

意向锁和行锁是息息相关的，我们在对行数据进行加锁时，会先产生一个表级别的意向锁，共享读行锁产生意向共享锁，排斥写行锁产生意向排斥锁。 

意向锁之间也不会发生冲突

•  意向共享锁读可以兼容共享读表锁，与排斥写表锁不兼容（读读锁共享）
• 意向排斥写锁与共享读表锁不兼容，与排斥写表锁不兼容（写读锁不共享，写写锁不共享）

 怎么产生意向锁

普通的select语句不会产生行锁，只会产生MDL读锁

这就要先讲一下怎么产生共享读行锁，排斥写行锁

共享读行锁：加上这个共享读行锁之前，先在表加上了意向共享读锁

select ... lock in share mode;

 排斥写行锁：加上这个排斥写行锁之前，先在表加上了意向排斥写锁

select ... for update;

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」 ，然后对该记录加独占锁。

执行插入操作

产生了意向排斥锁 

 IX

 select * from performance_schema.data_locks\G;

意向锁的作用

 在对一张表加表锁时，需要判断这张表里有没有行锁，如果没有意向锁，那就需要去全表扫描判断有没有行锁，这显然效率十分低下，如果有了意向锁就可以直接判断这张表里有没有行锁。

 AUTO-INC 锁

现在数据表进行插入数据时，主键都是自增的，使用auto_increment;

AUTO-INC锁就是实现这个的基础，在插入数据时，先给这个表加上AUTO-INC锁，然后把赋值数据给自增字段，插入完成之后，锁会自动释放

所以这个AUTO-INC锁不是事务结束后释放，而是执行插入语句结束后释放。

 AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞（等着被赋值）。 

InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

 

行级锁

innodb相比于myisam的三大特点：支持事务，外键约束，行级锁

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上（更新和删除数据时会对这个数据加排斥写锁，插入数据时也会，防止被其他事务修改）；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。（加入有1,3两个数据，next-lock锁的是3记录本身，和1，3之间的范围）

 记录锁

记录锁也分有记录共享读锁和记录排斥写锁，也是经典的读读锁兼容，读写锁不兼容，写写锁不兼容

记录共享锁

如何加上记录共享读锁

再使用select 语句后加上lock in share mode;

演示：

这里给id=1的数据加上了记录共享读锁

可以看到现在的锁有表级锁 意向共享锁，记录共享锁

S,REC_NOT_GAP就是 记录共享锁，然后lock_data加锁的行数据

 其他事务也可以继续加记录共享锁

现在就有四个锁了 ，也是可以发现意向锁之间可以兼容，而是与共享读表锁和排斥写表锁不兼容

但是不可以加记录排斥锁

 加了就阻塞

记录排斥锁 

使用更新，删除操作时，还有在select 语句后加for update时就加上记录排斥锁

给id=1的数据加上记录排斥锁

可以发现现有两个锁，意向排他锁， 

其他事务要更新这个id=1的数据时会阻塞，因为更新和删除操作都要去加排斥记录锁，但是现在事务a已经给id=1加了排斥记录锁，而排斥记录锁之间不兼容，所以会阻塞。

 

在事务a中给id=2的数据加上排斥记录锁

 

就会有两个意向排斥锁，也可以看出来意向锁之间互相兼容