本文对锁事务并发控制做一个总结, 看了网上很多文章, 描述非常不准确如有与您观点不一致, 欢迎有理有据的拍砖!
mysql 服务器逻辑架构
每个连接都会在 mysql 服务端产生一个线程(内部通过线程池管理线程), 比如一个 select 语句进入, mysql 首先会在查询缓存中查找是否缓存了这个 select 的结果集, 如果没有则继续执行 解析优化执行的过程; 否则会之间从缓存中获取结果集
mysql 并发控制共享锁排他锁
共享锁
共享锁也称为读锁, 读锁允许多个连接可以同一时刻并发的读取同一资源, 互不干扰;
排他锁
排他锁也称为写锁, 一个写锁会阻塞其他的写锁或读锁, 保证同一时刻只有一个连接可以写入数据, 同时防止其他用户对这个数据的读写
锁策略
锁的开销是较为昂贵的, 锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略
mysql 锁策略: talbe lock(表锁)
表锁是 mysql 最基本的锁策略, 也是开销最小的锁, 它会锁定整个表;
具体情况是: 若一个用户正在执行写操作, 会获取排他的写锁, 这可能会锁定整个表, 阻塞其他用户的读写操作;
若一个用户正在执行读操作, 会先获取共享锁读锁, 这个锁运行其他读锁并发的对这个表进行读取, 互不干扰只要没有写锁的进入, 读锁可以是并发读取统一资源的
通常发生在 DDL 语句 DML 不走索引的语句中, 比如这个 DML update table set columnA=A where columnB=B.
如果 columnB 字段不存在索引(或者不是组合索引前缀), 会锁住所有记录也就是锁表如果语句的执行能够执行一个 columnB 字段的索引, 那么会锁住满足 where 的行(行锁)
mysql 锁策略: row lock(行锁)
行锁可以最大限度的支持并发处理, 当然也带来了最大开销, 顾名思义, 行锁的粒度实在每一条行数据
事务
事务就是一组原子性的 sql, 或者说一个独立的工作单元
事务就是说, 要么 mysql 引擎会全部执行这一组 sql 语句, 要么全部都不执行(比如其中一条语句失败的话)
比如, tim 要给 bill 转账 100 块钱:
1. 检查 tim 的账户余额是否大于 100 块;
2.tim 的账户减少 100 块;
3.bill 的账户增加 100 块;
这三个操作就是一个事务, 必须打包执行, 要么全部成功, 要么全部不执行, 其中任何一个操作的失败都会导致所有三个操作不执行回滚
- CREATE DATABASE IF NOT EXISTS employees;
- USE employees;
- CREATE TABLE `employees`.`account` (
- `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
- `p_name` VARCHAR (4),
- `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
- PRIMARY KEY (`id`)
- ) ;
- INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200');
- INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200');
- START TRANSACTION;
- SELECT p_money FROM account WHERE p_name="tim";-- step1
- UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2
- UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3
- COMMIT;
一个良好的事务系统, 必须满足 ACID 特点:
事务的 ACID
A:atomiciy 原子性
一个事务必须保证其中的操作要么全部执行, 要么全部回滚, 不可能存在只执行了一部分这种情况出现
C:consistency 一致性
数据必须保证从一种一致性的状态转换为另一种一致性状态
比如上一个事务中执行了第二步时系统崩溃了, 数据也不会出现 bill 的账户少了 100 块, 但是 tim 的账户没变的情况要么维持原装(全部回滚), 要么 bill 少了 100 块同时 tim 多了 100 块, 只有这两种一致性状态的
I:isolation 隔离性
在一个事务未执行完毕时, 通常会保证其他 Session 无法看到这个事务的执行结果
D:durability 持久性
事务一旦 commit, 则数据就会保存下来, 即使提交完之后系统崩溃, 数据也不会丢失
隔离级别
查看系统隔离级别:
select @@global.tx_isolation;
查看当前会话隔离级别
select @@tx_isolation;
设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;
设置全局系统隔离级别
- SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
- READ UNCOMMITTED(未提交读, 可脏读)
事务中的修改, 即使没有提交, 对其他会话也是可见的
可以读取未提交的数据 脏读 脏读会导致很多问题, 一般不适用这个隔离级别
实例:
-- ------------------------- read-uncommitted 实例 ------------------------------
-- 设置全局系统隔离级别
- SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
- -- Session A
- START TRANSACTION;
- SELECT * FROM USER;
- UPDATE USER SET NAME="READ UNCOMMITTED";
- -- commit;
- -- Session B
- SELECT * FROM USER;
- //SessionB Console 可以看到 Session A 未提交的事物处理, 在另一个 Session 中也看到了, 这就是所谓的脏读
- id name
- 2 READ UNCOMMITTED
- 34 READ UNCOMMITTED
- READ COMMITTED(提交读或不可重复读, 幻读)
一般数据库都默认使用这个隔离级别(mysql 不是), 这个隔离级别保证了一个事务如果没有完全成功(commit 执行完), 事务中的操作对其他会话是不可见的
-- ------------------------- read-cmmitted 实例 ------------------------------
-- 设置全局系统隔离级别
- SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
- -- Session A
- START TRANSACTION;
- SELECT * FROM USER;
- UPDATE USER SET NAME="READ COMMITTED";
- -- COMMIT;
- -- Session B
- SELECT * FROM USER;
- //Console OUTPUT:
- id name
- 2 READ UNCOMMITTED
- 34 READ UNCOMMITTED
- ---------------------------------------------------
-- 当 Session A 执行了 commit,Session B 得到如下结果:
- id name
- 2 READ COMMITTED
- 34 READ COMMITTED
也就验证了 read committed 级别在事物未完成 commit 操作之前修改的数据对其他 Session 不可见, 执行了 commit 之后才会对其他 Session 可见
我们可以看到 Session B 两次查询得到了不同的数据
read committed 隔离级别解决了脏读的问题, 但是会对其他 Session 产生两次不一致的读取结果(因为另一个 Session 执行了事务, 一致性变化)
REPEATABLE READ(可重复读)
一个事务中多次执行统一读 SQL, 返回结果一样
这个隔离级别解决了脏读的问题, 幻读问题这里指的是 innodb 的 rr 级别, innodb 中使用 next-key 锁对当前读进行加锁, 锁住行以及可能产生幻读的插入位置, 阻止新的数据插入产生幻行
下文中详细分析
具体请参考 mysql 手册
- https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
- SERIALIZABLE(可串行化)
最强的隔离级别, 通过给事务中每次读取的行加锁, 写加写锁, 保证不产生幻读问题, 但是会导致大量超时以及锁争用问题
多版本并发控制 - MVCC
MVCC(multiple-version-concurrency-control)是个 行级锁 的变种, 它在 普通读情况下避免了加锁操作, 因此开销更低
虽然实现不同, 但通常都是实现 非阻塞读
, 对于
写操作只锁定必要的行
一致性读 (就是读取快照)
select * from table .;
当前读(就是读取实际的持久化的数据)
特殊的读操作, 插入 / 更新 / 删除操作, 属于当前读, 处理的都是当前的数据, 需要加锁
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert;
- update ;
- delete;
注意: select from where (没有额外加锁后缀)使用 MVCC, 保证了读快照(mysql 称为 consistent read), 所谓一致性读或者读快照就是读取当前事务开始之前的数据快照, 在这个事务开始之后的更新不会被读到详细情况下文 select 的详述
对于加锁读 SELECT with FOR UPDATE(排他锁) or LOCK IN SHARE MODE(共享锁)updatedelete 语句, 要考虑是否是唯一索引的等值查询
写锁 - recordLock,gapLock,next key lock
对于使用到唯一索引 等值查询: 比如, where columnA= , 如果 columnA 上的索引被使用到,
那么会在满足 where 的记录上加行锁 (for update 是排他锁, lock in shared 是共享锁, 其他写操作加排他锁) 这里是行级锁, record lock
对于范围查询(使用非唯一的索引):
比如(做范围查询):where columnA between 10 and 30 , 会导致其他会话中 10 以后的数据都无法插入(next key lock), 从而解决了幻读问题
这里是 next key lock 会包括涉及到的所有行
next key lock=recordLock+gapLock, 不仅锁住相关数据, 而且锁住边界, 从而彻底避免幻读
对于没有索引
锁表
通常发生在 DDL 语句 DML 不走索引的语句中, 比如这个 DML update table set columnA=A where columnB=B.
如果 columnB 字段不存在索引(或者不是组合索引前缀), 会锁住所有记录也就是锁表如果语句的执行能够执行一个 columnB 字段的索引, 那么会锁住满足 where 的行(行锁)
INNODB 的 MVCC 通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列, 第三列是用于事务回滚, 此处略去),
一个保存了行的创建版本号, 另一个保存了行的更新版本号(上一次被更新数据的版本号)
这个版本号是每个事务的版本号, 递增的
这样保证了 innodb 对读操作不需要加锁也能保证正确读取数据
MVCC select 无锁操作 与 维护版本号
下边在 mysql 默认的 Repeatable Read 隔离级别下, 具体看看 MVCC 操作:
- Select(快照读, 所谓读快照就是读取当前事务之前的数据):
- a.
InnoDB 只 select 查找版本号早于当前版本号的数据行
, 这样保证了读取的数据要么是在这个事务开始之前就已经 commit 了的(早于当前版本号), 要么是在这个事务自身中执行创建操作的数据(等于当前版本号)
b. 查找行的更新版本号要么未定义, 要么大于当前的版本号(为了保证事务可以读到老数据), 这样保证了事务读取到在当前事务开始之后未被更新的数据
注意: 这里的 select 不能有 for updatelock in share 语句
总之要只返回满足以下条件的行数据, 达到了快照读的效果:
- (行创建版本号 <= 当前版本号 && (行更新版本号 ==null or 行更新版本号> 当前版本号 ) )
- Insert
InnoDB 为这个事务中新插入的行, 保存当前事务版本号的行作为行的行创建版本号
Delete
InnoDB 为每一个删除的行保存当前事务版本号, 作为行的删除标记
Update
将存在两条数据, 保持当前版本号作为更新后的数据的新增版本号, 同时保存当前版本号作为老数据行的更新版本号
当前版本号写>新数据行创建版本号 && 当前版本号写>老数据更新版本号();
脏读 vs 幻读 vs 不可重复读
脏读 : 一事务未提交的中间状态的更新数据 被其他会话读取到 当一个事务正在访问数据, 并且对数据进行了修改, 而这种修改还没有 提交到数据库中(commit 未执行), 这时, 另外会话也访问这个数据, 因为这个数据是还没有提交, 那么另外一个会话读到的这个数据是脏数据, 依据脏数据所做的操作也可能是不正确的
不可重复读 : 简单来说就是在一个事务中读取的数据可能产生变化, ReadCommitted 也称为不可重复读
在同一事务中, 多次读取同一数据返回的结果有所不同换句话说就是, 后续读取可以读到另一会话事务已提交的更新数据 相反, 可重复读在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是, 后续读取不能读到另一会话事务已提交的更新数据
幻读 : 会话 T1 事务中执行一次查询, 然后会话 T2 新插入一行记录, 这行记录恰好可以满足 T1 所使用的查询的条件 然后 T1 又使用相同 的查询再次对表进行检索, 但是此时却看到了事务 T2 刚才插入的新行这个新行就称为幻像, 因为对 T1 来说这一行就像突然 出现的一样
innoDB 的 RR 级别无法做到完全避免幻读, 下文详细分析
---------------------------------- 前置准备 ----------------------------------------
prerequisite:
-- 创建表
- mysql>
- CREATE TABLE `t_bitfly` (
- `id` bigint(20) NOT NULL DEFAULT '0',
- `value` varchar(32) DEFAULT NULL,
- PRIMARY KEY (`id`)
- )
-- 确保当前隔离级别为默认的 RR 级别
- mysql> select @@global.tx_isolation, @@tx_isolation;
- +-----------------------+-----------------+
- | @@global.tx_isolation | @@tx_isolation |
- +-----------------------+-----------------+
- | REPEATABLE-READ | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
--------------------------------------- 开始 ---------------------------------------------
- session A | session B
- |
- |
- mysql> START TRANSACTION; | mysql> START TRANSACTION;
- Query OK, 0 rows affected (0.00 sec) | Query OK, 0 rows affected (0.00 sec)
- |
- |
- mysql> SELECT * FROM test.t_bitfly; | mysql> SELECT * FROM test.t_bitfly;
- Empty set (0.00 sec) | Empty set (0.00 sec)
- |
- | mysql> INSERT INTO t_bitfly VALUES (1, 'test');
- | Query OK, 1 row affected (0.00 sec)
- |
- |
- mysql> SELECT * FROM test.t_bitfly; |
- Empty set (0.00 sec) |
- |
- | mysql> commit;
- | Query OK, 0 rows affected (0.01 sec)
- mysql> SELECT * FROM test.t_bitfly; |
- Empty set (0.00 sec) |
-- 可以看到虽然两次执行结果返回的数据一致, |
-- 但是不能说明没有幻读接着看: |
- |
- mysql> INSERT INTO t_bitfly VALUES (1, 'test'); |
- ERROR 1062 (23000): |
- Duplicate entry '1' for key 'PRIMARY' |
- |
-- 明明为空的表, 为什么说主键重复? 幻读出现 !!! |
如何保证 rr 级别绝对不产生幻读?
在使用的 select where 语句中加入 for update(排他锁) 或者 lock in share mode(共享锁)语句来实现 其实就是锁住了可能造成幻读的数据, 阻止数据的写入操作
其实是因为数据的写入操作 (insert update) 需要先获取写锁, 由于可能产生幻读的部分, 已经获取到了某种锁, 所以要在另外一个会话中获取写锁的前提是当前会话中释放所有因加锁语句产生的锁
mysql 死锁问题
死锁, 就是产生了循环等待链条, 我等待你的资源, 你却等待我的资源, 我们都相互等待, 谁也不释放自己占有的资源, 导致无线等待下去
比如:
- //Session A
- START TRANSACTION;
- UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
- UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
- COMMIT;
- //Thread B
- START TRANSACTION;
- UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
- UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
- COMMIT;
当线程 A 执行到第一条语句 UPDATE account SET p_money=p_money-100 WHERE p_name=tim; 锁定了 p_name=tim 的行数据; 并且试图获取 p_name=bill 的数据;
, 此时, 恰好, 线程 B 也执行到第一条语句: UPDATE account SET p_money=p_money+100 WHERE p_name=bill;
锁定了 p_name=bill 的数据, 同时试图获取 p_name=tim 的数据;
此时, 两个线程就进入了死锁, 谁也无法获取自己想要获取的资源, 进入无线等待中, 直到超时!
innodb_lock_wait_timeout 等待锁超时回滚事务:
直观方法是在两个事务相互等待时, 当一个等待时间超过设置的某一阀值时, 对其中一个事务进行回滚, 另一个事务就能继续执行这种方法简单有效, 在 innodb 中, 参数 innodb_lock_wait_timeout 用来设置超时时间
wait-for graph 算法来主动进行死锁检测:
innodb 还提供了 wait-for graph 算法来主动进行死锁检测, 每当加锁请求无法立即满足需要并进入等待时, wait-for graph 算法都会被触发
如何尽可能避免死锁
1)以固定的顺序访问表和行比如两个更新数据的事务, 事务 A 更新数据的顺序 为 1,2; 事务 B 更新数据的顺序为 2,1 这样更可能会造成死锁
2)大事务拆小大事务更倾向于死锁, 如果业务允许, 将大事务拆小
3)在同一个事务中, 尽可能做到一次锁定所需要的所有资源, 减少死锁概率
4)降低隔离级别如果业务允许, 将隔离级别调低也是较好的选择, 比如将隔离级别从 RR 调整为 RC, 可以避免掉很多因为 gap 锁造成的死锁
5)为表添加合理的索引可以看到如果不走索引将会为表的每一行记录添加上锁, 死锁的概率大大增大
显式锁 与 隐式锁
隐式锁 : 我们上文说的锁都属于不需要额外语句加锁的隐式锁
显示锁
- :
- SELECT ... LOCK IN SHARE MODE(加共享锁);
- SELECT ... FOR UPDATE(加排他锁);
详情上文已经说过
通过如下 sql 可以查看等待锁的情况
select * from information_schema.innodb_trx where trx_state="lock wait";
或
show engine innodb status;
mysql 中的事务
- show variables like "autocommit";
- set autocommit=0; //0 表示 AutoCommit 关闭
- set autocommit=1; //1 表示 AutoCommit 开启
自动提交(AutoCommit,mysql 默认)
mysql 默认采用 AutoCommit 模式, 也就是每个 sql 都是一个事务, 并不需要显示的执行事务
如果 autoCommit 关闭, 那么每个 sql 都默认开启一个事务, 只有显式的执行 commit 后这个事务才会被提交
来源: https://www.thinksaas.cn/group/topic/839002/