SQL之事务-摩杜云开发者社区

SQL之事务_数据

🎈 什么是事务

💧 概念

事务是用于保证数据的一致性,它由一组相关的DML（增、删、改）语句，该组的DML语句要么全部成功，要么全部失败。使用事务可以确保数据库的一致性和完整性，避免数据出现异常或不一致的情况。

💧 特性

在 SQL 中，事务是指一组数据库操作，它们被视为单个逻辑单元并必须全部成功或全部失败。如果其中任何一个操作失败，则整个事务都会被回滚到之前的状态，保证数据的一致性和完整性。每个事务都拥有以下四个特性（简称ACID）：

原子性（Atomicity）：事务应该被视为一个原子操作，即要么全部成功，要么全部失败。如果任何单个操作失败，整个事务将回滚并且不会对数据库产生任何影响。
一致性（Consistency）：事务执行后，数据库应该保持一个一致性状态。一致性状态是由事务操作所指定的约束条件定义的，也就是说，事务所做的任何更改都必须遵守其定义的约束条件。
隔离性（Isolation）：事务应该与其他并发执行的事务隔离开来，以确保事务间不会相互干扰。通过各种技术，如锁定机制和多版本并发控制（MVCC），可以实现高度的隔离性。
持久性（Durability）：一旦事务提交，其更改就应该永久保存在数据库中，即使系统发生故障或电源被关闭也是如此。这通常通过将更改写入磁盘或其他非易失性存储介质来实现。

总之，事务是在 SQL 中用于处理一组数据库操作的机制，其中的 ACID 特性确保了事务的原子性、一致性、隔离性和持久性，从而确保了数据的完整性和可靠性。

在SQL中，可以使用BEGIN TRANSACTION 、COMMIT和ROLLBACK等关键字来控制事务。BEGIN TRANSACTION开始一个新的事务，COMMIT语句提交事务，ROLLBACK语句则是回滚事务。

💧 基本操作

🔥 事务的几个重要操作

start transaction -- 开始一个事务
savapoint 保存点名 -- 设置保存点
rollback to 保存点名 -- 回退事务到保存点
rollback -- 回退全部事务
commit -- 提交事务，所有操作全部生效，不能回退。

🔥 回退事务

在回退事务前，要先了解下保存点（savepoint）。保存点是事务中的点，用于取消部分事务，当结束事务（commit）时会自动删除该事务所定义的所有保存点。

当执行回退事务时，通过指定保存点可以回退到指定的点。

🔥 提交事务

使用commit语句可以提交事务。当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后，其他会话将可以查看事务变化后的数据。

🔥 事务注意事项

如果不开始事务，默认情况下，DML操作时自动提交的，不能回滚。

INSERT INTO goods VALUES (1,'java开发',29.9,'编程类','图书馆');
SELECT * FROM `goods` ;

SQL之事务_mysql_02

如果开始一个事务，没有创建保存点，可一致性rollback也就是默认回退到事务开始时的状态。

-- 开启事务
START TRANSACTION;
-- 插入数据
INSERT INTO goods VALUES (2,'C#开发',30.9,'编程类','图书馆');
SELECT * FROM `goods`

SQL之事务_mysql_03

-- 回滚 直接回退到事务开始时的状态。
ROLLBACK;
SELECT * FROM `goods`

SQL之事务_mysql_04

可以在事务中（未提交时），创建多个保存点。例：savepoint aaa；执行dml， savepoint bbb；

START TRANSACTION;
SAVEPOINT point_a;
INSERT INTO goods VALUES (3,'C++开发',30.9,'编程类','图书馆');
SELECT * FROM `goods`

SQL之事务_隔离级别_05

ROLLBACK TO point_a;

SQL之事务_mysql_06

可以在事务没有提交前，选择回退到某个保存点。
mysql的事务机制需要innodb的存储引擎还可以使用，myisam不好使。
开始一个事务 start transaction，set autocommit=off；

💧 事务的隔离级别

事务的隔离级别（Isolation Level）是指多个事务同时操作相同的数据时，系统要如何处理它们之间的相互影响和冲突的程度。目前，SQL标准定义了4种基本事务隔离级别。分别是：

Read Uncommitted（未提交读）：事务中的修改，即使没有提交，对其他事务也是可见的。
Read Committed（提交读）：一个事务只能看见已经提交的事务所做的修改。
Repeatable Read（重复读）：在同一事务中，查询同一个记录会返回相同的结果，即使在这个事务种其他地方已经修改过这个记录。
Serialize（串行化）：所有事务依次逐个执行，这样每个事务都感觉不到其他事务的存在。

