Mysql数据库锁定机制学习总结

最新推荐文章于 2024-03-29 21:14:06 发布

weixin_34128839

最新推荐文章于 2024-03-29 21:14:06 发布

阅读量109

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/HerrySun/blog/736274

版权

2019独角兽企业重金招聘Python工程师标准>>>

Mysql锁定机制简介

为了保证数据的一致性和完整性，数据库系统普遍存在锁定机制，锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一,Mysql的锁定机制有三种，分别是行级锁定，表级锁定，页级锁定，不同的存储引擎有着不同的锁定机制，要学会适时而用

行级锁定

颗粒度最小，发生资源争用的概率也最小，并发能力高，但每次获取锁和释放锁需要做的事情也更多，所消耗的系统资源例如内存也最大，并且容易发生死锁

表级锁定

颗粒度最大，所需系统资源也最小，由于锁定整张表，可以避免死锁，但是由于资源争用概率最高，所以并发能力也最差

页级锁定

颗粒度和并发能力以及消耗的系统资源介于上面两者之间，但也会发生死锁

注：在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式

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）定义，各值描述如下：

锁定类型	说明
IGNORE	当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储
UNLOCK	释放锁定请求的交互用所类型
READ	普通读锁定
WRITE	普通写锁定
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

读锁定

一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：

请求锁定的资源当前没有被写锁定
写锁定等待队列（Pendingwrite-lockqueue）中没有更高优先级的写锁定等待

如果满足了两个条件之后，该请求会被立即通过，并将相关的信息存入Currentread-lockqueue中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pendingread-lockqueue中等待资源的释放。

写锁定

当客户端请求写锁定的时候，MySQL首先检查在Currentwrite-lockqueue是否已经有锁定相同资源的信息存在，如果Currentwrite-lockqueue没有，则再检查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。反之，如果Pendingwrite-lockqueue为空，则再检测Currentread-lockqueue，如果有锁定存在，则同样需要进入Pendingwrite-lockqueue等待。当然，也可能遇到以下这两种特殊情况：

请求锁定的类型为WRITE_DELAYED;
请求锁定的类型为WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同时 Currentreadlock是READ_NO_INSERT的锁定类型。

当遇到这两种特殊情况的时候，写锁定会立即获得而进入Current write-lock queue 中

如果刚开始第一次检测就Currentwrite-lockqueue中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放了

读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：

除了READ_HIGH_PRIORITY的读锁定之外，Pendingwrite-lockqueue中的WRITE写锁定能够阻塞所有其他的读锁定
READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pendingwrite-lockqueue中的写锁定
除了WRITE写锁定之外，Pendingwrite-lockqueue中的其他任何写锁定都比读锁定的优先级低

写锁定出现在Currentwrite-lockqueue之后，会阻塞除了以下情况下的所有其他锁定的请求：

在某些存储引擎的允许下，可以允许一个WRITE_CONCURRENT_INSERT写锁定请求
写锁定为WRITE_ALLOW_WRITE的时候，允许除了WRITE_ONLY之外的所有读和写锁定请求
写锁定为WRITE_ALLOW_READ的时候，允许除了READ_NO_INSERT之外的所有读锁定请求
写锁定为WRITE_DELAYED的时候，允许除了READ_NO_INSERT之外的所有读锁定请求
写锁定为WRITE_CONCURRENT_INSERT的时候，允许除了READ_NO_INSERT之外的所有读锁定请求

Mysql行级锁定

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定

Innodb 锁定模式及实现机制

Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，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在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

合理利用锁机制优化MySQL

MyISAM 表锁优化建议

MyISAM存储引擎虽然消耗的资源小，但是颗粒度大，所以造成锁定资源的争用情况多，并发能力差，所以在优化上就要尽可能的让锁定的时间变短，让可能并发进行的操作尽可能的并发

1、缩短锁定时间

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短

尽量减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行

尽可能的建立足够高效的索引，让数据检索更迅速

尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型

2、分离能并行的操作

虽然MyISAM表锁读写互相阻塞，但是它提供了一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性，MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下

concurrent_insert=2，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行ConcurrentInsert
concurrent_insert=1，当MyISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行ConcurrentInsert
concurrent_insert=0，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许ConcurrentInsert

3、合理利用读写优先级

如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL尽量先处理读请求

Innodb 行锁优化建议

要想合理利用Innodb的行级锁定，做到扬长避短，我们必须做好以下工作：

尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定
合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行
尽可能减少基于范围的检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
尽量控制事务的大小，减少锁定的资源量和锁定时间长度
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本

Innodb的行级锁定和事务性会产生死锁，下面是一些比较常用的减少死锁产生概率的的小建议

类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生

查看两种锁定级别的系统内部锁资源争用情况

表级索引

show status like 'table%';

Variable_name Value
Table_locks_immediate 75
Table_locks_waited 0
Table_open_cache_hits 3
Table_open_cache_misses 0
Table_open_cache_overflows 0

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

Table_locks_immediate：产生表级锁定的次数

Table_locks_waited：出现表级锁定争用而发生等待的次数

两个状态值都是从系统启动后开始记录，没出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了

行级索引

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个状态变量，比较重要的主要是time_avg（等待平均时长），waits（等待总次数）以及time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划