devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

【MySQL】事务及其隔离性/隔离级别_数据库事务隔离

作者：知新_RL | 2024-07-06 23:15:35

赞

踩

数据库事务隔离

需要云服务器等云产品来学习Linux的同学可以移步/-->腾讯云<--/-->阿里云<--/-->华为云<--/官网，轻量型云服务器低至112元/年，新用户首次下单享超低折扣。

 目录

一、事务的概念

1、事务的四种特性

2、事务的作用

3、存储引擎对事务的支持

4、事务的提交方式

二、事务的启动、回滚与提交

1、准备工作：调整MySQL的默认隔离级别为最低/创建测试表

2、事务的启动、回滚与提交

3、启动事务后未commit，但是客户端崩了，MySQL会自动回滚

三、事务的隔离级别

1、隔离性

2、隔离级别

3、隔离性的查看和设置

3.1隔离性的查看

3.2设置隔离级别

4、四种隔离级别详解

4.1读未提交【Read Uncommitted】

4.2读提交【Read Committed】

4.3可重复读【Repeatable Read】

4.4串行化【Serializable】

一、事务的概念

1、事务的四种特性

事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。例如银行转账操作，上层看来是一个单纯的转账操作，但是下层却需要一条或多条SQL语句来完成转账操作，这一组SQL是一个整体，被称为事务。事务还规定不同的客户端看到的数据是不相同的。

一个 MySQL 数据库，可不止你一个事务在运行，同一时刻，有大量的请求被包装成事务，向 MySQL 服务器发起事务处理请求。如果多名用户都访问同样的表数据，在不加保护的情况，绝对会出现问题。并且事务由多条 SQL 构成，也会存在执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

所以一个完整的事务，绝对不是简单的 sql 集合，还需要满足如下四个属性：

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

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

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

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

上面四个属性，可以简称为 ACID 。

原子性（Atomicity，或称不可分割性）

一致性（Consistency）

隔离性（Isolation，又称独立性）

持久性（Durability）。

2、事务的作用

当程序员在编写上层代码逻辑访问数据库时，事务能够简化编程时需要考虑的多种细节问题，例如我们在使用事务时，要么提交要么回滚，不用去担心网络异常、服务器宕机等问题。

3、存储引擎对事务的支持

在 MySQL 中只有Innodb支持事务，而MyISAM不支持。

4、事务的提交方式

事务的提交方式分为自动提交和手动提交。

自动提交：

  1. --查看MySQL事务的提交方式发现是自动提交
  2. mysql> show variables like 'autocommit';
  3. +---------------+-------+
  4. | Variable_name | Value |
  5. +---------------+-------+
  6. | autocommit    | ON    |
  7. +---------------+-------+
  8. 1 row in set (0.02 sec)

使用set改变MySQL的事务提交方式： 

  1. --将MySQL的事务提交方式修改为禁止自动提交
  2. mysql> set autocommit=0;
  3. Query OK, 0 rows affected (0.01 sec)
  4.  
  5. mysql> show variables like 'autocommit';
  6. +---------------+-------+
  7. | Variable_name | Value |
  8. +---------------+-------+
  9. | autocommit    | OFF   |
  10. +---------------+-------+
  11. 1 row in set (0.00 sec)
  12.  
  13. --将MySQL的事务提交方式修改为开启自动提交
  14. mysql> set autocommit=1;
  15. Query OK, 0 rows affected (0.00 sec)
  16.  
  17. mysql> show variables like 'autocommit';
  18. +---------------+-------+
  19. | Variable_name | Value |
  20. +---------------+-------+
  21. | autocommit    | ON    |
  22. +---------------+-------+
  23. 1 row in set (0.00 sec)

二、事务的启动、回滚与提交

1、准备工作：调整MySQL的默认隔离级别为最低/创建测试表

为了看到演示现象，先将MySQL的默认隔离级别设置成读未提交（最低）后退出重启：

  1. mysql> set global transaction isolation level read uncommitted;
  2. Query OK, 0 rows affected (0.00 sec)
  3. --修改默认隔离级别生效
  4. mysql> select @@tx_isolation;
  5. +------------------+
  6. | @@tx_isolation   |
  7. +------------------+
  8. | READ-UNCOMMITTED |
  9. +------------------+
  10. 1 row in set, 1 warning (0.00 sec)

创建测试表：

  1. create table if not exists account(
  2. id int primary key,
  3. name varchar(50) not null default '',
  4. blance decimal(10,2) not null default 0.0
  5. )ENGINE=InnoDB DEFAULT CHARSET=UTF8;