在不同的隔离级别具有不同的并发控制机制，隔离级别越高，并发性能越差，但是数据一致性性越高。而较低的隔离级别可以提高并发性能，但是可能导致脏读、不可重复读和幻读等问题。

脏读（dirty read）：当一个事务读取另一个事务尚未提交的改变（insert、update、delete）时，产生脏读。
不可重复读（nonrepeatable read）：同一个查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
幻读（phantom read）：同一个查询在同一事物中多次进行，由于其他提交事务所做的插入或删除操作，每次返回不同的结果集，此时返回幻读。

MYSQL隔离级别定义了事务于事务之间的隔离程度。

mysql隔离级别	脏读	不可重复读	幻读	加锁读
Read Uncommitted（未提交读）	✔	✔	✔	不加锁
Read Committed（提交读）	❌	✔	✔	不加锁
Repeatable Read（重复读）	❌	❌	❌	不加锁
Serializable（串行化）	❌	❌	❌	加锁

说明：✔表示可能出现。❌表示不会出现。

🔥 事务隔离级别演示

🌀 未提交读（Read Uncommitted）隔离级别演示

开启两个mysql的控制台。

mysql -u root -p

查看当前mysql的隔离级别。

select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

把其中一个控制台的控制台的隔离级别设置为 Read Uncommitted（未提交读）

-- 修改隔离级别。
set session transaction isolation level read uncommitted;

表测试 Repeatable Read级别窗口：

-- 在级别为Repeatable Read的窗口创建表
create table `account` (
	id int,
	'name' varchar(32),
	money int
);
-- 创建事务。
start transaction;
-- 等`Read Uncommitted`级别窗口创建事务后，然后插入数据
insert into account value(1,'ning',8000);
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
+------+------+-------+
1 row in set (0.00 sec)

Read Uncommitted级别窗口：

-- 创建事务。
start transaction;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
+------+------+-------+
1 row in set (0.00 sec)

结果发现，即便开启了事务，在Read Uncommitted级别窗口依旧能查看到Repeatable Read的窗口的数据，这种结果就是脏读。 Repeatable Read级别窗口：

-- 再修改数据
update account set money=10000 where id=1;
-- 提交事务
commit;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning | 10000 |
+------+------+-------+
1 row in set (0.00 sec)

Read Uncommitted级别窗口：

-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning | 10000 |
+------+------+-------+
1 row in set (0.00 sec)

结果来看，在Repeatable Read的窗口执行插入和修改操作并提交后，Read Uncommitted级别窗口依旧能看到变化，也就是影响了Read Uncommitted级别窗口对于这个表的读的操作，这种结果就是幻读和不可重复读。

而正常的事务，我们应该查到的是事务开始前的数据（不会发生改变）不受到除本事务外的操作的影响，现在却能看到数据在发生改变，这就是因为事务的隔离等级较低，数据的一致性也较低。

🌀 提交读（Read Committed）隔离级别演示

开启两个mysql的控制台。

mysql -u root -p

查看当前mysql的隔离级别。

select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

把其中一个控制台的控制台的隔离级别设置为 Read Uncommitted（未提交读）

-- 修改隔离级别。
set session transaction isolation level read committed;

表测试 Repeatable Read级别窗口：

-- 创建事务。
start transaction;
-- 等`Read Committed`级别窗口创建事务后，在account表插入数据
insert into account value (3,'qing',5000);
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    2 | Ming | 10000 |
|    3 | qing |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)

Read Committed级别窗口：

-- 创建事务。
start transaction;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    2 | Ming | 10000 |
+------+------+-------+
2 rows in set (0.00 sec)

结果发现，在Repeatable Read级别窗口插入数据，但未提交（commit）时，Read Committed级别窗口并不能看到数据变化，也就是没有发生脏读。 Repeatable Read级别窗口：

-- 再修改数据
update account set money=15000 where id=2;
-- 提交事务
commit;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    2 | Ming | 15000 |
|    3 | qing |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)

Read Committed级别窗口：

-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    2 | Ming | 15000 |
|    3 | qing |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)

