数据库的引擎和锁

一、简介

行级锁：行级锁分为共享锁和排它锁。行级锁的加锁粒度最小，加锁开销最大。

表级锁：表级锁分为共享读锁（共享锁）和表独占写锁（排他锁）。加锁粒度最大，加锁开销最小。

页级锁：一次锁定向邻的一组记录，加锁粒度和开销在行级锁和表级锁之间。

二、介绍

行级锁（INODB引擎）

InnoDB 支持行级锁(row-level locking)和表级锁, 默认为行级锁。行级锁开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高。

因为锁定粒度小，发生锁定资源争用的概率小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

 

表级锁（MYISAM和INNODB引擎等大部分引擎都支持）

MyISAM 和 MEMORY 采用表级锁(table-level locking)。表级锁开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突概率高，并发度低。而且表级锁是对操作的整张表加锁，实现简单，资源消耗小
该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。锁定粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

 

页级锁（BerkeleyDB引擎）

BDB 采用页面锁(page-level locking)或表级锁，默认为页面锁。页级锁的开销和加锁时间，锁定粒度等介于行级锁和表级锁之间。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
页级锁定和行级锁定一样，会发生死锁。页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。

 

三、InnoDB的行级锁特征

InnoDB是通过给索引上的索引项加锁来实现行级锁的。

因此，只有通过索引条件检索数据，InnoDB才使用行级锁否则使用表级锁。

所以，在实际应用中需要注意这一特性，否则可能导致大量的锁冲突以致于印象并发性能。

• 在不通过索引条件查询的时候，InnoDB使用表级锁而不是行级锁
• 由于MySQL的行级锁是针对索引加的锁而不是记录，所以虽然是访问不同行的记录，但是如果是使用相同的索引键值，会出现锁冲突
• 当表中有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论使用主键索引，唯一索引或者普通索引，InnoDB都会使用行级锁来对数据进行加锁。
• 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

四、行级锁和死锁

MyISM中不会发生死锁，因为MyISM总是一次性获取所需的所有锁，要么全部满足，要么全部等待。

在InnoDB中，锁是逐步获取的，造成了死锁的可能。

 

有多种方法可以避免死锁，这里只介绍常见的三种

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

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

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

 

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

a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

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

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

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

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