数据库事务之mysql

Michael_lcf

已于 2023-03-06 21:17:55 修改

阅读量141

点赞数 2

分类专栏： mysql 文章标签： MYSQL事务详解

于 2020-10-19 11:00:26 首次发布

^_^ ^_^ ^_^

本文链接：https://blog.csdn.net/Michael_lcf/article/details/109139322

版权

mysql 专栏收录该内容

28 篇文章 2 订阅

订阅专栏

1 数据库并发问题及概念

数据库并发场景有三种，分别为：
读-读：不存在任何问题，也不需要并发控制。
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读。
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失。

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读：（Dirty Read）对于两个事务T1、T2；T1 读取了已经被T2 更新但还没有被提交的数据；之后，若T2 回滚，T1读取的内容就是临时且无效的。

不可重复读：（Non-Repeatable Read）（针对查询记录内容 update）对于两个事务T1、T2；T1 读取一条id=1的记录，然后T2 更新id=1的记录并提交；之后，T1再次读取数据，此时会话T1两次读取到的数据不同了。

幻读：（Phanton Read）（针对查询结果集不一致 insert delete）对于两个事务T1、T2；T1 读取了数据，然后T2 在插入、删除数据且提交；之后，如果T1 再次读取数据，会产生数据记录数不同的情况。

第一类丢失更新：撤销一个事务的时候，把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销。

第二类丢失更新：它和不可重复读本质上是同一类并发问题，通常将它看成不可重复读的特例。当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改。

2 存储引擎是否支持事务

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.1 mysql自动提交

BEGIN;
	数据库SQL操作......
COMMIT;	
===============================================
START TRANSACTION;
	数据库SQL操作......
COMMIT;	
===============================================
START TRANSACTION;
	数据库SQL操作......
ROLLBACK;
===============================================

SHOW VARIABLES LIKE 'autocommit';
SET autocommit = OFF;
SET autocommit = ON;

默认情况下，如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。

2.2 mysql隐式提交

当我们使用 START TRANSACTION 或者 BEGIN 语句开启了一个事务，或者把系统变量 autocommit = OFF 时，事务就不会自动提交。但是某些特殊的语句会导致事务自动提交，称为隐式提交，包括：

定义或修改数据库对象的数据定义语言（Data definition language，缩写为：DDL）。所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP 等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务。
隐式使用或修改mysql数据库中的表：当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、SET PASSWORD 等语句时也会隐式的提交前边语句所属于的事务。
事务控制或关于锁定的语句：当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务。或者当前的 autocommit 系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。或者使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
加载数据的语句：比如我们使用 LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
其它的一些语句：使用 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET 等语句也会隐式的提交前边语句所属的事务。

2.3 autocommit和START TRANSACTION辨析

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

1、不管autocommit 是1还是0
START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。

2、当autocommit 为 0 时
不管有没有START TRANSACTION。
只有当commit数据才会生效，ROLLBACK后就会回滚。

3、如果autocommit 为1 ，并且没有START TRANSACTION 。
调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

3 事务的ACID(acid)属性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

一个事务与其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

4 查看及设置mysql数据库事务隔离级别

1.查看数据库当前会话的隔离级别
-- mysql5.xx
select @@tx_isolation;
-- mysql8.xx
select @@transaction_isolation;

2.查看数据库当前隔离级别
select @@global.transaction_isolation;

3.设置当前会话事务隔离级别
set session transaction isolation level repeatable read;
SET session TRANSACTION ISOLATION LEVEL Serializable;
（参数可以为：`Read uncommitted`|`Read committed`|`Repeatable read`|`Serializable`）

4.设置系统当前事务隔离级别
set global transaction isolation level repeatable read;
SET global TRANSACTION ISOLATION LEVEL Serializable;
（参数可以为：`Read uncommitted`|`Read committed`|`Repeatable read`|`Serializable`）

4.1 数据库的4种事务隔离级别

隔离级别	描述
READ_UNCOMMITTED	一个事务可以读到其他事务还没有提交的数据。即会产生`脏读、不可重复读、幻读`。
READ_COMMITED	一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，会出现不可重复读、幻读。
REPEATABLE_READ	在数据读出来之后加锁，类似"select * from XXX for update"，明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。REPEATABLE_READ的意思也类似，读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，但是幻读的问题还是无法解决；确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读，但幻读的问题仍然存在。
SERLALIZABLE	各个事务依次执行，可`解决脏读、不可重复读、幻读`；但效率极差；确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可以避免，但性能十分低下。

