MySQL学习系列
为什么需要事务
事务是数据库管理系统（DBMS） 执行过程中的一个逻辑单位（不可再进行分割） ， 由一个有限的数据库 *** 作序列构成（多个 DML 语句， select 语句不包含
事务） ， 要不全部成功， 要不全部不成功。
A 给 B 要划钱， A 的账户-1000 元， B 的账户就要+1000 元， 这两个 update 语句必须作为一个整体来执行， 不然 A 扣钱了， B 没有加钱这种情况就是错误的。 那么事务就可以保证 A 、 B 账户的变动要么全部一起发生， 要么全部一起不发生。
事务特性
事务应该具有 4 个属性： 原子性、 一致性、 隔离性、 持久性。 这四个属性通
常称为 ACID 特性。
原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）
1. 原子性（atomicity）
一个事务必须被视为一个不可分割的最小单元， 整个事务中的所有 *** 作要么全部提交成功， 要么全部失败， 对于一个事务来说， 不能只执行其中的一部分 *** 作。 比如：
King 老师借给大飞老师生活费：
1.King 老师工资卡扣除 500 元
2.大飞老师工资卡增加 500
整个事务的 *** 作要么全部成功， 要么全部失败， 不能出现 King 老师工资卡扣除， 但是大飞老师工资卡不增加的情况。 如果原子性不能保证， 就会很自然的出现一致性问题。
2. 一致性（consistency）
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态， 在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏。
King 老师借给大飞老师生活费：
1.King 老师工资卡扣除 500 元
2.大飞老师工资卡增加 500
扣除的钱（-500） 与增加的钱（500） 相加应该为 0， 或者说 King 老师和大飞老师的账户的钱加起来， 前后应该不变。
3. 持久性（durability）
一旦事务提交， 则其所做的修改就会永久保存到数据库中。 此时即使系统崩溃， 已经提交的修改数据也不会丢失。
4. 隔离性（isolation）
一个事务的执行不能被其他事务干扰。 即一个事务内部的 *** 作及使用的数据对并发的其他事务是隔离的， 并发执行的各个事务之间不能互相干扰。
如果隔离性不能保证， 会导致什么问题？
King 老师借给大飞老师生活费， 借了两次， 每次都是 500， King 老师的卡里开始有 1200， 大飞老师的卡里开始有 300， 从理论上， 借完后， King 老师的卡里有 200， 大飞老师的卡里应该有 1300。
我们将 King 老师向大飞老师同时进行的两次转账 *** 作分别称为 T1 和 T2， 在现实世界中 T1 和 T2 是应该没有关系的， 可以先执行完 T1， 再执行 T2， 或者先执行完 T2， 再执行 T1， 结果都是一样的。 但是很不幸， 真实的数据库中 T1 和T2 的 *** 作可能交替执行的， 执行顺序就有可能是：
如果按照上图中的执行顺序来进行两次转账的话， 最终我们看到， King 老师的账户里还剩 700 元钱， 相当于只扣了 500 元钱， 但是大飞老师的账户里却成了1300 元钱， 多出现了 500 元， 这银行岂不是要亏死了？
所以对于现实世界中状态转换对应的某些数据库 *** 作来说， 不仅要保证这些 *** 作以原子性的方式执行完成， 而且要保证其它的状态转换不会影响到本次状态转换， 这个规则被称之为隔离性。
事务并发引起的问题
我们知道 MySQL 是一个客户端／ 服务器架构的软件， 对于同一个服务器来说， 可以有若干个客户端与之连接， 每个客户端与服务器连接上之后， 就可以称之为一个会话（Session） 。 每个客户端都可以在自己的会话中向服务器发出请求语句， 一个请求语句可能是某个事务的一部分， 也就是对于服务器来说可能同时处理多个事务。
在上面我们说过事务有一个称之为隔离性的特性， 理论上在某个事务对某个 数据进行访问时， 其他事务应该进行排队， 当该事务提交之后， 其他事务才可以 继续访问这个数据， 这样的话并发事务的执行就变成了串行化执行。 但是对串行化执行性能影响太大， 我们既想保持事务的一定的隔离性， 又想 让服务器在处理访问同一数据的多个事务时性能尽量高些， 当我们舍弃隔离性的时候， 可能会带来什么样的数据问题呢？
1. 脏读
当一个事务读取到了另外一个事务修改但未提交的数据， 被称为脏读。
事务 2 修改了一行记录， 但是没有提交。 然后事务 1 读取到了未提交的数据，如果事务 2 回滚其更改的数据或者再次更新， 那么在事务 1 中看到的记录可能就是错误的。 事务 1 读取到了 King 老师余额为 1500 的记录， 但是事务 2 执行了回滚 *** 作， 这时并不存在 King 老师余额为 1500 记录。
2. 不可重复读
当事务内相同的记录被检索两次， 且两次得到的结果不同时， 此现象称为不可重复读。
事务 2 对记录做了修改并提交成功， 这意味着修改的记录对其他事务是可见的，因此事务 1 两次读取的 money 值不同。
3. 幻读
在事务执行过程中， 另一个事务将新记录添加到正在读取的事务中时， 会发生幻读。
当事务 1 重复执行 SELECT… WHERe 语句时， 在这期间事务 2 执行 INSERT 语句插入了满足 where 条件的新记录。于是事务 1 执行两次一模一样的 SELECT… WHERe， 返回却是两组不同的记录。
有的同学会有疑问， 那如果事务 2 中是删除了符合的记录而不是插入新记录， 那事务 1 中之后再根据条件读取的记录变少了， 这种现象算不算幻读呢？ 明确说 一下， 在 MySQL 中这种现象不属于幻读， 幻读强调的是一个事务按照某个相同 条件多次读取记录时， 后读取时读到了之前没有读到的记录。
那对于先前已经读到的记录， 之后又读取不到这种情况， 算啥呢？ 其实这相当于对每一条记录都发生了不可重复读的现象。 幻读只是重点强调了读取到了之前读取没有获取到的记录。
SQL 标准中的四种隔离级别
我们上边介绍了几种并发事务执行过程中可能遇到的一些问题， 这些问题也有轻重缓急之分， 我们给这些问题按照严重性来排一下序：
脏读 > 不可重复读 > 幻读
我们上边所说的舍弃一部分隔离性来换取一部分性能在这里就体现在： 设立一些隔离级别， 隔离级别越低， 越严重的问题就越可能发生。 有一帮人（并不是设计 MySQL 的大叔们） 制定了一个所谓的 SQL 标准， 在标准中设立了 4 个隔离级别：
READ UNCOMMITTED： 未提交读。
READ COMMITTED： 已提交读。
REPEATABLE READ： 可重复读。
SERIALIZABLE： 可串行化。
SQL 标准中规定， 针对不同的隔离级别， 并发事务可以发生不同严重程度的
问题， 具体情况如下：
也就是说：
READ UNCOMMITTED 隔离级别下， 可能发生脏读、 不可重复读和幻读问题。
READ COMMITTED 隔离级别下， 可能发生不可重复读和幻读问题， 但是不可
以发生脏读问题。
REPEATABLE READ 隔离级别下， 可能发生幻读问题， 但是不可以发生脏读和
不可重复读的问题。
SERIALIZABLE 隔离级别下， 各种问题都不可以发生。
MySQL 中的隔离级别
不同的数据库厂商对 SQL 标准中规定的四种隔离级别支持不一样， 比方说 Oracle 就只支持 READ COMMITTED 和 SERIALIZABLE 隔离级别。 本书中所讨论的 MySQL 虽然支持 4 种隔离级别， 但与 SQL 标准中所规定的各级隔离级别允许发 生的问题却有些出入， MySQL 在 REPEATABLE READ 隔离级别下， 是可以禁止幻 读问题的发生的。
MySQL 的默认隔离级别为 REPEATABLE READ， 我们可以手动修改事务的隔离
级别。
查看事务隔离级别有两种方式
mysql> SHOW VARIABLES LIKE 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
我们可以通过下边的语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中的 level 可选值有 4 个：
level: {
REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE
}
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | SERIALIZABLE | +-------------------------+ 1 row in set (0.00 sec) mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec) mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-COMMITTED | +-------------------------+ 1 row in set (0.00 sec) mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED | +-------------------------+ 1 row in set (0.00 sec)
设置事务的隔离级别的语句中， 在 SET 关键字后可以放置 GLOBAL 关键字、SESSION 关键字或者什么都不放， 这样会对不同范围的事务产生不同的影响， 具体如下：
使用 GLOBAL 关键字（在全局范围影响） ：
比方说这样：
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则：
只对执行完该语句之后产生的会话起作用。 当前已经存在的会话无效。
使用 SESSION 关键字（在会话范围影响） ：
比方说这样：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则： 对当前会话的所有后续的事务有效。该语句可以在已经开启的事务中间执行， 但不会影响当前正在执行的事务。如果在事务之间执行， 则对后续的事务有效。
上述两个关键字都不用（只对执行语句后的下一个事务产生影响） ：
比方说这样：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则：只对当前会话中下一个即将开启的事务有效。 下一个事务执行完后， 后续事务将恢复到之前的隔离级别。 该语句不能在已经开启的事务中间执行， 会报错的。
如果我们在服务器启动时想改变事务的默认隔离级别， 可以修改启动参数transaction-isolation 的值， 比方说我们在启动服务器时指定了–transaction-isolation=SERIALIZABLE， 那么事务的默认隔离级别就从原来的REPEATABLE READ 变成了 SERIALIZABLE。
MySQL 事务
1. 事务基本语法
事务开始（三种方式）
begin START TRANSACTION（推荐） begin work
事务回滚
rollback
事务提交
commit
2. 保存点
如果你开启了一个事务， 执行了很多语句， 忽然发现某条语句有点问题， 你 只好使用 ROLLBACK 语句来让数据库状态恢复到事务执行之前的样子， 然后一切 从头再来， 但是可能根据业务和数据的变化， 不需要全部回滚。 所以 MySQL 里 提出了一个保存点（英文： savepoint） 的概念， 就是在事务对应的数据库语句中 打几个点， 我们在调用 ROLLBACK 语句时可以指定会滚到哪个点， 而不是回到最 初的原点。 定义保存点的语法如下：
SAVEPOINT 保存点名称;
当我们想回滚到某个保存点时， 可以使用下边这个语句（下边语句中的单词WORK 和 SAVEPOINT 是可有可无的） ：
ROLLBACK TO [SAVEPOINT] 保存点名称;
不过如果 ROLLBACK 语句后边不跟随保存点名称的话， 会直接回滚到事务执行之前的状态。
如果我们想删除某个保存点， 可以使用这个语句：
RELEASE SAVEPOINT 保存点名称;
savepoint show variables like '%autocommit%'; 自动提交事务是开启的 set autocommit=0; insert into testdemo values(5,5,5); savepoint order_exp; insert into testdemo values(6,6,6); savepoint order_exp_2; insert into testdemo values(7,7,7); savepoint s3; select * from testdemo rollback to savepoint order_exp_2 rollback
3. 隐式提交
当我们使用 START TRANSACTION 或者 BEGIN 语句开启了一个事务， 或者把 系统变量 autocommit 的值设置为 OFF 时， 事务就不会进行自动提交， 但是如果 我们输入了某些语句之后就会悄悄的提交掉， 就像我们输入了 COMMIT 语句了 一样， 这种因为某些特殊的语句而导致事务提交的情况称为隐式提交， 这些会导 致事务隐式提交的语句包括：
执行 DDL
定义或修改数据库对象的数据定义语言（Data definition language， 缩写为：DDL） 。所谓的数据库对象， 指的就是数据库、 表、 视图、 存储过程等等这些东西。当我们使用 CREATE、 ALTER、 DROP 等语句去修改这些所谓的数据库对象时， 就会隐式的提交前边语句所属于的事务， 就像这样：
BEGIN; SELECT ... # 事务中的一条语句 UPDATE ... # 事务中的一条语句 ... # 事务中的其它语句 CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务
隐式使用或修改 mysql 数据库中的表
当我们使用 ALTER USER、 CREATE USER、 DROP USER、 GRANT、 RENAME USER、REVOKE、 SET PASSWORD 等语句时也会隐式的提交前边语句所属于的事务。
事务控制或关于锁定的语句
当我们在一个会话里， 一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时， 会隐式的提交上一个事务，
比如这样：
BEGIN; SELECT ... # 事务中的一条语句 UPDATE ... # 事务中的一条语句 ... # 事务中的其它语句 BEGIN; # 此语句会隐式的提交前边语句所属于的事务
或者当前的 autocommit 系统变量的值为 OFF， 我们手动把它调为 ON 时，也会隐式的提交前边语句所属的事务。
或者使用 LOCK TABLES、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
加载数据的语句
比如我们使用 LOAD DATA 语句来批量往数据库中导入数据时， 也会隐式的提交前边语句所属的事务
关于 MySQL 复制的一些语句
使用 START SLAVE、 STOP SLAVE、 RESET SLAVE、 CHANGE MASTER TO 等语句时也会隐式的提交前边语句所属的事务。
其它的一些语句
使用 ANALYZE TABLE、 CACHE INDEX、 CHECK TABLE、 FLUSH、 LOAD INDEX INTO CACHE、 OPTIMIZE TABLE、 REPAIR TABLE、 RESET 等语句也会隐式的提交前边语句所属的事务。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）