数据库事务详解

最新推荐文章于 2024-05-01 13:09:55 发布

置顶疏狂

最新推荐文章于 2024-05-01 13:09:55 发布

阅读量347

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/chenpeng971019/article/details/100694687

版权

MySQL 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

轻松获知数据库事务

事务作用
事务流程
隔离级别
实例验证

事务作用

事务是确保数据库系统数据的完整性的功能，如现在互联网行业支付业务，不管服务器出于什么原因异常中断，客户要么支付成功要么支付不成功，支付成功数据库金额即会发生变化，支付不成功客户的金额就不发生变化，确保了交易业务的稳定性。支持事务的引擎必须满足ACID，满足ACID后才能满足事务，另外事务的回滚或恢复主要靠事务日志来完成，ACID含义分别如下：

A:atomicity(原子性)：整个事务中所有的操作为命令执行最小单元，全部执行、执行一半失败回滚或失败回滚；

C:consistency(一致性)：数据库从一个状态转化为另外一个状态，状态在转化前和转换后一致；

I:isolation(隔离性)：一个事务所做出的操作在提交之前，是不能被其他所见，因此隔离就出现多种隔离级别，具体包括read-uncommitted读为提交、read-committed读提交、repeatable-read可重复读和serializable串行化；

D:durability(持久性)：一旦事务提交，所做的会永久性保存数据库中。

事务流程

事务的工作流程具体可见下图
图1-1 事务工作流程

由上图可知，当数据库通过start transaction启动一个事务，启动事务后对数据库进行一系列的操作，最后提交事务，提交事务又有两种，第一种为commit提交，第二种rollback回滚，一旦提交事务数据库即处于新的状态保持持久性。另外在防止数据库在事务提交后数据从内存写入磁盘时，操作系统异常掉电导致无法保存，而启用日志功能，只要启用事务日志功能，事务先在磁盘连续空间写写日志，然后通过内存同步到磁盘，确保万一内存同步磁盘时机器异常掉电，通过事务日志进行恢复数据库数据。

隔离级别

隔离级别(INNODB默认隔离级别为repeatable read)：

READ UNCOMMITTED(读未提交)：此种隔离级别带来问题有脏读和不可重复读。

READ COMMITTED(读提交)：此种隔离级别解决了脏读，但仍然有不可重复度。

REPEATABLE READ(可重读)：此种隔离级别解决了脏读和不可重复读，带来问题幻读。

SERIALIZABLE(可串行化)：此种隔离级别解决了脏读、不可重复度和幻读，但带来的问题是加锁读。

问题解释：

脏读：当用户A修改数据但未提交，此时B用户读A修改后的数据，但是A用户将数据进行rollback回滚，因此B用户看到的是错误的数据；

不可重复读：如用户A启动一个事务设置某一值设为ON，经查询已经为ON状态，但B用户修改数值为OFF并提交，此时用户A再次查询时发现值又为OFF，或者数据库中又多了一条语句，表现为在同一事务中每次查询数据库总是不一致；

幻读：当用户A用户在同一事务中看到某一值为ON，用户B已经将值修改为OFF，并且已经提交，用户B看到的值为OFF，但用户A在此事务中一直看到的为ON，底层数据确实被修改为OFF，因此就体现了幻读，除非提交后再次生成一个事务查看值才为OFF；

加锁读：读数据时加锁，此时别人无法再读。

实例验证

根据理论概述，进行验证事务的工作流程和隔离级别，确保透彻了解事务的原理，具体操作如下

    MariaDB [(none)]> show processlist;                                      #查看数据库的进程列表，显示有两终端连接
    +----+------+-----------+------+---------+------+-------+------------------+----------+
    | Id | User | Host      | db   | Command | Time | State | Info             | Progress |
    +----+------+-----------+------+---------+------+-------+------------------+----------+
    |  2 | root | localhost | NULL | Sleep   |   23 |       | NULL             |    0.000 |
    | 10 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |    0.000 |
    +----+------+-----------+------+---------+------+-------+------------------+----------+
    2 rows in set (0.00 sec)
    MariaDB [(none)]> show global variables like 'tx_isolation';           #验证事务的隔离级别
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | tx_isolation  | REPEATABLE-READ |
    +---------------+-----------------+
    1 row in set (0.00 sec)

验证隔离级别 READ UNCOMMITTED（存在脏读、不可重复读）

图4-1 脏读

第一步：创建表并插入数据

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #创建表
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into employee  values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);      #给表中添加用户
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

第二步：在两个数据库连接线程的会话变量中设置隔离级别为READ-UNCOMMITTED

会话1
MariaDB [(none)]> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;                 #开启事务
Query OK, 0 rows affected (0.00 sec)

会话2
MariaDB [(none)]> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [test]> start transaction;                 #开启事务
Query OK, 0 rows affected (0.00 sec)