2、事务的启动、回滚与提交

使用者：jly

  1. --1、查看当前MySQL在线用户
  2. mysql> show processlist;--开始一个事务也可以用begin
  3. +-----+------+-----------+------------------+---------+------+----------+------------------+
  4. | Id  | User | Host      | db               | Command | Time | State    | Info             |
  5. +-----+------+-----------+------------------+---------+------+----------+------------------+
  6. | 474 | jly  | localhost | transaction_data | Query   |    0 | starting | show processlist |
  7. | 475 | jly  | localhost | NULL             | Sleep   |  132 |          | NULL             |
  8. | 476 | root | localhost | NULL             | Sleep   |    5 |          | NULL             |
  9. +-----+------+-----------+------------------+---------+------+----------+------------------+
  10. 3 rows in set (0.00 sec)
  11. --2、先确认事务的提交方式为自动提交
  12. mysql> show variables like 'autocommit';
  13. +---------------+-------+
  14. | Variable_name | Value |
  15. +---------------+-------+
  16. | autocommit    | ON    |
  17. +---------------+-------+
  18. 1 row in set (0.02 sec)
  19. --3、开始一个事务，start transaction执行之后，后方的SQL将处于同一个事务
  20. mysql> start transaction;
  21. Query OK, 0 rows affected (0.00 sec)
  22. --4、创建一个保存节点save1
  23. mysql> savepoint save1;
  24. Query OK, 0 rows affected (0.00 sec)
  25. --5、在当前表中插入一条数据
  26. mysql> insert into account values(1,'张三',1234.5);
  27. Query OK, 1 row affected (0.00 sec)
  28. --6、创建一个保存节点save2
  29. mysql> savepoint save2;
  30. Query OK, 0 rows affected (0.00 sec)
  31. --7、在当前表中再次插入一条数据
  32. mysql> insert into account values(2,'李四',998.561);
  33. Query OK, 1 row affected, 1 warning (0.00 sec)
  34. --8、创建一个保存节点save3
  35. mysql> savepoint save3;
  36. Query OK, 0 rows affected (0.00 sec)
  37. --9、在当前表中再次插入一条数据
  38. mysql> insert into account values(3,'王五',653.25);
  39. Query OK, 1 row affected (0.00 sec)
  40. --10、回滚到保存点save3
  41. mysql> rollback to save3;--如果全部回滚，可以直接rollback;
  42. Query OK, 0 rows affected (0.00 sec)
  43. --11、提交以结束本次事务
  44. mysql> commit;
  45. Query OK, 0 rows affected (0.00 sec)

使用者：root

  1. --当上方用户执行完第三步时，让另一个用户开始一个事务
  2. mysql> begin;
  3. Query OK, 0 rows affected (0.00 sec)
  4. --当上方用户执行完第五步时，root用户查看表发现表中存在一条数据
  5. mysql> select* from account;
  6. +----+--------+---------+
  7. | id | name   | blance  |
  8. +----+--------+---------+
  9. |  1 | 张三   | 1234.50 |
  10. +----+--------+---------+
  11. 1 row in set (0.00 sec)
  12. --当上方用户执行完第九步时，root用户查看表发现表中存在三条数据
  13. mysql> select* from account;
  14. +----+--------+---------+
  15. | id | name   | blance  |
  16. +----+--------+---------+
  17. |  1 | 张三   | 1234.50 |
  18. |  2 | 李四   |  998.56 |
  19. |  3 | 王五   |  653.25 |
  20. +----+--------+---------+
  21. 3 rows in set (0.00 sec)
  22. --当上方用户执行完第十步时，root用户查看表发现王五没了
  23. mysql> select* from account;
  24. +----+--------+---------+
  25. | id | name   | blance  |
  26. +----+--------+---------+
  27. |  1 | 张三   | 1234.50 |
  28. |  2 | 李四   |  998.56 |
  29. +----+--------+---------+
  30. 2 rows in set (0.00 sec)
  31. --提交以结束本次事务
  32. mysql> commit;
  33. Query OK, 0 rows affected (0.00 sec)

1、如果在一个事务中，用户最终commit提交了本次事务，那么本次事务发生的数据修改将被MySQL持久化，想回滚已经没机会了。

2、如果从save3回滚到save1，发现滚过头了，是不能再从save1往前滚到save3的，MySQL会提示SAVEPOINT s3 does not exist。

