mysql事务与锁

Mysql中的锁

mysql中的锁比较简单，最显著的特征是不同的存储引擎支持不同的锁，例如myisam只支持表锁，innodb支持行锁和表锁，默认是行锁

表级锁：开销小，加锁块，不回出现死锁，粒度颗粒大，发送锁冲突概率高，并发度最低
行级锁：开销大，加锁慢，会出现死锁，粒度颗粒小，发生锁冲突概率最低，并发度最高
页面锁：开销和加锁时间介于表级锁和行级锁之间，会出现死锁，锁粒度介于行锁和表锁之间，并发一般

MyISAM锁

myisam中表锁有两种：

表共享读锁 lock table xxx read
表独占写锁 lock table xxx write

总结：

对于myisam的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
对与myisam表的读操作，不回阻塞当前session对表读，当表进行修改会保存
一个session使用lock table 对表f加了读锁，这个session可以查询锁的表f中的记录，但更新和访问其他表都会报错
另一个session可以查询到表中的记录，但是更新会出现等待
对于myisam的写操作，会阻塞其他用户对同一表的读和写操作
对于myisam中的写操作，当前session可以进行curd，但对其他表操作会报错

InnoDB锁

共享锁排他锁

它们都是标准的行级锁。

共享锁（S） 共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；
排他锁（X） 排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

意向锁

InnoDB支持多粒度锁（行锁和表锁），为了实现这个功能，InnoDB使用了意向锁，

意向锁：表级别锁，先声明一个意向，并获取表级别的意向锁（IS或者IX），如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁(S或X)了。（除了 LOCK TABLES … WRITE,很少用,略)

举例来说：

SELECT … LOCK IN SHARE MODE，要获取IS锁；An intention shared lock (IS)

SELECT … FOR UPDATE ，要获取IX锁；An intention exclusive lock (IX)

意向锁协议 在事务能够获取表中的行上的共享锁之前，它必须首先获取表上的IS锁或更强的锁。在事务能够获取表中的行上的独占锁之前，它必须首先获取表上的IX锁。

上图的共享拍他锁均为表锁

意向锁之间是互相兼容的

意向锁不会与行级的共享 / 排他锁互斥

可以体会到，意向锁是比X\S更弱的锁，存在一种预判的意义！先获取更弱的IX\IS锁，如果获取失败就不必要再花费跟大开销获取更强的X\S锁

索引行锁

record lock是记录在索引行上的锁，例如

select c1 from t where ci=10 for update如果c1上面有索引并且被使用，

record lock 总是会在索引行上加锁。即使一个表并没有设置索引，这种时候innoDB会创建一个隐式的组件索引（primary Key）,然后在这个主件索引上加锁。

在DML语句不走索引的语句中，比如这个DML update table set columnA=“A” where columnB=“B".

如果columnB字段不存在索引（或者不是组合索引前缀），会锁住所有记录也就是锁表。如果语句的执行能够执行一个columnB字段的索引，那么会锁住满足where的行(RecordLock)。

间隙锁

锁定索引记录之间的间隙([2])，或者锁定一个索引记录之前的间隙([1])，或者锁定一个索引记录之后的间隙([3])。

示例：如图[1]、[2]、[3]部分。一般作用于我们的范围查询> 、< 、between…

例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事务将值3插入到列userId中。因为该范围内所有现有值之间的间隙都是锁定的。

对于使用唯一索引来搜索唯一行的语句 select a from ，不产生间隙锁定。. (不包含组合唯一索引，也就是说gapLock不作用于单例唯一索引）

例如，如果id列有唯一的索引，下面的语句只对id值为100的行使用索引记录锁，其他会话是否在前一个间隙中插入行并不重要:

SELECT * FROM t1 WHERE id = 100;  
# 如果id**没有索引或具有非惟一索引，则语句将锁定前面的间隙

间隙可以跨越单个索引值、多个索引值(如上图2,3)，甚至是空的。
间隙锁是性能和并发性之间权衡的一种折衷，用于某些特定的事务隔离级别，如RC级别
**在重叠的间隙中（或者说重叠的行记录）中允许gap共存 ** 比如同一个gap中，允许一个事务持有 gap X-Lock（gap 写锁\排他锁)，同时另一个事务在这个gap中持有(gap 写锁\排他锁)

######## 事务一 获取gap锁########
START TRANSACTION;
SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE;
##暂不commit

######## 事务二 获取gap锁 ########

SELECT * FROM new_table WHERE a = 4 FOR UPDATE;

##顺利执行！ 因为gap锁可以共存；


######## 事务三获取行锁 ########

 SELECT * FROM new_table WHERE a = 3 FOR UPDATE;
 
##获取锁超时，失败。因为事务一的gap锁定了 a=3的数据。

next-key lock

next-key lock 是record lock 与 gap lock 的组合 比如存在一个查询匹配b=3的行(b上有个非唯一索引)，那么所谓NextLock就是：在b=3的行加了RecordLock 并且使用 GapLock锁定了b=3之前（“之前”：索引排序）的所有行记录。

mysql查询时执行行级锁策略，会对扫描过程中匹配的行进行加锁（X 或S），也就是加Record Lock，同时会对这个记录之前的所有行加GapLock锁。

