[MySQL] 事务隔离级别
DataBase Isolation Levels
隔离级别以及对应级别存在问题
Isolation Level/Problem | P0 Dirty Write |
P1 Dirty Read |
P4C Cursor Lose Update |
P4 Lose Update |
p2 Fuzzy Read |
P3 Phantom |
A5A Read Skew |
A5B Write Skew |
---|---|---|---|---|---|---|---|---|
Read Uncommitted | Not Possible | Possible | Possible | Possible | Possible | Possible | Possible | Possible |
Read Committed | Not Possible | Not Possible | Possible | Possible | Possible | Possible | Possible | Possible |
Cursor Stabillity | Not Possible | Not Possible | Not Possible | Possible | Possible | Possible | Possible | Possible |
Repeatable Read | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible | Possible | Not Possible | Not Possible |
Snapshot | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible | Possible | Not Possible | Possible |
Serializable | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible | Not Possible |
隔离级别由弱到强
Degree 0 (everything goes) << Read Uncommitted << Read Committed << Cursor Stability << Repeatable Read << Serializable
这里还有其他2种隔离级别: <font color="green", size="5">Oracle Consistency Read和Snapshot Isolation, Read Committed << Oracle Consistent Read,
Read Committed << Snapshot Isolation << Serializable,
这2种隔离级别都是多版本并发控制机制.
Snapshot Isolation和Repeatable Read并不是对等的, 前者可以防止写偏序(Write Skew), 而Repeatable Read可以防止Snapshot Isolation情况下的一些不能防止的幻读(Phantoms)问题.
隔离级别总结
Love truth but pardon errors