[MySQL] [Transaction] [并发控制]

事务定义：
事务的状态：
- 事务被看成一个密不可分的整体，状态也只有三种：Active(执行中)、Commited和Failed。

Transaction

ACID

Atomic

一系列操作，要么全部都执行，要么都不执行，这其实是对事务原子性的刻画。
事务其本质就是一系列的数据库操作的集合，如果事务不具备原子性，那么就没办法保证同一个事务中的所有操作都被执行or不被执行了，整个数据库系统既不可用也不可信。

Implementation

TBD

Isolation

隔离性定义了事务之间的可见性。
隔离级别：
- read uncommited未提交读：会造成脏读，也就是事务A对数据做的修改，即使没有提交，对事务B也是可见的。这种级别在实际中会引起很多问题，一般不用。
  - eg：事务T1 更新了记录，但未提交，T2 读取了更新后的行，然后T1 回滚，此时T2 读取无效。
    事务1：更新一条数据
    ------------->事务2：读取事务1更新的记录
    事务1：调用commit进行提交
- read commited提交读：会造成不可重复读，即一次事务中两次读取的结果不同。这种情况一般发生在：T1的两次读取之间，T2修改了记录并提交。这是很多数据库默认隔离级别。
  - eg：
    事务1：查询一条记录
    -------------->事务2：更新事务1查询的记录
    -------------->事务2：调用commit进行提交
    事务1：再次查询上次的记录
- repeatable read可重复读：在该隔离级别下可能会出现幻读，即在T1的两次count(*)之间，T2插入了记录，那么得到的记录数会不同。这是Mysql的默认隔离级别。并且Mysql的innoDB引擎间隙锁解决了幻读问题。
  - 事务1：查询表中所有记录
    -------------->事务2：插入一条记录
    -------------->事务2：调用commit进行提交
    事务1：再次查询表中所有记录
- serializable可串行化：最高的隔离级别，强制要求所有事务串行执行。该级别下，读取的每行数据都加锁，会导致大量锁征用问题，性能最差。
显然，事务隔离级别越高，就越能保证数据的完整性和一致性，但同时对并发性能的影响越大。

Consistency

一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

Durability

持久性：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

Implementation

* 以下几乎都是对应InnoDB场景

事务的实现是基于数据库的存储引擎，不同的存储引擎对事务的支持不一样。
MySQL中支持事务的存储引擎有innoDB和NDB。
innoDB是mysql默认的存储引擎，默认的隔离级别是RR可重复读。
事务的隔离性是通过锁实现。而事务的原子性、一致性和持久性则是通过事务日志实现。
- 通过多版本并发控制(MVCC, Multiversion Concurrency Conrtol)解决不可重复读问题，加上间隙锁(也就是并发控制)解决幻读问题。因此，可以认为innoDB隔离级别实现了串行化级别的效果，但是保留了比较好的并发性能。

Lock

锁的分类：
- 读锁 & 写锁
  - 读锁：共享锁，即一个读锁不会阻塞其它读锁（但会阻塞其它写锁），多个用户可同时读取同一个资源，而不互相干扰。
  - 写锁：排他锁，即一个写锁会阻塞其它读写锁，在给定时间内，只有一个用户能执行写入。
- 隐式锁 & 显式锁：
  - 隐式锁：由存储引擎自动完成
  - 显式锁：用户可手动施加锁(表级锁)
    - LOCK TABLES tb_name {READ|WRITE}...
    - InnoDB也支持另外一种显式锁：只锁定挑选出来的行
      - SELECT ... LOCK IN SHARE MODE [S锁共享]
      - SELECT ... FOR UPDATE [X锁排他]
- 乐观锁 & 悲观锁：
  - 乐观锁：典型是MVCC[见下方介绍]
锁粒度：
- 表级锁：锁定整张表
- 行级锁：并发程度更高，但维护较麻烦，会增加系统开销，易产生死锁。行级锁只能在存储引擎级别实现，MyISAM存储引擎不支持行级锁。
- 当一条sql没有走任何索引的时候，会在所有行上加X锁，这个类似于表锁，但是原理完全不同。(下面有例子)
一些其他锁： [对于用户而言，可能不太使用]
- 意向锁Intention Locks：意向锁主要用于解决行锁与表锁间冲突问题。用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。(事务在请求S锁和X锁前，需要先获得对应的IS、IX锁。)
- Gap Locks间隙锁：区间锁, 仅仅锁住一个索引区间(开区间)。
- 自增锁AUTO-INC Locks：一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

事务日志

redo log
- 在innoDB的存储引擎中，事务日志通过redo log和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。
- 事务开启时，事务中的操作都会先写入存储引擎的日志缓冲中，在事务提交前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是经典的WAL(Write-Ahead Logging)。
- 当事务提交后，在Buffer Pool中映射的数据文件才会慢慢的刷新到磁盘。此时如果数据库崩溃或宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务可以继续提交，也可以选择回滚，这基于恢复的策略而定。
undo log
- undo log主要为事务的回滚服务。在事务执行过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态。
所以，redo log其实保障的是事务的持久性和一致性，undo log保障了事务的原子性。
事务的启动，提交和回滚
- 启动：START TRANSACTION|BEGIN
- 提交：COMMIT
- 回滚：ROLLBACK

并发控制

MVCC

MVCC: Multi-Version Concurrency Control
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。
好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。
- 快照锁：简单的select操作，属于快照读，不加锁。 [有例外，往下看]
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。e.g.:
  - select * from table where ? lock in share mode
  - select * from table where ? for update
  - insert into table values
  - update table set ? where ?;
  - delete from table where ?;

