深入理解MySQL的并发控制、锁和事务【转】

本文主要是针对MySQL/InnoDB的并发控制和加锁技术做一个比较深入的剖析，并且对其中涉及到的重要的概念，如多版本并发控制（MVCC），脏读（dirty read），幻读（phantom read），四种隔离级别（isolation level）等作详细的阐述，并且基于一个简单的例子，对MySQL的加锁进行了一个详细的分析。本文的总结参考了何登成前辈的博客，并且在前辈总结的基础上，进行了一些基础性的说明，希望对刚入门的同学产生些许帮助，如有错误，请不吝赐教。按照我的写作习惯，还是通过几个关键问题来组织行文逻辑，如下：

  • 什么是MVCC（多版本并发控制）？如何理解快照读（snapshot read）和当前读（current read）？
  • 什么是隔离级别？脏读？幻读？InnoDB的四种隔离级别的含义是什么？
  • 什么是死锁？
  • InnoDB是如何实现MVCC的？
  • 一个简单的sql在不同场景下的加锁分析
  • 一个复杂的sql的加锁分析

　　接下来，我将按照这几个关键问题的顺序，对以上问题作一一解答，并且在解答的过程中，争取将加锁技术的细节，阐述的更加清楚。

1.1 MVCC：Multi-Version Concurrent Control 多版本并发控制

　　MVCC是为了实现数据库的并发控制而设计的一种协议。从我们的直观理解上来看，要实现数据库的并发访问控制，最简单的做法就是加锁访问，即读的时候不能写（允许多个西线程同时读，即共享锁，S锁），写的时候不能读（一次最多只能有一个线程对同一份数据进行写操作，即排它锁，X锁）。这样的加锁访问，其实并不算是真正的并发，或者说它只能实现并发的读，因为它最终实现的是读写串行化，这样就大大降低了数据库的读写性能。加锁访问其实就是和MVCC相对的LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。为了提出比LBCC更优越的并发性能方法，MVCC便应运而生。

　　几乎所有的RDBMS都支持MVCC。它的最大好处便是，读不加锁，读写不冲突。在MVCC中，读操作可以分成两类，快照读（Snapshot read）和当前读（current read）。快照读，读取的是记录的可见版本（可能是历史版本，即最新的数据可能正在被当前执行的事务并发修改），不会对返回的记录加锁；而当前读，读取的是记录的最新版本，并且会对返回的记录加锁，保证其他事务不会并发修改这条记录。在MySQL InnoDB中，简单的select操作，如 select * from table where ? 都属于快照读；属于当前读的包含以下操作：

  1. select * from table where ? lock in share mode; （加S锁）
  2. select * from table where ? for update; （加X锁，下同）
  3. insert, update, delete操作

 　　针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后再读取下一条加锁，直至读取完毕。需要注意的是，以上需要加X锁的都是当前读，而普通的select（除了for update）都是快照读，每次insert、update、delete之前都是会进行一次当前读的，这个时候会上锁，防止其他事务对某些行数据的修改，从而造成数据的不一致性。我们广义上说的幻读现象是通过MVCC解决的，意思是通过MVCC的快照读可以使得事务返回相同的数据集。如下图所示：

　　

注意，我们一般说在MyISAM中使用表锁，因为MyISAM在修改数据记录的时候会将整个表锁起来；而InnoDB使用的是行锁，即我们以上所谈的MVCC的加锁问题。但是，并不是InnoDB引擎不会使用表锁，比如在alter table的时候，Innodb就会将该表用表锁锁起来。

1.2 隔离级别

　　在SQL的标准中，定义了四种隔离级别。每一种级别都规定了，在一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离可以执行更高级别的并发，性能好，但是会出现脏读和幻读的现象。首先，我们从两个基础的概念说起：

　　脏读（dirty read）：两个事务，一个事务读取到了另一个事务未提交的数据，这便是脏读。

　　幻读（phantom read）：两个事务，事务A与事务B，事务A在自己执行的过程中，执行了两次相同查询，第一次查询事务B未提交，第二次查询事务B已提交，从而造成两次查询结果不一样，这个其实被称为不可重复读；如果事务B是一个会影响查询结果的insert操作，则好像新多出来的行像幻觉一样，因此被称为幻读。其他事务的提交会影响在同一个事务中的重复查询结果。

