澳门金沙vip 1

【澳门金沙vip】MySQL性能调优与架构设计——第7章 MySQL数据库锁定机制

今天没事就学习一下mysql的三种锁机制的优缺点:
MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。
● 行级锁定(row-level)
行级锁定最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁。
● 表级锁定(table-level)
和行级锁定相反,表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并大度大打折扣。
● 页级锁定(page-level)
页级锁定是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)
读锁定
一个新的客户端请求在申请获取读锁定资源的时候,需要满足两个条件:
1、请求锁定的资源当前没有被写锁定;
2、写锁定等待队列(Pending write-lock
queue)中没有更高优先级的写锁定等待;
如果满足了上面两个条件之后,该请求会被立即通过,并将相关的信息存入Current
read-lock queue
中,而如果上面两个条件中任何一个没有满足,都会被迫进入等待队列Pending
read-lock queue中等待资源的释放。
写锁定
当客户端请求写锁定的时候,MySQL 首先检查在Current write-lock queue
是否已经有锁定相同资源的信息存在。如果Current write-lock queue
没有,则再检查Pending write-lock queue,如果在Pending write-lock queue
中找到了,自己也需要进入等待队列并暂停自身线程等待锁定资源。反之,如果Pending
write-lock queue 为空,则再检测Current read-lock
queue,如果有锁定存在,则同样需要进入Pending write-lock queue
等待。当然,也可能遇到以下这两种特殊情况:

MySQL的3种锁定机制

第7章 MySQL数据库锁定机制

  1. 请求锁定的类型为WRITE_DELAYED;
  2. 请求锁定的类型为WRITE_CONCURRENT_INSERT
    或者是TL_WRITE_ALLOW_WRITE, 同时Current read lock 是READ_NO_INSERT
    的锁定类型。
    当遇到这两种特殊情况的时候,写锁定会立即获得而进入Current write-lock
    queue 中如果刚开始第一次检测就Current write-lock queue
    中已经存在了锁定相同资源的写锁定存在,那么就只能进入等待队列等待相应资源锁定的释放了。读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定:
  3. 除了READ_澳门金沙vip,HIGH_PRIORITY 的读锁定之外,Pending write-lock queue
    中的WRITE 写锁定能够阻塞所有其他的读锁定;
  4. READ_HIGH_PRIORITY 读锁定的请求能够阻塞所有Pending write-lock queue
    中的写锁定;
  5. 除了WRITE 写锁定之外,Pending write-lock queue
    中的其他任何写锁定都比读锁定的优先级低。
    写锁定出现在Current write-lock queue
    之后,会阻塞除了以下情况下的所有其他锁定的请求:
  6. 在某些存储引擎的允许下,可以允许一个WRITE_CONCURRENT_INSERT
    写锁定请求
  7. 写锁定为WRITE_ALLOW_WRITE 的时候,允许除了WRITE_ONLY
    之外的所有读和写锁定请求
  8. 写锁定为WRITE_ALLOW_READ 的时候,允许除了READ_NO_INSERT
    之外的所有读锁定请求
  9. 写锁定为WRITE_DELAYED 的时候,允许除了READ_NO_INSERT
    之外的所有读锁定请求
  10. 写锁定为WRITE_CONCURRENT_INSERT 的时候,允许除了READ_NO_INSERT
    之外的所有读锁定请求

 

前言:

MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下…

数据库锁定机制是数据库为了保证数据的一致性而使各种共享资源在并发访问时访问变得有序所设计的一种规则。

  
为了保证数据的一致完整性,任何一个数据库都存在锁定机制。锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能,所以锁定机制的实现也就成为了各种数据库的核心技术之一。本章将对
MySQL 中两种使用最为频繁的存储引擎 MyISAM 和 Innodb
各自的锁定机制进行较为详细的分析。

 

   7.1 MySQL 锁定机制简介

MySQL各存储引擎使用了3种类型的锁定机制:

  
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制,所以
MySQL自然也不能例外。MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计,所以各存储引擎的锁定机制也有较大区别。

 

  
总的来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定,页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

  1. 行级锁定(row-level) — MyISAM、Memory、CSV

● 行级锁定(row-level)

 

  
行级锁定最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

*锁定对象颗粒度最小,发生资源争用的概率最小,在并发处理能力上有较大优势。

  
虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁。

 

● 表级锁定(table-level)

*缺点是每次获取锁和释放锁需要做的事情很多,带来消耗大,也最容易发生死锁。

  
和行级锁定相反,表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。

 

  
当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并发度大打折扣。

 

● 页级锁定(page-level)

  1. 表级锁定(table-level)— Innodb、NDB Cluster

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

 

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

