mysql 读已提交 实现 mysql如何实现读已提交

文章目录

• 什么是事务
• 创建事务

• 事务开启
• COMMIT——事务提交
• ROLLBACK——事务回滚

• ACID特性

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

• 隐式事务
• 显式事务
• 只读事务
• savepoint关键字
• 事务隔离性级别

• 读未提交：READ-UNCOMMITTED
• 读已提交：READ-COMMITTED
• 可重复读：REPEATABLE-READ
• 串行（hang）：SERIALIZABLE
• 关于隔离性级别的选择

---

● 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用
事务，可以对数据库中的数据更新处理的提交和取消进行管理。
● 事务处理的终止指令包括 COMMIT （提交处理）和 ROLLBACK （取消处
理）两种。
● DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性
（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母
结合起来，统称为ACID特性。
总结：事务指的是满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback进行回滚。

什么是事务

简单来讲，事务就是 需要在同一个处理单元中执行的一系列更新处理的集合 。对表进行更新需要使用 INSERT 、 DELETE 或者UPDATE 三种语句。但通常情况下，更新处理并不是执行一次就结束了，而是需要执行一系列连续的操作。这时，事务就能体现出它的价值了。

说到事务的例子，请大家思考一下下述情况。现在，请大家把自己想象为管理 Product （商品）表的程序员或者软件工程师。销售部门的领导对你提出了如下要求。“某某，经会议讨论，我们决定把运动 T 恤的销售单价下调 1000日元，同时把 T 恤衫的销售单价上浮 1000日元，麻烦你去更新一下数据库。”

此时的事务由如下两条更新处理所组成。

1：将运动T恤的销售单价降低1000日元

UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = ' 运动 T 恤 ';

2：将T恤衫的销售单价上浮1000日元

UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T 恤衫 ';

上述①和②的操作一定要作为同一个处理单元执行。如果只执行了①的操作而忘记了执行②的操作，或者反过来只执行了②的操作而忘记了执行①的操作，一定会受到领导的严厉批评。遇到这种需要在同一个处理单元中执行一系列更新操作的情况，一定要使用事务来进行处理。

---

创建事务

事务的语法

事务开始语句 ;
	DML 语句① ;
	DML 语句② ;
	DML 语句③ ;
	.
	.
	.
事务结束语句（ COMMIT 或者 ROLLBACK ）

使用事务开始语句和事务结束语句，将DML括起来就实现了一个事务的处理。实际上，在标准 SQL 中并没有定义事务的开始语句，而是由各个 DBMS 自己来定义的。反之，事务的结束需要用户明确地给出指示。

MySQL中的事务语法

START TRANSACTION;
	--  将运动 T 恤的销售单价降低 1000 日元
	UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = ' 运动 T 恤 ';
	--  将 T 恤衫的销售单价上浮 1000 日元
	UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T 恤衫 ';
COMMIT;

事务开启

大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指令。
像这样不使用指令而悄悄开始事务的情况下，应该如何区分各个事务呢？通常会有如下两种情况。
A： 每条SQL语句就是一个事务（自动提交模式）
B： 直到用户执行 COMMIT 或者 ROLLBACK 为止算作一个事务
通常DBMS都是选择其中一种模式，MySQL默认使用自动提交模式
该模式下的 DML 语句如下所示，每一条语句都括在事务的开始语句和结束语句之中。

START TRANSACTION;
	--  将运动 T 恤的销售单价降低 1000 日元
	UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = ' 运动 T 恤 ';
COMMIT;
START TRANSACTION;
	--  将 T 恤衫的销售单价上浮 1000 日元
	UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T 恤衫 ';
COMMIT;

COMMIT——事务提交

COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。

ROLLBACK——事务回滚

ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态。

START TRANSACTION;
	--  将运动 T 恤的销售单价降低 1000 日元
	UPDATE Product
	SET sale_price = sale_price - 1000
	WHERE product_name = ' 运动 T 恤 ';
	--  将 T 恤衫的销售单价上浮 1000 日元
	UPDATE Product
	SET sale_price = sale_price + 1000
	WHERE product_name = 'T 恤衫 ';
ROLLBACK;

上述代码中使用ROLLBACK，所以数据库中数据不会发生改变。

---

ACID特性

DBMS 的事务都遵循四种特性，将这四种特性的首字母结合起来统称为 ACID 特性。这是所有 DBMS 都必须遵守的规则。

原子性（Atomicity）

原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。
从事务中途停止的角度去考虑，就能比较容易理解原子性的重要性了。由于用户在一个事务中定义了两条 UPDATE 语句，DBMS 肯定不会只执行其中一条，否则就会对业务处理造成影响。

一致性（Consistency）

一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。 例如，设置了 NOT NULL 约束的列是不能更新为 NULL 的，试图插入违反主键约束的记录就会出错，无法执行。

对事务来说，这些不合法的 SQL 会被回滚。也就是说，这些 SQL 处理会被取消，不会执行。一致性也称为完整性。

隔离性（Isolation）

隔离性指的是保证不同事务之间互不干扰的特性。
该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。

持久性（Durability）

持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。 即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。
保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。 当发生故障时，可以通过日志恢复到故障发生前的状态。

---

隐式事务

事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量 autocommit 是否开启了自动提交：autocommit 为ON表示开启了自动提交。

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

