说起关系型数据库,总是绕不开事务(transaction)一说,本文旨在简单叙述mysql的事务概念,并通过实际的sql数据来解释事务隔离级别。下面所有测试引擎均使用innodb。

 

  • 什么是事务?

事务(transaction)是对数据库库操作的一个序列,当一个事务被提交给数据库后,应该要保证事务里的所有执行都成功,或者都失败。

 

  • 事务的基本特性

一般我们是的事务的基本特性,就是指事务的ACID,

1)、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做。

 

2)、一致性(Consistency): 指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。

 

3)、隔离性(Isolation):数据库库不同的事务之间彼此没有任何干扰。

 

4)、持久性(Durability):事务提交后,事务对数据库的所有更新将被持久化,不能回滚。

 

在具体解释事务前我们要先了解下数据库缓冲池的概念还有MVCC。

 

数据库缓冲池就是内存中的一块区域。通过内存的高速来弥补硬盘的速度对数据库的应影响。数据库读取的时候,会将读到的数据存放在缓冲池中。

mysql的基本数据查询流程如下在发起sql请求的时候,首先会在缓存中去查新缓存结果,这里的缓存只要查询有一个字节不同,就不会匹配缓存结果。

如果没有命中缓存,就会走如下流程。解析sql发起查询请求,查询到结果返回到缓存中去,并返回给客户端。

 

mysql事务简述插图

MVCC(Multi-Version Concurrency Control)多版本的并发控制协议,mysql的innodb引擎实现就是基于mvcc的。与之相对应的是LBCC(Lock-Based Concurrent Control)基于锁的并发控制。mvcc的主要优点在于读不加锁,读写不冲突。

在一个mvcc控制的系统中,读可以分为,快照读和当前读。

 

快照读:select * from wcs  where xxxx; 这类以的简单查询,不加锁

当前读:1>增删改语句以及

2>select * from xxx where xxx for update

3>select * from xxx where xxx lock in share mode;

当前读会读取最新的记录版本。并且会加锁,保证在事务中读取后,不允许被其他事务修改。

分为S锁(共享锁)(上述示例:3)和X锁(排他锁)(上述示例:1,2),

这里不做mvcc的具体介绍,只为了引入快照读和当前读,以便后面的事务并发控制。

 

  • 事务并发的问题
  • 、脏读(dirty read),指的是一个事务读取了另一个事务修改未提交的数据
  • 、不可重复读(unrepeatable read),和脏读有一定的关联,一个事务读取了一个数据,再次读取的时候读到了被另一个事务修改过的数据。
  • 、幻读(phantom problem),一个事务读取到了另一个事务提交的新增或删除的结果。这和不可重复读有点类似。侧重点在新增删除

脏读很容易了解,在事务隔离级别最低的情况下会发生这个情况,

幻读和不可重复读比较奇怪。

假设有一个表User,有age,name,sex字段

事务A,查询sex=’男’的人,查出10个人

事务B,新增一个sex=’男’的人。并提交事务

事务A,继续操作这个时候,修改sex=’男’的人的age=15岁,发现修改结果为11个人。产生了幻读。

 

mysql事务隔离级别:

 

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)

Mysql innodb默认事务隔离级别是repeatable-read

顺带一提,oracle的默认事务隔离级别是read committed.

 

为什么serializable的事务隔离级别这么高,mysql的默认不是它呢。因为serializable是事务隔离级别最高的一种,因为它有mvcc控制调整到LBCC控制,对于他来说都是当前读,并对数据加锁。在该隔离级别下,读写冲突,并发性能下降。

 

  • 具体示例

(1).查看当前会话mysql服务的事务隔离级别:

mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

 

 

(2).查看当前会话和全局事务隔离级别

mysql> select @@global.tx_isolation,@@tx_isolation;

+-----------------------+------------------+

| @@global.tx_isolation | @@tx_isolation   |

+-----------------------+------------------+

| REPEATABLE-READ       | READ-UNCOMMITTED |

+-----------------------+------------------+

1 row in set (0.00 sec)

 

(3).修改当前会话mysql事务隔离级别

mysql> set session transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.02 sec)




mysql> select @@tx_isolation;