*锁定对象颗粒度最大,实现逻辑简单,获取锁和释放锁的速度很快,一次会将整个表锁定,可以很好避免死锁。

  
在MySQL数据库中,使用表级锁定的主要是MyISAM,Memory,CSV等一些非事务性存储引擎,而使用行级锁定的主要是Innodb存储引擎和NDB
Cluster存储引擎,页级锁定主要是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通过接口传入的锁定类型而自行决定该怎样锁定数据。

*主要分为2种类型:读锁定和写锁定

    7.2 各种锁定机制分析

 

  
在整体了解了MySQL锁定机制之后,这一节我们将详细分析MySQL自身提供的表锁定机制和其他储引擎实自身实现的行锁定机制,并通过MyISAM存储引擎和Innodb存储引擎实例演示。

*通过4个锁定队列来维护这2种锁定:

表级锁定

 

  
MySQL的表级锁定主要分为两种类型,一种是读锁定,另一种是写锁定。在MySQL中,主要通过四个队列来维护这两种锁定:两个存放当前正在锁定中的读和写锁定信息,另外两个存放等待中的读写锁定信息,如下:

Current read-lock queue (lock->read) —
存放持有读锁的所有线程,按获取到锁的时间顺序存放

    Current read-lock queue (lock->read)

 

    Pending read-lock queue (lock->read_wait)

Pending read-lock queue (lock->read_wait) —
存放正在等待锁定资源的线程

    Current write-lock queue (lock->write)

 

    Pending write-lock queue (lock->write_wait)

Current write-lock queue (lock->write) —
存放持有写锁的所有线程,按获取到锁的时间顺序存放

   当前持有读锁的所有线程的相关信息都能够在Current read-lock
queue中找到,队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在
Pending read-lock queue
里面,另外两个存放写锁信息的队列也按照上面相同规则来存放信息。

 

  
虽然对于我们这些使用者来说MySQL展现出来的锁定(表锁定)只有读锁定和写锁定这两种类型,但是在MySQL内部实现中却有多达11种锁定类型,由系统中一个枚举量(thr_lock_type)定义,各值描述如下:
澳门金沙vip 1

Pending write-lock queue (lock->write_wait) —
存放正在等待锁定资源的线程

读锁定

 

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

*MySQL内部实现表锁定多达11种类型,由系统中的一个枚举量(thr_lock_type)定义,各值描述如下:

   1、请求锁定的资源当前没有被写锁定;

 

   2、写锁定等待队列(Pending write-lock
queue)中没有更高优先级的写锁定等待;

IGNORE —
当发生锁请求的时候内部交互使用,在锁定结构和队列中并不会有任何信息存储

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

 

写锁定

UNLOCK — 释放锁定请求的交互用锁类型

   当客户端请求写锁定的时候,MySQL首先检查在Current write-lock
queue是否已经有锁定相同资源的信息存在。

 

   如果 Current write-lock queue 没有,则再检查 Pending write-lock
queue,如果在 Pending write-lock
queue中找到了,自己也需要进入等待队列并暂停自身线程等待锁定资源。反之,如果Pending
write-lock queue为空,则再检测Current read-lock
queue,如果有锁定存在,则同样需要进入Pending write-lock
queue等待。当然,也可能遇到以下这两种特殊情况:

READ — 普通读锁定

   1. 请求锁定的类型为WRITE_DELAYED;

 

   2. 请求锁定的类型为 WRITE_CONCURRENT_INSERT 或者是
TL_WRITE_ALLOW_WRITE,同时Current read
lock是READ_NO_INSERT的锁定类型。

WRITE — 普通写锁定

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

 

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

READ_WITH_SHARED_LOCKS — 在Innodb中使用到,由如下方式产生:SELECT
… LOCK IN SHARE MODE

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

 

   1. 除了READ_HIGH_PRIORITY的读锁定之外,Pending write-lock
queue中的WRITE写锁定能够阻塞所有其他的读锁定;

READ_HIGH_PRIORITY — 高优先级读锁定

   2. READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pending write-lock
queue中的写锁定;

 

   3. 除了WRITE写锁定之外,Pending write-lock
queue中的其他任何写锁定都比读锁定的优先级

READ_NO_INSERT — 不允许Concurrent Insert的锁定

   低。

 

写锁定出现在Current write-lock
queue之后,会阻塞除了以下情况下的所有其他锁定的请求:

WRITE_ALLOW_WRITE —
当由存储引擎自行处理锁定的时候,mysqld允许其他的线程再获取读或写锁定,因为即使资源冲突,存储引擎自己也会知道怎么处理

   1.
在某些存储引擎的允许下,可以允许一个WRITE_CONCURRENT_INSERT写锁定请求