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