　　下面简单描述一下SQL中定义的四种标准隔离级别：

  1. READ UNCOMMITTED (未提交读) ：隔离级别：0. 可以读取未提交的记录。会出现脏读。
  2. READ COMMITTED (提交读) ：隔离级别：1. 事务中只能看到已提交的修改。不可重复读，会出现幻读。（在InnoDB中，会加行所，但是不会加间隙锁）该隔离级别是大多数数据库系统的默认隔离级别，但是MySQL的则是RR。
  3. REPEATABLE READ (可重复读) ：隔离级别：2. 在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象。但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的。
  4. SERIALIZABLE （可串行化）：隔离级别：3. 该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。

 　　需要注意的是，MVCC只在RC和RR两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容。

1.3 死锁

　　死锁是指两个或者多个事务在同一资源上相互作用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。且看下面的两个产生死锁的例子：

 

 　　第一个死锁很好理解，而第二个死锁，由于在主索引（聚簇索引表）上仍旧是对两条记录进行了不同顺序的加锁，因此仍旧会造成死锁。死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。因此，我们通过分析加锁细节，可以判断所写的sql是否会发生死锁，同时发生死锁的时候，我们应该如何处理。

1.4 InnoDB的MVCC实现机制

　　MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。下面我们简述一下MVCC在InnoDB中的实现。

　　InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

  1. select操作。a. InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。b. 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。
  2. insert操作。将新插入的行保存当前版本号为行版本号。
  3. delete操作。将删除的行保存当前版本号为删除标识。
  4. update操作。变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。

　　由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

1.5 一个简单SQL的加锁分析

　　在MySQL的InnoDB中，都是基于聚簇索引表的。而且普通的select操作都是基于快照读，是不需要加锁的。那么我们在分析其他的sql语句的时候，如何分析加锁细节？下面我们以一个简单的delete操作的SQL为例，进行一个详细的阐述。且看下面的SQL：

　　delete from t1 where id=10;

　　如果对这条SQL进行加锁分析，那么MySQL是如何加锁的呢？一般情况下，我们直观的感受是：会在id=10的记录上加锁。但是，这样轻率的下结论是片面的，要想确定MySQL的加锁情况，我们还需要知道更多的条件。还需要知道哪些条件呢？比如：

  1. id列是不是主键？
  2. 系统的隔离级别是什么？
  3. id非主键的话，其上有建立索引吗？
  4. 建立的索引是唯一索引吗？
  5. 该SQL的执行计划是什么？索引扫描？全表扫描？

　　接下来，我将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁。

  • 组合1：id列是主键，RC隔离级别
  • 组合2：id列是二级唯一索引，RC隔离级别
  • 组合3：id列是二级非唯一索引，RC隔离级别
  • 组合4：id列上没有索引，RC隔离级别
  • 组合5：id列是主键，RR隔离级别
  • 组合6：id列是二级唯一索引，RR隔离级别
  • 组合7：id列是二级非唯一索引，RR隔离级别
  • 组合8：id列上没有索引，RR隔离级别
  • 组合9：Serializable隔离级别

　　组合1：id列是主键，RC隔离级别

　　当id是主键的时候，我们只需要在该id=10的记录上加上x锁即可。如下图所示：

　　组合2：id列是二级唯一索引，RC隔离级别

　　在这里我先解释一下聚簇索引和普通索引的区别。在InnoDB中，主键可以被理解为聚簇索引，聚簇索引中的叶子结点就是相应的数据行，具有聚簇索引的表也被称为聚簇索引表，数据在存储的时候，是按照主键进行排序存储的。我们都知道，数据库在select的时候，会选择索引列进行查找，索引列都是按照B+树（多叉搜索树）数据结构进行存储，找到主键之后，再回到聚簇索引表中进行查询，这叫回表查询。那我们自然会问，当使用索引进行查询的时候，与索引相对应的记录会被上锁吗？会的。如果id是唯一索引，那么只给该唯一索引所对应的索引记录上x锁；如果id是非唯一索引，那么所对应的所有的索引记录上都会上x锁。如下图所示：

