长春网站建设有什么,史志网站建设,免费行情软件下载入口,网络营销与网站建设MySQL根据加锁的范围#xff0c;可以分为全局锁、表级锁、行级锁三类。
2.5.1. 锁定读
2.5.1.1. 共享锁和独占锁
事务的 读-读 情况并不会引起什么问题#xff0c;对于 写-写、读-写 或 写-读 这些情况可能会引起一些问题#xff0c;需要使用MVCC或者加锁的方式来解决。在…MySQL根据加锁的范围可以分为全局锁、表级锁、行级锁三类。
2.5.1. 锁定读
2.5.1.1. 共享锁和独占锁
事务的 读-读 情况并不会引起什么问题对于 写-写、读-写 或 写-读 这些情况可能会引起一些问题需要使用MVCC或者加锁的方式来解决。在使用加锁的方式解决问题时由于既要允许 读-读 情况不受影响又要使 写-写、读-写 或 写-读 情况中的操作相互阻塞所以MySQL给锁分了个类
共享锁Shared Locks简称 S 锁。在事务要读取一条记录时需要先获取该记录的 S 锁。独占锁也常称排他锁英文名Exclusive Locks简称 X 锁。在事务要改动一条记录时需要先获取该记录的X锁。
事务 T1 首先获取了一条记录的 X 锁之后那么不管事务 T2 接着想获取该记录的 S 锁还是 X 锁都会被阻塞直到事务 T1 提交。
2.5.1.2. 锁定读语句
有时候想在读取记录时就获取记录的 X 锁来禁止别的事务读写该记录为此MySQL存在两种比较特殊的SELECT语句格式
对读取的记录加 S 锁
SELECT ... LOCK IN SHARE MODE;
在普通的SELECT语句后边加LOCK IN SHARE MODE当前事务执行了该语句会为读取到的记录加S锁允许别的事务继续获取这些记录的S锁但是不能获取这些记录的X锁。别的事务想获取这些记录的 X 锁就会阻塞直到当前事务提交之后将这些记录上的S锁释放掉。
对读取的记录加X锁
SELECT ... FOR UPDATE;
在普通的SELECT语句后边加FOR UPDATE当前事务执行了该语句会为读取到的记录加X锁这样既不允许别的事务获取这些记录的S锁也不允许获取这些记录的X锁。如果别的事务想要获取这些记录的S锁或者X锁就会阻塞直到当前事务提交之后将这些记录上的X锁释放掉。 2.5.2. 全局锁
全局锁使用方法
-- 加全局锁
flush tables with read lock
-- 释放全局锁
unlock tables
加了全局锁后整个数据库处于只读状态其他线程执行以下状态都会被阻塞
对数据库的增删改操作如 insert、delete、update等语句对表结构的更改操作比如 alter table、drop table 等语句。
会话断开后全局锁自动释放 全局锁的应用场景
主要用于做全库逻辑备份备份数据库期间不会因为数据或表结构的更新造成数据不一致。
例如
全库逻辑备份期间用户购买了一件商品商品业务逻辑涉及多张数据库表的更新
先备份了用户表的数据用户购买了商品备份商品表的数据
备份用户表和商品表之间用户购买了商品备份的结果就是用户余额没有减少库存减少了。 全局锁带来的缺点
整个数据库都是只读状态庞大的数据量进行备份会花费很长时间而且不能更新数据会造成业务停滞。 避免全局锁会影响业务
数据库引擎的事务支持可重复读隔离级别备份前先开启事务会先创建Read View整个事务期间都在用这个Read View由于MVCC的支持备份期间业务依然可以对数据进行更新这就是隔离性。
备份数据库的工具是 mysqldump使用 mysqldump 时加上 –single-transaction 参数就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎可以采用这种方式来备份数据库对于 MyISAM 这种不支持事务的引擎在备份数据库时就要使用全局锁的方法。
2.5.3. 表级锁
MySQL 里面表级别的锁有这几种
表锁元数据锁MDL;意向锁AUTO-INC 锁
2.5.3.1. 表锁
加表锁可使用以下命令
//表级别的共享锁也就是读锁
lock tables t_student read;//表级别的独占锁也就是写锁
lock tables t_stuent write;
-- 释放当前会话所有表锁 会话退出也会释放所有表锁
unlock tables
本线程对学生表加了「共享表锁」任何写操作包括当前线程和其他线程的写操作都会被阻塞直到锁被释放。
本线程对学生表加了「独占表锁」该锁持有期间当前线程可以对表进行任何操作读写其他事务无法对该表进行任何类型的锁定包括共享锁和其他独占锁保证了当前事务对该表的独占访问权限通常在事务结束时提交或回滚释放。
尽量避免在使用 InnoDB 引擎的表使用表锁因为表锁的颗粒度太大会影响并发性能。
2.5.3.2. 元数据锁MDL
对数据库表进行操作时会自动给这个表加上 MDL
对一张表进行 CRUD 操作时加的是 MDL 读锁对一张表做结构变更操作的时候加的是 MDL 写锁
防止对用户表进行CRUD时其他线程对这个表结构做了变更。
当有线程在执行 select 语句 加 MDL 读锁的期间其他线程要更改该表的结构 申请 MDL 写锁将会被阻塞直到执行完 select 语句 释放 MDL 读锁。
当有线程对表结构进行变更 加 MDL 写锁的期间其他线程执行了 CRUD 操作 申请 MDL 读锁就会被阻塞直到表结构变更完成 释放 MDL 写锁。 MDL不需要显示调用它是在什么时候释放的
事务执行期间MDL会一直持有事务提交后会释放。
一个长事务开启了未提交对表结构进行变更操作可能会出现的问题
线程A开启事务执行select语句表上会加MDL读锁线程B执行查询语句此时不会阻塞读读 不冲突线程C修改表字段MDL读锁还在占用线程C无法申请到MDL写锁会被阻塞后续对该表的查询语句都会被阻塞如果有大量的查询语句线程很快就会爆满
因为申请MDL锁的操作会形成队列写锁获取优先级高于读锁出现写锁后续的CRUD都会被阻塞。 2.5.3.3. 意向锁
意向共享锁英文名Intention Shared Lock简称IS锁。当事务准备在某条记录上加S锁时需要先在表级别加一个IS锁。意向独占锁英文名Intention Exclusive Lock简称IX锁。当事务准备在某条记录上加X锁时需要先在表级别加一个IX锁。
IS、IX锁是表级锁它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁以避免用遍历的方式来查看表中有没有上锁的记录也就是说其实IIS锁和IX锁是兼容的IX锁和IX锁是兼容的 兼容性 X IX S IS X 不兼容 不兼容 不兼容 不兼容 IX 不兼容 兼容 不兼容 兼容 S 不兼容 不兼容 兼容 兼容 IS 不兼容 兼容 兼容 兼容
执行插入、更新、删除操作需要先对表加上「意向独占锁」然后对该记录加独占锁。
普通的 select 是不会加行级锁的普通的 select 语句是利用 MVCC 实现一致性读是无锁的。
select 也是可以对记录加共享锁和独占锁的具体方式如下
//先在表上加上意向共享锁然后对读取的记录加共享锁
select ... lock in share mode;//先在表上加上意向独占锁然后对读取的记录加独占锁
select ... for update; 2.5.3.4. AUTO-INC锁
MySQL 5.1.22 版本开始InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
插入数据的时候为被 AUTO_INCREMENT 修饰的字段加上轻量级锁然后给该字段赋一个自增的值就把这个轻量级锁释放了而不需要等待整个插入语句执行完后才释放锁。
innodb_autoinc_lock_mode 系统变量是用来控制选择用 AUTO-INC 锁还是轻量级的锁。
当 innodb_autoinc_lock_mode 0就采用 AUTO-INC 锁语句执行结束后才释放锁当 innodb_autoinc_lock_mode 2就采用轻量级锁申请自增主键后就释放锁并不需要等语句执行后才释放。当 innodb_autoinc_lock_mode 1 普通 insert 语句自增锁在申请之后就马上释放类似 insert … select 这样的批量插入数据的语句自增锁还是要等语句结束后才被释放
innodb_autoinc_lock_mode 2 是性能最高的方式但是当搭配 binlog 的日志格式是 statement 一起使用的时候在「主从复制的场景」中会发生数据不一致的问题。
当 innodb_autoinc_lock_mode 2 时并且 binlog_format row既能提升并发性又不会出现数据一致性问题。
具体案例查看参考文档。 2.5.4. 行级锁记录锁
InnoDB 引擎是支持行级锁的而 MyISAM 引擎并不支持行级锁。
普通的 select 语句属于快照读不会对记录加锁。要在查询时对记录加行锁可以使用下面这两个方式这种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁
select ... lock in share mode;//对读取的记录加独占锁
select ... for update;
上面这两条语句必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin、start transaction 或者 set autocommit 0。
共享锁S锁满足读读共享读写互斥。独占锁X锁满足写写互斥、读写互斥。 行级锁的类型主要有三类
Record Lock记录锁也就是仅仅把一条记录锁上Gap Lock间隙锁锁定一个范围但是不包含记录本身Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。
2.5.4.1. Record Lock
Record Lock 称为记录锁锁住的是一条记录。而且记录锁是有 S 锁共享锁和 X 锁独占锁之分的
一个事务对一条记录加了 S 锁后其他事务也可以继续对该记录加 S锁S 锁与 S 锁兼容但是不可以对该记录加 X锁S 锁与 X 锁不兼容;一个事务对一条记录加了 X锁后其他事务既不可以对该记录加 S 锁S 锁与 X 锁不兼容也不可以对该记录加 X 锁X 锁与 X 锁不兼容。
2.5.4.2. Gap Lock
Gap Lock 称为间隙锁只存在于可重复读隔离级别目的是为了解决可重复读隔离级别下幻读的现象。
表中有一个范围 id 为35间隙锁那么其他事务就无法插入 id 4 这条记录有效的防止幻读现象的发生。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁但是并没有什么区别间隙锁之间是兼容的即两个事务可以同时持有包含共同间隙范围的间隙锁并不存在互斥关系因为间隙锁的目的是防止插入幻读记录而提出的。
给一条记录加了gap锁只是不允许其他事务往这条记录前面的间隙插入新记录那对于最后一条记录之后的间隙该咋办呢两条伪记录
Infimum记录表示该页面中最小的记录。Supremum记录表示该页面中最大的记录。
为了实现阻止其他事务在该记录后插入新记录可以给索引中的最后一条记录加上一个gap锁
详细案例查看参考文档。
2.5.4.3. Next-Key Lock
Next-Key Lock 称为临键锁是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。
假设表中有一个范围 id 为35] 的 next-key lock那么其他事务即不能插入 id 4 记录也不能修改 id 5 这条记录。
next-key lock 是包含间隙锁记录锁的如果一个事务获取了 X 型的 next-key lock那么另外一个事务在获取相同范围的 X 型的 next-key lock 时是会被阻塞的。
相同范围的间隙锁是多个事务相互兼容的但对于记录锁要考虑 X 型与 S 型关系X 型的记录锁与 X 型的记录锁是冲突的。 2.5.4.4. MySQL行级锁的加锁规则
唯一索引等值查询
当查询的记录「存在」在索引树上定位到这一条记录后该记录的索引中的 next-key lock 会退化成「记录锁」。当查询的记录「不存在」在索引树找到第一条大于该查询记录的记录后该记录的索引中的 next-key lock 会退化成「间隙锁」。
非唯一索引等值查询
当查询的记录「存在」时可能存在索引值相同的记录所以非唯一索引等值查询的过程是一个扫描的过程扫描到第一个不符合条件的二级索引记录就停止扫描然后在扫描的过程中对扫描到的二级索引记录加的是 next-key 锁而对于第一个不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。当查询的记录「不存在」时扫描到第一条不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录所以不会对主键索引加锁。
非唯一索引和主键索引的范围查询加锁规则不同之处在于
唯一索引在满足一些条件的时候索引的 next-key lock 退化为间隙锁或者记录锁。非唯一索引范围查询索引的 next-key lock 不会退化为间隙锁和记录锁。
在线上在执行 update、delete、select ... for update 等具有加锁性质的语句一定要检查语句是否走了索引如果是全表扫描的话会对每一个索引加 next-key 锁相当于把整个表锁住了这是挺严重的问题。
唯一索引主键索引加锁的流程图如下。如果是二级索引的唯一索引除了流程图中对二级索引的加锁规则之外还会对查询到的记录的主键索引项加「记录锁」流程图没有提示这一个点所以在这里用文字补充说明下 非唯一索引加锁的流程图 2.5.5. 插入意向锁
一个事务在插入一条记录的时候需要判断插入位置是否已被其他事务加了间隙锁next-key lock 也包含间隙锁。
如果有的话插入操作就会发生阻塞直到拥有间隙锁的那个事务提交为止释放间隙锁的时刻在此期间会生成一个插入意向锁表明有事务想在某个区间插入新记录但是现在处于等待状态。
插入意向锁名字虽然有意向锁但是它并不是意向锁它是一种特殊的间隙锁属于行级别锁。
如果说间隙锁锁住的是一个区间那么「插入意向锁」锁住的就是一个点。