另外，值得一提的是： innodb中默认隔离级别(RR)下，next key Lock自动开启。 （很好理解，因为gap作用于RR，如果是RC，gapLock不会生效，那么next key lock自然也不会）

插入意向锁

发生在insert插入语句时的间隙锁，锁定插入之前的所有行。这个锁以这样一种方式表明插入的意图，如果插入到同一索引间隙中的多个事务没有插入到该间隙中的相同位置，则它们不需要等待对方。

事务

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

自动提交：AutoCommit
显示事务：START TRANSACTION…COMMIT

ACID

A:atomiciy原子性一个事务必须保证其中的操作要么全部执行，要么全部回滚，不可能存在只执行了一部分这种情况出现。
C:consistency一致性数据必须保证从一种一致性的状态转换为另一种一致性状态。比如上一个事务中执行了第二步时系统崩溃了，数据也不会出现bill的账户少了100块，但是tim的账户没变的情况。要么维持原装（全部回滚），要么bill少了100块同时tim多了100块，只有这两种一致性状态的
I：isolation隔离性在一个事务未执行完毕时，通常会保证其他Session 无法看到这个事务的执行结果
D:durability持久性事务一旦commit，则数据就会保存下来，即使提交完之后系统崩溃，数据也不会丢失。

隔离级别

为提交读

事务中的修改，即使没有提交，对其他会话也是可见的。

可以读取未提交的数据——脏读。脏读会导致很多问题，一般不适用这个隔离级别。

已提交读

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

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

read committed隔离级别解决了脏读的问题，但是会对其他Session 产生两次不一致的读取结果（因为另一个Session 执行了事务，一致性变化）。

可重复读

一个事务中多次执行统一读SQL,返回结果一样。这个隔离级别解决了脏读的问题，幻读问题。

这里指的是innodb的rr级别，innodb中使用next-key锁对"当前读"进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行。

串性化

最强的隔离级别，通过给事务中每次读取的行加锁，写加写锁，保证不产生幻读问题，但是会导致大量超时以及锁争用问题。

并发控制与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 with FOR UPDATE(排他锁) or LOCK IN SHARE MODE(共享锁)、update、delete语句，要考虑是否是唯一索引的等值查询。INNODB的MVCC通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列，第三列是用于事务回滚，此处略去)，

一个保存了行的创建版本号，另一个保存了行的更新版本号（上一次被更新数据的版本号）这个版本号是每个事务的版本号，递增的。

这样保证了innodb对读操作不需要加锁也能保证正确读取数据。

MVCC select 无锁操作与维护版本号

下边在mysql默认的Repeatable Read隔离级别下，具体看看MVCC操作：

Select（快照读，所谓读快照就是读取当前事务之前的数据。）：
- a.InnoDB只select查找版本号早于当前版本号的数据行，这样保证了读取的数据要么是在这个事务开始之前就已经commit了的（早于当前版本号），要么是在这个事务自身中执行创建操作的数据（等于当前版本号）。
- b.查找行的更新版本号要么未定义，要么大于当前的版本号(为了保证事务可以读到老数据)，这样保证了事务读取到在当前事务开始之后未被更新的数据。注意：这里的select不能有for update、lock in share 语句。总之要只返回满足以下条件的行数据，达到了快照读的效果：

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

Insert

InnoDB为这个事务中新插入的行，保存当前事务版本号的行作为行的行创建版本号。
Delete InnoDB为每一个删除的行保存当前事务版本号，作为行的删除标记。
Update

将存在两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作为老数据行的更新版本号。

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

脏读幻读不可重复读

脏读：一事务未提交的中间状态的更新数据被其他会话读取到。

当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中(commit未执行)，这时，另外会话也访问这个数据，因为这个数据是还没有提交，那么另外一个会话读到的这个数据是脏数据，依据脏数据所做的操作也可能是不正确的。
不可重复读（针对跟新）：简单来说就是在一个事务中读取的数据可能产生变化，ReadCommitted也称为不可重复读。

在同一事务中，多次读取同一数据返回的结果有所不同。换句话说就是，后续读取可以读到另一会话事务已提交的更新数据。相反，“可重复读”在同一事务中多次读取数据时，能够保证所读数据一样，也就是，后续读取不能读到另一会话事务已提交的更新数据。
幻读（针对插入）：会话T1事务中执行一次查询，然后会话T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。

这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。

如何保证rr级别绝对不产生幻读？

在使用的select …where语句中加入 for update(排他锁) 或者 lock in share mode(共享锁)语句来实现。其实就是锁住了可能造成幻读的数据，阻止数据的写入操作。

其实是因为数据的写入操作(insert 、update)需要先获取写锁，由于可能产生幻读的部分，已经获取到了某种锁，所以要在另外一个会话中获取写锁的前提是当前会话中释放所有因加锁语句产生的锁

查看锁情况

通过如下sql可以查看等待锁的情况

select * from information_schema.innodb_trx where trx_state="lock wait";
或
show engine innodb status;

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算法都会被触发。

如何尽可能避免死锁

以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁。
大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。（我司mysql规范做法）
为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大