数据库隔离级 ( Isolation level )

最新推荐文章于 2022-01-12 14:03:23 发布

转载最新推荐文章于 2022-01-12 14:03:23 发布 · 62 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/istrong/archive/2012/09/13/2684136.html

本文详细解释了数据库系统中四种不同的事务隔离级别：Serializable、Repeatable Reads、Read Committed 和 Read Uncommitted，包括每种级别的特点及可能出现的问题，如幻读、重复读取不一致和脏读等。

专业术语：
phantom reads[幻读] - Repeatable Reads Level:事务A的两次范围查询之间，事物B在范围内插入了数据，导致事务A的两次查询结果不一致(因为范围锁range-locks)

non-repeatable reads[重复读取不一致] - Read Committed Level:事务A对某一行进行两次查询，在这两次查询之间，事物B对这一行的数据进行了修改，导致事务A的两次查询结果不一致

dirty reads[脏读] - Read Uncommitted Level:事务A对某一行进行两次查询,在这两次查询之间，事物B对这一行的数据进行了修改,但是未提交而是回滚，导致事务A的第二次查询读取了错误的数据

隔离级：

Serializable [串行]

This is the highest isolation level.

With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon (see below).

When using non-lock based concurrency control, no locks are acquired; however, if the system detects a write collision among several concurrent transactions, only one of them is allowed to commit. Seesnapshot isolation for more details on this topic.

Repeatable reads [重复读]

In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so the phantom reads phenomenon can occur (see below).

Read committed [读提交]

In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as theSELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level, as discussed below). As in the previous level, range-locks are not managed.

Read uncommitted [读未提交]

This is the lowest isolation level. In this level, dirty reads are allowed (see below), so one transaction may see not-yet-committed changes made by other transactions.

MySQL参数配置：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
    REPEATABLE READ
  | READ COMMITTED
  | READ UNCOMMITTED
  | SERIALIZABLE
}

感觉其实看懂了还是非常好理解的，只是不太好描述～

参考文献：

[wiki]:http://en.wikipedia.org/wiki/Isolation_(database_systems)

[MySQL]:http://dev.mysql.com/doc/refman/5.5/en/dynindex-isolevel.html

转载于:https://www.cnblogs.com/istrong/archive/2012/09/13/2684136.html