浅谈MySQL中的锁

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。

锁分类

在mysql中有三种锁的级别:页级锁,表级锁,行级锁.其中:

• 页级锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般.会发生在BDB存储引擎
• 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低.会发生在MyISAM,InnoDB,BDB等存储引擎中
• 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高.会发生在InnoDB存储引擎

mysql中的表锁包括读锁(表共享读锁)和写锁(表独占写锁),对myisam表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞写请求; 写操作,则会阻塞其他用户对同一表的读和写操作,myisam表的读操作和写操作之间,以及写与写操作之间都是串行的.

如何加表锁

myisam在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给myisam表显式加锁。

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

说明:上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录;
在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的 情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。
并发插入(Concurrent Inserts)
上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

MyISAM的锁调度

前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原 因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
上面已经讨论了写优先调度机制带来的问题和解决办法。这 里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

InnoDB存储引擎中的锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
该引擎中的锁分为行锁和表锁.
行锁包括共享锁(S)和排它锁(X).共享锁允许一个事务去读一行,阻止其他事务获取相同数据集的排它锁;排它锁允许获得排它锁的事务更新数据,阻止其他事务取得相同数据集的共享度锁和排他写锁.
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁,这两种意向锁都是表锁.
表锁分为三种:

• 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁.
• 意向排它锁(IX):事务打算给数据行加行排它锁,事务在给一个数据行加排它锁前必须先取得该表的IX锁.
• 自增锁:特殊表锁,自增长计数器通过该"锁"来获得自增长计数器最大的计数值.

注:我们在设计表结构时,通常会建议添加一列作为自增主键,这里就会涉及自增锁,它属于表锁,在insert结束后立即释放.
对表加表锁的前提是没有其他任何事务已经锁定该表的任一行.
InnoDB行锁是通过对索引数据页上的记录(record)加锁实现.主要实现算法有三种:

• record lock锁:单个行记录的锁(锁数据,不锁gap)
• gap lock锁:间隙锁,锁定一个范围,不包括记录本身(不锁数据,仅仅锁数据前面的gap)
• next-key lock锁:临键锁,同时锁住数据和数据前面的gap,(innodb默认)

注:同一把间隙锁不冲突,可重复获取.gap锁住了一个区间,临键锁也锁住了下一区间,所以解决了幻读问题.
排查锁问题的两种方法:一.打开innodb_lock_monitor表,注意使用后关闭,会影响性能.二.在mysql5.5之后,可以通过查看information_schema库下面的innodb_locks,innodb_lock_waits,innodb_trx三个视图.
mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

InnoDB不同索引的加锁行为

一.主键
条件:update t1 set name=‘XX’ where id=10; id为主键索引,name不是一个key.
行为:仅在id=10的主键索引记录上加排他锁.
二.唯一键
条件:同上面的sql语句; id为唯一索引,name为主键索引.
行为:先在唯一索引id上加id=10的排它锁,再在id=10的主键索引记录上加排它锁.
三.非唯一键
条件:同上面的sql语句; name为主键索引,id非唯一索引(只是一个key).
行为:通过id=10定位到第一个满足的记录,对该记录加排它锁,要在这个记录之前,上个记录之后这个之间加上gap lock,防止幻读.然后在主键索引name上加对应记录的排它锁.以此类推找到所有满足的记录.最后还要在最后一个满足的记录后面再加上一个gap lock.
四.无索引
条件:同上面的sql语句; name为主键,id不是一个key.
行为:表里所有行和间隙均加排它锁.
**注意:InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ **

InnoDB死锁的产生和避免优化

在mysql中死锁不会发生在myisam中,但会在innodb中发生,因为innodb是逐行加锁.
产生死锁的四个条件如下:

• 互斥条件:一个资源每次只能被一个进程使用
• 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放
• 不剥夺条件:进程已获得的资源,在没使用完之前不能强行剥夺
• 循环等待条件:多个进程之间形成一种互相循环等待资源的关系

在发生死锁时,innodb存储引擎会自动检测,并且会自动回滚代价较小的事务来解决死锁.但很多时候一旦死锁发生,innodb的处理效率是很低下的或者有时根本解决不了,需要人为手动解决.
避免死锁产生的一些建议:
加锁顺序一致; 尽量基于主键或者唯一键更新数据; 单次操作数据量不宜过多,涉及表尽量少; 减少表上索引,减少锁定资源; 相关工具:pt-deadlock-logger
避免线上业务因死锁造成的不必要影响的开发建议:

• 更新sql的where条件尽量用索引
• 加锁索引准确,缩小锁定范围
• 减少范围更新,尤其非主键/非唯一索引上的范围更新
• 控制事务大小,减少锁定数据量和锁定时间长度
• 加锁顺序一致,尽可能一次性锁定所有所需数据行

事务

事务是由一组sql语句组成的逻辑处理单元.具有一下4个属性:

• 原子性(Actomicity):事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
• 一致性(Consistent):在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的.比如业务一致性,-100元 +50元?
• 隔离性(Isolation):数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然
• 持久性(Durable):事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持

并发事务带来的问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失(Lost Update):当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
• 脏读(Dirty Reads):读到了其他事务已修改但未提交的数据
• 不可重复读(Non-Repeatable Reads):由于其他事务的修改，导致同一事务中两次查询读到的数据不同
• 幻读(Phantom Reads):由于其他事务的修改，导致同一事务中两次查询读到的记录数不同

事务隔离级别
在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

1. 是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
2. 是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

• 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

select * from table where ?; 

• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
  下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

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 ?;

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。
四个隔离级别分别是:

1. 未提交读(read uncommitted)
   这种隔离级别下普通select语句是不加事务锁的，因此会产生脏读，这种事务隔离级别是应当完全避免的。除select语句以外的其他语句加锁模式与READ COMMITTED一样。(未解决任何并发问题)
2. 已提交读(read committed)
   同REPEATABLE READ一样，这种隔离级别下也实现了一致性非锁定读，但区别在于此隔离级别下的一致性读是语句级的，即只能避免脏读，不能避免不可重复读和幻读。在这种隔离级别下，InnoDB的锁定读（SELECT with FOR UPDATE or LOCK IN SHARE MODE）只使用record lock类型的行锁，不使用gap锁。(只解决了脏读)
3. 可重复读(repeatable read)
   这是MySQL的默认事务隔离级别。在一个事务当中第一次读会建立一个全库snapshot，同事务下的select语句会读取这个snapshot来实现一致性非锁定读。而第一个snapshot的建立猜测与READ COMMITTED下的读取机制一样。同事务的select语句会读取这个snapshot的数据来实现一致性非锁定读，这个snapshot是针对整个数据库中所有支持MVCC机制的表的，即在snapshot建立后读取任意其他表都只会读取到snapshot中的快照数据.
   对于select for update/select lock in share mode/update/delete这些锁定读，加行锁模式取决于索引的类型：
   (1).对唯一索引的访问只会添加record lock，而不会使用gap lock（即也没有next-key lock）。
   (2).对非唯一索引的访问使用gap lock或者next-key lock，如果访问的记录不存在就是gap lock，否则就是next-key lock。
   (解决脏读和不可重复读,在innodb中也解决了幻读)
4. 串行化(serializable)
   这种事务隔离级下select语句即便不加lock in share mode也使用lock_mode=S的行锁，select自成事务，锁直到事务结束才释放。官网对于这个隔离级别的解释是只有将autocommit设置为0后select才会被隐式转换为lock in share mode的加锁模式，但是经测验发现在此模式下只要为select语句开启事务就会阻塞其他事物的更改.(给表加锁,不存在并发了)