　　组合3：id列是二级非唯一索引，RC隔离级别

　　解释同上，如下图：

　　组合4：id列上没有索引，RC隔离级别

 　  由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。有人说会在表上加X锁；有人说会在聚簇索引上，选择出来的id = 10 的记录加上X锁。真实情况如下图：

　　

　　若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束（同时加锁同时放锁）。

　　组合5，6同以上（因为只有一条结果记录，只能在上面加锁）

　　组合7：id列是二级非唯一索引，RR隔离级别

 　　在RR隔离级别下，为了防止幻读的发生，会使用Gap锁。这里，你可以把Gap锁理解为，不允许在数据记录前面插入数据。首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。如下图所示：

　　

 

　　组合8：id列无索引，RR隔离级别

　　在这种情况下，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。如下图：

　　

　　但是，MySQL是做了相关的优化的，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁，同时也不会添加Gap锁。

　　组合9：Serializable隔离级别

　　和RR隔离级别一样。

1.6 一个复杂的SQL的加锁分析

 　这里我们只是列出一个结论，因为要涉及到MySQL的where查询条件的分析，因此这里先不做详细介绍，我会在之后的博客中详细说明。如下图：

　

　　结论：在RR隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。加锁的结果如下所示：

 

总结

本文只是对MVCC的一些基础性的知识点进行了详细的总结，参考了网上和书上比较多的资料和实例。希望能对各位的学习有所帮助。

转自

MySQL的并发控制与加锁分析 - Yelbosh - 博客园
http://www.cnblogs.com/yelbosh/p/5813865.html

 

Mysql服务器逻辑架构

这里写图片描述

每个连接都会在mysql服务端产生一个线程（内部通过线程池管理线程），比如一个select语句进入，mysql首先会在查询缓存中查找是否缓存了这个select的结果集，如果没有则继续执行 解析、优化、执行的过程；否则会之间从缓存中获取结果集。

Mysql并发控制——共享锁、排他锁

共享锁

共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

排他锁

排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

锁策略

锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

  • Mysql锁策略：talbe lock(表锁)

表锁是Mysql最基本的锁策略，也是开销最小的策略，它会锁定整个表；具体情况是：若一个用户正在执行写操作，会获取排他的“写锁”，这是会锁定整个表，阻塞其他用户的读、写操作；

若一个用户正在执行读操作，会先获取共享锁“读锁”，这个锁运行其他读锁并发的对这个表进行读取，互不干扰。只要没有写锁的进入，读锁可以是并发读取统一资源的。

  • Mysql锁策略：row lock(行锁)

行锁可以最大限度的支持并发处理，当然也带来了最大开销，顾名思义，行锁的粒度实在每一条行数据。

事务

事务就是一组原子性的sql，或者说一个独立的工作单元。就是说要么mysql引擎会全部执行这一组sql语句，要么全部都不执行（比如其中一条语句失败的话）。

比如，tim要给bill转账100块钱： 
1.检查tim的账户余额是否大于100块； 
2.tim的账户减少100块； 
3.bill的账户增加100块； 
这三个操作就是一个事务，必须打包执行，要么全部成功，要么全部不执行，其中任何一个操作的失败都会导致所有三个操作“不执行”——回滚。

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隔离性

在一个事务未执行完毕时，通常会保证其他事务无法看到这个事务的执行结果

  • D:durability持久性 
    事务一旦commit，则数据就会保存下来，即使提交完之后系统崩溃，数据也不会丢失。

隔离级别

  • READ UNCOMMITTED(未提交读)

事务中的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据——脏读。脏读会导致很多问题，一般不适用这个隔离级别。 
实例：

-- ------------------------- read-uncommitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Thread A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- commit;

-- Thread B
SELECT * FROM USER;

//ThreadB Console 可以看到线程A未提交的事物处理，在另一个线程中也看到了，这就是所谓的脏读
id  name
2   READ UNCOMMITTED
34  READ UNCOMMITTED
  • READ COMMITTED(提交读)

