数据库-mysql数据库事务

最新推荐文章于 2024-11-01 12:20:35 发布

自信人间三百年

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量967

点赞数 9

分类专栏：数据库文章标签：数据库 mysql oracle

本文链接：https://blog.csdn.net/qq_36115196/article/details/136217299

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细解释了数据库事务的概念、ACID特性（原子性、一致性、隔离性和持久性）以及MySQL中不同隔离级别的应用，通过实例展示了事务处理、隔离性问题（如脏读和幻读）及其解决方案。

摘要由CSDN通过智能技术生成

数据库事务

所谓事务：是指作为单个逻辑工作单元执行的多个数据库操作，要么同时成功，要么同时失败，它必须满足ACID特性。

事务场景

发红包: 小白给小黑发红包。

事务特性ACID

数据库的事务特性：原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability)

原子性

事务中包含的所有操作要么都做，要么都不做，保证数据库是一致的。

例如：转账是一个完整的过程，不能分割为几部分。

一致性

数据库中的数据在事务操作前和事务处理后都必须满足业务规则约束。事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

例如：转账的账户扣除-100元，接收者账户+100元，不能结果不一致。

隔离性

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

例如：在转账的时候不能被干扰，其他人取走了你的钱，转账失败。

持久性

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

例如：当转账完成应该永久保存，不应该忽然不见了。

mysql事务隔离性

事务的隔离级别：读未提交，读已提交，可重复读，串行。

查询事务当前隔离级别

global.transaction_isolation: 全局事务隔离级别

tansacation_isolation: 当前事务隔离级别

SELECT
	@@global.transaction_isolation,
	@@transaction_isolation;

mysql> SELECT
    -> @@global.transaction_isolation,
    -> @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

读未提交

事务流程

产生脏读数据。

测试

设置数据全局事务隔离级别为读未提交(read uncommitted)
```
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
```

开启一个事务

退出容器重新登入容器，打开mysql客户端。

mysql> SELECT @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

开启事务

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

修改功夫片–>kongfu

mysql> update genre_tab set genre_name='kongfu' where genre_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from genre_tab;
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
| genre_id | genre_create_by | genre_create_time | genre_introduction          | genre_name | genre_update_time |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
|        1 | NULL            | NULL              | 逗人发笑的电影类型          | 喜剧片     | NULL              |
|        2 | NULL            | NULL              | Chines KongFu               | kongfu     | NULL              |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
2 rows in set (0.00 sec)

开启另一个事务

再打开一个mysql客户端,查询表genre_tab；

mysql> SELECT @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

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

mysql> select * from genre_tab;
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
| genre_id | genre_create_by | genre_create_time | genre_introduction          | genre_name | genre_update_time |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
|        1 | NULL            | NULL              | 逗人发笑的电影类型          | 喜剧片     | NULL              |
|        2 | NULL            | NULL              | Chines KongFu               | kongfu     | NULL              |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+

事务一回滚

回顾到事务开始的时候，genre_name又回到了’功夫片’；

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from genre_tab;
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
| genre_id | genre_create_by | genre_create_time | genre_introduction          | genre_name | genre_update_time |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
|        1 | NULL            | NULL              | 逗人发笑的电影类型          | 喜剧片     | NULL              |
|        2 | NULL            | NULL              | Chines KongFu               | 功夫片     | NULL              |
+----------+-----------------+-------------------+-----------------------------+------------+-------------------+
2 rows in set (0.00 sec)

事务二再次查询

读已提交

事务执行流程

测试

设置事务级别为读已提交(READ COMMITTED)

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

退出再启动客户端

mysql> SELECT @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED                 | READ-COMMITTED          |
+--------------------------------+-------------------------+

开启第一个事务
```
start transaction;
```
开启第二个事务，并查询
```
start transaction;
```
第一个事务查询结果

第二个事务修改类型为-music,并提交事务

mysql> update genre_tab set genre_name='music' where genre_id =2;
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 InnoDB默认为可重复读隔离，我们使用它测试之前的隔离流程。

设置为可重复读（Docker 从启mysql自动恢复)
事务一开启事务，并查询表
事务二开启事务，修改表并提交
事务一查询表

幻读问题

幻读是指对表添加、删除时产生的问题。

开启事务一，统计表中电影类型数量

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

mysql> select count(*) from genre_tab;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

开启事务二，添加一行数据提交事务

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

mysql> insert into genre_tab(genre_name) values('story');
Query OK, 1 row affected (0.00 sec)

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

事务一再次统计数据,数据变化产生幻读
```
mysql> select count(*) from genre_tab;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
```
mysql解决幻读的方案
1. 通过使用select … for update 添加行级锁。
  
  原理（mvvc:多版本并发控制): 每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。
  
  next-key 锁包含两部分：记录锁（行锁）、间隙锁。
  记录锁是加在索引上的锁，间隙锁是加在索引之间的。
  原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。
  - 第一个事务
```
mysql> select count(*) from genre_tab for update;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
```
  - 第二个事务
```
mysql> insert into genre_tab(genre_name) values('story');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
2. 提升隔离级别为Serializable