3、启动事务后未commit，但是客户端崩了，MySQL会自动回滚

使用者：jly

  1. --1、启动事务并插入数据
  2. mysql> begin;
  3. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select* from account;
  5. Empty set (0.01 sec)
  6. mysql> insert into account values(1,'张三',1234.5);
  7. Query OK, 1 row affected (0.01 sec)
  8. mysql> insert into account values(2,'李四',998.561);
  9. Query OK, 1 row affected, 1 warning (0.02 sec)
  10. mysql> insert into account values(3,'王五',653.25);
  11. Query OK, 1 row affected (0.00 sec)
  12. --2、未commit，使用ctrl+\终止MySQL
  13. mysql> Aborted

使用者：root

  1. --1、启动事务
  2. mysql> begin;
  3. --当上方用户执行完第一步时，查看表
  4. mysql> select* from account;
  5. +----+--------+---------+
  6. | id | name   | blance  |
  7. +----+--------+---------+
  8. |  1 | 张三   | 1234.50 |
  9. |  2 | 李四   |  998.56 |
  10. |  3 | 王五   |  653.25 |
  11. +----+--------+---------+
  12. 3 rows in set (0.00 sec)
  13. --当上方用户执行完第二步时，查看表，发现数据回退
  14. mysql> select* from account;
  15. Empty set (0.00 sec)

启动事务后未commit，但是客户端崩了或者自己强行关闭客户端，MySQL会自动回滚

1、autocommit并不会影响begin后启动的事务的提交，使用begin后必须输入commit才能使数据持久化。

2、单句SQL本质就是事务，它的持久化和autocommit有关，autocommit为ON，表示单SQL无需手动commit自动提交，否则需要手动commit令数据持久化。（select有特殊情况，因为MySQL 有 MVCC ）

3、事务可以手动回滚，同时，当操作异常，MySQL会自动回滚

4、我们能看到事务本身的原子性(rollback)，持久性(commit)

三、事务的隔离级别

1、隔离性

1、MySQL服务可能会同时被多个客户端进程(线程)访问，访问的方式以事务方式进行。

2、一个事务可能由多条SQL构成，也就意味着，任何一个事务，都有执行前，执行中，执行后的阶段。而所谓的原子性，其实就是让用户层，要么看到执行前，要么看到执行后。执行中出现问题，可以随时回滚。所以单个事务，对用户表现出来的特性，就是原子性。

3、但是所有事务都要有个执行过程，那么在多个事务各自执行多个SQL的时候，就还是有可能会出现互相影响的情况。比如：多个事务同时访问同一张表，甚至同一行数据。

4、数据库中，为了保证事务执行过程中尽量不受干扰，就有了一个重要特征：隔离性 。

5、数据库中，允许事务受不同程度的干扰，就有了一种重要特征：隔离级别。

2、隔离级别

这四点都是关于读写并发的情况：

读未提交【Read Uncommitted】： 在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等，我们上面为了做实验方便，用的就是这个隔离性。

读提交【Read Committed】：该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select，可能得到不同的结果。

可重复读【Repeatable Read】： 这是 MySQL 默认的隔离级别，它确保同一个事务，在执行中，多次读取操作数据时，会看到同样的数据行。但是有的数据库会有幻读问题。（MySQL不会）

串行化【Serializable】: 这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，。但是可能会导致超时和锁竞争（这种隔离级别太极端，实际生产基本不使用）

隔离级别如何实现：隔离，基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的。常见有，表锁，行锁，读锁，写锁，间隙锁(GAP),Next-Key锁(GAP+行锁)等。 

3、隔离性的查看和设置

3.1隔离性的查看

  1. --查看全局的隔离级别
  2. mysql> select @@global.tx_isolation;
  3. +-----------------------+
  4. | @@global.tx_isolation |
  5. +-----------------------+
  6. | READ-UNCOMMITTED      |
  7. +-----------------------+
  8. 1 row in set, 1 warning (0.02 sec)
  9.  
  10. --查看当前会话的隔离级别
  11. mysql> select @@session.tx_isolation;
  12. +------------------------+
  13. | @@session.tx_isolation |
  14. +------------------------+
  15. | READ-UNCOMMITTED       |
  16. +------------------------+
  17. 1 row in set, 1 warning (0.00 sec)
  18.  
  19. --默认同上
  20. mysql> select @@tx_isolation;
  21. +------------------+
  22. | @@tx_isolation   |
  23. +------------------+
  24. | READ-UNCOMMITTED |
  25. +------------------+
  26. 1 row in set, 1 warning (0.00 sec)