一般数据库都默认使用这个隔离级别（Mysql不是），这个隔离级别保证了一个事务如果没有完全成功（commit执行完），事务中的操作对其他事务是不可见的。

-- ------------------------- read-cmmitted实例 ------------------------------
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ  COMMITTED;
-- Thread A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
-- COMMIT;

-- Thread B
SELECT * FROM USER;

//Console OUTPUT:
id  name
2   READ UNCOMMITTED
34  READ UNCOMMITTED


---------------------------------------------------
-- 当线程A执行了commit，线程B得到如下结果：
id  name
2   READ COMMITTED
34  READ COMMITTED

 

也就验证了read committed级别在事物未完成commit操作之前修改的数据对其他线程不可见，执行了commit之后才会对其他线程可见。 
我们可以看到线程B两次查询得到了不同的数据。

这种隔离级别解决了脏读的问题，但是会对其他线程产生两次不一致的读取结果。

  • REPEATABLE READ(可重复读) 
    这个隔离级别解决了脏读的问题，但会产生幻读，问题。

脏读与幻读与不可重复读：

脏读：一个事务读取到另一事务未提交的更新新据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有 
提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据， 那么另 
外一个事务读到的这个数据是脏数据，依据脏数据所做的操作也可能是不正确的。

不可重复读：在同一事务中，多次读取同一数据返回的结果有所不同。换句话说就是，后续读取可以读到另一事务已提交的 
更新数据。相反，“可重复读”在同一事务中多次读取数据时，能够保证所读数据一样，也就是，后续读取不能读到另一事务 
已提交的更新数据。

幻读：事务T1执行一次查询，然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 
的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然 
出现的一样。 
- SERIALIZABLE(可串行化) 
最强的隔离级别，通过给事务中每次读取的行加锁（行锁），保证不产生幻读问题，但是会导致大量超时以及锁争用问题。

这里写图片描述

Mysql死锁问题

死锁，就是产生了循环等待链条，我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源，导致无线等待下去。 
比如：

//Thread 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”的数据； 
此时，两个线程就进入了死锁，谁也无法获取自己想要获取的资源，进入无线等待中，直到超时！

对于死锁，数据库一般通过死锁监测、死锁超时机制解决；通常会执行回滚，打破死锁状态，然后再次执行之前死锁的事务即可。

Mysql中的事务

  • 自动提交（AutoCommit）

mysql默认采用AutoCommit模式，也就是每个sql都是一个事务，并不需要显示的执行事务

多版本并发控制-MVCC

MVCC是个行级锁的变种，它在很多情况下避免了加锁操作，因此开销更低。虽然实现不同，但通常都是实现非阻塞读，对于写操作只锁定必要的行。

通常MVCC实现有乐观并发控制与悲观并发控制，INNODB的MVCC通常是通过在每行数据后边保存两个隐藏的列来实现，一个保存了行的创建时间，另一个保存了行的删除时间。当然存储的并不是实际的时间值，而是系统版本号,每个事务开始，系统版本号就会递增!,每个事务开始时刻的版本号也会作为这个事务的版本号，用来和查询到的每行版本号做比较。下边在Mysql默认的Repeatable Read隔离级别下，具体看看MVCC操作：

  • Select： 
    a.InnoDB只查找版本号早于当前版本号的数据行，这样保证了读取的数据要么实在这个事务开始之前就已经commit了的（早于当前版本号），要么是在这个事务自身中执行操作的数据（等于当前版本号）。 
    b.行的删除版本号要么未定义，要么早于当前的版本号，这样保证了事务读取到的数据在事务开始之前未被删除。

  • Insert

InnoDB为这个事务中新插入的行，保存当前事务版本号的行（作为行的版本号）。

  • Delete 
    InnoDB为每一个删除的行保存当前事务版本号，最为行的删除标记。

  • Update

InnoDB将保存当前版本号最为行版本号，同时保存当前版本号到原来行（更新前）的删除版本号标识处。

转自

深入理解Mysql——锁、事务与并发控制 - 张硕的专栏 - CSDN博客
http://blog.csdn.net/lemon89/article/details/51477497

posted @ 2017-10-26 15:23  paul_hch  阅读(5122)  评论(0)  编辑  收藏  举报