MySQL InnoDB事务与锁的详细讲解

62次阅读

共计 2665 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要介绍“MySQL InnoDB 事务与锁的详细讲解”，在日常操作中，相信很多人在 MySQL InnoDB 事务与锁的详细讲解问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL InnoDB 事务与锁的详细讲解”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

引题: 为何引入事务?
1 . 数据完整性
2 . 数据安全性
3 . 充分利用系统资源, 提高系统并发处理的能力
1. 事务的特征
事务具有四个特性: 原子性(Atomiocity)、一致性(Consistency)、隔离性(Isolation) 和持久性(Durability), 这四个特性简称 ACID 特性.
1.1 原子性
事务是数据库的逻辑工作单位, 事务中包括的所有操作要么都做, 要么都不做.
1.2 一致性
事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态.
1.3 隔离性
一个事务的执行不能被其他事务干扰. 即一个事务内部的操作及使用的数据对其他
事务是隔离的, 并发执行的各个事务之间互相不干扰.
1.4 持久性
一个事务一旦成功提交, 对数据库中数据的修改就是持久性的. 接下来其他的其他
操作或故障不应该对其执行结果有任何影响.
2. MySQL 的 InnoDB 引擎中事物与锁
2.1 SELECT hellip; hellip; LOCK IN SHARE MODE
会话事务中查找的数据, 加上一个共享锁. 若会话事务中查找的数据已经被其他会话事务加上独占锁的话, 共享锁会等待其结束再加, 若等待时间过长就会显示事务需要的锁等待超时.
2.2 SELECT hellip;.. FOR UPDATE
会话事务中查找的数据, 加上一个读更新琐, 其他会话事务将无法再加其他锁, 必须等待其结束.
2.3 INSERT、UPDATE、DELETE
会话事务会对 DML 语句操作的数据加上一个独占锁, 其他会话的事务都将会等待其释放独占锁.
2.4 gap and next key lock(间隙锁)
InnoDB 引擎会自动给会话事务中的共享锁、更新琐以及独占锁, 需要加到一个区间值域的时候, 再加上个间隙锁(或称范围锁), 对不存在的数据也锁住, 防止出现幻写.
备注:
以上 2.1,2.2,2.3,2.4 中描述的情况, 跟 MySQL 所设置的事务隔离级别也有关系.
3. 四种事务隔离模式
3.1 READ UNCOMMITED
SELECT 的时候允许脏读, 即 SELECT 会读取其他事务修改而还没有提交的数据.
3.2 READ COMMITED
SELECT 的时候无法重复读, 即同一个事务中两次执行同样的查询语句, 若在第一次与第二次查询之间时间段, 其他事务又刚好修改了其查询的数据且提交了, 则两次读到的数据不一致.
3.3 REPEATABLE READ
SELECT 的时候可以重复读, 即同一个事务中两次执行同样的查询语句, 得到的数据始终都是一致的.
3.4 SERIALIZABLE
与可重复读的唯一区别是, 默认把普通的 SELECT 语句改成 SELECT hellip;. LOCK IN SHARE MODE. 即为查询语句涉及到的数据加上共享琐, 阻塞其他事务修改真实数据.
4. 验证事务与锁定示例
接下来, 我们将以 MySQL 中的 InnoDB 引擎, 解释其如何实现 ACID 特性, 不同隔离级别下事务与事务之间的影响. 示例表结构:
CREATE TABLE `account ` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`VACCOUNT_ID` varchar(32) NOT NULL,
`GMT_CREATE` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID ` (`VACCOUNT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
然后向表 account 中写入 10W 条创建日期分布合理的帐号数据, 以方便测试之用.
tx_isolation:SET GLOBAL tx_isolation= read-uncommitted
ID 事务 1 事务 1 输出事务 2 事务 2 输出
1 START TRANSACTION;
2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510
START TRANSACTION;
3
UPDATE account set VACCOUNT_ID= uncommitted where ID =1001;
4
SELECT VACCOUNT_ID from account where ID =1001; uncommitted
5 SELECT VACCOUNT_ID from account where ID =1001; uncommitted
6
ROLLBACK;
7 SELECT VACCOUNT_ID from account where ID =1001; caimao101510
8 COMMIT;
tx_isolation:SET GLOBAL tx_isolation= read-committed
ID 事务 1 事务 1 输出事务 2 事务 2 输出
1 START TRANSACTION;
2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510
3
START TRANSACTION;
4
UPDATE account set VACCOUNT_ID= uncommitted where ID =1001;
5
SELECT VACCOUNT_ID from account where ID =1001; uncommitted
6 SELECT VACCOUNT_ID from account where ID =1001; caimao101510
7
COMMIT;
8 SELECT VACCOUNT_ID from account where ID =1001; uncommitted
9 COMMIT;
tx_isolation:SET GLOBAL tx_isolation= REPEATABLE-READ