　　　　　-> 以上语句都属于当前读，读取记录的最新版本。并且读取之后还需要保证其他并发事务不能修改当前记录，对读取记录加锁。
　　　　　　并且，第一条属于加S锁(共享锁)，其他都是X锁(排他锁)。

加锁分析

2PL: Two-Phase Locking
- 传统RDBMS加锁的一个原则：说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。
mysql加锁处理分析Example：问 "delete from t1 where id=10" 加的是什么锁？
- 首先，最重要的是，要看具体的场景！前提：id是不是主键？id列上有索引吗？id如果是二级索引，那么它是唯一索引吗？当前系统的隔离等级是什么？
- id主键 + RR(存在幻读)：只需要将主键上，id = 10的记录加上X锁即可。[id是聚簇索引]
- id唯一索引 + 存在幻读：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。 [二级索引的读需要读两次索引]
- id非唯一索引 + 存在幻读：若id列只是一个普通的索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
- id无索引 + 幻读：既然没法通过索引进行过滤，那么只能走全表扫描做过滤。由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。
- 对于不允许存在幻读的情况，在id为主键or为唯一索引时，情况都和上述相应运行存在幻读的情况相同
- id非唯一索引 + 不允许幻读：上面提到innoDB通过MVCC做到RR，再加GAP间隙锁避免的幻读。具体关于GAP锁的原理这里就不介绍了。
  在不允许幻读的场景下：
  如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

死锁

死锁的例子
- 如下是最常见的死锁：每个事务执行两天sql，分别持有了一把锁，然后加另一把锁，产生死锁
- 如下虽然每个session都只有一条语句，但仍然会产生死锁。
  要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。
结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。（而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。）

Implementation

MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。
InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。

In Action

* 以下都是基于innoDB

场景一：并发select，update
- Solution1：悲观锁。利用select for update(必须放在transaction内)。适合频繁写入的场景。 [行级锁]
```
set auto_commit=false;
begin;
select * from table where id=1 for update;
update table set num=num + 1 where id=1;
commit;
set auto_commit=true;
```
- Solution2：乐观锁。不加锁，更新之前增加条件，保证只有一个请求更新成功。适合多读少写的场景。
```
select * as rs from table where id=1;
update table set num=num+1 where id=1 and num=rs.num;
```
  这里可以采用重试机制来保证更新成功：check update之后的affected_rows是否等于1。
client锁表
- lock table taskQueue read; 获取读锁，不允许写操作
- lock table taskQueue write; 获取写锁，读写都不允许

　　　　e.g: 线程A lock table taskQueue write; 线程B的所有操作都会阻塞，直到线程A UNLOCK TABLES; [注意这里必须还是原来那个线程执行unlock]　　

MySQL Task Queue

表结构:

create table `taskQueue` (
	id int not null AUTO_INCREMENT,
	status int not null default '0',
	params VARCHAR(1024) not null default '',
	result VARCHAR(1024) not null default '',
	PRIMARY KEY(id)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Solution1: lock the whole table

-- Solution1: Lock the whole table
LOCK TABLES taskQueue WRITE, taskQueue READ;
SELECT id, status, params FROM taskQueue WHERE status=0 ORDER BY id ASC limit 1;
UPDATE taskQueue SET status=1 WHERE id=xx;  -- in process
UNLOCK TABLES;
-- execute task
UPDATE taskQueue SET status=2 WHERE id=xx;  -- completed

使用X锁锁住整张表，显然效率不佳。

Solution2: row-level locking

-- Solution2: select ... for update(Should be put inside transaction) [Row-level locking]
START TRANSACTION;
SELECT id, status, params FROM taskQueue WHERE status=0 ORDER BY id ASC for UPDATE;
UPDATE taskQueue SET status=1 WHERE id=xx;  -- in process
COMMIT;
-- execute task
UPDATE taskQueue SET status=2 WHERE id=xx;  -- completed

select for update limit 1的话就只会锁住那一行。注意select for update 要放在transaction内部。

Solution3: lock free

-- Solution4: lock-free: update then select (Using MySQL 'User-Defined variables')
            -- UDV: store a value in a user-defined variable in one statement and refer to it later in another statement
    -- The statements must be put inside one session.
    --   (User-defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients.)
-- enable autocommit(Each cmd outside transaction will be treated as a transaction)
UPDATE taskQueue SET status=1, id=(@id:=id) WHERE status=0 ORDER BY id ASC LIMIT 1;
SELECT id, status, params FROM taskQueue WHERE id=@id;

　　　利用mysql提供的User-Defined variables，就可以在一个session内部，先update然后通过UDV拿到update的那条记录的id，然后再select出来。

follow-up: 一致性问题：update之后线程没有执行完task就挂掉了该怎么办。
- heartbeat：定时发心跳，超时重做。
- 表结构增加一行last_update_time记录上次更新的时间戳，超时重做。
follow-up: 上面都是order by id asc limit 1; 这样做的问题是，冲突率很高。那么我们是否可以减少各个worker取回来的记录的冲突率呢？
- 显然，可以通过不顺序取，而是随机取，来降低冲突率。
- 关于在mysql如何随机取记录，请看this link。

基本操作加锁过程

insert：
- 简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。
- 不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。
- e.g.: 假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
- 如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作是会发生死锁的。

Reference

mysql加锁处理分析

发表于 2018-06-06 22:07 橘子不是唯一的水果阅读(196) 评论(0) 收藏举报

刷新页面返回顶部

[MySQL] [Transaction] [并发控制]

Transaction

ACID

Atomic

Implementation

Isolation

Consistency

Durability

Implementation

Lock

事务日志

并发控制

MVCC

加锁分析

死锁

Implementation

In Action

MySQL Task Queue

基本操作加锁过程

Reference

公告

导航