北京轨道交通建设公司网站,制作微信公众号的软件,中国水土保持生态环境建设网站,安阳网站建设官网目录 mysql的锁
概述#xff1a;根据mysql锁的大类型可以分为
我们先来讲一下范围最大的全局锁
使用
为什么要使用全局锁#xff1f;
使用全局锁进行备份的缺点
表级锁
表锁
1.共享读表锁的语法
2.排斥写表锁
元数据锁
意向锁
什么是意向锁 怎么产生意向锁
意向…目录 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 LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。加入有1,3两个数据next-lock锁的是3记录本身和13之间的范围 记录锁
记录锁也分有记录共享读锁和记录排斥写锁也是经典的读读锁兼容读写锁不兼容写写锁不兼容
记录共享锁
如何加上记录共享读锁
再使用select 语句后加上lock in share mode;
演示
这里给id1的数据加上了记录共享读锁 可以看到现在的锁有表级锁 意向共享锁记录共享锁
S,REC_NOT_GAP就是 记录共享锁然后lock_data加锁的行数据 其他事务也可以继续加记录共享锁 现在就有四个锁了 也是可以发现意向锁之间可以兼容而是与共享读表锁和排斥写表锁不兼容 但是不可以加记录排斥锁 加了就阻塞
记录排斥锁
使用更新删除操作时还有在select 语句后加for update时就加上记录排斥锁
给id1的数据加上记录排斥锁 可以发现现有两个锁意向排他锁 其他事务要更新这个id1的数据时会阻塞因为更新和删除操作都要去加排斥记录锁但是现在事务a已经给id1加了排斥记录锁而排斥记录锁之间不兼容所以会阻塞。 在事务a中给id2的数据加上排斥记录锁 就会有两个意向排斥锁也可以看出来意向锁之间互相兼容
