文章目录
- 1. 事务的概念
- 2. 存储引擎
- 查看mysql支持的存储引擎
- 存储引擎分类
- 3. 事务的属性(ACID)
- 4. 事务的创建
- 隐式事务
- 显式事务
- 5. 隔离机制
- 原因
- 事务隔离级别
- 查看并设置
- 示例
- 参考链接
1. 事务的概念
事务由单独单元的一个或者多个sql语句组成,在这个单元中,每个mysql语句时相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条sql语句一旦执行失败或者产生错误,整个单元将会回滚,也就是所有受到影响的数据将会返回到事务开始以前的状态。如果单元中的所有sql语句均执行成功,则事务被顺利执行。
要么全部执行,要么全部不执行
要么全部成功,要么全部失败
要么全部有效,要么全部无效
2. 存储引擎
查看mysql支持的存储引擎
show engines
存储引擎分类
- Innodb
- MyISAM
- MEMORY
- 详细查看执行引擎
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
3. 事务的属性(ACID)
- 原子性(Atomicity,或称不可分割性)
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
- 一致性(Consistency)
一个事务的执行会使数据从一个一致状态切换到另一个一致的状态。在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性(Isolation,又称独立性)
一个事务的执行不受其他事务的干扰。数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
- 持久性(Durability)
一个事务一旦提交,对数据的修改就是永久的。
4. 事务的创建
隐式事务
- 事务没有明显的开启和结束的标记
- 比如insert、update、delete语句
- 自动提交的属性默认开启
SHOW VARIABLES LIKE ‘autocommit’;
Variable_name | Value |
autocommi | on |
- 存在的问题
比如有多条delete语句,没有办法让这些语句统一为一个事务
显式事务
- 前提:必须先设置自动提交功能为禁用
# 步骤1:开启事务
# 只针对当前事务,当前事务结束后,自动提交功能恢复开启
set autocommit=0;
start transaction;# 可选的
# 步骤2:编写事务中的sql语句(select insert update delete)
select ...
insert ...
update ...
delete ...
# 步骤3:结束事务
commit;# 提交事务
rollback;# 回滚事务
5. 隔离机制
原因
对于同时运行的多个事务,当这些事务访问数据库中的相同的数据时,如果没有采取必要的隔离机制就会产生并发问题: 脏读,不可重复读,幻读
- 脏读(读取未提交数据)
A事务读取B事务尚未提交的数据,此时如果B事务发生错误并执行回滚操作,那么A事务读取到的数据就是脏数据。
这种情况常发生于转账与取款操作中
时间顺序 | 转账事务 | 取款事务 |
1 | 开始事务 | |
2 | 开始事务 | |
3 | 查询账户余额为2000元 | |
4 | 取款1000元,余额被更改为1000元 | |
5 | 查询账户余额为1000元(产生脏读) | |
6 | 取款操作发生未知错误,事务回滚,余额变更为2000元 | |
7 | 转入2000元,余额被更改为3000元(脏读的1000+2000) | |
8 | 提交事务 | |
备注 | 按照正确逻辑,此时账户余额应该为4000元 |
- 不可重复读(前后多次读取,数据内容不一致)
事务A在执行读取操作,由于整个事务A比较大,前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据后,事务B执行更新操作,此时事务A再次读取,发现和之前的数据不一样了,也就是数据不重复了,系统不可以在同一个事务内读取到不重复的数据,称为不可重复读
- 幻读(前后多次读取,数据总量不一致)
事务A从数据表中读取了一个字段,而后事务B在该表中又插入了一些记录,事务A再次读取该字段,发现在同一个事务内前后两次读取到的数据记录总量不一致
事务隔离级别
- MySQL 事务隔离其实是依靠锁来实现的
- MyISAM:表锁、InnoDB:行锁+表锁
- 行锁 / 记录锁:作用在索引上
- 间隙锁 Gap Locks:是开区间,用于阻止其他事务在该间隙内插入新记录,而自身事务是允许在该间隙内插入数据的
- 临键锁 Next-Key Locks:是左开右闭区间,即行锁+间隙锁
- 共享锁 S / 排他锁 X :尽管共享锁/排他锁是行锁,与间隙锁无关,但一个事务在请求共享锁/排他锁时,获取到的结果却可能是行锁,也可能是间隙锁,也可能是临键锁,这取决于数据库的隔离级别以及查询的数据是否存在
行锁 | 兼容性 | 加锁方式 |
S锁:共享锁/读 | 加了S锁的记录,允许其他事务再加S锁,不允许其他事务再加X锁 | select…lock in share mode |
X锁:排他锁/写 | 加了X锁的记录,不允许其他事务再加S锁或者X锁 | select…for update |
- 意向共享锁 IS /意向排他锁 IX:属于表锁,且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件
- 插入意向锁(IIX)
- 自增锁
- 死锁问题:设置锁等待超时参数、优化SQL语句
- 如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表
- 如果要更新记录,应该申请排他锁,而不先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁
- 在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题
- READ UNCOMMITTED
读取未提交的数据,该隔离级别默认不加锁,所以它的并发性最高,一致性最差。
- READ COMMITTED
读取提交的数据。但是,可能出现多次读取的数据结果、数据总量不一致(不可重复读,幻读)
- MySQL 采用了 MVCC (多版本并发控制 Multi-Version Concurrency Control) 的方式,数据每次变化都会产生一个新的版本,版本=数据本身+事务id
- 普通的select不加锁,使用快照读(snapshot read,这是一种不加锁的一致性读,底层使用MVCC来实现),该隔离级别每次执行语句的时候都重新生成一次快照,能防止脏读
- 加锁的select(select … in share mode / select … for update)、update、delete等语句,使用当前读,除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间,其他时刻都只使用行锁,不使用间隙锁(gap lock)、临键锁(next-key lock)
- REPEATABLE READ(InnoDB默认的隔离级别)
可以重复读取,避免了脏读和不可重复度,使用MVCC避免了读数据时的幻读问题,写数据时依旧可能出现幻读。
- 普通的select不加锁,使用快照读(snapshot read,这是一种不加锁的一致性读,底层使用MVCC来实现),该隔离级别在事务开始的时候生成一个当前事务全局性的快照
- 加锁的select(select … in share mode / select … for update)、update、delete等语句,使用当前读,加锁策略取决于它们是否在唯一索引上使用了唯一的查询条件
- 在唯一索引上使用唯一的查询条件,只使用行锁(record lock),而不会封锁记录之间的间隔,即不会使用间隙锁(gap lock)与临键锁(next-key lock)
- 范围查询条件或者是非唯一索引,使用间隙锁与临键锁,锁住索引记录之间的范围,避免范围间插入记录,以避免产生不可重复读和部分幻读
对比 | READ COMMITTED | REPEATABLE READ |
普通select,使用快照读(snapshot read),这是一种不加锁的一致性读,底层使用MVCC来实现 | 允许读已提交的数据,每一次一致性读操作都会重新生成一次快照,避免脏读 | 事务开始的时候生成一个当前事务全局性的快照,避免脏读和不可重复读 |
加锁的select(select … in share mode / select … for update)、update、delete,使用当前读 | 只使用行锁,没法避免不可重复读和幻读 | 加锁策略取决于它们是否在唯一索引上使用了唯一的查询条件,可避免不可重复读和部分幻读 |
- SERIALIZABLE
读的时候加共享锁,也就是其他事务可以并发读,但是不能写。
写的时候加排它锁,其他事务不能并发写也不能并发读。
这是一致性最好,但并发性最差的隔离级别。
所有select语句都会被隐式的转化为select … in share mode,也就是默认上共享读锁(S锁)。所以,如果事务A先执行如下sql之后,会尝试获取所查询行的IS锁(和别的IS、IX锁是兼容的),这时别的事务也能获取这些行的IS锁甚至是S锁。
但是如果接下来,事务A如果update或delete其中的某些行,这时就获取了X锁,别的事务即便是执行普通的select语句也会阻塞,因为它们尝试获取IS锁,但是IS锁和X锁是互斥的,这样就避免了读脏、不可重复读以及幻读,所有事务就只能串行了。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交 | √ | √ | √ |
读已提交 | x | √ | √ |
可重复读 | x | x | √ |
可串行化 | x | x | x |
查看并设置
# 查看当前会话隔离级别
SELECT @@session.transaction_isolation;
# 查看当前系统隔离级别
SELECT @@global.transaction_isolation;
# 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# 设置当前系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
示例
- 设置隔离级别READ-UNCOMMITTED,事务A企图修改数据
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 李四 |
+----+----------+
2 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update stu set stu_name='王五' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 王五 |
+----+----------+
2 rows in set (0.00 sec)
- 此时,有事务B进行读操作
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED |
+---------------------------------+
1 row in set (0.00 sec)
mysql> use test1;
Database changed
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 王五 |
+----+----------+
2 rows in set (0.00 sec)
- 事务A遇到未知错误,进行回滚操作
mysql> rollback;
Query OK, 0 rows affected (0.09 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 此时事务B再进行读操作,出现脏读
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 李四 |
+----+----------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- 提交A、B事务,修改事务隔离级别为READ-COMMITTED
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-COMMITTED |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 李四 |
+----+----------+
2 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update stu set stu_name='赵六' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 赵六 |
+----+----------+
2 rows in set (0.00 sec)
- 此时事务B进行读操作,没有出现脏读
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-COMMITTED |
+---------------------------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 李四 |
+----+----------+
2 rows in set (0.00 sec)
- 但此时若事务A提交事务
mysql> commit;
Query OK, 0 rows affected (0.21 sec)
- 事务B尚未提交,再次读取数据,出现不可重复读
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 赵六 |
+----+----------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.21 sec)
- 提交事务,修改隔离级别为REPEATABLE-READ,A事务继续修改
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE read;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.transaction_isolation;;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 赵六 |
+----+----------+
2 rows in set (0.00 sec)
mysql> update stu set stu_name='孙七' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 孙七 |
+----+----------+
2 rows in set (0.00 sec)
- 此时,事务B在事务A修改数据前后进行两次读操作,没有出现脏读、不可重复读
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 赵六 |
+----+----------+
2 rows in set (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 赵六 |
+----+----------+
2 rows in set (0.00 sec)
- 此时提交A事务,再重新开启A事务
mysql> commit;
Query OK, 0 rows affected (0.11 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+----------+
| id | stu_name |
+----+----------+
| 1 | 张三 |
| 2 | 孙七 |
+----+----------+
2 rows in set (0.00 sec)
- 此时B事务插入一条数据
mysql> insert into stu values(null,'钱八');
Query OK, 1 row affected (0.00 sec)
- A事务将所有数据的 stu_name 统一更新为 cc,超时错误,原因是出现幻读
mysql> update stu set stu_name='cc';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 设置为SERIALIZABLE就可以解决脏读、不可重复读、幻读问题