MySQL行锁详解（共享锁和排他锁）及应用级JUC锁和数据库锁的区别

个人理解悲观锁、乐观锁、行锁：悲观锁的实现，往往依靠数据库提供的锁机制加入排他锁，而乐观锁乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性，即版本号控制等

一、概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。
1.表级锁定（table-level）
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
2.行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。
3.页级锁定（page-level）
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
使用页级锁定的主要是BerkeleyDB存储引擎。
总的来说，MySQL这3种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；   
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

二、行级锁定

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下InnoDB的锁定特性。
 

1.InnoDB锁定模式及实现机制

考虑到行级锁定均由各个存储引擎自行实现，而且具体实现也各有差别，而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下InnoDB的锁定特性。
总的来说，InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁是InnoDB自动加的，不需用户干预。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

事务可以通过以下语句显示的给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁。

对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

2.InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
3.间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
例：
假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的：
（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
（2）为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
除了间隙锁给InnoDB带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：
（1）当Query无法利用索引的时候，InnoDB会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
（2）当Query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
（3）当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。
因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。
 

4.死锁
上文讲过，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。
那InnoDB是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题，实际上在InnoDB发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。
但是有一点需要注意的就是，当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。
需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法：
（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
（4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
（5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。
 

5.什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：
（1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
（2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。
在InnoDB下，使用表锁要注意以下两点。
（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

6.InnoDB行锁优化建议
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：
a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；
b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。
（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：
a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。
（3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |
+-------------------------------+-------+

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_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
锁冲突的表、数据行等，并分析锁争用的原因。具体方法如下：

mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

然后就可以用下面的语句来进行查看：

mysql> show engine InnoDB status;

监视器可以通过发出下列语句来停止查看：

mysql> drop table InnoDB_monitor;

设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“--console”选项来启动服务器以关闭写日志文件。
查看更多：
MySQL优化
MySQL各存储引擎
MySQL事务
MySQL索引类型

三、表级锁定

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。
1.MySQL表级锁的锁模式
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性：
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
2.如何加表锁
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
3.MyISAM表锁优化建议
对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。
（1）查询表级锁争用情况
MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况：

mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；
两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。
（2）缩短锁定时间
如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。
a)尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；
b)尽可能的建立足够高效的索引，让数据检索更迅速；
c)尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
d)利用合适的机会优化MyISAM表数据文件。
（3）分离能并行的操作
说到MyISAM的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。
MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：
concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置；
concurrent_insert=0，不允许并发插入。
可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。
（4）合理利用读写优先级
MyISAM存储引擎的是读写互相阻塞的，那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？
答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。
这是因为MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。
所以，如果我们可以根据各自系统环境的差异决定读与写的优先级：
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置；
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
虽然上面方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”，因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

------------------------------------------------------------------------------------------------------------------------------

Mysql数据库锁的应用场景_数据库的读锁和写锁在业务上的应用场景总结

一、背景

熟悉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查询更新数据。​​​​​​​