第三步：两边同时启用事务,其中会话1添加bailongma，但不提交，在会话2上查看验证

会话1                     #插入数据但未提交，会话2上查询后验证
MariaDB [test]> insert into employee values(5,'bailongma',300);
Query OK, 1 row affected (0.00 sec)
会话2                     #经查询验证会话1尚未提交已经可以读取，若会话1回滚，会话2读取数据为脏数据
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
|    5 | bailongma  | 300  |
+------+------------+------+
5 rows in set (0.00 sec)

验证隔离级别READ-COMMITTED（解决脏读问题，存在不可重复读

图4-2 不可重复读

第一步：创建表并插入数据

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #创建表
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into employee  values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);      #给表中添加用户
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

第二步：在两个数据库连接线程的会话变量中设置隔离级别为READ-COMMITTED

会话1
MariaDB [(none)]> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

会话2
MariaDB [(none)]> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

第三步：在两个连接数据库的线程进程添加bailongma，但不提交进行验证

会话1
MariaDB [test]> insert into employee values(5,'bailongma',305);
Query OK, 1 row affected (0.00 sec)

会话2              #在会话1未提交时，会话2是无法读取数据
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
+------+------------+------+
4 rows in set (0.00 sec)

第四步：在连会话1上进行提交，然后在会话2上进行验证

会话1
MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

会话2                 
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
|    5 | bailongma  | 305  |
+------+------------+------+
5 rows in set (0.00 sec)    #说明读提交可以解决脏读的问题

验隔离级别REPEATABLE READ(解决脏读和重复读的问题，带来新的问题幻读)

图4-3 幻读

第一步：创建表并插入数据

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #创建表
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into employee  values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);      #给表中添加用户
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

第二步：在两个数据库连接线程的会话变量中设置隔离级别为REPEATABLE-READ


会话1
MariaDB [(none)]> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

会话2
MariaDB [(none)]> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

第三步：先会话2中开启一个事务查询表中数据，然后在会话1中添加bailongma用户，再次在会话2中的同一事务中查看表中数据（发现会话1中数据已经发生变化，会话2的同一事务中任然是之前的数据，因此解决了事务的可重复读）

会话2           #开启一个事务并查询表中数据
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
+------+------------+------+
4 rows in set (0.00 sec)
会话1        #添加bailongma数据后，提交并查询
MariaDB [test]> insert into employee values(5,'bailongma',305);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
|    5 | bailongma  | 305  |
+------+------------+------+
5 rows in set (0.00 sec)
会话2      #在会话2上再次查询，结果任然是4条数据，原因是会话2上的事务并未提交，并且解决了可重复读，因此只能看到4条，除非提交事务后再次查询；
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
+------+------------+------+
4 rows in set (0.00 sec)
MariaDB [test]> select * from employee;
+------+------------+------+
| id   | name       | age  |
+------+------------+------+
|    1 | tangseng   | 38   |
|    2 | sunwukong  | 505  |
|    3 | zhubajie   | 485  |
|    4 | shaheshang | 408  |
|    5 | bailongma  | 305  |
+------+------------+------+
5 rows in set (0.00 sec)

验隔离级别SERIALIZABLE(解决重复读的问题，需注意每次操作都需要重启新的事务和提交，因为有加锁，一个事务只能是一组语句)

图4-4 串行读

第一步：创建表并插入数据

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #创建表
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into employee  values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);      #给表中添加用户
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

第二步：在两个数据库连接线程的会话变量中设置隔离级别为SERIALIZABLE

会话1
MariaDB [(none)]> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

会话2
MariaDB [(none)]> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> start transaction;              #开启事务
Query OK, 0 rows affected (0.00 sec)

第三步：当会话1上进行插入bailongma用户前，在会话2上进查询并提交，会话1添加bailongma并提交，然后再次在会话2上进行查询

会话1       #添加用户后，并未提交
MariaDB [test]> insert into employee values(5,'bailongma',305);
Query OK, 1 row affected (0.00 sec)
会话2       #在会话1上未提交时，会话1对表进行加锁，因此会话2上时无法查询，因此解决幻读
MariaDB [test]> select * from employee;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

疏狂

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库事务详解

轻松获知数据库事务事务作用事务流程隔离级别实例验证事务作用事务是确保数据库系统数据的完整性的功能，如现在互联网行业支付业务，不管服务器出于什么原因异常中断，客户要么支付成功要么支付不成功，支付成功数据库金额即会发生变化，支付不成功客户的金额就不发生变化，确保了交易业务的稳定性。支持事务的引擎必须满足ACID，满足ACID后才能满足事务，另外事务的回滚或恢复主要靠事务日志来完成，ACID含义分别...
复制链接

扫一扫