MySQL锁——行锁(InnoDB)
简介
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是 采用了行级锁。
行锁特点 :偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元。
事务具有以下4个特性,简称为事务ACID属性。
ACID属性 | 含义 |
---|---|
原子性(Atomicity) | 事务是一个原子操作单元,其对数据的修改,要么全部成功,要么全部失败。 |
一致性(Consistent) | 在事务开始和完成时,数据都必须保持一致状态。 |
隔离性(Isolation) | 数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的 “独立” 环境下运行。 |
持久性(Durable) | 事务完成之后,对于数据的修改是永久的。 |
并发事务处理带来的问题
问题 | 含义 |
---|---|
丢失更新(Lost Update) | 当两个或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖。 |
脏读(Dirty Reads) | 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。 |
不可重复读(Non-Repeatable Reads) | 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现和以前读出的数据不一致。 |
幻读(Phantom Reads) | 一个事务按照相同的查询条件重新读取以前查询过的数据,却发现其他事务插入了满足其查询条件的新数据。 |
事务隔离级别
为了解决上述提到的事务并发问题,数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行,这显然与“并发” 是矛盾的。
数据库的隔离级别有4个,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。
隔离级别 | 丢失更新 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
Read uncommitted | × | √ | √ | √ |
Read committed | × | × | √ | √ |
Repeatable read(默认) | × | × | × | √ |
Serializable | × | × | × | × |
备注 : √ 代表可能出现 , × 代表不会出现 。
InnoDB行锁
InnoDB 实现了以下两种类型的行锁。
- 共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
- 排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;
可以通过以下语句显示给记录集加共享锁或排他锁 。
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(X) :SELECT * FROM table_name WHERE ... FOR UPDATE (悲观锁)
悲观锁和乐观锁
悲观锁:事务必须排队执行。数据锁住了,不允许并发。(行级锁:select后面添加for update)
乐观锁:支持并发,事务也不需要排队,只不过需要一个版本号。
因为事务的原子性,要验证innodb的行锁,需要先关闭自动提交。
客户端1 | 客户端2 |
---|---|
关闭自动提交set autocommit=0; | 关闭自动提交set autocommit=0; |
可以正常查询数据 | 可以正常查询数据 |
修改id=4的数据update test_innodb_lock set name = '1234' where id = 4; 更新,但不提交,加行锁(排他锁) | 修改id=4的数据update test_innodb_lock set name = '1234' where id = 4; 发生阻塞,id=4的行被锁,无法更新,等待客户端1提交事务,解决了脏读,不可重复读,体现了事务的隔离性 |
修改id=3的数据update test_innodb_lock set name = '1234' where id = 3; 更新id=3的数据成功,同时加锁 ,因为客户端1只锁了id=4的行 |
如果执行了更新语句,会对这一行数据加上排他锁(写锁),提交commit之后,会释放锁。另外一个线程update语句才可以执行解除阻塞状态。前提是两个线程操作同一行数据。
错误使用行锁
行锁升级为表锁
无索引或索引失效,行锁会升级为表锁
已知表有name和id两个单值索引。
客户端1 | 客户端2 |
---|---|
关闭自动提交set autocommit=0; | 关闭自动提交set autocommit=0; |
更新不同行update test_innodb_lock set sex = '1' where name = 100; name的类型是varchar,这里name=100导致索引失效,因此行锁升级为表锁 | 更新update test_innodb_lock set sex = '1' where name = 100; 产生阻塞 ,因为升级为表锁后,其他客户端不能对此表操作,必须等到客户端1结束其当前事务 |
由于 执行更新时 , name字段本来为varchar类型, 我们是作为数组类型使用,存在类型转换,索引失效,最终行锁变为表锁
;(字符串类型,在SQL语句使用的时候没有加单引号,导致索引失效,查询没有走索引,进行全表扫描时,索引失效,行锁就升级为表锁)
其他索引失效的情况可以看我之前的文章 MySQL索引
产生间隙锁
当我们用范围条件,而不是使用相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据进行加锁;
对于键值在条件范围内但并不存在的记录,叫做 “间隙(GAP)” , InnoDB也会对这个 “间隙” 加锁,这种锁机制就是所谓的
间隙锁(Next-Key锁)
客户端1 | 客户端2 |
---|---|
关闭自动提交set autocommit=0; | 关闭自动提交set autocommit=0; |
更新id<4的数据(已知id的取值为1,3,4…没有2)update test_innodb_lock set sex = '1' where id<4; | 插入id=2的数据insert into test_innodb_lock(id,name,sex) values(2,'asd','1'); 由于表数据中不存在id=2的数据,但是id<4的行被加了排他锁,此时,这行数据就被加了间隙锁。无法插入,发生阻塞 |
为了避免间隙锁。在更新的时候,或者对数据行进行加锁的时候,尽量去缩小条件,使得间隙数据尽量的少,最大程度避免间隙锁的存在。
InnoDB行锁的争用情况
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
`Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
`Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高,而且每次等待的时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划。
总结
InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是,InnoDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。
优化建议:
- 尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级为表锁。
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少索引条件,及索引范围,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可使用低级别事务隔离(但是需要业务层面满足需求)