说起关系型数据库,总是绕不开事务(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,发生了幻读。

 

 

 

11 对 “mysql事务简述”的想法;

  1. Alacakaranlik Safak Vakti izle, Alacakaranlik Safak Vakti full izle, Alacakaranlik Safak Vakti t�rk�e dublaj izle, Alacakaranlik Safak Vakti hd izle, The Twilight Saga: Breaking Dawn – Part 1 izle, Sonunda iki asik bela ve edward evlenmek’dedir. Edward ile Jacop arasinda bir se�im bir se�im yapmak zorunda kalan bela tercihini tutkuyla asik oldugu edward’dan yana kullanmaktadir. Film Bela ve Edward’in �evresinde d�nerken esrarengiz olaylar olur ikili tatildeyken �ocuklarinin olacaginin farkina varir bu olay akillara sigmayacak bir sey vampir adam ve insan kani tasiyan kisin �ocuklari olmasi bu konuyu aile ile konusmak isteyen ikili bakalim ne diyecekler nasil bir durum ortaya �ikacaktir. Katerina Bartram Isadore

  2. Alacakaranlik Safak Vakti izle, Alacakaranlik Safak Vakti full izle, Alacakaranlik Safak Vakti t�rk�e dublaj izle, Alacakaranlik Safak Vakti hd izle, The Twilight Saga: Breaking Dawn – Part 1 izle, Sonunda iki asik bela ve edward evlenmek’dedir. Edward ile Jacop arasinda bir se�im bir se�im yapmak zorunda kalan bela tercihini tutkuyla asik oldugu edward’dan yana kullanmaktadir. Film Bela ve Edward’in �evresinde d�nerken esrarengiz olaylar olur ikili tatildeyken �ocuklarinin olacaginin farkina varir bu olay akillara sigmayacak bir sey vampir adam ve insan kani tasiyan kisin �ocuklari olmasi bu konuyu aile ile konusmak isteyen ikili bakalim ne diyecekler nasil bir durum ortaya �ikacaktir. Renelle Maurice Columbyne

  3. Alacakaranlik Safak Vakti izle, Alacakaranlik Safak Vakti full izle, Alacakaranlik Safak Vakti t�rk�e dublaj izle, Alacakaranlik Safak Vakti hd izle, The Twilight Saga: Breaking Dawn – Part 1 izle, Sonunda iki asik bela ve edward evlenmek’dedir. Edward ile Jacop arasinda bir se�im bir se�im yapmak zorunda kalan bela tercihini tutkuyla asik oldugu edward’dan yana kullanmaktadir. Film Bela ve Edward’in �evresinde d�nerken esrarengiz olaylar olur ikili tatildeyken �ocuklarinin olacaginin farkina varir bu olay akillara sigmayacak bir sey vampir adam ve insan kani tasiyan kisin �ocuklari olmasi bu konuyu aile ile konusmak isteyen ikili bakalim ne diyecekler nasil bir durum ortaya �ikacaktir. Norah Adam Stevenson

  4. Alacakaranlik Safak Vakti izle, Alacakaranlik Safak Vakti full izle, Alacakaranlik Safak Vakti t�rk�e dublaj izle, Alacakaranlik Safak Vakti hd izle, The Twilight Saga: Breaking Dawn – Part 1 izle, Sonunda iki asik bela ve edward evlenmek’dedir. Edward ile Jacop arasinda bir se�im bir se�im yapmak zorunda kalan bela tercihini tutkuyla asik oldugu edward’dan yana kullanmaktadir. Film Bela ve Edward’in �evresinde d�nerken esrarengiz olaylar olur ikili tatildeyken �ocuklarinin olacaginin farkina varir bu olay akillara sigmayacak bir sey vampir adam ve insan kani tasiyan kisin �ocuklari olmasi bu konuyu aile ile konusmak isteyen ikili bakalim ne diyecekler nasil bir durum ortaya �ikacaktir. Vonny Xever Yankee

  5. Alacakaranlik Safak Vakti izle, Alacakaranlik Safak Vakti full izle, Alacakaranlik Safak Vakti t�rk�e dublaj izle, Alacakaranlik Safak Vakti hd izle, The Twilight Saga: Breaking Dawn – Part 1 izle, Sonunda iki asik bela ve edward evlenmek’dedir. Edward ile Jacop arasinda bir se�im bir se�im yapmak zorunda kalan bela tercihini tutkuyla asik oldugu edward’dan yana kullanmaktadir. Film Bela ve Edward’in �evresinde d�nerken esrarengiz olaylar olur ikili tatildeyken �ocuklarinin olacaginin farkina varir bu olay akillara sigmayacak bir sey vampir adam ve insan kani tasiyan kisin �ocuklari olmasi bu konuyu aile ile konusmak isteyen ikili bakalim ne diyecekler nasil bir durum ortaya �ikacaktir. Thia Edvard Kevina

  6. Each Monday during the NBA season, All Tar Heels will review the prior week for each Tar Heel in the NBA and preview the week ahead. This week, we are looking at games from April 12-18 and looking ahead to games from April 19-25.

  7. Each Monday during the NBA season, All Tar Heels will review the prior week for each Tar Heel in the NBA and preview the week ahead. This week, we are looking at games from April 12-18 and looking ahead to games from April 19-25.

发表评论

邮箱地址不会被公开。