+------------------+

| @@tx_isolation   |

+------------------+

| READ-UNCOMMITTED |

+------------------+

1 row in set (0.00 sec)

 

 

(4).修改全局事务隔离级别

 

mysql>  set global transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)




mysql> select @@global.tx_isolation,@@tx_isolation;

+-----------------------+------------------+

| @@global.tx_isolation | @@tx_isolation   |

+-----------------------+------------------+

| READ-UNCOMMITTED      | READ-UNCOMMITTED |

+-----------------------+------------------+

1 row in set (0.00 sec)

 

 

(5).事务并发问题示例

  • 脏读

首先我们打开两个链接,分别修改事务隔离级别为read-uncommitted,

mysql> set session transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)




mysql> select @@tx_isolation;

+------------------+

| @@tx_isolation   |

+------------------+

| READ-UNCOMMITTED |

+------------------+

1 row in set (0.00 sec)

 

事务一,查询一张表,




mysql> begin;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


mysql> update wcs set status = 2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 2      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)

 

这个时候这个事务并没有提交。

再开启另外一个事务,查询这张表

mysql> begin;

Query OK, 0 rows affected (0.00 sec)


mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 2      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)

查询到了第一个事务未提交的数据,属于脏读

第一个事务,rollback后,在查询

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)
  • 不可重复度

为了显示和脏读的区别,这里我们不在使用read uncommitted事务隔离级别,用read-committed隔离级别

打开两个连接分别修改事务隔离级别:

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)


mysql> select @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| READ-COMMITTED |

+----------------+

1 row in set (0.00 sec)

 

事务一,开启事务后查询



mysql> begin;

Query OK, 0 rows affected (0.00 sec)

###步骤一:

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 2      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


###步骤三

mysql> update wcs set status = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0


###步骤五

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

事务二,快开启事务后:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)


###步骤二

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 2      |

+---------+-----------+-------+--------+

1 row in set (0.01 sec)


###步骤四

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 2      |

+---------+-----------+-------+--------+

1 row in set (0.01 sec)


###步骤六

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


mysql> 

1>事务一,先查询表,得到一个结果status为1

2>事务二,查询表,也得到了status为1

3>事务一,更新表,将status修改为2

4>事务二,查询表,得到表为1(该事务隔离级别,未发生脏读)

5>事务一,提交事务,

6>事务二,查询表,得到status为2(未重复读)

 

  • 幻读

先打开两个链接,分别修改事务隔离级别为repeatable-read

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)


mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

 

事务一

mysql> begin;

Query OK, 0 rows affected (0.00 sec)


###步骤一

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)

###步骤三

mysql> insert into wcs values('WCS2','0.0.0.1',30000,1);

Query OK, 1 row affected (0.01 sec)


###步骤五

mysql> commit;

Query OK, 0 rows affected (0.01 sec)


mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

| WCS2    | 0.0.0.1   | 30000 | 1      |

+---------+-----------+-------+--------+

2 rows in set (0.00 sec)

事务二

mysql> begin;

Query OK, 0 rows affected (0.00 sec)


###步骤二

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


####步骤四

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


####步骤六

mysql> select * from wcs;

+---------+-----------+-------+--------+

| WCSNAME | IPADDRESS | PORT  | STATUS |

+---------+-----------+-------+--------+

| WCS1    | 127.0.0.1 | 20000 | 1      |

+---------+-----------+-------+--------+

1 row in set (0.00 sec)


###步骤七

mysql> update wcs set status = 2 where status = 1;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

1>事务一,查询表,结果为一行数据WCS1,status为1

2>事务二,查询表,结果为一行数据WCS1,status 为1

3>事务一,插入一行数据WCS2,status为1

4>事务二,查询数据,一行数据WCS1,status为1(该事务隔离级别,未发生脏读)

5>事务一,提交事务,查询结果两行数据,WCS1,WCS2。status为1

6>事务二,查询数据,一行数据WCS1,status为1(该事务隔离级别,重复读)

7>事务二,更新数据status为1的,查询为1,更新matched为2,changed为2,发生了幻读。

 

 

 

发表评论

电子邮件地址不会被公开。