深入理解MySQL——锁、事务与并发控制

深入理解MySQL——锁、事务与并发控制

本文对锁、事务、并发控制做一个总结，看了网上很多文章，描述非常不准确。如有与您观点不一致，欢迎有理有据的拍砖！

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

隔离级别

查看系统隔离级别： select @@global.tx_isolation; 查看当前会话隔离级别 select @@tx_isolation; 设置当前会话隔离级别 SET session TRANSACTION ISOLATION LEVEL serializable; 设置全局系统隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED(未提交读,可脏读)

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

READ COMMITTED(提交读或不可重复读，幻读)

REPEATABLE READ(可重复读)

一个事务中多次执行统一读SQL,返回结果一样。 这个隔离级别解决了脏读的问题，幻读问题。这里指的是innodb的rr级别，innodb中使用next-key锁对”当前读”进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。 下文中详细分析。

具体请参考mysql手册

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(共享锁)、update、delete语句，要考虑是否是唯一索引的等值查询。

写锁-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操作：

(行创建版本号< =当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )

InsertInnoDB为这个事务中新插入的行，保存当前事务版本号的行作为行的行创建版本号。Delete  InnoDB为每一个删除的行保存当前事务版本号，作为行的删除标记。Update将存在两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作为老数据行的更新版本号。

当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

脏读 vs 幻读 vs 不可重复读

不可重复读：简单来说就是在一个事务中读取的数据可能产生变化，ReadCommitted也称为不可重复读。

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

幻读：会话T1事务中执行一次查询，然后会话T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同 的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然 出现的一样。 innoDB的RR级别无法做到完全避免幻读，下文详细分析。

如何保证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中的事务

自动提交（AutoCommit，mysql默认）

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。