---

显式事务

事务需要手动开启、提交或回滚，由开发者自己控制。

有2种方式手动控制事务：

方式1
//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;
方式2
start transaction;//开启事务
//执行事务操作
commit|rollback;

---

只读事务

表示在事务中执行的是一些只读操作，如查询，但是不会做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会报错。

---

savepoint关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用 savepoin 来实现，效果如下：

下面演示savepoint效果

mysql> select * from test1;
Empty set (0.00 sec)

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 之间的操作回滚掉。

---

事务隔离性级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，比如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)

上面使用show variables like ‘transaction_isolation’;查看隔离性级别。

隔离性级别设置

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

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=READ-UNCOMMITTED

2：以管理员身份打开cmd窗口，重启mysql，如下：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

各种隔离性级别会出现的问题

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	有	有	无
READ-COMMITTED	无	有	无
REPEATABLE-READ	无	无	有
SERIALIZABLE	无	无	无

脏读：一个事务在执行的过程中读取到了其他事务还没有提交的数据，脏读其实就是读取未提交。
读已提交：事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据。
可重复读：一个事务操作中对于一个读取操作不管多少次，读取到的结果都是一样的。
幻读：幻读在可重复读的模式下才会出现，其他隔离级别中不会出现。

幻读演示

幻读只会在 REPEATABLE-READ （可重复读）级别下出现，需要先把隔离级别改为可重复读。将隔离级别置为 REPEATABLE-READ

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=REPEATABLE-READ

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离性级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name     | Value      |
+-----------------------+----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

准备数据：

mysql> create table t_user(id int primary key,name varchar(16) unique key);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

按时间顺序在2个窗口中执行下面操作：

时间	窗口A	窗口B
T1	start transaction;
T2		start transaction;
T3		insert into t_user values (1,‘Lw中’);
T4		select * from t_user;
T5	select * from t_user where name=‘Lw中’;
T6		commit;
T7	insert into t_user values (2,‘Lw中’);
T8	select * from t_user where name=‘Lw中’;
T9	commit;

A窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user where name='Lw中';
Empty set (0.00 sec)

mysql> insert into t_user values (2,'Lw中');
ERROR 1062 (23000): Duplicate entry 'Lw中' for key 'name'

mysql> select * from t_user where name='Lw中';
Empty 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 t_user values (1,'Lw中');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+---------------+
| id | name      |
+----+---------------+
|  1 | Lw中   |
+----+---------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

A想插入数据Lw中 ，插入之前先查询了一下（T5时刻）该用户是否存在，发现不存在，然后在T7时刻执行插入，报错了，报数据已经存在了，因为T6时刻 B 已经插入了Lw中 。
然后A有点郁闷，刚才查的时候不存在的，然后A不相信自己的眼睛，又去查一次（T8时刻），发现Lw中还是不存在的。
此时A心里想：数据明明不存在啊，为什么无法插入呢？这不是懵逼了么，A觉得如同发生了幻觉一样。

读未提交：READ-UNCOMMITTED

读未提交情况下，可以读取到其他事务还未提交的数据，多次读取结果不一样，出现了脏读、不可重复读

读已提交：READ-COMMITTED

读已提交情况下，无法读取到其他事务还未提交的数据，可以读取到其他事务已经提交的数据，多次读取结果不一样，未出现脏读，出现了读已提交、不可重复读。

可重复读：REPEATABLE-READ

可重复读情况下，未出现脏读，未读取到其他事务已提交的数据，多次读取结果一致，即可重复读。

串行（hang）：SERIALIZABLE

SERIALIZABLE会让并发的事务串行执行（多个事务之间读写、写读、写写会产生互斥，效果就是串行执行，多个事务之间的读读不会产生互斥）。
读写互斥：事务A中先读取操作，事务B发起写入操作，事务A中的读取会导致事务B中的写入处于等待状态，直到A事务完成为止。
表示我开启一个事务，为了保证事务中不会出现上面说的问题（脏读、不可重复读、读已提交、幻读），那么我读取的时候，其他事务有修改数据的操作需要排队等待，等待我读取完成之后，他们才可以继续。
写读、写写也是互斥的，读写互斥类似。

串行演示

将隔离级别置为 SERIALIZABLE

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=SERIALIZABLE

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离性级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name     | Value     |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set, 1 warning (0.00 sec)

先清空test1表数据：

delete from test1;
select * from test1;

按时间顺序在2个窗口中执行下面操作：

时间	窗口A	窗口B
T1	start transaction;
T2	select * from test1;
T3		start transaction;
T4		insert into test1 values (1);
T5	commit;
T6		commit;

按时间顺序运行上面的命令，会发现T4-B这样会被阻塞，直到T5-A执行完毕。
上面这个演示的是读写互斥产生的效果，大家可以自己去写一下写读、写写互斥的效果。
可以看出来，事务只能串行执行了。串行情况下不存在脏读、不可重复读、幻读的问题了。

关于隔离性级别的选择

1： 需要对各种隔离级别产生的现象非常了解，然后选择的时候才能游刃有余
2：隔离级别越高，并发性也低，比如最高级别 SERIALIZABLE 会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低
3： 具体选择哪种需要结合具体的业务来选择
4： 读已提交（READ-COMMITTED）通常用的比较多