一篇教会你MySQL事务

百万拳去见

已于 2024-04-11 17:02:13 修改

阅读量764

点赞数 24

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-04-11 16:56:36 首次发布

本文链接：https://blog.csdn.net/SPNOS__WCY/article/details/137633907

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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

在MySQL中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。

在MySQL中只有数据库引擎InnoDB才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行成功，要么全部执行失败
事务用来管理insert，update，dalete语句

事务的特性：ACID

原子性(Atomicity):一个事务中的所有操作，要么全部成功，要么全部失败，不会结束在中间某个环节，事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consistency):在事务开始之前和事务结束之后，数据库的完整性没有没破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性(Isolation):数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交(Read uncommitted)、读提交(Read committed)、可重复读(Repeatable read)、串行化(Serializable)
持久性(Durability):事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

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

事务控制语句

begin或start transaction：显式地开启一个事务；
commit：提交事务，并使已对数据库进行地所有修改成为永久性地；
rollback：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
savepoint identifier：savepoint允许在事务中创建一个保存点，一个事务中可以有多个saveppint
release savepoint identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常’；
rollback to identifier：把事务回滚到标记点；
set transaction：用来设置事务的隔离级别

事务的隔离级别

如果不考虑隔离性，就会产生以下问题

脏读：指一个事务读取到了另一个事务未提交的数据
不可重复读：指事务中两次相同的查询查询到了不同的结果
幻读：指在一个事务内读取到了别的事务插入的数据，导致前后读取的不一样

幻读和不可重复读的区别：

不可重复读：是同一条SQL语句查询的内容不同（内容被修改了）
幻读：查询的结果条数不同了

幻读的影响：会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行
幻读产生的原因：行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录
如何解决幻读：将两行记录间的空隙加上锁，阻止新纪录的插入，这个锁称为间隙锁

事务的隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeated read	×	×	√
serializable	×	×	×

在级别read uncommitted 脏读、不可重复读、幻读都有可能发生，在read committed 不可重复读，幻读可能会发生，在repeatable read 幻读可能发生，在serializable不论脏读、不可重复读还是幻读都不可能发生

实例：begin，delete，commit

mysql> select * from zone;
+------+-----------+--------+
| id   | parent_id | name   |
+------+-----------+--------+
|    1 |      NULL | 中国   |
|    2 |         1 | 陕西   |
|    3 |         1 | 河南   |
|    4 |         2 | 西安   |
|    5 |         2 | 咸阳   |
|    6 |         3 | 周口   |
|    7 |         4 | 雁塔   |
|    8 |         4 | 未央   |
|    9 |         6 | 沈丘   |
|   10 |      NULL | 美国   |
|   11 |        10 | 纽约   |
|   12 |         4 | 长安   |
+------+-----------+--------+
#开启一个事务
mysql> begin;

#执行delete操作        
mysql> delete from zone where id in (8,9,12);

#提交使其永久化
mysql> commit;

实例：start transaction、savepoint、rollback to、rollback

mysql> select * from zone;
+------+-----------+--------+
| id   | parent_id | name   |
+------+-----------+--------+
|    1 |      NULL | 中国   |
|    2 |         1 | 陕西   |
|    3 |         1 | 河南   |
|    4 |         2 | 西安   |
|    5 |         2 | 咸阳   |
|    6 |         3 | 周口   |
|    7 |         4 | 雁塔   |
|   10 |      NULL | 美国   |
|   11 |        10 | 纽约   |
+------+-----------+--------+
#开启一个事务
mysql> start transaction;

# 插入数据
mysql> insert into zone values(8,4,'未央区');

#设置标记点
mysql> savepoint point1;

#插入数据
mysql> insert into zone values(9,4,'雁塔区');

#再次查询
mysql> select * from zone;
+------+-----------+-----------+
| id   | parent_id | name      |
+------+-----------+-----------+
|    1 |      NULL | 中国      |
|    2 |         1 | 陕西      |
|    3 |         1 | 河南      |
|    4 |         2 | 西安      |
|    5 |         2 | 咸阳      |
|    6 |         3 | 周口      |
|    7 |         4 | 雁塔      |
|   10 |      NULL | 美国      |
|   11 |        10 | 纽约      |
|    8 |         4 | 未央区    |
|    9 |         4 | 雁塔区    |
+------+-----------+-----------+

#回滚到标记1
mysql> rollback to point1;

#再次查询
mysql> select * from zone;
+------+-----------+-----------+
| id   | parent_id | name      |
+------+-----------+-----------+
|    1 |      NULL | 中国      |
|    2 |         1 | 陕西      |
|    3 |         1 | 河南      |
|    4 |         2 | 西安      |
|    5 |         2 | 咸阳      |
|    6 |         3 | 周口      |
|    7 |         4 | 雁塔      |
|   10 |      NULL | 美国      |
|   11 |        10 | 纽约      |
|    8 |         4 | 未央区    |
+------+-----------+-----------+

#rollback回滚到事务开始前，并结束此次事务
mysql> rollback;

#结束事务之后再查询
mysql> select * from zone;
+------+-----------+--------+
| id   | parent_id | name   |
+------+-----------+--------+
|    1 |      NULL | 中国   |
|    2 |         1 | 陕西   |
|    3 |         1 | 河南   |
|    4 |         2 | 西安   |
|    5 |         2 | 咸阳   |
|    6 |         3 | 周口   |
|    7 |         4 | 雁塔   |
|   10 |      NULL | 美国   |
|   11 |        10 | 纽约   |
+------+-----------+--------+

#查询当前隔离级别
mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

#设置隔离级别 read uncommitted
set session transaction isolation level read uncommitted;

#设置隔离级别 read committed
set session transaction isolation level read committed;

#设置隔离级别 repeatable read
set global transaction isolation level repeatable read;

#设置隔离级别 serializable
set gloabl transaction isolation level serializable;

global和session的区别：

当设置global时：只对执行完该语句之后产生的会话生效
当设置session时：对当前会话后续的所有事务有效，该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务，如果在事务之间执行，则对后续的事务生效