MySQL数据库，详解事务处理(二)

savepoint关键字

在事务中我们执⾏了⼀⼤批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将⼀⼤批操作分为⼏个部分，然后指定回滚某个部分。可以使⽤savepoin来实

现，效果如下：

先清除test1表数据：

mysql> delete from test1;

Query OK, 3 rows affected (0.00 sec)

mysql> select * from test1;

Empty set (0.00 sec)

演⽰savepoint效果，认真看：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);

Query OK, 1 row affected (0.00 sec)

mysql> savepoint part1;//设置⼀个保存点

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);

Query OK, 1 row affected (0.00 sec)

mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作

回滚

Query OK, 0 rows affected (0.00 sec)

mysql> commit;//提交事务

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+------+

| a |

+------+| 1 |

+------+

1 row in set (0.00 sec)

从上⾯可以看出，执⾏了2次插⼊操作，最后只插⼊了1条数据。

savepoint需要结合rollback to sp1⼀起使⽤，可以将保存点sp1到

rollback to之间的操作回滚掉。

只读事务

表⽰在事务中执⾏的是⼀些只读操作，如查询，但是不会做insert、update、delete操

作，数据库内部对只读事务可能会有⼀些性能上的优化。

⽤法如下：

start transaction read only;

⽰例：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction read only;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+------+

| a |

+------+

| 1 |

| 1 |

+------+

2 rows in set (0.00 sec)

mysql> delete from test1;

ERROR 1792 (25006): Cannot execute statement in a READ ONLY

transaction.

mysql> commit;

Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;

+------+

| a |

+------+

| 1 |

| 1 |

+------+

2 rows in set (0.00 sec)

只读事务中执⾏delete会报错。

事务中的⼀些问题

这些问题主要是基于数据在多个事务中的可见性来说的。

脏读

⼀个事务在执⾏的过程中读取到了其他事务还没有提交的数据。这个还是⽐较好理解

的。

读已提交

从字⾯上我们就可以理解，即⼀个事务操作过程中可以读取到其他事务已经提交的数据。

事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据（相当于当

前读）

可重复读

⼀个事务操作中对于⼀个读取操作不管多少次，读取到的结果都是⼀样的。

幻读

脏读、不可重复读、可重复读、幻读，其中最难理解的是幻读

以mysql为例：

幻读在可重复读的模式下才会出现，其他隔离级别中不会出现幻读现象例⼦：

可重复读模式下，⽐如有个⽤户表，⼿机号码为主键，有两个事物进⾏如下操作

事务A操作如下： 1、打开事务 2、查询号码为X的记录，不存在 3、插⼊号码为X的数

据，插⼊报错（为什么会报错，先向下看） 4、查询号码为X的记录，发现还是不存在

（由于是可重复读，所以读取记录X还是不存在的）

事物B操作：在事务A第2步操作时插⼊了⼀条X的记录，所以会导致A中第3步插⼊报错

（违反了唯⼀约束）

上⾯操作对A来说就像发⽣了幻觉⼀样，明明查询X（A中第⼆步、第四步）不存在，但却

⽆法插⼊成功

幻读可以这么理解：事务中后⾯的操作（插⼊号码X）需要上⾯的读取操作（查询号码X

的记录）提供⽀持，但读取操作却不能⽀持下⾯的操作时产⽣的错误，就像发⽣了幻觉⼀

样。

如果还是理解不了的，继续向下看，后⾯后详细的演⽰。

事务的隔离级别

当多个事务同时进⾏的时候，如何确保当前事务中数据的正确性，⽐如A、B两个事物同

时进⾏的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的

隔离级别来保证，不同的隔离级别中所产⽣的效果是不⼀样的。

事务隔离级别主要是解决了上⾯多个事务之间数据可见性及数据正确性的问题。

隔离级别分为4种：

1. 读未提交：READ-UNCOMMITTED

2. 读已提交：READ-COMMITTED

3. 可重复读：REPEATABLE-READ

4. 串⾏：SERIALIZABLE上⾯4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别

mysql> show variables like 'transaction_isolation';

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

| Variable_name | Value |

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

| transaction_isolation | READ-COMMITTED |

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

1 row in set, 1 warning (0.00 sec)

隔离级别的设置

分2步骤，修改⽂件、重启mysql，如下：

修改mysql中的my.init⽂件，我们将隔离级别设置为：READ-UNCOMMITTED，如下：

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLEREAD可重复读,SERIALIZABLE串⾏

transaction-isolation=READ-UNCOMMITTED

以管理员⾝份打开cmd窗⼜，重启mysql，如下：

C:\Windows\system32>net stop mysql

mysql 服务正在停⽌..

mysql 服务已成功停⽌。

C:\Windows\system32>net start mysql

mysql 服务正在启动 .

mysql 服务已经启动成功。A窗⼜如下：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

Empty set (0.00 sec)

mysql> select * from test1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B窗⼜如下：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);

Query OK, 1 row affected (0.00 sec)

T

7

c

o

m

m

it

;

T

8

c

o

m

m

it

;mysql> select * from test1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

看⼀下:

T2-A：⽆数据，T6-A：有数据，T6时刻B还未提交，此时A已经看到了B插⼊的数据，说

明出现了脏读。

T2-A：⽆数据，T6-A：有数据，查询到的结果不⼀样，说明不可重复读。

结论：读未提交情况下，可以读取到其他事务还未提交的数据，多次读取结果不⼀样，出

现了脏读、不可重复读

READ-COMMITTED：读已提交

将隔离级别置为READ-COMMITTED

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLEREAD可重复读,SERIALIZABLE串⾏

transaction-isolation=READ-COMMITTED

重启mysql：

C:\Windows\system32>net stop mysql

mysql 服务正在停⽌..

mysql 服务已成功停⽌。

C:\Windows\system32>net start mysql

mysql 服务正在启动 .

mysql 服务已经启动成功。A窗⼜如下：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

Empty set (0.00 sec)

mysql> select * from test1;

T

7

c

o

m

m

it

;

T

8

s

e

l

e

c

t

*

f

r

o

m

t

e

s

t

1

;

T

9

c

o

m

m

it

;Empty set (0.00 sec)

mysql> select * from test1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B窗⼜如下：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

看⼀下:

T5-B：有数据，T6-A窗⼜：⽆数据，A看不到B的数据，说明没有脏读。

T6-A窗⼜：⽆数据，T8-A：看到了B插⼊的数据，此时B已经提交了，A看到了B已提交的

数据，说明可以读取到已提交的数据。

T2-A、T6-A：⽆数据，T8-A：有数据，多次读取结果不⼀样，说明不可重复读。结论：读已提交情况下，⽆法读取到其他事务还未提交的数据，可以读取到其他事务已经

提交的数据，多次读取结果不⼀样，未出现脏读，出现了读已提交、不可重复读。