Isolation Level
来源:互联网 发布:游泳 肌肉 知乎 编辑:程序博客网 时间:2024/05/19 03:18
Isolation Level
SQL-99 标准,定义了isolation levels的4个层级:
- Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
- Read committed (SQLServer default level)
- Repeatable read (MySQL default level)
- Serializable (the highest level, where transactions are completely isolated from one another)
下表显示了“并发边界效应(concurrency side effects)”在不同隔离级别的允许情况:
READ
Read uncommitted
Yes
Yes
Yes
Read committed
No
Yes
Yes
Repeatable read
No
No
Yes
Snapshot(SQLServer独有)
No
No
No
Serializable
No
No
No
完整的文字论述,参见:http://technet.microsoft.com/en-us/library/ms173763(v=sql.90).aspx
===============================================================================
以下事例解释,转自:http://www.tangxiaojuan.info/4080/isolation-level/
Isolation就是ACID(Atomicity, Consistency, Isolation, Durability )中的I,isolation一般分为四个level: read uncommited, read commited, repeatable read, serializable read。
假设我们有一个user表:
idnameage1John182Ann30Read Uncommited
T1T2start transaction;
update user set age = 20 where id = 1;
rollback;
select * from user where id = 1;
T1先开始事务,更新John的年龄为20,然后T2查看John的年龄为20,而这时T1回滚事务,John的年龄依然为18,所以T2查看到的年龄有可能是并没有提交的数据。
为了避免这样的问题,我们有了read committed。
Read Committed
T1T2start transaction;
update user set age = 20 where id = 1;
commit;
start transaction;
select * from user where id = 1;
同上T1先开始事务,更新John的年龄为20,然后T2想查看John的年龄,但发现T1没有提交,所以直到等到T1回滚或者提交之后方能查看。T1提交或者回滚后,T2查看到John的年龄与数据库的数据一致。read committed同read uncommitted不同的就是加了write lock,只有当write的事务完成后(提交或者回滚),才释放排他锁(exclusive lock),另外的事务才能进行select或update。
但问题又来了,我们考虑以下的情况:
T1T2start transaction;
update user set age = 20 where id = 1;
commit;
start transaction;
select * from user where id =1;
select * from user where id = 1;
commit;
如果在T1更新数据之前和之后,T2分别查看John的年龄,这时发现两次查看到的数据不同,分别为18和20。
为了避免这个问题,我们有了repeatable read。
Repeatable Read
T1T2start transaction;
update user set age = 20 where id = 1;
commit;
start transaction;
select * from user where id =1;
select * from user where id = 1;
commit;
repeatable read同read committed不同之处就在于,read committed在T2读完某一行数据之后就释放排他锁,读完这行数据之后,T1就可以更新这行数据; 而repeatable read是在T2完成提交后才释放排他锁,这期间T1不能都不能进行其他操作。所以T1两次查询的数据是一样的,等T2提交之后T1就能更新了。
那么看上去repeatable read已经万无一失了,可是它只能应付”where id = xx”的情况,并不能对付”where id between xx and yy”的情况。所以我们有了serializable read
Serializable Read
T1T2start transaction;
insert into user values (3, ‘Jim’, 30);
commit;
start transaction;
select * from user where age between 20 and 40;
select * from user where age between 20 and 40;
commit;
如果是repeatable read level, T2两次的查询不一致,因为T1这时插入了一条数据。而serializable read就加入了range lock, 这样就使得T2的两次查询一致了。
我们来总结下四个不同level的区别:
write lockread lockrange lockread uncommittedXXXread committedYXXrepeatable readYYXserializable readYYY另外MySQL默认支持的isolation level都是repeatable read。可以通过
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
来查询global和当前session的isolation level
- Isolation Level
- isolation level
- Isolation Level
- Isolation level
- SET TRANSACTION ISOLATION LEVEL
- innodb_locks_unsafe_for_binlog && transaction isolation level
- Transaction, Lock, Isolation Level
- SET TRANSACTION ISOLATION LEVEL
- ACID ISOLATION LEVEL
- oracle isolation level
- Transaction Isolation Level
- Transaction Isolation Level
- 隔离级别(Isolation Level)
- 数据库的事物处理ISOLATION LEVEL
- [转]SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
- informix 进程隔离级(isolation level)
- SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
- SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
- GDI+
- 通过system()看信号处理机制(多位牛博的阐述日志)
- 字符串专项:LA 5913
- LeetCode_Unique Paths
- linux驱动学习--第二十二天:第十二章:Linux 字符设备驱动综合实例(三)DSP HPI 的设备驱动
- Isolation Level
- 近几年,产品经理这个岗位越来越火(2012-2013)
- Android 使用Jsoup解析HTML页面
- image classification 资源摘录
- Oracle rowid and urowid
- 数学专项number_theory:UVa 756
- gcc 编译多个源文件
- Xcode4.6 开发 metaio 增强现实 项目(一)--新建项目
- 如何更好的阅读代码(1)