管家婆免费资料网站,北京快三走势图今天,网站制作需要什么,企业网站维护外包Mysql InnoDB存储引擎的锁相关
InnoDB下#xff0c;mysql四个级别隔离下加锁操作 四个级别隔离的写操作都加X锁串行化下读加S锁select … for update, select … lock in share mode 分别加x锁#xff0c;s锁在需要加锁的场景下#xff0c;会根据情况使用三种加锁策略…Mysql InnoDB存储引擎的锁相关
InnoDB下mysql四个级别隔离下加锁操作 四个级别隔离的写操作都加X锁串行化下读加S锁select … for update, select … lock in share mode 分别加x锁s锁在需要加锁的场景下会根据情况使用三种加锁策略算法 Record LockGap LockNext-Key Lock mysql RR 为啥能隔离幻读 快照读 select当前读 select for update, select lock in share mode, delete,insert,update InnoDB提供了一致性的非锁定读、行级锁支持Lock 与Latch Lock 主要用于锁定数据库中的对象如表、页、行而Latch主要是线程并发上的资源锁定。Latch的实现采用了乐观spin-wait,所以在竞争比较激烈的并发环境下性能不是很好是一种轻量锁。Latch mutex(互斥liang)rwlock(读写锁 Lock 行锁、表锁、页锁在没有索引的情况下一般是锁全表的所有行然后逐行判断解锁没有实现锁升级 行级锁 共享锁S Lock)排他锁 (X Lock)S与S兼容而X与什么都不兼容 意向锁事务在更细的粒度上加锁 意向共享锁(IS Lock) ,事务想要获得一张表中某几行的共享锁意向排他锁(IX Lock),事务想要获得一张表中某几行的排他锁InnoDB的意向锁是表级别的锁 一致性的非锁定读 通过行多版本控制的方式来读取当前执行时间数据中行的数据。如果当前行的X锁已经被获得那么通过undo 段来获得X锁被获得前的快照返回该快照的数据非锁定读不需要X锁的释放就可以进行操作。可以等同于在事务开始时获得全局快照在事务运行期间无论其他事务是否对数据进行提交都不影响。 一致性锁定读 SELECT…FOR UPDATE 加X锁)SELECT… LOCK IN SHARE MODE (加S锁) 自增长与锁 SELECT MAX(auto_inc_col) FROM t FOR UPDATE; 外键和锁 对外键值的插入和更新的时候需要读父表这时不能采用一致的非锁定读否则会出现子表与父表不一致的现象。所以需要采用一致性读也就是给父表加S锁。 行锁的三种算法 Record Lock: 单个行记录上的锁 锁的是索引如果没有设置索引那么锁的是主键 Gap Lock: 间隙锁锁定一个范围不包含记录本身 *Next-Key Lock: Gap Lock Record Lock, 锁定一个范围并且锁定记录本身 唯一索引情况下降级为Record Lock InnoDB通过Next-key Lock避免了幻读问题 通过加两个间隙锁来保证对于insert,delete操作不会造成幻读 所有隔离级别都实现了写写互斥四种隔离级别 读未提交 读读不互斥、读写不互斥、写写互斥不是没有加锁 读已提交一致性的非锁定读 读读不互斥、读写不互斥、写写互斥每次读都是读的最新快照 避免了脏读通过undo log实现 可重复读一致性的非锁定读 读读不互斥、读写不互斥、写写互斥每次读都是事务开始时的最新快照 避免了脏读不可重复读 通过undo log实现 串行化 读读不互斥、读写互斥、写写互斥读读加的是S锁容易发生死锁 死锁 发生死锁时会自动回滚事务
Mysql Innodb 中的锁
先列出我本地的运行环境 数据库版本是5.7隔离级别是Repeatable-Read(可重复读)不同的数据库版本和隔离级别对语句的执行结果影响很大。讨论锁的时候不指明版本和隔离级别都是耍流氓。
一、为什么要加锁
数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据破坏数据库的一致性。
锁是用于管理对公共资源的并发控制。也就是说并发的情况下会出现资源竞争所以需要加锁。
举个例子转账操作。简单来说张三给李四转账x元可以分为三步
1先查询张三的账户余额y是否大于x2张三的余额 y y - x元3李四的余额 x z x元假设张三账户余额有1000元李四余额也有1000元如果不加锁的话同时有两个请求A要求转500元B要求转600元第一步查询余额都是足够的第2步和第3步也能执行成功但是最终结果却是错误第二个请求可能会覆盖掉第一个请求。
这种问题叫做 丢失更新 多个事务操作同一行后面的更新覆盖了前面的更新值。需要在应用级别加锁来避免。
数据库有ACID原则其中I是隔离性标准SQL规范中定义了四种隔离级别 越往下隔离级别越高问题越少同时并发度也越低。隔离级别和并发度成反比的。
脏读事务A读取了事务B未提交的数据不可重复读对于一条记录事务A两次读取的数据变了幻读事务A按照相同的查询条件读取到了新增的数据
MySQL中的隔离级别如下 和标准SQL规范相比MySQL中可重复读解决了幻读实现了串行化隔离级别的功能同时没有严重影响并发。是通过加锁、阻止插入新数据来解决幻读的。
二、锁的分类 我们听说过读锁、写锁、共享锁、互斥锁、行锁等等各种名词根据自己的理解简单对这些锁进行了分类。 加锁机制
1、乐观锁先修改保存时判断是够被更新过应用级别
2、悲观锁先获取锁再操作修改数据库级别
锁粒度
表级锁开销小加锁快粒度大锁冲突概率大并发度低适用于读多写少的情况。
页级锁BDB存储引擎
行级锁Innodb存储引擎默认选项
兼容性
S锁也叫做读锁、共享锁对应于我们常用的 select * from users where id 1 lock in share mode
X锁也叫做写锁、排它锁、独占锁、互斥锁对应对于select * from users where id 1 for update
下面这个表格是锁冲突矩阵可以看到只有读锁和读锁之间兼容的写锁和读锁、写锁都是冲突的。 冲突的时候会阻塞当前会话直到拿到锁或者超时
这里要提到的一点是S锁 和 X锁是可以是表锁也可以是行锁
索引组织表
先理解下索引组织表。
辅助索引
聚集索引
Innodb中的索引数据结构是 B 树数据是有序排列的从根节点到叶子节点一层层找到对应的数据。普通索引也叫做辅助索引叶子节点存放的是主键值。主键上的索引叫做聚集索引表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候会先在辅助索引中找到对应的主键值然后用主键值在聚集索引中找到该条记录。举个例子用nameAlice来查询的时候会先找到对应的主键值是18 然后用18在下面的聚集索引中找到nameAlice的记录内容是 77 和 Alice。
表中每一行的数据是组织存放在聚集索引中的所以叫做索引组织表。
了解索引数据结构的目的是为了说明行锁是加在索引上的。
1.select * from user where id10 for update 一条简单的SQL。在user表中查找id为10的记录并用for update加X锁。
这里User表中有3个字段 主键id 和 另外一个字段name。下面的表格是B树索引的简化表达。第一行id是索引的节点第二行和第三行是这行记录包含了姓名和性别。
如图所示通过锁住聚集索引中的节点来锁住这条记录。
聚集索引上的锁比较好理解锁住id10的索引即锁住了这条记录。
2. select * from user where name‘b’ for update 查询user表中name为d的记录并用for update加X锁
这里的name上加了唯一索引唯一索引本质上是辅助索引加了唯一约束。所以会先在辅助索引上找到name为d的索引记录在辅助索引中加锁然后查找聚集索引锁住对应索引记录。
为什么聚簇索引上的记录也要加锁试想一下如果有并发的另外一个SQL是直接通过主键索引id30来更新会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话两个并发SQL之间是互相感知不到的。
3. select * from user where name‘b’ for update 查询user表中name为b的记录并用for update加X锁。这里name上加了普通的索引不是唯一索引。普通索引的值是可以重复的。会先在辅助索引中找到name为b的两条记录加X锁然后得到主键值7和30到聚集索引中加X锁。
事情并没有那么简单如果这时有另一个事务插入了nameb,id40的记录却发现是可以插入的。 位置在途中红色线条标注的间隙内这样就会出现幻读两次查询得到的结果是不一致的第一次查到两条数据插入之后得到三条数据。
为了防止这种情况出现了另一种锁gap lcok 间隙锁。锁住的是索引的间隙。 即图中红色线条标识的空隙。因为新插入nameb的记录可能出现在这三个间隙内。
这张图里出现了三种锁
记录锁单行记录上的锁
间隙锁锁定记录之间的范围但不包含记录本身。
Next Key Lock: 记录锁 间隙锁锁定一个范围包含记录本身。
4. 意向锁( Intention Locks )
InnoDB为了支持多粒度(表锁与行锁)的锁并存引入意向锁。意向锁是表级锁
IS: 意向共享锁 IX: 意向排他锁
事务在请求某一行的S锁和X锁前需要先获得对应表的IS、IX锁。
意向锁产生的主要目的是为了处理行锁和表锁之间的冲突用于表明“某个事务正在某一行上持有了锁或者准备去持有锁”。比如表中的某一行上加了X锁就不能对这张表加X锁。
如果不在表上加意向锁对表加锁的时候都要去检查表中的某一行上是否加有行锁多麻烦。
意向锁的兼容性矩阵
5. 插入意向锁Insert Intention Lock
Gap Lock中存在一种插入意向锁在insert操作时产生。
有两个作用
和next-key互斥阻塞next-key 锁防止插入数据这样就不会幻读。插入意向锁互相是兼容的允许相同间隙、不同数据的并发插入
三、常见语句的加锁分析
后面会有多个SQL语句先说明一下表结构
CREATE TABLE user (id int(11) unsigned NOT NULL AUTO_INCREMENT,id_no varchar(255) DEFAULT NULL COMMENT 身份证号,name varchar(255) DEFAULT NULL COMMENT 姓名,mobile varchar(255) DEFAULT NULL COMMENT 手机号,age int(11) DEFAULT NULL COMMENT 年龄,address varchar(255) DEFAULT NULL COMMENT 地址,PRIMARY KEY (id),UNIQUE KEY uniq_id_no (id_no),KEY idx_name (name)
) ENGINEInnoDB AUTO_INCREMENT10002 DEFAULT CHARSETutf8 COMMENT用户表;这里有一个user表5个字段其中id是主键id_no是身份证号加了唯一索引name是用户姓名可以重复的加了普通索引手机号、年龄、地址都没有索引。
1. 普通select
select * from user where id 1;begin;
select * from user where id 1;
commit:普通的select 语句是不加锁的。select包裹在事务中同样也是不加锁的。where后面的条件不管多少普通的select是不加锁的。
2. 显式加锁
select * from user where id 1 lock in share mode;select * from user where id 1 for update;显式指出要加什么样的锁。上面一个加的是共享锁下面的是互斥锁。
这里需要强调的一点需要明确在事务中是用这些锁不在事务中是没有意义的。
3. 隐式加锁
update user set address 北京 where id1;
delete from user where id1;update和delete也会对查询出的记录加X锁隐式加互斥锁。加锁类型和for update 类似
后面只按照显式加锁的select for update 举例子更新和删除的加锁方式是一样的。
4. 按索引类型
elect * from user where id 1 for update;select * from user where id_no a22 for update;select * from user where name 王二 for update;select * from user where address 杭州 for update;四条SQL区别在于where条件的过滤列分别是主键、唯一索引、普通索引、无索引。 主键之前提到过索引组织表这里会在聚集索引上对查询出的记录加X锁
唯一索引会在辅助索引上把在对应的id_noa22的索引加X锁因为是唯一的所以不是next-key锁。然后在主键上也会在这条记录上加X锁。
普通索引因为不是唯一的会在辅助索引上把对应的id_noa22的索引加next-key锁。然后在主键加X锁。
无索引首先是不推荐这种写法没有索引的话因为会全表扫描数据量大的话查询会很慢。这里讨论的是这种情况下会加什么锁 答案 首先聚簇索引上的所有记录都被加上了X锁。其次聚簇索引每条记录间的间隙(GAP)也同时被加上了GAP锁。在这种情况下这个表上除了不加锁的快照度其他任何加锁的并发SQL均不能执行不能更新不能删除不能插入全表被锁死。这是一个很恐怖的事情请注意。
5. 记录不存在的情况
前面几个例子中都是可以查到结果的。如果对应记录不存在会怎样答案是锁住间隙不允许插入。mysql要保证没有其他人可以插入所以锁住间隙。
6. 普通 insert 语句
在插入之前会先在插入记录所在的间隙加上一个插入意向锁。
insert会对插入成功的行加上排它锁这个排它锁是个记录锁而非next-key锁当然更不是gap锁了不会阻止其他并发的事务往这条记录之前插入 。
7. 先查询后插入
类似于这样的insert
insert into target_table select * from source_table ...
create target_table select * from source_table ...将select查询的结果集插入到另一张表中或者使用结果集创建一个新表。
和之前简单插入的情况类似已插入成功的数据加X锁间隙加上一个插入意向锁。
对于select的源表中的记录会加共享的 next-key 锁。这是为了防止主从同步出问题。
举个例子 session1 先开启事务然后查询user2表的结果集插入到user表中session2开启事务在插入user2中插入数据所插入的数据刚好是session1能查询到的数据如果不加锁的话session2可以插入成功然后session2提交事务接着session1提交数据。这样看起来是没问题的但是session2先提交的所以bin log中会这样记录先在user2表中插入数据然后在user中插入数据这样的bin log在从库执行的时候就会出问题。
主库 user2插入一条数据user 插入一条数据
从库 user2插入一条数据user 插入两条数据
user表会比主库多一条数据。所以需要锁住select查询表中加next-key锁不允许user2表中新增数据。
四、分析当前锁的情况
先说一下死锁的定义死锁是指两个或两个以上的事务在执行过程中因争夺资源而造成的一种互相等待的现象。这个定义适用于数据库有几个重点两个或两个以上的事务一个事务是不会出现死锁的。争夺的资源一般都是表或者记录。
出现死锁了会怎样正常情况下mysql会检查出死锁并回滚某一个事务让另一个事务正常运行。
Mysql 会回滚副作用小的事务判定的标准是执行的时间以及影响的范围。
1.如何知道系统有没有发生过死锁如何去查看发生过的锁 show status like ‘innodb_row_lock%; 从系统启动到现在的数据
Innodb_row_lock_current_waits当前正在等待锁的数量
Innodb_row_lock_time 锁定的总时间长度单位ms
Innodb_row_lock_time_avg 每次等待所花平均时间
Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits 从系统启动到现在总共等待的次数。
平均时间和锁等待次数比较大的话说明可能会存在锁争用情况
2. show engine innodb status 展示innodb存储引擎的运行状态
通过这个命令显示的内容比较多其中有一项lasted detected deadlock 显示最近发生的死锁。
图中红色线条标注的是执行的SQL以及加了什么锁可以看出是在这行记录上加了X锁没有gap锁。
3. 错误日志中查看历史发生过的死锁
set global innodb_print_all_deadlocks1;上一个命令只能看到最近发生的锁如果我想看历史发生的锁怎么办 执行这一句更改innodb 的一个配置innodb_print_all_deadlocks打印所有的死锁。会将死锁的信息输出到mysql的错误日志中默认是不输出格式和show engine innodb status 是差不多的。
4. information_schema.innodb_locks information_schema 数据库是mysql自带的保存着关于MySQL服务器所维护的所有其他数据库的信息。其中innodb_locks表记录了事务请求但是还没获得的锁即等待获得的锁。
lock_id锁的id由锁住的空间id编号、页编号、行编号组成
lock_trx_id锁的事务id。
lock_mode锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]
lock_type锁的类型表锁还是行锁
lock_table要加锁的表。
lock_index锁住的索引。
lock_spaceinnodb存储引擎表空间的id号码
lock_page被锁住的页的数量如果是表锁则为null值。
lock_rec被锁住的行的数量如果表锁则为null值。
lock_data被锁住的行的主键值如果表锁则为null值。
5. information_schema.innodb_lock_waits 查看等待中的锁
requesting_trx_id申请锁资源的事务id。
requested_lock_id申请的锁的id。
blocking_trx_id阻塞的事务id当前拥有锁的事务ID。
blocking_lock_id阻塞的锁的id当前拥有锁的锁ID
6. information_schema.innodb_trx 查看已开启的事务
trx_idinnodb存储引擎内部事务唯一的事务id。
trx_state当前事务的状态。
trx_started事务开始的时间。
trx_requested_lock_id等待事务的锁id如trx_state的状态为LOCK WAIT那么该值代表当前事务之前占用锁资源的id如果trx_state不是LOCK WAIT的话这个值为null。
trx_wait_started事务等待开始的时间。
trx_weight事务的权重反映了一个事务修改和锁住的行数。在innodb的存储引擎中当发生死锁需要回滚时innodb存储引擎会选择该值最小的事务进行回滚。
trx_mysql_thread_id正在运行的mysql中的线程idshow full processlist显示的记录中的thread_id。
trx_query事务运行的sql语句
五、预防死锁
以相同的顺序更新不同的表
这样执行的话会出现锁等待但不容易出现死锁。
假设有这么两个接口增加老师和学生的幸运值、减少老师和学生的幸运值这个需求是我造出来的先别管需求是不是合理。 现在有两个请求一个增加幸运值一个降低幸运值如果更新顺序不同的话就是这样第一个事务先给老师加幸运值第二个接口给学生减幸运值然后第一个事务给学生加幸运值因为锁已经被第二个事务持有了所以第一个事务等待。然后第二个事务给老师幸运值这时就互相等待锁出现了死锁。
预先对数据进行排序 比如一个接口批量操作数据如果乱序的话并发的情况下也是有可能出现死锁的。给学生批量加分的接口按照表格中的执行顺序的话第一个事务持有A的锁请求B的锁第二个事务持有B的锁请求A的锁出现死锁。
直接申请足够级别的锁而非先共享锁再申请排他锁。 比如这种情况两个事务先申请共享锁共享锁是兼容的然后申请互斥锁的时候需要互相等待就出现了死锁。 事务的粒度及时间尽量保持小这样锁冲突的概率就小了也就不容易出现死锁。不建议在数据库的事务中执行API调用。 正确加索引。没有索引会引起全表扫描类似于锁表。
六总结
1正确的加索引尽量先查询然后使用主键去加锁等于操作来加锁而尽量避免辅助索引或者不是范围比较来加锁。
2出现了锁的问题根据数据库已有的信息分析死锁。
3举了几个例子可能很多都是上线之后才发现的最好能在开发阶段就避免死锁。
