MySQL实操基础全过程学习笔记（四）

六、事务

（一）什么是事务

事务定义：
一个数据库操作序列，一个不可分割的工作单位，恢复和并发控制的基本单位

存在意义：
解决多条语句同时执行时，有点语句出现失误的情况

（二）如何控制事务—commit_rollback

1.查看数据库是否自动回滚
一般数据库都是默认开启事务的，即执行一个SQL语句后，不可回滚。如果取消默认设置，则在手动提交数据前，可以进进行回滚操作

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

2.测试

（1）新建表
mysql> CREATE TABLE user (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(20),
    ->     money INT
    -> );
    
（2）插入数据
    INSERT INTO user VALUES (1, 'a', 1000);

（3）回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
虽然回滚执行成功，但是并没有真正撤销操作

（4）设置自动提交为0（即faulse）
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

（5）设置完成后，再次插入一条新数据
mysql> INSERT INTO user VALUES (2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

（6）进行回滚，可以看到上一步操作取消了
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

（7）如果在SQL语句后面加入commit提交，则不能再回滚了
mysql> INSERT INTO user VALUES (2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)

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

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

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

（8）如果同时对数据进行操作，则属于同一事务单元，具有一致性
同时执行以下转账操作
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

（9）可以看到数据以及更新完成，如果中间出现什么问题，可以通过rollback使两条语句都不成功
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
事务给了我们可以返回的机会

（三）手动开启事务 -—begin_start transaction

如果我们在设置默认自动提交的情况下，还想rollback的话，通过begin或start transaction就可以帮我们手动开启一个事务

（1）先把默认值还原回去
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

（2）查看账户原有金额
mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

（2）进行转账操作
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

（3）这回进行rollback操作，发现没有办法回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

（4）如果在执行语句前加上begin/start transaction，就可以对该事务进行回滚操作了
begin;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+

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

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

（5）如果确定好了，就要手动commit提交
start transaction;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+

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

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+

（6）这回rollback就会发现已经没有效果了
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+

（四）事务的ACID特性：

（面试常考）
原子性（Atomicity）：
一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功。
事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态。
一致性（Consistency）：
一致性是指在事务处理时，无论执行成功还是失败，都要保证数据库系统处于一致的状态，保证数据库系统从不返回到一个未处理的事务中。
MySQL中的一致性主要由日志机制实现的，通过日志记录数据库的所有变化，为事务恢复提供了跟踪记录。
隔离性（Isolation）：
隔离性是指当一个事务在执行时，不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离，直到事务完成为止，才能看到事务的执行结果。
隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持续性（Durability ）：
持久性是指事务一旦提交，其对数据库的修改就是永久性的。
事务的持久性不能做到百分百的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所有提交的数据可能都会丢失。

七、事务隔离性

（一）脏读

MySQL中事务的隔离级别：
REPEATABLE READ：可重复读
READ UNCOMMITTED：读取未提交
READ COMMITTED：读取提交
SERIALIZABLE：可串行化

设置事务的隔离级别：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 参数值
SESSION：当前会话
GLOBAL：全局
直接省略：下一个事务的隔离级
TRANSACTION：事务
ISOLATION：隔离
LEVEL：级别

READ UNCOMMITTED（读取未提交）
事务中最低的级别，可以读取到其他事务中未提交的数据。
也称为脏读（Dirty Read）：一个事务读取了另外一个事务未提交的数据。
实际开发是不允许出现脏读的

设置客户端B的隔离级别，允许脏读：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

情景模拟：Alex给Bill转账100元购买商品。
Alex开启事务后转账，但不提交事务，通知Bill来查询，
如果Bill的隔离级别较低，就会读取到Alex的事务中未提交的数据，
发现Alex确实给自己转了100元，就给Alex发货。
等Bill发货成功后，Alex将事务回滚，Bill就会受到损失。

（二）不可重复读

READ COMMITTED（读取提交）
大多数DBMS（如SQL Server、Oracle）的默认隔离级，但不包括MySQL。
只能读取其他事务已经提交的数据，避免了脏读问题。
但是会出现不可重复读（NON-REPEATABLE READ）问题。

情景模拟：在网站后台统计所有用户的总金额。
第1次查询Alex有900元，
第2次查询Alex有800元。
问题：在同一个事务中，同样的两次查询结果不同，
原因：第2次查询前Alex取出了100元。

（三）幻读

REPEATABLE READ（可重复读）
MySQL的默认事务隔离级，它解决了脏读和不可重复读的问题，
确保了同一事务的多个实例在并发读取数据时，会看到同样的结果。
该级别理论上会出现幻读（PHANTOM READ）问题。

幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致，
如：其他事务做了插入记录的操作，导致记录数有所增加。
不过，MySQL的InnoDB存储引擎已经解决了幻读问题。

情景模拟：在网站后台统计所有用户的总金额时，
当前只有两个用户，总金额为2000元，
此时新增一个用户，并且存入1000元。
再次统计会，虽然表面上还是之前的数据，但是对数据进行操作时，发现总金额变为3000元，造成了幻读的情况。

（四）串行化—SERIALIZABLE

隔离级的*别，它在每个读的数据行上加锁，使之不会发生冲突，
解决了脏读、不可重复读和幻读的问题。
由于加锁可能导致超时（Timeout）和锁竞争（Lock Contention）现象，
性能是4种隔离级中最低的。
除非为了数据的稳定性，需要强制减少并发的情况时，才会选择此种隔离级。

情景模拟：A与B同时操作
当A端开启事务后，B端提交数据处于“卡顿”状态，当A端提交COMMIT后，B端SQL语句将会执行
若客户端A一直未提交事务，客户端B会等待到超时后报错：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

补充：
（1）锁等待超时默认为50秒，可进行更改

SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+

（2）隔离级别越高，性能越差

本文地址：https://blog.csdn.net/weixin_45818336/article/details/107120074