ci mysql 事务_MySQL系列详解四：MySQL事务-技术流ken

MySQL 事务

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

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件(ACID)：：原子性(Atomicity，或称不可分割性)、一致性(Consistency)、隔离性(Isolation，又称独立性)、持久性(Durability)。

原子性：一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

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

事务控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET GLOBAL AUTOCOMMIT=0 禁止自动提交

SET GLOBAL AUTOCOMMIT=1 开启自动提交

MYSQL事务演示

准备

切换至相应的数据库下，并查看表信息

1 mysql>use jobs;2 mysql> show table status like "teachers"\G3 *************************** 1. row ***************************

4 Name: teachers5 Engine:  MyISAM <<6 Version: 10

7 Row_format: Dynamic8 Rows: 0

9 Avg_row_length: 0

10 Data_length: 16384

11 Max_data_length: 0

12 Index_length: 0

13 Data_free: 0

14 Auto_increment: 5

15 Create_time: 2018-10-19 18:20:52

16 Update_time: 2018-10-19 18:22:59

17 Check_time: NULL18 Collation: utf8_general_ci19 Checksum: NULL20 Create_options:21 Comment:22 1 row in set (0.00 sec)

修改表引擎为InnoDB

1 mysql> alter table teachers engine=innodb;2 Query OK, 1 row affected (0.03sec)3 Records: 1 Duplicates: 0 Warnings: 0

4

5 mysql> show table status like "teachers"\G6 *************************** 1. row ***************************

7 Name: teachers8 Engine: InnoDB9 Version: 10

10 Row_format: Dynamic11 Rows: 0

12 Avg_row_length: 0

13 Data_length: 16384

14 Max_data_length: 0

15 Index_length: 0

16 Data_free: 0

17 Auto_increment: 5

18 Create_time: 2018-10-20 04:17:01

19 Update_time: 2018-10-20 04:17:01

20 Check_time: NULL21 Collation: utf8_general_ci22 Checksum: NULL23 Create_options:24 Comment:25 1 row in set (0.00 sec)

回滚事务演示

1 mysql>start transaction; <

4 mysql> select * fromteachers; <

6 | TID | Name | Age | Gender |

7 +-----+------------+-----+--------+

8 | 1 | Song Jiang | 45 | M |

9 +-----+------------+-----+--------+

10 1 row in set (0.00sec)11

12 mysql> delete fromteachers; <

15 mysql> select * fromteachers; <

18 mysql>rollback; <

21 mysql> select * fromteachers; <

23 | TID | Name | Age | Gender |

24 +-----+------------+-----+--------+

25 | 1 | Song Jiang | 45 | M |

26 +-----+------------+-----+--------+

27 1 row in set (0.00 sec)

提交事务演示

mysql>start transaction; <

Query OK,0 rows affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

+-----+------------+-----+--------+

1 row in set (0.00sec)

mysql> insert into teachers values (2,'ken',25,'M'); <

Query OK,1 row affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | ken | 25 | M |

+-----+------------+-----+--------+

2 rows in set (0.00sec)

mysql>commit; <

Query OK,0 rows affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | ken | 25 | M |

+-----+------------+-----+--------+

2 rows in set (0.00 sec)

设置和使用保存点演示

mysql>start transaction; <

Query OK,0 rows affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | ken | 25 | M |

+-----+------------+-----+--------+

2 rows in set (0.00sec)

mysql>savepoint ken1; <

Query OK,0 rows affected (0.00sec)

mysql> delete fromteachers; <

Query OK,2 rows affected (0.00sec)

mysql> select * fromteachers; <

Emptyset (0.00sec)

mysql>rollback to ken1; <

Query OK,0 rows affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | ken | 25 | M |

+-----+------------+-----+--------+

2 rows in set (0.00sec)

mysql>commit; <

Query OK,0 rows affected (0.00sec)

mysql> select * fromteachers; <

| TID | Name | Age | Gender |

+-----+------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | ken | 25 | M |

+-----+------------+-----+--------+

2 rows in set (0.00 sec)

关闭自动提交

mysql中一个sql语句就是一个事务，mysql系统默认是开启了事务提交的功能，可以使用如下命令进行查看。

mysql> show global variables like "autocommit";+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.01 sec)

如果想要关闭自动提交的功能，可以试下如下命令进行操作。

mysql> set global autocommit=0;

Query OK,0 rows affected (0.00sec)

mysql> show global variables like "autocommit";+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)