| |||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||
| 同一个事务中 select c from a where a=1 for update 与 select c from a where a=1 取出的值不一样
| ||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||||||
| 一致性的非锁定读: 通过Undo段实现,不同的隔离级别读取的快照数据不同 多版本并发控制(Multi Version Concurrency Control, MVCC)
| |||||||||||||||||||||||||||||||||||
|
| 事务一 | 事务二 | 结果(更新后 读最新值) |
| start transaction; |
|
|
|
| start transaction; |
|
Q1 |
| select c from a where a=1; | 0 |
|
| update a set c=c+1 where a=1; |
|
Q1 |
| select c from a where a=1; | 1 |
|
| commit; |
|
P1 | select c from a where a=1; |
| 1 |
|
|
|
|
| 事务一 | 事务二 | 结果(更新后 读最新值) |
| start transaction; |
|
|
|
| start transaction; |
|
P1 | select c from a where a=1; |
| 0 |
|
| update a set c=c+1 where a=1; |
|
Q1 |
| select c from a where a=1; | 1 |
| update a set c=c+1 where a=1; |
|
|
|
| commit; |
|
P2 | select c from a where a=1; |
| 3 |
|
|
|
|
| |||||||||||||||||||||
|
| ||||||||||||||||||||
| 写一致性
| ||||||||||||||||||||
|
事务一 | 事务二 | next-key-lock |
begin; |
|
|
select c from a where a<3 lock in share mode; |
|
|
| begin; |
|
| Insert into a(a) select 1; | 锁等待 |
Commit; |
|
|
SELECT@@tx_isolation;
SET [SESSION |GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE}
SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ;