11、事务

FeiYi

MySQL

发布于：2019年12月11日

次浏览

字数：1.9k字

时长：8分钟

事务

概念

事务是多条sql语句的集合，要么都成功，要么都失败，且事务是基于存储引擎实现的。

创建的事务都是基于DML语言来操作的

特点

1、原子性（atomicity）：一个事务必须被视为一个不可分割的单元

2、一致性（consistency）：数据库从一种状态切换到另一张状态如：银行转账过程中，来回转金额的数量不会改变

3、隔离性（isolation）：事务在提交之前对其他事务不可见

4、持久性（durablity）：一旦事务提交，永久保存到数据库

事务的使用方法

开启事务

begin

start transaction

==如果执行第二次bigin，表示第一次的事务会自动提交，且不能回滚==

SHOW VARIABLES LIKE 'AUTOCOMMIT' 查看自动提交状态

SET autocommit=1（表示开启自动提交事务）0（表示关闭）

提交事务

commit

回滚

rollback

例：

mysql> CREATE TABLE bank(
    -> name VARCHAR(30),
    -> money DECIMAL(8,2));
mysql> INSERT INTO bank('ljj',20000),('yj',5000);
mysql> INSERT INTO bank VALUES('ljj',20000),('yj',5000);
mysql> SELECT * FROM bank;
+------+----------+
| name | money    |
+------+----------+
| ljj  | 20000.00 |
| yj   |  5000.00 |
+------+----------+
mysql> BEGIN;            /*开启事务*/
mysql> UPDATE bank SET money=money-20000 WHERE name='ljj';

mysql> SELECT * FROM bank;
+------+---------+
| name | money   |
+------+---------+
| ljj  |    0.00 |
| yj   | 5000.00 |
+------+---------+
mysql> rollback;        /*回滚数据*/

mysql> SELECT * FROM bank;
+------+----------+
| name | money    |
+------+----------+
| ljj  | 20000.00 |
| yj   |  5000.00 |
+------+----------+

mysql> UPDATE bank SET money=money-20000 WHERE name='ljj';

mysql> COMMIT;        /*提交事务*/

mysql> ROLLBACK;        /*回滚*/

mysql> SELECT * FROM bank;                /*发现没用*/                                                
+------+---------+
| name | money   |
+------+---------+
| ljj  |    0.00 |
| yj   | 5000.00 |
+------+---------+

mysql> UPDATE bank SET money=money+20000 WHERE name='yj';

mysql> SELECT * FROM bank;
+------+----------+
| name | money    |
+------+----------+
| ljj  |     0.00 |
| yj   | 25000.00 |
+------+----------+
2 rows in set (0.00 sec)

mysql> COMMIT;        /*提交事务*/

mysql隔离级别

设置隔离级别语法：SET SESSION TX_SIOLATION=隔离级别

查看隔离级别语法：SELECT @@TX_ISOLATION;

（1）read-uncommitted 未提交读

未提交读的问题：

1）：脏读：事务未提交时，对其他事务可见

2）：不可重复读：事务提交后，不能对数据进行重复读取

3）：幻读：数据存在但是无法查看

例：

/*开启两个终端做如下设置*/1终端2终端
mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';        /*设置为未提交读*/
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;            /*同时开启两个事务*/

/*其中1终端提交掉一个事务，并创建表并写入数据*/
mysql> COMMIT;
mysql> CREATE TABLE student(                /*创建测试表*/
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(30) NOT NULL,
    -> PRIMARY KEY(id));
mysql> BEGIN;        /*再次开启事务*/
mysql> INSERT INTO student(name) VALUES('ljj');        /*写入数据并没有提交*/

/*切换到2终端，进行查看student表数据*/
mysql> select * from student;        /*发现可以读取数据*/
+----+------+
| id | name |
+----+------+
|  1 | ljj  |
+----+------+

（2）read-committed 已提交读

解决了未提交读脏读的问题，不可重复读的问题依然没有解决，幻读还是存在

例：

/*同样是两个终端中设置*/1终端2终端
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';    /*设置为已提交度的隔离级别*/
mysql> BEGIN;
/*在1终端，继续写入数据*/
mysql> INSERT INTO student(name) VALUES('sy');
/*切换2终端，发现查看不到sy这条数据*/
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | ljj  |
|  2 | yj   |
+----+------+
/*回到1终端，将事务提交*/
mysql> COMMIT;
/*再次切换2终端查看*/
mysql> select * from student;        /*只有提交事务之后才可以查看数据*/
+----+------+
| id | name |
+----+------+
|  1 | ljj  |
|  2 | yj   |
|  3 | sy   |
+----+------+

（3）repeatable-read 可重复读

没有脏读问题，没有不可重复读问题

