MySQL数据库进阶（事务Transaction）

╰半夏微凉° 2023-02-11 11:30 27阅读 0赞

### 文章目录 ###

*   *   *   *  1、事务定义
             *  2、事务的四大特征
             *  3、事务操作
             *  4、savepoint练习
             *  5、事务在Python中的使用
             *  6、隔离级别

#### 1、事务定义 ####

**1）用于大数据**  
MySQL 事务用于处理操作量大，复杂度高的数据。比如，在人员管理系统中，删除了一个人员的信息，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如注册号码、信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

**2）能保障安全**  
一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务。如：手机转账，该业务就是一个最小的工作单元，它涉及了你各方面的信息和转账对方的信息，这是一种链接式的。你的账户钱少了，对方账户钱多了，两者必须都成功了，这项操作才算真正结束，如果进行到一半出问题了，你的钱是不会少的，因为这一个事务还没有结束。

**3）事务前提**

*  在 MySQL 中只有**使用了 Innodb 数据库引擎**的数据库或表才支持事务。
 *  事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句**要么全部执行，要么全部不执行。**
 *  事务用来管理 insert,update,delete 语句

*综上所述，事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。*

#### 2、事务的四大特征 ####

**1）原子性：** 一个事务中的所有操作，要么都发生，要么都不发生，不会结束在中间某个环节。

**2）一致性：** 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。无论事务是否执行成功，事务结束后的数据库中的数据也应该是符合完整性约束的。

**3）隔离性：** 多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰。

**4）持久性：** 一个事务一旦被提交，就代表一个事务的结束，它对数据的修改就是永久的，即便系统故障也不会丢失。

注意：

> 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT  
> 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET  
> AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

#### 3、事务操作 ####

*  **开启事务：** Start Transaction
 *  **事务结束：** End Transaction
 *  **提交事务：** Commit Transaction
 *  **回滚事务：** Rollback Transaction  
    即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置
 *  **设保留点：** Savepoint identifier  
    事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)
 *  **删保留点：** Release Savepoint identifier
 *  **改提交模式：**  
    SET AUTOCOMMIT=0 禁止自动提交  
    SET AUTOCOMMIT=1 开启自动提交

#### 4、savepoint练习 ####

1）首先创建带有innodb引擎的数据表

--创建使用事务引擎的数据表（设置engine为innodb）
    create table emp(id INT PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
    
    --插入数据
    INSERT INTO emp(name) VALUE ("ViewIN"),
                                  ("Zoro"),
                                  ("ZaHuw");

![1][]  
2）开启事务，并再插入一个数据

--开启事务
    start transaction;
    --再次插入数据
    insert into emp (name) values ('ZaHuw_tow');
    --此时查看，并不会有变化
    select * from emp;
    --提交之后才会有变化
    commit;

然后查看表的内容，会发现并没有新的数据进入，因为我们还没有提交事务。当commit之后，表才会变化。

3）设置保留点

--设置保留点
    start transaction;
    insert into emp (name) values('OK');
    --设置一个名为insert_OK的保留点
    savepoint insert_OK;
    select * from emp;

![2][]

4）进行删除操作，并再设一个保留点

--进行删除操作
    delete from emp where id=5;
    --再次设置一个名为delete1的保留点
    savepoint delete1;
    select * from emp;

5）再删一次，并设保留点

--再删一次，并设保留点
    delete from emp where id=3;
    savepoint delete2;
    select * from emp;

此时已经设置了三个保留点，并且三个操作其实都有效果，当我们使用回滚事务时，就可以返回到对应的保留点处

6）回滚保留点

--回滚保留点
    rollback to delete1;
    select * from emp;

这样我们就回到了delete1保留点，也就是只删除了id=5的数据  
![3][]

#### 5、事务在Python中的使用 ####

import pymysql
    
    # 连接MySQL
    conn = pymysql.connect(host='192.168.43.247', port=3306, user='root', passwd='', db='sqlpwd')
    # 创建游标
    cursor = conn.cursor()
    
    # 创建事务数据表
    # CR= "create table account2(id INT PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb"
    # cursor.execute(CR)
    
    
    # 插入数据
    # into_name= "INSERT INTO account2 (name) VALUE ("ViewIN"),("Zoro"),("ZaHuw")"
    # cursor.execute(into_name)
    
    
    try:
        # 插入数据
        insertSQL0= "INSERT INTO account2 (name,balance) VALUES ('Zero',4000)"
        insertSQL1= "UPDATE account2 set balance=balance-1000 WHERE name='One'"
        insertSQL2= "UPDATE account2 set balance=balance+1000 WHERE name='Tow'"
    
        cursor = conn.cursor()
    
        cursor.execute(insertSQL0)
        conn.commit()
    
        cursor.execute(insertSQL1)
        raise Exception
        cursor.execute(insertSQL2)
        cursor.close()
        conn.commit()
    
    except Exception as e:
        conn.rollback()
        conn.commit()
    
    cursor.close()
    conn.close()

#### 6、隔离级别 ####

**1）四种隔离级别**

*  Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
 *  Repeatable read：可避免脏读、不可重复读情况的发生，不可以避免虚读（可重复读）
 *  Read committed：可避免脏读情况发生（读已提交）
 *  Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

**2）均衡对照**

> 安全性考虑：Serializable > Repeatable read > Read committed > Read uncommitted  
> 数据库效率：Read uncommitted > Read committed > Repeatable read > Serializable

一般情况下，我们会使用Repeatable read、Read committed mysql数据库默认的数据库隔离级别Repeatable read

**3）mysql中设置数据库的隔离级别语句：**

set [global/session] transaction isolation level xxxx;

> 注意：如果使用global则修改的是数据库的默认隔离级别，所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改，则修改的是当前客户端的隔离级别，和数据库默认隔离级别无关。当前的客户端是什么隔离级别，就能防止什么隔离级别问题，和其他客户端是什么隔离级别无关。

[1]: https://img-blog.csdnimg.cn/20200523180051175.png
[2]: https://img-blog.csdnimg.cn/20200523181010938.png
[3]: https://img-blog.csdnimg.cn/20200523181915424.png