深圳网站建设(信科网络),做啥类型网站,wordpress禁用灯箱效果,xml网站地图在线生成工具5. 1MySQL有哪些锁#xff1f;
为保证数据的一致性#xff0c;需要对并发操作进行控制#xff0c;因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要#xff0c;也更加…5. 1MySQL有哪些锁
为保证数据的一致性需要对并发操作进行控制因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要也更加复杂。
按照数据操作的类型可以分为读锁、写锁。 读锁 也称为 共享锁 、英文用 S 表示。针对同一份数据多个事务的读操作可以同时进行而不会互相影响相互不阻塞的。 写锁 也称为 排他锁 、英文用 X 表示。当前写操作没有完成前它会阻断其他写锁和读锁。这样就能确保在给定的时间里只有一个事务能执行写入并防止其他用户读取正在写入的同一资源。
在 MySQL 里根据加锁的范围可以分为全局锁、表级锁和行锁三类。
全局锁
使用全局锁整个数据库就处于只读状态了。 应用场景主要应用于做全库逻辑备份这样在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。 缺点业务只能读数据而不能更新数据这样会造成业务停滞。 避免方法使用可重复读的隔离级别在备份数据库之前先开启事务整个数据库的数据就都是可重复读的而且由于 MVCC 的支持备份期间业务依然可以对数据进行更新操作。 # 加全局锁 FTWRL
flush tables with read lock 表级锁
MySQL 里面表级别的锁有表锁、元数据锁MDL、意向锁、AUTO-INC 锁。 表锁 表锁也有表级别的共享锁表级别的独占锁。 表锁除了会限制别的线程的读写外也会限制本线程接下来的读写操作。表锁的颗粒度太大会影响并发性能应该尽量避免使用表锁。 元数据锁Meta Datebase LockMDL 对一张表进行 CRUD 操作时加的是 MDL 读锁 只允许读不能做结构的修改比如修改表字段等。 对一张表做结构变更操作的时候加的是 MDL 写锁 只允许写修改表结构时不能通过CRUD读取数据。 MDL 不需要显示调用在事务提交后才会释放这意味着事务执行期间MDL 是一直持有的。 申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现 MDL 写锁等待线程申请不到 MDL 写锁会阻塞后续该表的所有 CRUD 操作MDL读锁。 所以为了能安全的对表结构进行变更在对表结构变更前先要看看数据库中的长事务是否有事务已经对表加上了 MDL 读锁如果可以考虑 kill 掉这个长事务然后再做表结构的变更。 意向锁 意向锁的目的是快速判断表里是否有记录被加锁。 意向共享锁intention shared lock, IS事务有意向对表中的某些行加共享锁S锁 意向排他锁intention exclusive lock, IX事务有意向对表中的某些行加排他锁X锁 普通的SELECT语句利用MVCC实现一致性读是无锁的但是可以使用以下方式加锁 //先在表上加上意向共享锁然后对读取的记录加共享锁
select ... lock in share mode;//先表上加上意向独占锁然后对读取的记录加独占锁
select ... for update;意向锁之间不冲突也不会和行级的共享锁和独占锁发生冲突只会和共享表锁lock tables … read或独占表锁lock tables … write发生冲突。 AUTO-INC 锁自动增长 表里的主键通常都会设置成自增的这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。 在插入数据时会加一个表级别的 AUTO-INC 锁然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值等插入语句执行完成后才会把 AUTO-INC 锁释放掉。 InnoDB 存储引擎提供了一种轻量级的锁来实现自增。只是在赋值完成后就把该锁释放。
行级锁
对于表锁和行锁满足读读共享、读写互斥、写写互斥的。
不同隔离级别下行级锁的种类不同。 在读已提交隔离级别下行级锁的种类只有记录锁也就是仅仅把一条记录锁上。 在可重复读隔离级别下行级锁的种类除了有记录锁还有间隙锁目的是为了避免幻读。
行级锁的类型主要有三类 记录锁Record Lock也就是仅仅把一条记录锁上记录锁是有 S 锁和 X 锁之分的。 共享锁S锁指的就是对于多个不同的事务对同一个资源共享同一个锁。 相当于对于同一把门它拥有多个钥匙一样。独占锁X锁也叫排他锁是指该锁一次只能被一个线程所持有。 共享锁S锁满足读读共享读写互斥独占锁X锁满足写写互斥、读写互斥。 间隙锁Gap Lock锁定一个范围但是不包含记录本身只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。 间隙锁之间是兼容的即两个事务可以同时持有包含共同间隙范围的间隙锁并不存在互斥关系因为间隙锁仅仅是为了防止插入幻影记录而提出的。 临键锁Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。next-key lock 即能保护记录又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。 next-key lock 是包含间隙锁记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的。 插入意向锁插入意向锁名字虽然有意向锁但是它并不是意向锁它是一种特殊的间隙锁属于行级别锁。存在间隙锁时执行Insert语句时会用到。 插入意向锁是一种特殊的间隙锁但不同于间隙锁的是该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间那么「插入意向锁」锁住的就是一个点。因而从这个角度来说插入意向锁确实是一种特殊的间隙锁。
5.2 MySQL是怎么加锁的
从语句角度来看
普通的SELECT默认是不加锁的属于快照读是使用MVCC的方式实现的。但是可以在查询时对记录加行级锁查询会加锁的语句称为锁定读。锁定读的语句必须在事务中因为当事务提交了锁就会被释放。而update 和 delete 操作都会加行级锁且锁的类型都是独占锁(X型锁)。
//对读取的记录加共享锁(S型锁)
select ... lock in share mode;//对读取的记录加独占锁(X型锁)
select ... for update;而Insert 语句在正常执行时是不会生成锁结构的它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。但此时记录之间加有间隙锁隐式锁会转换为显示锁。
如果已加间隙锁此时会生成一个插入意向锁然后锁的状态设置为等待状态现象就是Insert语句被阻塞。
因为插入意向锁与间隙锁是冲突的所以当其它事务持有该间隙的间隙锁时需要等待其它事务释放间隙锁之后才能获取到插入意向锁。
如果记录之间加有间隙锁为了避免幻读此时是不能插入记录的因为插入意向锁会被设置为等待状态
如果 Insert 的记录和已有记录存在唯一键冲突此时也不能插入记录会对这条记录加上S型的锁
至于是记录锁还是 next-key 锁跟是「主键冲突」还是「唯一二级索引冲突」有关系。 如果主键冲突给已存在的主键索引记录添加S型记录锁。如果唯一二级索引冲突给已存在的二级索引记录添加S型next-key锁。 从MySQL角度来看
MySQL加锁的对象是索引加锁的基本单位是 next-key lock。
next-key lock 是前开后闭区间而间隙锁是前开后开区间。在能使用记录锁或者间隙锁就能避免幻读现象的场景下 next-key lock 就会退化成退化成记录锁或间隙锁。
唯一索引等值查询加锁情况分析主键索引为例
当查询的记录是存在的在索引树上定位到这一条记录后该记录的索引中的 next-key lock 会退化成「记录锁」。 等值查询唯一索引只需要加锁一条记录并且加记录锁就可以避免幻读。 当查询的记录是不存在的在索引树找到第一条大于该查询记录的记录后将该记录的索引中的 next-key lock 会退化成「间隙锁」因为仅靠间隙锁就可以避免幻读。
唯一索引范围查询加锁情况分析 首先会对每一个扫描到的索引加 next-key 锁如果遇到下面这些情况会退化成记录锁或者间隙锁 对大于的范围查询next-key锁不会退化。 select * from user where id 15 for update;对大于等于的范围查询如果“等于”的等值查询的记录是存在于表中那么该记录的索引中的 next-key 锁会退化成记录锁。 select * from user where id 15 for update;对小于/小于等于的范围查询扫描到终止范围查询的记录时next-key锁就会退化为间隙锁。小于等于时等值查询的记录在表中next-key不会退化因为next-key本就是左开右闭避免幻读。 select * from user where id 6 for update;非唯一索引等值查询 当查询的记录存在时扫描到的二级索引记录加的是next-key lock扫描到的第一个不符合条件的二级索引记录next-key 锁会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。 select * from user where age 22 for update;当查询的记录不存在时扫描到第一条不符合条件的二级索引记录 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录所以不会对主键索引加锁。 select * from user where age 25 for update;非唯一索引范围查询 对扫描到的二级索引记录加锁都是加 next-key 锁主键索引加记录锁。 没有加索引的查询 在线上在执行 update、delete、select … for update 等具有加锁性质的语句一定要检查语句是否走了索引如果是全表扫描的话会对每一个索引加 next-key 锁相当于把整个表锁住了。
5.3 update没加索引会锁全表
当我们执行 update 语句时实际上是会对记录加独占锁X 锁的此时其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外这个锁并不是执行完 update 语句就会释放的而是会等事务结束时才会释放。
在 update 语句的 where 条件没有使用索引就会全表扫描于是就会对所有记录加上 next-key 锁记录锁 间隙锁相当于把整个表锁住了。即使where条件使用了索引还得看这条语句在执行过程中优化器最终选择的是索引扫描还是全表扫描如果走了全表扫描就会对全表的记录加锁了。
5.4 MySQL死锁了怎么办
RR隔离级别下会存在幻读的问题InnoDB为了解决可重复读隔离级别下的幻读问题就引出了next-key 锁是记录锁和间隙锁的组合。
我们可以执行 select * from performance_schema.data_locks\G; 语句 确定事务加了什么类型的锁。 为什么会出现死锁 建了一张订单表其中 id 字段为主键索引order_no 字段普通索引也就是非唯一索引二级索引 # 插入六条记录id 1-6 、order_on 1001-1006事务A要插入1007订单记录在插入之前给订单做幂等性校验目的是为了保证不会出现重复的订单。 SELECT id FROM t_order WHERE order_no 1007 for UPDATE;
# 需要对订单做幂等性校验所以两个事务先要查询该订单是否存在不存在才插入记录执行该语句事务A在二级索引加了X型next-key锁范围是(1006 ∞)。 事务B也做幂等性校验 SELECT id FROM t_order WHERE order_no 1008 for UPDATE;事务B在二级索引加了X型next-key锁范围也是(1006 , ∞)。 事务A、B执行insert语句插入1007、1008。 Insert into t_order (order_no, create_date) values (1007, now());
Insert into t_order (order_no, create_date) values (1008, now());此时两个事务都陷入了等待状态也就是发生了死锁因为都在相互等待对方释放锁。 因为当我们执行insert语句时会在插入间隙上获取插入意向锁而插入意向锁与间隙锁是冲突的所以当其它事务持有间隙锁时需要等待其它事务释放间隙锁之后才能获取到插入意向锁。 而间隙锁与间隙锁之间是兼容的并且两个事务中 select ... for update 语句并不会相互影响。 因为间隙锁的意义只在于阻止区间被插入一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁共享和排他的间隙锁是没有区别的他们相互不冲突且功能相同即两个事务可以同时持有包含共同间隙的间隙锁。next-key lock 是包含间隙锁记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务再获取相同范围的 X 型的 next-key lock 时是会被阻塞的。但是对于这种范围为 (1006, ∞] 的 next-key lock两个事务是可以同时持有的不会冲突。因为 ∞ 并不是一个真实的记录自然就不需要考虑 X 型与 S 型关系。 如何避免死锁 死锁的四个必要条件互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁这些条件必然成立但是只要破坏任意一个条件就死锁就不会成立。 在数据库层面有两种策略通过「打破循环等待条件」来解除死锁状态 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后就对这个事务进行回滚于是锁就释放了另一个事务就可以继续执行了。在 InnoDB 中参数 innodb_lock_wait_timeout 是用来设置超时时间的默认值时 50 秒。 开启主动死锁检测。主动死锁检测在发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on表示开启这个逻辑默认就开启。
5.5 字节面试 加了什么锁导致死锁的 创建一张学生表其中id为主键索引其他都是普通字段。
事务 A 和 事务 B 都在执行 insert 语句后都陷入了等待状态也就是发生了死锁因为都在相互等待对方释放锁。 Time1阶段此时事务 A 在主键索引INDEX_NAME : PRIMARY上加的是间隙锁锁范围是(20, 30)。唯一索引等值查询查询id不在索引中退化成间隙锁Time2阶段此时事务B在主键索引上加的是也是间隙锁和事务A相同。Time3阶段事务 A 的状态为等待状态LOCK_STATUS: WAITING因为向事务 B 生成的间隙锁范围 (20, 30)中插入了一条记录所以事务 A 的插入操作生成了一个插入意向锁LOCK_MODE:INSERT_INTENTION。Time4阶段与Time3阶段相同。
本次案例中事务 A 和事务 B 在执行完后 update 语句后都持有范围为(20, 30的间隙锁而接下来的插入操作为了获取到插入意向锁都在等待对方事务的间隙锁释放于是就造成了循环等待满足了死锁的四个条件互斥、占有且等待、不可强占用、循环等待因此发生了死锁。 整理自小林Coding
