MySQL数据库的锁有多少种和怎么编写加锁的sql语句

一、概述

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

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

1.表级锁定(table-level)

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

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

使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎

2.行级锁定(row-level)

行级锁定最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁

使用行级锁定的主要是InnoDB存储引擎

3.页级锁定(page-level)

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

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

使用页级锁定的主要是BerkeleyDB存储引擎

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

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
  • 页级锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

适用:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统

二、表级锁定

由于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内部有两组专门的状态变量记录系统内部锁资源争用情况:

1
2
3
4
5
6
7
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语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

三、行级锁定

文章目录
  1. 1. 一、概述
    1. 1.1. 1.表级锁定(table-level)
    2. 1.2. 2.行级锁定(row-level)
    3. 1.3. 3.页级锁定(page-level)
  2. 2. 二、表级锁定
    1. 2.1. 1.MySQL 的表级锁的锁模式
    2. 2.2. 2.如何加表锁
    3. 2.3. 3.MyISAM 表锁优化建议
      1. 2.3.1. (1) 查询表级锁争用情况
      2. 2.3.2. (2) 缩短锁定时间
      3. 2.3.3. (3) 分离能并行的操作
      4. 2.3.4. (4) 合理利用读写优先级
  3. 3. 三、行级锁定
本站总访问量 | 本页面被访问 | 您是第位小伙伴

© XueSi博客 版权所有 备案号 : 赣ICP备19008485号-1