用过SQL的童鞋应该都对SQL的数据隔离级别有所了解。知其然,更应知其所以然。本文从锁的角度解析SQL数据库的隔离级别,以期能够对SQL数据隔离有更深入的理解。
首先简短的回顾一下与隔离级别相关的三种数据库读写异常:
读写异常 | 异常描述 |
---|---|
脏读(Dirt Read) | 事务读取到其它事务未提交的变更 |
不可重复读(Unrepeatable Read) | 事务两次读取的记录不一致(由Update引起) |
幻读(Phantom Read) | 事务两次读取的记录数不一致(由Insert或Delete引起) |
对应于三种读写异常,SQL标准定义了4中事务隔离级别:
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 允许 | 允许 | 允许 |
读提交 | 不允许 | 允许 | 允许 |
可重复读 | 不允许 | 不允许 | 允许 |
序列化 | 不允许 | 不允许 | 不允许 |
用过Linux的pthread多线程库的童鞋应该都知道读写锁。读写锁有下述逻辑:
- 多次上读锁,不会有线程被阻塞
- 先上读锁后上谢谢,写线程阻塞
- 先上写锁,后上读锁,读线程阻塞
- 多次上写锁,写线程阻塞
SQL锁用的锁跟读写锁机器类似,也分为读锁和写锁。为了实现4中事务的隔离级别,还需要实现锁的粒度的划分。所谓粒度,即锁的作用范围,比如:表级别的作用范围,或者元组级别的作用范围。
现在可以简单的说明四种隔离级别所对应的加锁机制。
- 序列化:操作前对表上锁(粒度到表),读写操作都加上写锁。因为任何操作都需要获取写锁,所以一旦写锁被其他事务占用,该事务将被阻塞,因此不会出现不可重复读。由于粒度到表,因此不会出现幻读。
- 可重复读:读上读锁,写上写锁,粒度到行。读操作上读锁,实现了读取操作的并发访问;写操作上写锁,先读后写,写操作被阻塞,因此不会出现不可重复读取;先写后读,读操作将被阻塞,因此也不会造成不可重复读取;最后两个写操作自然也是阻塞。又因为锁的粒度到行,对于表级别的操作,由于不同的行被上了不同的锁,所以,会出现幻读。
- 读提交:与可重复读类似,只不过上锁的时期是读操作瞬间,读操作结束后就解锁。能够实现了读写的并发,允许读取的数据被修改,会出现两次读操作不一致的情况。
- 读未提交:读不加锁,实现读读、读写的并发。注意,两个写操作是不能并发的。
一张表总结:
事务隔离级别 | 读读并发 | 读写并发 | 写读并发 | 写写并发 |
---|---|---|---|---|
读未提交 | 允许 | 允许 | 允许 | 不允许 |
读提交 | 允许 | 允许 | 不允许 | 不允许 |
可重复读 | 允许 | 不允许 | 不允许 | 不允许 |
序列化 | 不允许 | 不允许 | 不允许 | 不允许 |
最后,查询数据库隔离级别的语句:select @@global.tx_isolation
。MySQL默认的隔离级别是可重复读。