MySql之事务操作

谁践踏了优雅 2023-02-25 02:19 42阅读 0赞

# Mysql中事务操作 #

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。

是否开启隐式事务是由变量`autocommit`控制的。

所以事务分为**隐式事务**和**显式事务**。

# 隐式事务 #

> 事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量`autocommit`是否开启了自动提交

> `autocommit`为ON表示开启了自动提交。

# 显式事务 #

> 事务需要手动开启、提交或回滚，由开发者自己控制。

2种方式手动控制事务：

## **方式1：** ##

语法：

//设置不自动提交事务
    set autocommit=0;
    //执行事务操作
    commit|rollback;

示例1：提交事务操作，如下：

mysql> create table test1 (a int);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select * from test1;
    Empty set (0.00 sec)
    
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values(1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)

示例2：回滚事务操作，如下：

mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values(2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)

> 可以看到上面数据回滚了。

我们把`autocommit`还原回去：

mysql> set autocommit=1;
    Query OK, 0 rows affected (0.00 sec)

## **方式2：** ##

语法：

start transaction;//开启事务
    //执行事务操作
    commit|rollback;

示例1：提交事务操作，如下：

mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values (2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into test1 values (3);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)

> 上面成功插入了2条数据。

示例2：回滚事务操作，如下：

mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from test1;
    Query OK, 3 rows affected (0.00 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)

> 上面事务中我们删除了`test1`的数据，显示删除了3行，最后回滚了事务。

## savepoint关键字 ##

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用`savepoint`来实现，效果如下：

先清除`test1`表数据：

mysql> delete from test1;
    Query OK, 3 rows affected (0.00 sec)
    
    mysql> select * from test1;
    Empty set (0.00 sec)

演示`savepoint`效果，认真看：

mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values (1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> savepoint part1;//设置一个保存点
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values (2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> commit;//提交事务
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)

> 从上面可以看出，执行了2次插入操作，最后只插入了1条数据。
> 
> `savepoint`需要结合`rollback to sp1`一起使用，可以将保存点`sp1`到`rollback to`之间的操作回滚掉。

## 只读事务 ##

表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有一些性能上的优化。

用法如下：

start transaction read only;

示例：

mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start transaction read only;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    |    1 |
    +------+
    2 rows in set (0.00 sec)
    
    mysql> delete from test1;
    ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test1;
    +------+
    | a    |
    +------+
    |    1 |
    |    1 |
    +------+
    2 rows in set (0.00 sec)

> 只读事务中执行delete会报错。