mysql如何通过条件筛选符合条件的项_MySql数据库锁机制详解

概述

数据库锁定机制简单的来说，就是数据库为了保证数据的一致性与完整性，而使各种共享资源在被并发访问时变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁机制，所以MySQL也不能例外。MySQL数据库根据锁锁定数据的颗粒度可分为表级锁、行级锁和页级锁。

一、表级锁

1.1 什么是表级锁

表级锁会直接锁定整张表。表级锁是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁定颗粒度大所带来负面影响就是出现争用锁定资源的概率也会最高，致使并发度大大降低。

1.2 表级锁的锁模式 MySQL的表级锁有两种模式：表共享读锁和表独占写锁；

表共享读锁，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；表独占写锁，会阻塞其他用户对同一表的读和写操作；表共享读锁与表独占写锁之间，以及表独占写锁之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

1.3 表锁优化建议

虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也最少。但是由于锁定的颗粒度比较大，所以造成争用锁定资源的情况也会比其他的锁级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化表级锁问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，

所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

二、行级锁

2.1 什么是行级锁

行级锁仅对指定的记录加锁。行级锁最大的特点就是锁定对象的颗粒度小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生争用锁定资源的概率也最小，能尽可能大的提高数据库的并发处理能力从而提高高并发应用系统的性能。虽然行级锁能够极大提高并发处理能力，但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

2.2 行级锁的锁模式

MySQL行级锁有共享锁和排他锁两种。当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

2.3 行锁优化建议

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

1、在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁；

2、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的查询条件，是会出现锁冲突的；

3、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁；

4、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁；

2.4 间隙锁

当我们用范围条件而不是相等条件检索数据并请求共享锁或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是间隙锁。

假如userInfo表中只有50条记录，其userId的值分别是1，2，3，....,49,50;

下面的SQL：

mysql> select * from emp where userId > 49 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的userId值为50的记录加锁，也会对userId大于50（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的是防止幻读，以满足相关隔离级别的要求。对于上面的例子，如果不使用间隙锁，若其他事务插入了userId大于50的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；因为间隙锁锁定的是一个范围，而不是具体的索引键所以在使用范围条件检索并锁定记录时，间隙锁会将不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，

尽量使用相等条件来访问更新数据，避免使用范围条件

。还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

2.5 行锁优化建议

1、尽量让所有的查询条件都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

2、合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其它SQL的执行；

3、减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

4、尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

5、在业务环境允许的情况下，可使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；

6、类似业务模块中，尽量按照相同的访问顺序来访问，防止产生死锁；

7、在同一个事务中，尽量做到一次锁定所需要的所有资源，减少死锁产生概率；

8、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

三、页级锁

页级锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

四、行级锁和表级锁能共存吗？

行级锁与表级锁是可以共存的。InnoDB为了让表锁和行锁共存而引入了意向锁。

举个例子（此时假设行锁和表锁能共存）： 事务A锁住表中的一行（写锁）。事务B锁住整个表（写锁）。

但你就会发现一个很明显的问题，事务A既然锁住了某一行，其他事务就不可能修改这一行。这与"事务B锁住整个表就能修改表中的任意一行"形成了冲突。所以，没有意向锁的时候，行锁与表锁共存就会存在问题！

那么意向锁是如何让表锁和行锁共存的？

有了意向锁之后，前面例子中的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。由此我们可得出意向锁是表锁。

(更多文章可关注微信公众号：IT鸡窝)

参考文章: https:// blog.csdn.net/zcl_love_ wx/article/details/82015281 https://www. cnblogs.com/sessionbest /articles/8689071.html