博主最新文章
博主热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)熟悉MySQL数据库的朋友们都知道，查询数据常见模式有三种：
1. select ... ：快照读，不加锁
2. select ... in share mode：当前读，加读锁
3. select ... for update：当前读，加写锁
从技术层面理解三种方式的应用场景其实并不困难，下面我们先快速复习一下这三种读取模式的在技术层面上的区别。
注：为了简化问题的描述，下面所有结论均是针对MySQL数据库InnoDB储存引擎RR隔离级别的。
1.1 select ... &
读取当前事务开始时结果集的快照版本，快照版本也可以理解为历史版本。
因为只需读取一个历史版本，而历史不会被修改，故历史版本本身就是一个不可变版本，所以本读取模式对读取前后的资源处理相对简单：
1. 读取行为发生之前，如果有其他尚未提交的事务已经修改了结果集，本读取模式不会等待这些事务结束，自然也读取不到这些修改。
2. 读取行为发生之后，当前事务提交之前，本读取模式也不会阻止其他事务修改数据，产生更新版本的结果集。
1.2 select ...&in share mode
读取结果集的最新版本，同时防止其他事务产生更新的数据版本。
由于数据的最新版本是不断变化的，所以本读取模式需要强制阻断最新版本的变化，保证自己读取到的是所有人都一致认可的名副其实的最新版本。
本读取模式在读取前后对资源处理如下：
1. 读取行为发生之前，获取读锁。这意味着如果有其他尚未提交的事务已经修改了结果集，本读取模式会等待这些事务结束，以确保自己稍后可以读取到这些事务对结果集的修改，同时等待期间会阻塞其他事务对结果集的修改。
2. 读取行为发生之后，当前事务提交之前，本读取模式会持续阻塞其他事务对结果集的修改。
3. 当前事务提交后，释放读锁。这意味着所有之前被阻塞的事务可恢复继续执行。
1.3 select ... for update
本读取模式拥有select ... in share mode的一切功能，同时它还额外具备阻止其他事务读取最新版本的能力。
本读取模式在读取前后对资源的处理如下：
1. 读取行为发生之前，获取写锁。这意味着如果有其他尚未提交的事务已经修改了结果集，本读取模式会等待这些事务结束，以确保自己稍后可以读取到这些事务对结果集的修改，同时等待其他会组织其他事务对结果集最新版本的读取和修改。
2. 读取行为发生之后，当前事务提交之前，本读取模式会持续阻塞其他事务对结果集的修改，也会阻塞其他事务对结果集最新版本的读取。
3. 当前事务提交后，释放写锁。这意味着所有之前被阻塞的事务可恢复继续执行。
三种读取模式在技术层面的区别到此就复习完了，可是我们在实际业务编程过程中，读取数据库中的记录到底什么时候要加读锁，什么时候要加写锁呢？
读取快照版本的历史数据和读取最新版本的数据映射到业务层面是怎样的一种业务逻辑需求？难道每写一处数据库查询代码，都要从技术层面去细细思考不同读取模式其读取行为发生之前、之后对资源的处理是否符合业务需求吗？这样编程也太辛苦啦。
带着上述疑问，本文将尝试从每种读取模式的技术性功能出发，将不同模式下的技术功能差异转换为业务需求差异，从而总结出不同功能的应用场景，最终产出少数的操作性强的场景判定规则，用于快速回答不同业务场景下查询数据库是否应该加读锁或写锁这一问题。
不过在讨论数据库加锁的应用场景之前，我们先弄清楚一个问题，应用层可以加锁，数据库也可以加锁，他们之间的功能似乎有一点重叠，那么什么情况下需要使用数据库锁而不是应用层锁呢？
二、应用层加锁 vs 数据库加锁
应用层加锁，指的是在同一个进程内，通过同步代码块（临界区）、信号量、Lock锁对象等编程组件，实现并发资源的有序访问。
理论上来说，数据库加锁需要解决的问题，通过应用层锁都能解决。
但是应用层加锁最大的局限在于其作用范围是单进程内。在分布式集群系统盛行的今天，绝大部分模块都有可能会启动多个进程实例，以实现负载均衡功能。如果两个进程并发访问数据库，通过进程内的应用层锁，是无法将跨进程的多个处理流程协调成有序执行的。
同时我们也应该认识到，数据库锁是稀缺资源，因为储存着状态的数据库难以横向扩展，几乎是整个系统的最终瓶颈。而无状态的计算处理模块可以轻松的弹性伸缩，一个性能不够启动两个，两个不够启动三个。。。
所以，我们可以得出如下结论：
结论1：只会在单进程内形成的资源争用，进程内部应优先使用应用层锁自己解决，而不应该将其转嫁给数据库锁（虽然很多时候用巧妙地使用数据库锁可能编程更加方便）。数据库锁应主要用于解决多进程间并发处理数据库中的数据时可能形成的混乱。
下面我们讨论的数据库加锁应用场景，其间提及的多个事务，均是指的这些事务在不同进程中开启的情况。
三、技术功能差异到业务需求差异的转换
2.1 select ... for update vs select ... in share mode
select ... for update相对于select ... in share mode而言，对读取到的结果集的最新版本具有更强的独占性。select ... in share mode只是阻塞其他事务对结果集产生更新版本，而select .. for update还会阻塞其他事务对结果集最新版本的读取。
业务层面在什么情况下需要阻塞其他事务对结果集最新版本的读取呢？
不想让别人也可以读取到最新版本，往往是因为自己想在最新版本上进行修改，同时担心其他人也和自己一样。因为大家在修改数据时，总是希望自己的修改与数据的最新版本（而不是历史版本）合并后存入数据库中，所以大家在修改数据前，都会尝试获取数据的最新版本，基于最新版本进行修改。如果每个人都可以同时获取到数据的最新版本并在最新版本上加入自己的修改，最后大家一起提交数据，必然会出现一个人的修改覆盖了其他人修改的情况，这就是经典的&更新丢失&问题。如下图所示：
其实这个问题还可以反过来问，什么情况下不必阻塞其他事务对结果集的读取呢？
试想如果无论你阻不阻塞读取，其他事务读取到的结果集都是一样的，你又何必阻塞它呢？如果你不修改读取出的结果集，那么别人早读晚读又有什么区别？
通过上面的思考，我们可以得出如下结论：
结论2：如果读取出的数据自己不需要修改它，是肯定不需要使用select ... for update的。
结论3：如果读取出的数据自己需要修改它，&更新丢失&问题在绝大部分业务场景中都是应该避免的，所以此时需要使用select ... for update。
2.2 select ... in share mode vs select ...
select ... in share mode相对于select ... 而言，主要新增了两点约束：
1. 读取数据之前，等待修改了这些数据的事务提交。
2. 读取数据之后，防止其他事务修改这些数据。
我们先用业务层面的语言将上述两点约束合并简述为：希望读取到所有人都一致认可的最新版本的数据（即没有其他人还正在修改这些数据）并锁定它。
那么什么样的业务场景下，我们需要达到这样的效果呢？
我能想到的有如下两个典型的场景：
例1. 基于更新时间戳增量处理数据
我此次读取并处理了时间点A之前的数据，下次就不会再读取并处理这个范围内的数据了，这就是增量处理的要求。如果我读取之前有人已经修改这个范围内的数据，只是事务尚未提交（由于修改行为发生在时间点A之前，所以这些数据的更新时间戳也在时间点A之前），我读取之后这些修改提交了。
若我采用的是普通的select ... 意味着虽然我读取并处理了时间点A之前的数据，但是在我读取之后这个范围内又出现了新的数据。这就会漏掉部分尚未处理的数据。如下图所示：
如果我采用的是select ... in share mode，则会等待待查询时间范围内的修改均提交后，再处理这个范围内的数据，就可以避免漏处理问题。
本例中出现的问题隐含了一个前提条件，那就是新的数据提交时，新增数据的一方并没有主动通知我进行处理，而是由我去基于时间戳扫描新增数据。相当于业务逻辑的完整性由我单方面保证，而另外一方并不愿意为此事效劳。事实上基于更新时间戳增量处理数据的场景中，通常处理程序是第三方，基于时间戳扫描增量数据只是为了尽量保证原数据表上应用系统无需修改，即减少侵入性。
（注：基于更新时间戳处理新增数据时，设置安全读取时延是更加常用的解决方式。即每次读取的时间点设置为当前时间X分钟前，X分钟大于系统中事物持续的最大时间，以保证抽取时间点之前的所有修改都已提交。但是这种方式会降低数据处理的实时性。）
那么，假设修改数据的每一方都愿意通力配合，竭尽全力地保证数据的一致性和业务逻辑的完整性时，就不会出问题了么？请看下面这个例子。
例2. 更新关联关系
比如，比如有Books和Students两张表，一张BooksToStudents的多对多关联表。新增Book需要让每个Studuent都有这个Book。新增Student需要让所有Book都属于该Student。无论何时，对数据一致性的要求是：所有Student都拥有所有的Book。
如果两个人A和B，同时开启事务，一人新增BookA，一人新增StuduentB，大家各自严格按照数据一致性要求去维护BooksToStudents关联表。
如果不使用select ... in share mode而是使用select ... ，由于每个事务都无法读取到对方的尚未提交的新增实体，A不知道有StudentB，所以A的BookA不会属于StudentB；B不知道有BookA，所以B的StudentB下不会有BookA。最终两个事务提交后，结果就是StudentB没有拥有BookA。如下图所示：
A和B都有机会建立起StudentB下拥有BookA这一关联记录，但是这份关联记录的建立只在A添加BookA时，以及B添加StudentB时处理，如果这两个时刻均读取不到需要的记录，这份关联记录的建立将永远不会再被触发。
但是，如果使用select ... in share mode，当A读取Students表时，发现没有StudentB后，B也无法再往Students表中添加StudentB，直至A的事务提交。届时，B再读取Books表时，也能发现A提交的BookA，进而正确新增StudentB下拥有BookA这一关联记录。
本例虽以多对多关联关系为例，其实在一对多、多对一关联关系中也可能存在类似问题。原理都大同小异，只不过一对多、多对一的关联关系通常直接储存在关联实体的某一列中，而不是储存在独立的关联关系表中。
例1呈现出来的场景可以总结为：
结论4：当数据一致性和业务逻辑完整性只能由自己单方面保证时，且自己利用了数据的某种单调性增量处理数据时，需使用select ... in share mode查询更新数据。
例2呈现出来的场景可以总结为：
结论5：当有关联关系的两个实体可能同时新增时，一方因新增实体修改关联关系，需使用select ... in share mode查询另一方数据进行关联关系的更新。
2.3 select ... 快照读有那么危险吗？
看了上面的介绍，大家可能恨不得所有查询都使用最严格的select ... for update，这样至少不会错。但是作为最常见的普通select语句，真的有那么危险吗？
快照读意味着读取历史数据，其实把时间放长远了看，基本上绝大部分数据后续都有更新的可能。所以即便是使用最严格的select ... for update读取模式，读到的数据也终究抵不过时间的流逝，沦为历史数据。用户更多关注的并不是某份数据有多新，而是某份数据不要太过时，快照读读取的历史数据通常也就是最近几十毫秒到几秒前的历史版本，完全能够满足用户的查看需求。
当读取数据是为了后台严格的逻辑控制判定时，我们会担心读取过程中出现的更新版本的数据会错过本次事务中的处理逻辑，但是这个担心一般来说也是多余的，因为别人产生新版本的数据时，必然也会触发一系列的处理来保证数据的一致性和业务逻辑的完整性，不必在自己的事务中过于操心别人的事情。
我们的原则通常是，优先使用锁范围小的查询模式，以尽量提升数据库的并发性能。即先选select ... ，不行再用select ... in share mode，再不行再提升为select ... for update。而结论2告诉我们何时无需用select ... for update，在此原则下，我们需要搞清楚的是何时需要用select ... for update，所以这个结论可以忽略。
我们的日常开发中，大部分情况下不需要自己单方面保证数据的一致性和业务逻辑的完整性，所有数据的修改方都可以通力合作。所以结论4可以暂时忽略。
综上，日常开发过程中，我们需记住：
1.&只会在单进程内形成的资源争用，进程内部应优先使用应用层锁自己解决，而不应该将其转嫁给数据库锁。数据库锁应主要用于解决多进程间并发处理数据库中的数据时可能形成的混乱。
2. 优先使用select ...
3.&当有关联关系的两个实体可能同时新增时，一方因新增实体修改关联关系，需使用select ... in share mode查询另一方数据进行关联关系的更新。
4. 如果读取出来的数据需要修改后再提交，需使用select ... for update读取数据。
如果你不幸需要与第三方系统（或难以修改的遗留系统）以数据库的方式进行集成时，需再多记住一点：
5.&当数据一致性和业务逻辑完整性只能由自己单方面保证时，且自己利用了数据的某种单调性增量处理数据时，需使用select ... in share mode查询更新数据。
如果还有其他漏掉的场景规则，欢迎大家补充。
阅读(...) 评论()多线程同步如何不加锁?加锁不加锁各有什么优势和性能? - 知乎53被浏览<strong class="NumberBoard-itemValue" title="分享邀请回答37 条评论分享收藏感谢收起43 条评论分享收藏感谢收起写回答数据库为什么需要锁机制？有哪些锁机制_百度知道
数据库为什么需要锁机制？有哪些锁机制
我有更好的答案
数据库锁的产生原因:数据库和操作系统一样，是一个多用户使用的共享资源。当多个用户并发地存取数据 时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并 发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严 重影响应用的正常执行。 在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。
采纳率：77%
来自团队：
为您推荐：
其他类似问题
&#xe675;换一换
回答问题，赢新手礼包&#xe6b9;
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。Mysql数据库锁定机制详细介绍
转载 & & 投稿：junjie
这篇文章主要介绍了Mysql数据库锁定机制详细介绍,本文用大量内容讲解了Mysql中的锁定机制,例如MySQL锁定机制简介、合理利用锁机制优化MySQL等内容,需要的朋友可以参考下
为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。本章将对MySQL中两种使用最为频繁的存储引擎MyISAM和Innodb各自的锁定机制进行较为详细的分析。
MySQL锁定机制简介
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。
行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
表级锁定（table-level）
和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
页级锁定（page-level）
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。
MySQL的如此的锁定机制主要是由于其最初的历史所决定的。在最初，MySQL希望设计一种完全独立于各种存储引擎的锁定机制，而且在早期的MySQL数据库中，MySQL的存储引擎（MyISAM和Momery）的设计是建立在“任何表在同一时刻都只允许单个线程对其访问（包括读）”这样的假设之上。但是，随着MySQL的不断完善，系统的不断改进，在MySQL3.23版本开发的时候，MySQL开发人员不得不修正之前的假设。因为他们发现一个线程正在读某个表的时候，另一个线程是可以对该表进行insert操作的，只不过只能INSERT到数据文件的最尾部。这也就是从MySQL从3.23版本开始提供的我们所说的Concurrent Insert。
当出现Concurrent Insert之后，MySQL的开发人员不得不修改之前系统中的锁定实现功能，但是仅仅只是增加了对Concurrent Insert的支持，并没有改动整体架构。可是在不久之后，随着BerkeleyDB存储引擎的引入，之前的锁定机制遇到了更大的挑战。因为BerkeleyDB存储引擎并没有MyISAM和Memory存储引擎同一时刻只允许单一线程访问某一个表的限制，而是将这个单线程访问限制的颗粒度缩小到了单个page，这又一次迫使MySQL开发人员不得不再一次修改锁定机制的实现。
由于新的存储引擎的引入，导致锁定机制不能满足要求，让MySQL的人意识到已经不可能实现一种完全独立的满足各种存储引擎要求的锁定实现机制。如果因为锁定机制的拙劣实现而导致存储引擎的整体性能的下降，肯定会严重打击存储引擎提供者的积极性，这是MySQL公司非常不愿意看到的，因为这完全不符合MySQL的战略发展思路。所以工程师们不得不放弃了最初的设计初衷，在锁定实现机制中作出修改，允许存储引擎自己改变MySQL通过接口传入的锁定类型而自行决定该怎样锁定数据。
MySQL的表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定。在MySQL中，主要通过四个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的读写锁定信息，如下：
Current read-lock queue (lock-&read)
Pending read-lock queue (lock-&read_wait)
Current write-lock queue (lock-&write)
Pending write-lock queue (lock-&write_wait)
当前持有读锁的所有线程的相关信息都能够在Currentread-lockqueue中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在Pendingread-lockqueue里面，另外两个存放写锁信息的队列也按照上面相同规则来存放信息。
虽然对于我们这些使用者来说MySQL展现出来的锁定（表锁定）只有读锁定和写锁定这两种类型，但是在MySQL内部实现中却有多达11种锁定类型，由系统中一个枚举量（thr_lock_type）定义，各值描述如下：
当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储
释放锁定请求的交互用所类型
普通读锁定
普通写锁定
READ_WITH_SHARED_LOCKS
在Innodb中使用到，由如下方式产生如：SELECT...LOCKINSHAREMODE
READ_HIGH_PRIORITY
高优先级读锁定
READ_NO_INSERT
不允许ConcurentInsert的锁定
WRITE_ALLOW_WRITE
这个类型实际上就是当由存储引擎自行处理锁定的时候，mysqld允许其他的线程再获取读或者写锁定，因为即使资源冲突，存储引擎自己也会知道怎么来处理
WRITE_ALLOW_READ
这种锁定发生在对表做DDL（ALTERTABLE...）的时候，MySQL可以允许其他线程获取读锁定，因为MySQL是通过重建整个表然后再RENAME而实现的该功能，所在整个过程原表仍然可以提供读服务
WRITE_CONCURRENT_INSERT
正在进行ConcurentInsert时候所使用的锁定方式，该锁定进行的时候，除了READ_NO_INSERT之外的其他任何读锁定请求都不会被阻塞
WRITE_DELAYED
在使用INSERTDELAYED时候的锁定类型
WRITE_LOW_PRIORITY
显示声明的低级别锁定方式，通过设置LOW_PRIORITY_UPDAT=1而产生
WRITE_ONLY
当在操作过程中某个锁定异常中断之后系统内部需要进行CLOSETABLE操作，在这个过程中出现的锁定类型就是WRITE_ONLY
一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：
1、请求锁定的资源当前没有被写锁定；
2、写锁定等待队列（Pendingwrite-lockqueue）中没有更高优先级的写锁定等待；
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Currentread-lockqueue中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pendingread-lockqueue中等待资源的释放。
当客户端请求写锁定的时候，MySQL首先检查在Currentwrite-lockqueue是否已经有锁定相同资源的信息存在。
如果Currentwrite-lockqueue没有，则再检查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。反之，如果Pendingwrite-lockqueue为空，则再检测Currentread-lockqueue，如果有锁定存在，则同样需要进入Pendingwrite-lockqueue等待。当然，也可能遇到以下这两种特殊情况：
1. 请求锁定的类型为WRITE_DELAYED;
2. 请求锁定的类型为WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同时Currentreadlock是READ_NO_INSERT的锁定类型。
当遇到这两种特殊情况的时候，写锁定会立即获得而进入Current write-lock queue 中
如果刚开始第一次检测就Currentwrite-lockqueue中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放了。
读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：
1. 除了READ_HIGH_PRIORITY的读锁定之外，Pendingwrite-lockqueue中的WRITE写锁定能够阻塞所有其他的读锁定；
2. READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pendingwrite-lockqueue中的写锁定；
3. 除了WRITE写锁定之外，Pendingwrite-lockqueue中的其他任何写锁定都比读锁定的优先级低。
写锁定出现在Currentwrite-lockqueue之后，会阻塞除了以下情况下的所有其他锁定的请求：
1. 在某些存储引擎的允许下，可以允许一个WRITE_CONCURRENT_INSERT写锁定请求
2. 写锁定为WRITE_ALLOW_WRITE的时候，允许除了WRITE_ONLY之外的所有读和写锁定请求
3. 写锁定为WRITE_ALLOW_READ的时候，允许除了READ_NO_INSERT之外的所有读锁定请求
4. 写锁定为WRITE_DELAYED的时候，允许除了READ_NO_INSERT之外的所有读锁定请求
5. 写锁定为WRITE_CONCURRENT_INSERT的时候，允许除了READ_NO_INSERT之外的所有读锁定请求
随着MySQL存储引擎的不断发展，目前MySQL自身提供的锁定机制已经没有办法满足需求了，很多存储引擎都在MySQL所提供的锁定机制之上做了存储引擎自己的扩展和改造。
MyISAM存储引擎基本上可以说是对MySQL所提供的锁定机制所实现的表级锁定依赖最大的一种存储引擎了，虽然MyISAM存储引擎自己并没有在自身增加其他的锁定机制，但是为了更好的支持相关特性，MySQL在原有锁定机制的基础上为了支持其ConcurrentInsert的特性而进行了相应的实现改造。
而其他几种支持事务的存储存储引擎，如Innodb，NDBCluster以及BerkeleyDB存储引擎则是让MySQL将锁定的处理直接交给存储引擎自己来处理，在MySQL中仅持有WRITE_ALLOW_WRITE类型的锁定。
由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎，来实例演示表级锁定的一些基本特性。由于，为了让示例更加直观，我将使用显示给表加锁来演示：RITE_ALLOW_READ 类型的写锁定。
行锁定基本演示
mysql& set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql& set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql& update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新，但是不提交
mysql& update test_innodb_lock set b = 'b1' where a = 1;
被阻塞，等待
mysql& Query OK, 0 rows affected (0.05 sec) 提交
mysql& update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 0 rows affected (36.14 sec)
Rows matched: 1 Changed: 0 Warnings: 0
解除阻塞，更新正常进行
无索引升级为表锁演示
mysql& update test_innodb_lock set b = '2' where b = 2000;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& update test_innodb_lock set b = '3' where b = 3000;
被阻塞，等待
mysql& Query OK, 0 rows affected (0.10 sec)
mysql& update test_innodb_lock set b = '3' where b = 3000;
Query OK, 1 row affected (1 min 3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
阻塞解除，完成更新
间隙锁带来的插入问题演示
mysql& select * from test_innodb_
+------+------+ | a | b |+------+------+
| 1 | b2 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql& update test_innodb_lock set b = a * 100 where a & 4 and a & 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& insert into test_innodb_lock values(2,'200');
被阻塞，等待
Query OK, 0 rows affected (0.02 sec)
mysql& insert into test_innodb_lock values(2,'200');
Query OK, 1 row affected (38.68 sec)
阻塞解除，完成插入
使用共同索引不同数据的阻塞示例
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; 被阻塞
Query OK, 0 rows affected (0.02 sec)
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; Query OK, 1 row affected (42.89 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 提交事务，阻塞去除，更新完成
mysql& update t1 set id = 110 where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql& update t2 set id = 210 where id = 21;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql&update t2 set id=2100 where id=21;
等待sessionb释放资源，被阻塞
mysql&update t1 set id=1100 where id=11;
Query OK,0 rows affected (0.39sec)
Rows matched: 0 Changed: 0 Warnings:0
等待sessiona释放资源，被阻塞
两个 session 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁
行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。
Innodb 锁定模式及实现机制
考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下Innodb的锁定特性。
总的来说，Innodb的锁定机制和Oracle数据库有不少相似之处。Innodb的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
共享锁（S）
排他锁（X）
意向共享锁（IS）
意向排他锁（IX）
共享锁（S）
排他锁（X）
意向共享锁（IS）
意向排他锁（IX）
虽然Innodb的锁定机制和Oracle有不少相近的地方，但是两者的实现确是截然不同的。总的来说就是Oracle锁定数据是通过需要锁定的某行记录所在的物理block上的事务槽上表级锁定信息，而Innodb的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEYlocking”（间隙锁），因为Query执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了组织幻读的出现，所以他们选择的间隙锁来实现锁定。
除了间隙锁给Innodb带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：
当Query无法利用索引的时候，Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定
Innodb 各事务隔离级别下锁定及死锁
Innodb实现的在ISO／ANSISQL92规范中所定义的ReadUnCommited，ReadCommited，RepeatableRead和Serializable这四种事务隔离级别。同时，为了保证数据在事务中的一致性，实现了多版本数据访问。
之前在第一节中我们已经介绍过，行级锁定肯定会带来死锁问题，Innodb也不可能例外。至于死锁的产生过程我们就不在这里详细描述了，在后面的锁定示例中会通过一个实际的例子为大家爱展示死锁的产生过程。这里我们主要介绍一下，在Innodb中当系检测到死锁产生之后是如何来处理的。
在Innodb的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当Innodb检测到系统中产生了死锁之后，Innodb会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。那Innodb是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题，实际上在Innodb发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。但是有一点需要注意的就是，当产生死锁的场景中涉及到不止Innodb存储引擎的时候，Innodb是没办法检测到该死锁的，这时候就只能通过锁定超时限制来解决该死锁了。另外，死锁的产生过程的示例将在本节最后的Innodb锁定示例中演示。
Innodb 锁定机制示例
mysql& create table test_innodb_lock (a int(11),b varchar(16)) engine=
Query OK, 0 rows affected (0.02 sec)
mysql& create index test_innodb_a_ind on test_innodb_lock(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql& create index test_innodb_lock_b_ind on test_innodb_lock(b);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
行锁定基本演示
mysql& set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql& set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql& update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新，但是不提交
mysql& update test_innodb_lock set b = 'b1' where a = 1;
被阻塞，等待
mysql& Query OK, 0 rows affected (0.05 sec) 提交
mysql& update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 0 rows affected (36.14 sec)
Rows matched: 1 Changed: 0 Warnings: 0
解除阻塞，更新正常进行
无索引升级为表锁演示
mysql& update test_innodb_lock set b = '2' where b = 2000;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& update test_innodb_lock set b = '3' where b = 3000;
被阻塞，等待
mysql& Query OK, 0 rows affected (0.10 sec)
mysql& update test_innodb_lock set b = '3' where b = 3000;
Query OK, 1 row affected (1 min 3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
阻塞解除，完成更新
间隙锁带来的插入问题演示
mysql& select * from test_innodb_
+------+------+ | a | b |+------+------+
| 1 | b2 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql& update test_innodb_lock set b = a * 100 where a & 4 and a & 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& insert into test_innodb_lock values(2,'200');
被阻塞，等待
Query OK, 0 rows affected (0.02 sec)
mysql& insert into test_innodb_lock values(2,'200');
Query OK, 1 row affected (38.68 sec)
阻塞解除，完成插入
使用共同索引不同数据的阻塞示例
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; 被阻塞
Query OK, 0 rows affected (0.02 sec)
mysql& update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; Query OK, 1 row affected (42.89 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 提交事务，阻塞去除，更新完成
mysql& update t1 set id = 110 where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql& update t2 set id = 210 where id = 21;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql&update t2 set id=2100 where id=21;
等待sessionb释放资源，被阻塞
mysql&update t1 set id=1100 where id=11;
Query OK,0 rows affected (0.39sec)
Rows matched: 0 Changed: 0 Warnings:0
等待sessiona释放资源，被阻塞
两个 session 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁
合理利用锁机制优化MySQL
MyISAM 表锁优化建议
对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。
所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。
&1、缩短锁定时间
缩短锁定时间，短短几个字，说起来确实听容易的，但实际做起来恐怕就并不那么简单了。如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。
尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；
尽可能的建立足够高效的索引，让数据检索更迅速；
尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
利用合适的机会优化MyISAM表数据文件；
2、分离能并行的操作
说到MyISAM的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。
MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：
concurrent_insert=2，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行ConcurrentI
concurrent_insert=1，当MyISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行ConcurrentI
concurrent_insert=0，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许ConcurrentInsert。
3、合理利用读写优先级
在本章各种锁定分析一节中我们了解到了MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。所以，如果我们可以根据各自系统环境的差异决定读与写的优先级。如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL尽量先处理读请求。当然，如果我们的系统需要有限保证数据写入的性能的话，则可以不用设置low_priority_updates参数了。
这里我们完全可以利用这个特性，将concurrent_insert参数设置为1，甚至如果数据被删除的可能性很小的时候，如果对暂时性的浪费少量空间并不是特别的在乎的话，将concurrent_insert参数设置为2都可以尝试。当然，数据文件中间留有空域空间，在浪费空间的时候，还会造成在查询的时候需要读取更多的数据，所以如果删除量不是很小的话，还是建议将concurrent_insert设置为1更为合适。
Innodb 行锁优化建议
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
要想合理利用Innodb的行级锁定，做到扬长避短，我们必须做好以下工作：
尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；
由于Innodb的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率
的的小建议，读者朋友可以根据各自的业务特点针对性的尝试：a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
系统锁定争用情况查询对于两种锁定级别，MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况，我们先看看
MySQL 实现的表级锁定的争用状态变量：
mysql& show status like 'table%';
+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
&+-----------------------+-------+
这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；
两个状态值都是从系统启动后开始记录，没出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。
对于Innodb所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：
mysql&showstatuslike'innodb_row_lock%';
+-------------------------------+--------+|Variable_name|Value|+-------------------------------+--------+
|Innodb_row_lock_current_waits|0|
|Innodb_row_lock_time|490578|
|Innodb_row_lock_time_avg|37736|
|Innodb_row_lock_time_max|121411|
|Innodb_row_lock_waits|13|
+-------------------------------+--------+
Innodb 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是Innodb_row_lock_time_avg（等待平均时长），Innodb_row_lock_waits（等待总次数）以及Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
此外，Innodb出了提供这五个系统状态变量之外，还提供的其他更为丰富的即时状态信息供我们分析使用。可以通过如下方法查看：
1.通过创建InnodbMonitor表来打开Innodb的monitor功能：
mysql& create table innodb_monitor(a int) engine=
Query OK, 0 rows affected (0.07 sec)
2.然后通过使用“SHOWINNODBSTATUS”查看细节信息（由于输出内容太多就不在此记录了）；
可能会有读者朋友问为什么要先创建一个叫innodb_monitor的表呢？因为创建该表实际上就是告诉Innodb我们开始要监控他的细节状态了，然后Innodb就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。
本章以MySQLServer中的锁定简介开始，分析了当前MySQL中使用最为广泛的锁定方式表级锁定和行级锁定的基本实现机制，并通过MyISAM和Innodb这两大典型的存储引擎作为示例存储引擎所使用的表级锁定和行级锁定做了较为详细的分析和演示。然后，再通过分析两种锁定方式的特性，给出相应的优化建议和策略。最后了解了一下在MySQLServer中如何获得系统当前各种锁定的资源争用状况。希望本章内容能够对各位读者朋友在理解MySQL锁定机制方面有一定的帮助。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具