/*同样是两个终端中设置*/1终端2终端
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';    /*设置为不可重复读的隔离级别*/
mysql> BEGIN;            /*开启事务*/
/*在1终端，更新数据*/
mysql> UPDATE student SET name='a' WHERE id =1;
mysql> SELECT * FROM student;        /*在本终端可以看到*/
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | yj   |
|  3 | sy   |
+----+------+
mysql> COMMIT;            /*提交事务*/
/*在2终端查看表，发现还没有变*/（可重复读）
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | ljj  |
|  2 | yj   |
|  3 | sy   |
+----+------+
/*将2终端的事务提交*/
mysql> COMMIT;            /*提交事务*/
mysql> select * from student;      /*发现可以看到了*/
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | yj   |
|  3 | sy   |
+----+------+

（4）serializable 可串行读

最高的隔离级别，解决一切问题

将事务进行排序，逐一执行

/*同样是两个终端中设置*/1终端和2终端
mysql> SET SESSION TX_ISOLATION='SERIALIZABLE';            /*设置为可串行读*/
mysql> BEGIN;
/*用1终端查看数据可以正常查看*/
/*切换2终端，进行写入数据*/
mysql> INSERT INTO student(name) VALUES('b');        /*会一直卡着，直到报错*/(加了写锁)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
/*将1终端事务提交后，发现w2终端可以写入b数据*/
/*再次切换回1终端，开启一个事务*/
mysql> BEGIN;
mysql> SELECT * FROM student;        /*不能查看数据*/
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

缺点：逐一执行之后，效率变低，并发低

锁

读锁

共享锁，s锁，如果事务T对于A加了一个s锁，其他事务不能对A加写锁，但是可以对A加读锁，直到T释放对A的加锁，能读不能写。

写锁

排他锁，x锁，如果事务T对A加了一个x锁，其他事务不能对A加任何锁，直到T释放对A的x锁，保证数据既不能读也不能写

表锁

操作对象是表，属于开销最低但是并发也最低的策略，如果事务T对表加读锁，那么其他事务可读不可写，操作对象为整个表。

行级别锁

将表中的数据进行加锁，不会锁整个表，仅锁住使用的数据，对于该表的其他数据来说，其他事务仍可以对数据进行操作，并发高但是开销大

MVCC（InnoDB）

属于锁的妥协机制，在很多情况下避免使用锁，提供更小的开销，允许非阻塞式读取，

优点：在读取数据时，对写数据的锁要求不冲突，不会阻塞写，也不会阻塞读。

事务保存点

设置事务保存点：SAVEPOINT 保存点名

回滚至指定保存点：ROLLBACK TO 保存点名

保存点只适用于事务，对于事务之外的数据没有任何作用

mysql> CREATE TABLE student(id INT NOT NULL PRIMARY KEY,grade DECIMAL(8,2));
/*创建测试表*/

mysql> INSERT INTO student VALUES(1,80.2),(2,90);
/*创建测试数据*/

mysql> BEGIN;        /*开始事务*/

mysql> INSERT INTO student VALUES(3,84.9);    /*插入一条数据*/

mysql> SAVEPOINT A;            /*将上一条数据添加保存点A*/

mysql> INSERT INTO student VALUES(4,95.5);        /*插入一条数据*/

mysql> SAVEPOINT B;        /*将开始事务到上条数据添加保存点B*/

mysql> INSERT INTO student VALUES(5,50);    /*插入一条数据*/

mysql> SAVEPOINT C;        /*从开始事务到上条数据添加保存点C*/

mysql> INSERT INTO student VALUES(6,1);    /*在插入一条数据*/

mysql> SELECT * FROM  student;        /*查看表中数据*/
+----+-------+
| id | grade |
+----+-------+
|  1 | 80.20 |
|  2 | 90.00 |
|  3 | 84.90 |
|  4 | 95.50 |
|  5 | 50.00 |
|  6 |  1.00 |
+----+-------+
6 rows in set (0.00 sec)

mysql> ROLLBACK TO C;        /*回滚到保存点C*/

mysql> SELECT * FROM  student;
+----+-------+
| id | grade |
+----+-------+
|  1 | 80.20 |
|  2 | 90.00 |
|  3 | 84.90 |
|  4 | 95.50 |
|  5 | 50.00 |
+----+-------+
5 rows in set (0.00 sec)

mysql> ROLLBACK TO A;            /*回滚到保存点A*/

mysql> SELECT * FROM  student;    
+----+-------+
| id | grade |
+----+-------+
|  1 | 80.20 |
|  2 | 90.00 |
|  3 | 84.90 |
+----+-------+
mysql> ROLLBACK TO B;        /*保存点B已经消失*/        
ERROR 1305 (42000): SAVEPOINT C does not exist