结果来看，在Repeatable Read的窗口插入和修改操作并提交后，Read Uncommitted级别窗口能看到数据变化，也就是影响了Read Uncommitted级别窗口对于这个表的读的操作，这种结果就是幻读和不可重复读。

🌀 重复读（Repeatable Read）隔离级别演示

开启两个mysql的控制台。

mysql -u root -p

查看当前mysql的隔离级别。

select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

确认两个窗口都是重复读（Repeatable Read）隔离级别，不是则修改

-- 修改隔离级别。
set session transaction isolation level repeatable read;

SQL之事务_数据_07

表测试 Repeatable Read级别窗口A：

-- 创建事务。
start transaction;
-- 等B窗口创建事务后，在account表插入数据
insert into account value (4,'tian',10000);
-- 修改数据
update account set money=8000 where id=1;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    4 | tian | 10000 |
+------+------+-------+
2 rows in set (0.00 sec)

Repeatable Read级别窗口B：

-- 创建事务。
start transaction;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning | 10000 |
+------+------+-------+
1 row in set (0.00 sec)

结果发现，在窗口A插入数据，但未提交（commit）时，窗口B并不能看到数据变化，也就是没有发生脏读。 Repeatable Read级别窗口A：

-- 提交事务
commit;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    4 | tian | 10000 |
+------+------+-------+
2 rows in set (0.00 sec)

Read Committed级别窗口B：

-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning | 10000 |
+------+------+-------+
1 row in set (0.00 sec)

结果来看，在窗口A执行插入和修改操作并提交后，窗口B能依旧看不到数据变化，这种结果就是没有出现幻读和不可重复读。

🌀 串行化（Serializable）隔离级别演示

开启两个mysql的控制台。

mysql -u root -p

查看当前mysql的隔离级别。

select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改一个窗口隔离级别为串行化（Serializable）

-- 修改隔离级别。
set session transaction isolation level Serializable;

表测试 Repeatable Read级别窗口A：

-- 创建事务。
start transaction;
-- 等B窗口创建事务后，在account表插入数据
insert into account value (5,'369',12000);
-- 修改数据
update account set money=9000 where id=4;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    4 | tian |  9000 |
|    5 | 369  | 12000 |
+------+------+-------+

Serializable级别窗口B：

-- 创建事务。
start transaction;
-- 查询数据
select * from account;
-- 结果会发现程序卡在这里。

会发现B窗口执行操作时会卡住不执行，这是因为在执行事务期间，一个或多个资源被其他会话锁定。默认情况下，数据库会在卡住一段时间后提示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。这就是在一个事务中更新一条记录，但在这个事务还没有提交之前，另一个事务也想要更新同一行记录。那么这个请求就会导致锁等待，如果等待时间超过了系统设置的时间限制，则会出现“Lock wait timeout exceeded”的错误提示。这个错误通常意味着当前事务需要等待其他正在运行的事务释放锁定的资源。 Repeatable Read级别窗口A：

-- 提交事务
commit;
-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    4 | tian |  9000 |
|    5 | 369  | 12000 |
+------+------+-------+
3 rows in set (1.02 sec)

Serializable级别窗口B：

-- 查询数据
select * from account;
-- 结果
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | ning |  8000 |
|    4 | tian |  9000 |
|    5 | 369  | 12000 |
+------+------+-------+
3 rows in set (1.02 sec)

会发现，在B窗口执行查询语句卡住的时候，将A窗口的操作提交，B窗口会随之立刻执行查询语句，这是因为在串行化隔离级别下，所有的事务都是一个接一个地执行，不会有两个事务同时并发访问同一组数据的情况出现，因此能保证查询结果的准确性和一致性。因此不会出现幻读和不可重复读的问题。

🔥 设置隔离

查看当前会话隔离级别

select @@transaction_isolation;

查看当前系统隔离级别

select @@global.transaction_isolation;

设置当前会话隔离级别

-- <isolation_level> 表示要设置的隔离级别，
-- 可以是 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 中的任意一个。
SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

设置当前系统隔离级别

-- <isolation_level> 表示要设置的隔离级别
-- 可以是 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 中的任意一个。
SET GLOBAL TRANSACTION ISOLATION LEVEL <isolation_level>;

mysql默认的事务隔离级别是REPEATABLE-READ，一般情况下，没有要求，没必要修改。

-- 全局修改，修改mysql.ini配置文件，在最后加上：
-- 可以是 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 中的任意一个。
[mysqld]
transaction-isolation=REPEATABLE-READ