[toc]
mysql事务隔离级别
mysql事务
一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
- **原子性:**一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
- **一致性:**在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- **隔离性:**数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
- **持久性:**事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
mysql 4种事务隔离级别
级别 | symbol | 对应值 | 含义 | 存在问题 |
---|---|---|---|---|
读未提交 | READ-UNCOMMITTED | 0 | 一个事务可以读到另一个事务未提交的数据 | 存在脏读、不可重复读、幻读的问题 |
读已提交 | READ-COMMITTED | 1 | 一个事务能读到另一个事务已提交的数据 | 解决脏读的问题,存在不可重复读、幻读的问题 |
可重复读 | REPEATABLE-READ | 2 | 同一事务的多个实例在并发读取数据时,会看到同样的数据行 | mysql默认级别,解决脏读、不可重复读的问题,存在幻读的问题。使用 MMVC机制 实现可重复读 |
串行化 | SERIALIZABLE | 3 | 强制事务排序,使之不可能相互冲突 | 解决脏读、不可重复读、幻读,可保证事务安全,但完全串行执行,性能最低 |
关于mysql事务的一些知识点
- 最开始的时候,5.1.5之前的版本binlog format只支持stament,并没有row模式,在RC一些场景下会造成主从数据不一致,所以选择RR才能最大限度保证主从数据一致性
- 之后的mysql直接使用RC+row是完全没有问题的
mysql5.7表的默认存储引擎是InnoDB
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(3) NOT NULL,
`name` char(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
mysql5.7 InnoDB存储引擎默认的事务隔离级别,全局和当前会话都是REPEATABLE-READ(可重复读)
RR的并发性没有RC好
#mysql5.7.22
mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)
设置事务隔离级别
#配置文件修改,可选参数 READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、 SERIALIZABLE
[mysqld]
transaction-isolation = REPEATABLE-READ
#设置全局
SET @@global.tx_isolation = 0;
SET @@global.tx_isolation = 'READ-UNCOMMITTED';
SET @@global.tx_isolation = 1;
SET @@global.tx_isolation = 'READ-COMMITTED';
SET @@global.tx_isolation = 2;
SET @@global.tx_isolation = 'REPEATABLE-READ';
SET @@global.tx_isolation = 3;
SET @@global.tx_isolation = 'SERIALIZABLE';
#设置会话
SET @@session.tx_isolation = 0;
SET @@session.tx_isolation = 'READ-UNCOMMITTED';
SET @@session.tx_isolation = 1;
SET @@session.tx_isolation = 'READ-COMMITTED';
SET @@session.tx_isolation = 2;
SET @@session.tx_isolation = 'REPEATABLE-READ';
SET @@session.tx_isolation = 3;
SET @@session.tx_isolation = 'SERIALIZABLE';
mysql查看/设置自动提交
//查看自动提交是否开启,默认开启
#方法一
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.07 sec)
#方法二
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.09 sec)
//关闭自动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.11 sec)
以下所有示例都基于mysql5.7.22
先创建一张示例表t1
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22 |
+-----------+
mysql> create table t1(id int(3) primary key,name char(30));
mysql> insert into t1 values(1,'xiaoming');
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
+----+----------+
1 读未提交 Read Uncommitted
含义:一个事务可以读到另一个事务未提交的数据!
示例:
1.mysql默认的事务隔离级别是RR 可用值2来设置
mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
2.手动修改当前会话为RU
mysql> SET @@session.tx_isolation = 0;
Query OK, 0 rows affected, 1 warning (0.12 sec)
mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.04 sec)
3.关闭自动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.08 sec)
如图所示,一个事务检索的数据被另一个未提交的事务给修改了。
官网对脏读定义的地址 其内容为
dirty read An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed.
翻译过来就是
检索操作出来的数据是不可靠的,是可以被另一个未提交的事务修改的!
你会发现,我们的演示结果和官网对脏读的定义一致。根据我们最开始的推理,如果存在脏读,那么不可重复读和幻读一定是存在的。
2 读提交 Read Committed
含义:一个事务能读到另一个事务已提交的数据
示例:
1.mysql默认的事务隔离级别是RR 可用值2来设置
mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
2.手动修改当前会话为RC
mysql> SET @@session.tx_isolation = 1;
Query OK, 0 rows affected, 1 warning (0.12 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.04 sec)
3.关闭自动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.08 sec)
如图所示,一个事务检索的数据只能被另一个已提交的事务修改。
其内容为
**non-repeatable read The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime). **
翻译过来就是
一个查询语句检索数据,随后又有一个查询语句在同一个事务中检索数据,两个数据应该是一样的,但是实际情况返回了不同的结果。!
ps
:作者注,这里的不同结果,指的是在行不变的情况下(专业点说,主键索引没变),主键索引指向的磁盘上的数据内容变了。如果主键索引变了,比如新增一条数据或者删除一条数据,就不是不可重复读。
显然,我们这个现象符合不可重复读的定义。下面,大家做一个思考:
- 这个不可重复读的定义,放到脏读的现象里是不是也可以说的通。显然脏读的现象,也就是**读未提交(READ_UNCOMMITTED)**的那个例子,是不是也符合在同一个事务中返回了不同结果!
- 但是反过来就不一定通了,一个事务A中查询两次的结果在被另一个事务B改变的情况下,如果事务B未提交就改变了事务A的结果,就属于脏读,也属于不可重复读。如果该事务B提交了才改变事务A的结果,就不属于脏读,但属于不可重复读。
3 可重复读 Repeatable Read
phantom A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.
翻译过来就是
在一次查询的结果集里出现了某一行数据,但是该数据并未出现在更早的查询结果集里。例如,在一次事务里进行了两次查询,同时另一个事务插入某一行或更新某一行数据后(该数据符合查询语句里where后的条件),并提交了!
幻读(Phantom Read)说明
原因:事务A根据相同条件第二次查询,虽然查询不到事务B提交的新增数据,但是会影响事务A之后的一些操作,比如:事务A进行了一次select * from t1表查询,查询出id为1的数据,同时事务B进行了一次insert into t1 values(2,'xx'),也就是此时表中有了id为2的数据,但是在事务A中再次进行查询的时候,根本就查不到id为2的数据,但是当事务A进行insert into t1 values(2,'xx'),也想插入id为2的数据的时候,发现报错了,但是事务A怎么查也查不到有id为2的数据,这就让事务A的使用者出现了幻觉,what happend!。如果不想出现幻读问题,那么自己在查询语句中手动加锁 for update,如果查询的是id为2的数据,即便是现在没有id为2的数据,其他事务也无法对id为2的索引位置进行数据的处理。
含义:同一事务的多个实例在并发读取数据时,会看到同样的数据行
示例:
1.mysql默认的事务隔离级别是RR 可用值2来设置
mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
显然,该现象是符合幻读的定义的。即同一事务的两次相同查询出现不同行。
原文内容如下
By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 14.7.4, “Phantom Rows”).
翻译过来就是
InnoDB默认用了REPEATABLE READ。在这种情况下,使用next-key locks解决幻读问题!
以下两步均在session2中执行,正确结果是id为1的数据name已经改为abc
不加next-key locks
是快照读,根本不能解决幻读问题
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 2 | hehe |
+----+----------+
2 rows in set (0.09 sec)
加上next-key locks
就能解决幻读问题
mysql> select * from t1 lock in share mode;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | hehe |
+----+------+
2 rows in set (0.05 sec)