注意：MySQL在 REPEATABLE READ 隔离级别下，是可以禁止幻读问题的发生的。

事务的隔离级别	脏读	不可重复读	幻读
read uncommitted	`√`	`√`	`√`
read committed	×	`√`	`√`
repeatable read	×	×	`√`
serializable	×	×	×

5 mysql的MVVC

5.1 版本链

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，当创建的表中有主键或者非NULL唯一键时就不会包含row_id列）：
trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
roll_pointer：每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息。

5.2 ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用
SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITTED和
REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下
版本链中的哪个版本是当前事务可见的。
ReadView中主要包含4个比较重要的内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的 id 值。
creator_trx_id：表示生成该ReadView的事务的事务 id。

注意：max_trx_id 并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：
如果被访问版本的 trx_id 属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值小于ReadView中的 min_trx_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值大于ReadView中的 max_trx_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的 trx_id 属性值在ReadView的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

5.3 READ_COMMITED的实现方式

每次读取数据前都生成一个ReadView。

5.4 REPEATABLE_READ

在第一次读取数据时生成一个ReadView。

5.5 MVCC总结

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ 这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

6 mysql的锁

读锁：共享锁、Shared Locks、S锁。
写锁：排他锁、Exclusive Locks、X锁。

——	X锁	S锁
X锁	冲突	冲突
S锁	冲突	不冲突

6.1 读操作

对于普通 SELECT 语句，InnoDB 不会加任何锁

select ... lock in share mode

将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁（会阻塞）

select ... for update

将查找到的数据加上一个X锁，不允许其他事务获取这些记录的S锁和X锁。

6.2 写操作

DELETE：删除一条数据时，先对记录加X锁，再执行删除操作。
INSERT：插入一条记录时，会先加隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问到。
UPDATE：
如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改。
如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再 insert 一条新记录。

隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想来读取这个记录会发现事务id不对应，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式锁。

7 附录

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(10) NOT NULL,
  money decimal(10,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert  into account(id,username,money) values 
(null,'白居易',1000),
(null,'李白',2000);

5.1 脏读演示

第一步：
session 1:

查看事务隔离级别使用
select @@tx_isolation;
修改当前会话事务隔离级别使用
SET session TRANSACTION ISOLATION LEVEL Read uncommitted;
开启事务
SET autocommit=0;
START TRANSACTION;
select * from account;
+----+-----------+---------+
| id | username  | money   |
+----+-----------+---------+
|  1 | 白居易    | 1000.00 |
|  2 | 李白      | 2000.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

session 2:

开启事务
SET autocommit=0;
START TRANSACTION;
update account set money=money-500 where id=1;
update account set money=money+500 where id=2;

第二步：
session 1：

select * from account;
+----+-----------+---------+
| id | username  | money   |
+----+-----------+---------+
|  1 | 白居易    |  500.00 |
|  2 | 李白      | 2500.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

session 2：

回滚结束事务
ROLLBACK;

第三步：
session 1：

mysql> select * from account;
+----+-----------+---------+
| id | username  | money   |
+----+-----------+---------+
|  1 | 白居易    | 1000.00 |
|  2 | 李白      | 2000.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

session 2：

mysql> select * from account;
+----+-----------+---------+
| id | username  | money   |
+----+-----------+---------+
|  1 | 白居易    | 1000.00 |
|  2 | 李白      | 2000.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)

5.2 不可重复读演示

5.3 幻读演示

5.4 第一类丢失更新

5.5 第二类丢失更新

参考：
https://www.jianshu.com/p/8845ddca3b23
https://www.jianshu.com/p/592b2cdbc589

Michael_lcf

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库事务之mysql

1 数据库并发问题及概念2 存储引擎是否支持事务3 事务的ACID(acid)属性4 查看及设置mysql数据库事务隔离级别5 mysql的MVVC6 mysql的锁7 附录5.1 脏读演示5.2 不可重复读演示5.3 幻读演示5.4 第一类丢失更新5.5 第二类丢失更新
复制链接

扫一扫

专栏目录