杭州营销网站建设公司,成都网站排名优化报价,互联网营销平台,制作网站的公司不干了目录
一. 前言
二. 锁的分类
三. 共享锁#xff08;读锁#xff09;和排他锁#xff08;写锁#xff09;
3.1. 共享锁#xff08;Shared Lock#xff09;
3.2. 排他锁#xff08;Exclusive Lock#xff09;
四. 全局锁、表级锁、页级锁和行级锁
4.1. 全局锁
4.…目录
一. 前言
二. 锁的分类
三. 共享锁读锁和排他锁写锁
3.1. 共享锁Shared Lock
3.2. 排他锁Exclusive Lock
四. 全局锁、表级锁、页级锁和行级锁
4.1. 全局锁
4.2. 表级锁
4.2.1. 表锁
4.2.2. 元数据锁MDL
4.2.3. AUTO-INC 锁
4.3. 页级锁
4.4. 行级锁
五. 行锁四兄弟记录锁、间隙锁、临键锁和插入意向锁
5.1. 记录锁Record Lock
5.2. 间隙锁Gap Lock
5.3. 临键锁Next-Key Lock
5.4. 插入意向锁Insert Intention Lock
六. 意向锁Intention Lock
七. 乐观锁和悲观锁
7.1. 乐观锁
7.2. 悲观锁
7.3. 小结
八. 总结
8.1. InnoDB 的加锁方法
8.1.1. select for update
8.1.2. select lock in share mode
8.2. 查看 InnoDB 的锁争用情况
8.3. 死锁
8.3.1. 什么是死锁
8.3.2. 产生死锁的四个必要条件
8.3.3. 如何避免死锁
8.3.4. 怎么排查死锁问题 一. 前言 锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySql 中为了保证数据访问的一致性与有效性等功能实现了锁机制MySql 中的锁是在服务器层或者存储引擎层实现的。 在实际的数据库系统中每时每刻都在发生锁定当某个用户在修改一部分数据时MySql 会通过锁定防止其他用户读取同一数据。
一条 update 语句执行流程 二. 锁的分类
MySql 中的锁有很多按照模式、粒度等可以分为如下几种类型 三. 共享锁读锁和排他锁写锁
3.1. 共享锁Shared Lock
共享锁 Shared LocksS 锁也叫读锁当事务对数据加上读锁后其他事务只能对该数据加读锁不能加写锁。为了方便理解下文我们全部使用读锁来称呼加了读锁的记录。
加锁方式
# 方式1
select ... lock in share mode;
# 方式2
select ... for share;
如果事务 T1 在某对象持有共享S锁则事务 T2 需要再次获取该对象的锁时会出现下面两种情况
如果 T2 想获取该对象的共享S锁则可以立即获取锁如果 T2 想获取该对象的排他X锁则无法获取锁操作被堵塞。
举例 3.2. 排他锁Exclusive Lock 排他锁 Exclusive LocksX 锁也叫写锁或独占锁主要是防止其它事务和当前加锁事务锁定同一对象。同一对象主要有两层含义
当排他锁加在表上则其它事务无法对该表进行 insert、update、delete、alter、drop 等更新操作当排他锁加在表的行上则其它事务无法对该行进行 insert、update、delete、alter、drop 等更新操作。
加锁方式select…for update
MySql InnoDB 引擎默认 insert、update、delete 都会自动给涉及到的数据加上排他锁select 语句默认不会加任何锁类型。
举例 四. 全局锁、表级锁、页级锁和行级锁
4.1. 全局锁 全局锁顾名思义就是对整个数据库实例加锁它是粒度最大的锁。当加全局锁时该数据库下面所有的表都处于只读状态不管是当前事务还是其他事务对于库下面所有的表只能读不能执行 insert、update、delete、alter、drop 等更新操作。
全局锁加锁方式为FTWRL
flush tables with read lock;
指令执行完整个数据库就处于只读状态了其他线程执行以下操作都会被阻塞
数据更新语句被阻塞包括 insert、update、delete 语句数据定义语句被阻塞包括建表 create table、alter table、drop table 语句更新操作事务 commit 语句被阻塞。
全局锁释放方式为
方法一执行 unlock tables
方法二加锁的会话断开全局锁也会被自动释放
使用场景
全局锁的典型使用场景是做全库逻辑备份在备份过程中整个库完全处于只读状态。如下图 假如在主库上备份备份期间业务服务器不能对数据库执行更新操作因此涉及到更新操作的业务就瘫痪了。假如在从库上备份备份期间从库不能执行主库同步过来的 binlog会导致主从延迟越来越大如果做了读写分离那么从库上获取数据就会出现延时影响业务。
使用全局锁进行数据备份不管是在主库还是在从库上进行备份操作对业务总是不太友好。那不加锁行不行我们可以通过下面还钱转账的例子看看不加锁会不会出现问题 备份前账户 A 有1000账户 B 有500此时发起逻辑备份假如数据备份时不加锁此时客户端 A 发起一个还钱转账的操作账户 A 往账户 B 转200当账户 A 转出200完成账户 B 转入200还未完成时整个数据备份完成如果用该备份数据做恢复会发现账户 A 转出了200账户 B 却没有对应的转入记录这样就会产生纠纷A 说我账户少了 200B 说我没有收到最后A、B谁都不干。
既然不加锁会产生错误加全局锁又会影响业务那么有没有两全其美的方式呢
有MySql 官方自带的逻辑备份工具 mysqldump具体指令如下
mysqldump –single-transaction
执行该指令在备份数据之前会先启动一个事务来确保拿到一致性视图 加上 MVCC 的支持保证备份过程中数据是可以正常更新。但是single-transaction 方法只适用于库中所有表都使用了事务引擎如果有表使用了不支持事务的引擎备份就只能用 FTWRL 方法。
4.2. 表级锁
MySql 表级锁有两种表锁、元数据锁Metadata LockMDL)。
4.2.1. 表锁 表锁就是对整张表加锁由 MySQL Server 实现行锁则是存储引擎实现不同的引擎实现的不同。在 MySql 的常用引擎中 InnoDB 支持行锁而 MyISAM 则只能使用 MySQL Server 提供的表锁。
表锁的特点
加锁过程的开销小加锁的速度快不会出现死锁的情况锁定的粒度大发生锁冲突的几率大并发度低。 表锁包含读锁和写锁且需要显示加锁或释放锁具体指令如下
-- 给表加写锁
lock tables tablename write;-- 给表加读锁
lock tables tablename read;-- 释放锁
unlock tables;
除了使用 unlock tables 显示释放锁之外会话持有其他表锁时执行 lock table 语句会释放会话之前持有的锁会话持有其他表锁时执行 start transaction 或者 begin 开启事务时也会释放之前持有的锁。
表读锁代表当前表为只读状态读锁是一种共享锁。需要注意的是读锁除了会限制其它线程的操作外也会限制加锁线程的行为具体限制如下
加锁线程只能对当前表进行读操作不能对当前表进行更新操作不能对其它表进行所有操作其它线程只能对当前表进行读操作不能对当前表进行更新操作可以对其它表进行所有操作。
表写锁写锁是一种独占锁需要注意的是写锁除了会限制其它线程的操作外也会限制加锁线程的行为具体限制如下
加锁线程能对当前表进行所有操作不能对其它表进行任何操作其它线程不能对当前表进行任何操作可以对其它表进行任何操作。
实例演示
1. 加表锁 2. 解除表锁
第一步找出被锁的表show processlist 第二步kill 掉锁表的进程
kill 21;
kill 22;
再次更新 user 表数据 发现可以可以正常更新了。
4.2.2. 元数据锁MDL 元数据锁Metadata Lock简称 MDL它是在 MySQL 5.5 版本引进的。元数据锁不用像表锁那样显式的加锁和释放锁而是在访问表时被自动加上以保证读写的正确性。加锁和释放锁规则如下
MDL 读锁之间不互斥也就是说允许多个线程同时对加了 MDL 读锁的表进行 CRUD增删改查操作MDL 写锁它和读锁、写锁都是互斥的目的是用来保证变更表结构操作的安全性。也就是说当对表结构进行变更时会被默认加 MDL 写锁。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。MDL 读写锁是在事务 commit 之后才会被释放
4.2.3. AUTO-INC 锁 AUTO-INC 锁是一种特殊的表级锁当表中有 AUTO_INCREMENT 的列时如果向这张表插入数据时InnoDB 会先获取这张表的 AUTO-INC 锁等插入语句执行完成后AUTO-INC 锁会被释放。 AUTO-INC 锁可以使用 innodb_autoinc_lock_mode 变量来配置自增锁的算法innodb_autoinc_lock_mode 变量可以选择三种值如下表
innodb_autoinc_lock_mode含义0传统锁模式采用 AUTO-INC 锁1连续锁模式采用轻量级锁2交错锁模式(MySQL8默认)AUTO-INC和轻量级锁之间灵活切换
锁的兼容性
XIXSISX冲突冲突冲突冲突IX冲突兼容冲突兼容S冲突冲突兼容兼容IS冲突兼容兼容兼容
4.3. 页级锁 页级锁是 MySql 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。因此采取了折衷的页级锁一次锁定相邻的一组记录。BDB 引擎支持页级锁。
4.4. 行级锁 行锁是针对数据表中行记录的锁。MySql 的行锁是在引擎层实现的并不是所有的引擎都支持行锁比如InnoDB 引擎支持行锁而 MyISAM 引擎不支持。
行锁的特点
加锁过程的开销大加锁的速度慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高。
InnoDB 引擎的行锁主要有4类
Record Lock记录锁是在索引记录上加锁Gap Lock间隙锁锁定一个范围不包含记录Next-key Lock临键锁Gap Lock Record Lock锁定一个范围Gap Lock 实现并且锁定记录本身Record Lock 实现Insert Intention Lock插入意向锁。 行锁是作用在 索引 上的哪怕你在建表的时候没有定义一个索引InnoDB 也会创建一个聚簇索引并将其作为锁作用的索引。 每一个 InnoDB 表都需要一个聚簇索引有且只有一个。如果你为该表定义一个主键那么MySql 将使用主键作为聚簇索引如果你没有定义主键那么 MySql 将会把第一个唯一索引而且要求 NOT NULL作为聚簇索引如果上诉两种情况都不满足那么 MySql 将自动创建一个名字为 GEN_CLUST_INDEX 的隐藏聚簇索引。 因为是聚簇索引所以 B树上的叶子节点都存储了数据行那么如果现在是二级索引呢InnoDB 中的二级索引的叶节点存储的是主键值或者说聚簇索引的值所以通过二级索引查询数据时还需要将对应的主键去聚簇索引中再次进行查询。 更新单行记录的加锁原理
update user set age 10 where id 49;
update user set age 10 where name Tom;
第一条 SQL 使用主键查询只需要在 id 49 这个主键索引上加上锁第二条 SQL 使用二级索引来查询那么首先在 name Tom 这个索引上加写锁然后由于使用 InnoDB二级索引还需再次根据主键索引查询所以还需要在 id 49 这个主键索引上加锁也就是说使用主键索引需要加一把锁使用二级索引需要在二级索引和主键索引上各加一把锁。
更新多行记录的加锁原理
update user set age 10 where id 49;
MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录然后 InnoDB 引擎会将第一条记录返回并加锁接着 MySQL Server 发起更新该行记录的 UPDATE 请求更新这条记录一条记录操作完成再读取下一条记录直至没有匹配的记录为止。 示例 五. 行锁四兄弟记录锁、间隙锁、临键锁和插入意向锁
5.1. 记录锁Record Lock Record Lock记录锁是针对索引记录的锁锁定的总是索引记录。行锁是加在索引上的如果当你的查询语句不走索引的话那么它就会升级到表锁最终造成效率低下所以在写 SQL 语句时需要特别注意。 例如select id from user where id 1 for update; for update 就显式在索引 id 上加行锁排他锁防止其它任何事务 update 或 delete id1 的行但是对 user 表的 insert、alter、drop 操作还是可以正常执行。
5.2. 间隙锁Gap Lock Gap Lock间隙锁锁住两个索引记录之间的间隙由 InnoDB 隐式添加。比如1,3表示锁住记录1和记录3之间的间隙这样记录2就无法插入间隙可能跨越单个索引值、多个索引值甚至是空。间隙锁只是锁住间隙内部的范围在间隙外的 insert/update 操作不会受影响。 间隙锁是锁在两个存在的索引之间是一个开区间。间隙锁是可以共存的共享间隙锁与独占间隙锁之间是没有区别的两者之间并不冲突。其存在的目的都是防止其他事务往间隙中插入新的记录故而一个事务所采取的间隙锁是不会去阻止另外一个事务在同一个间隙中加锁的。 示例
select * from user where id 10 for update;
即所有在 [110区间内的记录行都会被锁住所有 id 为 1、2、3、4、5、6、7、8、9 的数据行的插入会被阻塞。 5.3. 临键锁Next-Key Lock Next-Key 锁称为临键锁它是 Record Lock Gap Lock 的组合用来锁定一个范围并且锁定记录本身锁它是一种左开右闭的范围可以用符号表示为(a,b]。 当我们使用范围条件而不是相等条件去检索并请求锁时InnoDB 就会给符合条件的记录的索引项加上锁而对于键值在条件范围内但并不存在参考上面所说的空闲块的记录就叫做间隙InnoDB 在此时也会对间隙加锁。 Next-Key Lock 被用来解决可重复读隔离级别下 幻读 的现象。 5.4. 插入意向锁Insert Intention Lock 插入意向锁它是一种特殊的间隙锁特指插入操作产生的间隙锁。如果多个事务 INSERT 到同一个索引间隙之间但没有在同一位置上插入则不会产生任何的冲突。 假设有值为 4 和 7 的索引记录现在有两事务分别尝试插入值为 5 和 6 的记录在获得插入行的排他锁之前都使用插入意向锁锁住 4 和 7 之间的间隙但两者之间并不会相互阻塞因为这两行并不冲突。 插入意向锁 只会和 间隙锁 或者 Next-key锁 冲突正如上面所说间隙锁作用就是防止其他事务插入记录造成幻读正是由于在执行 INSERT 语句时需要加插入意向锁而插入意向锁和间隙锁冲突从而阻止了插入操作的执行。
六. 意向锁Intention Lock 由于表锁和行锁虽然锁定范围不同但是会相互冲突。当你要加表锁时势必要先遍历该表的所有记录判断是否有排他锁这种遍历检查的方式显然是一种低效的方式为了快速的判断表中是否存在行锁MySql 引入了意向锁来检测表锁和行锁的冲突。 意向锁Intention Lock它是一种表锁用来标识事务打算在表中的行上获取什么类型的锁。 不同的事务可以在同一张表上获取不同种类的意向锁但是第一个获取表上意向排他锁IX的事务会阻止其它事务获取该表上的任何 S锁 或 X锁。反之第一个获得表上意向共享锁IS的事务可防止其它事务获取该表上的任何 X锁。
意向锁通常有两种类型
意向共享锁IS表示事务打算对表中的行设置共享锁。意向排他锁IX表示事务打算对表中的行设置排他锁。
意向锁是 InnoDB 自动加上的加锁时遵从下面两个协议
事务在获取表中行的共享锁之前必须先获取表上的意向共享锁IS或更强的锁。事务在获取表中行的排他锁之前必须先获取表上的意向排他锁IX。
意向锁之间是不会产生冲突的它只会阻塞表级读锁或写锁。意向锁不与行级锁发生冲突。下表是各种锁之间的兼容情况
排他锁 X写意向锁 IX共享锁 S读意向锁 IS排他锁 X写意向锁 IX兼容兼容共享锁 S兼容兼容读意向锁 IS兼容兼容兼容
注意上面的 X 与 S 是说表级的 X锁和 S锁意向锁不和行级锁发生冲突。
如果一个事务请求的锁模式与当前的锁兼容InnoDB 就将请求的锁授予该事务如果两者不兼容那么该事务就需要等待锁的释放。
七. 乐观锁和悲观锁
7.1. 乐观锁 乐观锁的“乐观情绪”体现在它认为数据的变动不会太频繁。因此它允许多个事务同时对数据进行变动。实现方式乐观锁一般会使用版本号机制或 CAS 算法实现。
乐观锁适合读取频繁的场景。 使用乐观锁
乐观锁思想就是有线程过来先放过去修改如果正式更新时确认记录没被其他线程修改过就可以修改成功如果已被其他线程修改过就修改失败或者重试。
实现方式一般会使用「版本号机制」或「CAS算法」实现。这里的版本号可以是一个数字每次更新时比较版本号更新的时候同步更新版本号1也可以是该条记录的最后一次更新时间戳每次更新时比较时间戳更新的时候同步更新这个时间戳。
7.2. 悲观锁 悲观锁她专一且缺乏安全感了她的心只属于当前事务每时每刻都担心着它心爱的数据可能被别的事务修改所以一个事务拥有获得悲观锁后其他任何事务都不能对数据进行修改只能等待锁被释放才可以执行。
悲观锁适合写入频繁的场景。 使用悲观锁
悲观锁思想就是当前线程要进来修改数据时别的线程都得拒之门外。
实现方式可以使用 select…for update
select * from User where name‘jay’ for update
以上这条 sql 语句会锁定了 User 表中所有符合检索条件name‘jay’的记录。本次事务提交之前别的线程都无法修改这些记录。
7.3. 小结 在 MySql 中无论是悲观锁还是乐观锁都是人们对概念的一种思想抽象它们本身还是利用 MySql 提供的锁机制来实现的。
悲观锁可以理解成在对任意记录进行修改前先尝试为该记录加上排他锁exclusive locking采用的是先获取锁再操作数据的策略可能会产生死锁乐观锁是相对悲观锁而言一般不会利用数据库的锁机制而是采用类似版本号比较之类的操作因此乐观锁不会产生死锁的问题
示例
假设有 A、B 两个用户同时各购买一件 id1 的商品用户 A 获取到的库存量为 1000用户 B 获取到的库存量也为 1000用户 A 完成购买后修改该商品的库存量为 999用户 B 完成购买后修改该商品的库存量为 999此时库存量数据产生了不一致。有两种解决方案
悲观锁方案每次获取商品时对该商品加排他锁。也就是在用户 A 获取 id1 的商品信息时对该行记录加锁期间其他用户阻塞等待访问该记录
begin;
select * from goods where id 1 for update;
update goods set stock stock - 1 where id 1;
commit;
乐观锁方案每次获取商品时不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等如果相等则进行更新反之程序重新获取库存量再次进行比较直到两个库存量的数值相等才进行数据更新
select * from goods where id 1;
begin;
-- 更新 stock 值这里需要注意 where 条件 stock cur_stock只有程序中获取到的库存量与数据库中的库存量相等才执行更新
update goods set stock stock - 1 where id 1 and stock cur_stock;
commit;
八. 总结
8.1. InnoDB 的加锁方法
1. 意向锁是 InnoDB 自动加的不需要用户干预。
2. 对于 INSERT、UPDATE 和 DELETE 语句InnoDB 会自动给涉及的数据集加上排他锁。
3. 对于普通的 SELECT 语句InnoDB 不会加任何锁事务可以通过以下语句显示给记录集添加共享锁或排他锁
共享锁Sselect * from table_name where ... lock in share mode此时其他事务仍然可以查询该记录也可以对该记录加共享锁但是无法对该记录加排他锁。排他锁Xselect * from table_name where ... for update其他事务可以查询该记录但是不能对该记录加共享锁或排他锁只能等待锁释放后在加锁。
8.1.1. select for update 在执行这个 select 查询语句的时候会将对应的索引访问条目加上排他锁X锁也就是说这个语句对应的锁就相当于 update 带来的效果。 使用场景为了确保自己查找到的数据一定是最新数据并且查找到的数据值允许自己来修改此时就需要用到 select for update 语句。 性能分析select for update 语句相当于一个 update 语句。在业务繁忙的情况下如果事务没有及时地 commit 或者 rollback 可能会造成事务长时间的等待从而影响数据库的并发使用效率。
8.1.2. select lock in share mode in share mode 子句的作用就是将查找的数据加上一个 share 锁这个就是表示其他的事务只能对这些数据进行简单的 select 操作而不能进行 DML 操作。 使用场景为了确保自己查询的数据不会被其他事务正在修改也就是确保自己查询到的数据是最新的数据并且不允许其他事务来修改数据。与 select for update 不同的是本事务在查找完之后不一定能去更新数据因为有可能其他事务也对同数据集使用了 in share mode 的方式加上了 S锁。 性能分析select lock in share mode 语句是一个给查找的数据上一个共享锁S 锁的功能它允许其他的事务也对该数据上 S锁但是不允许对该数据进行修改。如果不及时的 commit 或者rollback 也可能会造成大量的事务等待。
8.2. 查看 InnoDB 的锁争用情况
可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况
mysql show status like innodb_row_lock%;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
--------------------------------------
5 rows in set (0.01 sec)
8.3. 死锁
8.3.1. 什么是死锁 当并发系统中不同线程出现循环资源依赖涉及的线程都在等待别的线程释放资源时就会导致这几个线程都进入无限等待的状态称为死锁。
可以通过下面的指令查看死锁
show engine innodb status\G MyISAM 表锁是 deadlock free 的这是因为 MyISAM 总是一次性获得所需的全部锁要么全部满足要么等待因此不会出现死锁。但是在 InnoDB 中除单个 SQL 组成的事务外锁是逐步获得的这就决定了 InnoDB 发生死锁是可能的。
8.3.2. 产生死锁的四个必要条件
互斥条件一个资源每次只能被一个进程使用。请求和保持条件一个进程因请求资源而阻塞时对已获得的资源保持不放。不剥夺条件进程已获得的资源在未使用完之前不能被其他进程强行剥夺只能由该进程自己释放。循环等待条件若干进程之间形成一种头尾相接的循环等待资源关系。
8.3.3. 如何避免死锁
要避免死锁一般就是切断环路尽量避免并发形成环路
如果不同程序会并发存取多个表尽量约定以相同的顺序访问表可以大大降低死锁几率。在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率。对于非常容易产生死锁的业务部分可以尝试使用升级锁的粒度通过表级锁定来减少死锁产生的概率。如果业务处理不好可以用分布式事务锁或者使用乐观锁。死锁与索引密不可分解决索引问题需要合理优化你的索引。将参数 innodb_deadlock_detect 设置为 on开启死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其它事务得以继续执行。当涉及表锁或者外部锁时死锁检测机制可能会失效这时可以通过参数 innodb_lock_wait_timeout 设置超时时间当两个事务互相等待超过设置的阈值时其中一个事务进行回滚另外一个等待的事务就能继续执行。
8.3.4. 怎么排查死锁问题
查看死锁日志 show engine innodb status 找出死锁 sql 分析 sql 加锁情况 找出死锁原因 制定解决方案