当前会话的session的隔离级别是根据全局global的隔离级别来的。 

3.2设置隔离级别

语法：

  1. --设置当前会话 or 全局隔离级别语法
  2. SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
  3. COMMITTED | REPEATABLE READ | SERIALIZABLE}

设置会话的隔离级别为读提交： 

  1. mysql> set session transaction isolation level read committed;
  2. Query OK, 0 rows affected (0.00 sec)
  3. --修改后，全局隔离级别没有变，会话隔离级别被修改为读提交
  4. mysql> select @@global.tx_isolation;
  5. +-----------------------+
  6. | @@global.tx_isolation |
  7. +-----------------------+
  8. | READ-UNCOMMITTED      |
  9. +-----------------------+
  10. 1 row in set, 1 warning (0.00 sec)
  11.  
  12. mysql> select @@session.tx_isolation;
  13. +------------------------+
  14. | @@session.tx_isolation |
  15. +------------------------+
  16. | READ-COMMITTED         |
  17. +------------------------+
  18. 1 row in set, 1 warning (0.00 sec)

将隔离级别修改为读提交，此时其他用户的隔离级别是不会被修改的，这修改的仅仅是当前用户的的隔离级别。

重新登录后，会话的隔离级别会按照全局隔离级别重新配置。

设置全局的隔离级别为串行化：

  1. mysql> set global transaction isolation level SERIALIZABLE;
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. --修改全局的隔离级别只影响全局的，当前会话和默认的隔离级别不变
  5. mysql> select @@global.tx_isolation;
  6. +-----------------------+
  7. | @@global.tx_isolation |
  8. +-----------------------+
  9. | SERIALIZABLE          |
  10. +-----------------------+
  11. 1 row in set, 1 warning (0.00 sec)
  12.  
  13. mysql> select @@session.tx_isolation;
  14. +------------------------+
  15. | @@session.tx_isolation |
  16. +------------------------+
  17. | READ-COMMITTED         |
  18. +------------------------+
  19. 1 row in set, 1 warning (0.00 sec)
  20.  
  21. mysql> select @@tx_isolation;
  22. +----------------+
  23. | @@tx_isolation |
  24. +----------------+
  25. | READ-COMMITTED |
  26. +----------------+
  27. 1 row in set, 1 warning (0.00 sec)

当一个会话修改了全局隔离级别，将会同时修改其他所有会话的全局隔离级别。

后续登录时，会话和默认的隔离级别将会引用全局隔离级别的设置。

尽量保证隔离级别一致，闲的没事不要改事务的隔离级别。

4、四种隔离级别详解

4.1读未提交【Read Uncommitted】

  1. --先设置当前的全局隔离级别为读未提交，再退出MySQL重新登录一下让其生效
  2. mysql> set global transaction isolation level read uncommitted;
  3. Query OK, 0 rows affected (0.01 sec)

本文第二章就是用读未提交来举例的。

在多个并行的会话中启动事务，一个事务在改动数据库哪怕没有commit提交，其他事务也是能够实时的看到它修改的数据。一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种不合理的现象叫做脏读(dirty read)

读未提交的其他不合理现象还有不可重复读、幻读

读未提交几乎没有加锁，虽然效率高，但是问题太多，严重不建议采用。

4.2读提交【Read Committed】

使用者：jly

  1. --1、先设置当前的全局隔离级别为读提交，再退出MySQL重新登录一下让其生效
  2. mysql> set global transaction isolation level read committed;
  3. Query OK, 0 rows affected (0.00 sec)
  4. --2、开始事务
  5. mysql> begin;
  6. Query OK, 0 rows affected (0.00 sec)
  7. --3、插入一条数据
  8. mysql> insert into account values (4,'赵六',123);
  9. Query OK, 1 row affected (0.01 sec)
  10. --4、提交
  11. mysql> commit;
  12. Query OK, 0 rows affected (0.02 sec)

使用者：root

  1. --开始事务
  2. mysql> begin;
  3. Query OK, 0 rows affected (0.00 sec)
  4. --当上方用户执行完第3步时，查看表数据，并没有看到插入的赵六的信息
  5. mysql> select* from account;
  6. +----+--------+---------+
  7. | id | name   | blance  |
  8. +----+--------+---------+
  9. |  1 | 张三   | 1234.50 |
  10. |  2 | 李四   |  998.56 |
  11. |  3 | 王五   |  653.25 |
  12. +----+--------+---------+
  13. 3 rows in set (0.01 sec)
  14. --当上方用户执行完第4步时，查看表数据，能够看到赵六的信息
  15. mysql> select* from account;
  16. +----+--------+---------+
  17. | id | name   | blance  |
  18. +----+--------+---------+
  19. |  1 | 张三   | 1234.50 |
  20. |  2 | 李四   |  998.56 |
  21. |  3 | 王五   |  653.25 |
  22. |  4 | 赵六   |  123.00 |
  23. +----+--------+---------+
  24. 4 rows in set (0.02 sec)

通过试验可以发现读提交级别，事务A在commit提交事务之前，所做的修改是不会被其他事务看到的，一旦事务A发起commit之后，其他事务就能看到事务A对数据的修改。这就造成了其他事务在不同的时间点select查看数据库时，会查到不同的数据。这种现象叫不可重复读。（事务中的读取不是原子的）

4.3可重复读【Repeatable Read】

可重复读是MySQL默认的隔离级别。

使用者：jly

  1. --1、重启MySQL
  2. systemctl restart mysqld
  3. --2、重启后查看默认的隔离级别为读提交
  4. mysql> select @@tx_isolation;
  5. +-----------------+
  6. | @@tx_isolation  |
  7. +-----------------+
  8. | REPEATABLE-READ |
  9. +-----------------+
  10. 1 row in set, 1 warning (0.00 sec)
  11. --3、启动事务
  12. mysql> begin;
  13. Query OK, 0 rows affected (0.00 sec)
  14. --4、将id为2的名字（李四）修改为张三
  15. mysql> update account set name='张三' where id=2;
  16. Query OK, 1 row affected (0.00 sec)
  17. Rows matched: 1  Changed: 1  Warnings: 0
  18. mysql> select* from account;
  19. +----+--------+---------+
  20. | id | name   | blance  |
  21. +----+--------+---------+
  22. |  1 | 张三   | 1234.50 |
  23. |  2 | 张三   |  998.56 |
  24. |  3 | 王五   |  653.25 |
  25. |  4 | 赵六   |  123.00 |
  26. +----+--------+---------+
  27. 4 rows in set (0.01 sec)
  28. --5、提交事务
  29. mysql> commit;
  30. Query OK, 0 rows affected (0.00 sec)

使用者：root

  1. --启动事务
  2. mysql> begin;
  3. Query OK, 0 rows affected (0.00 sec)
  4. --当上方用户执行完第4步时，查看表数据，李四并没有被修改为张三
  5. mysql> select *from account;
  6. +----+--------+---------+
  7. | id | name   | blance  |
  8. +----+--------+---------+
  9. |  1 | 张三   | 1234.50 |
  10. |  2 | 李四   |  998.56 |
  11. |  3 | 王五   |  653.25 |
  12. |  4 | 赵六   |  123.00 |
  13. +----+--------+---------+
  14. 4 rows in set (0.00 sec)
  15. --提交事务
  16. mysql> commit;
  17. Query OK, 0 rows affected (0.00 sec)
  18. --当上方用户执行完第5步时，查看表数据，李四被修改为张三
  19. mysql> select *from account;
  20. +----+--------+---------+
  21. | id | name   | blance  |
  22. +----+--------+---------+
  23. |  1 | 张三   | 1234.50 |
  24. |  2 | 张三   |  998.56 |
  25. |  3 | 王五   |  653.25 |
  26. |  4 | 赵六   |  123.00 |
  27. +----+--------+---------+
  28. 4 rows in set (0.00 sec)

在该隔离级别下，MySQL的一个事务在commit提交前，不会影响到另一个事务的读取，该隔离级别被称为可重复读。

一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据，因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题,这会造成大部分内容虽然是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读(phantom read，主要针对插入场景)。很明显，MySQL在RR级别的时候，是解决了幻读问题的(解决的方式是用Next-Key锁 (GAP+行锁)解决的。) 

4.4串行化【Serializable】

串行化就是对所有事务进行加锁，事务的执行全部挨个排队，这就导致了效率低下问题。

开启事务A和事务B，两个事务同时select读取将使用共享锁，不会串行化；事务A中有更新等操作，会阻塞A，直到事务B提交。如果事务A阻塞时间过长，将会由于锁等待超时退出当前事务。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/794270
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号