MySQL数据库锁介绍

r囧r小猫 2022-06-09 04:57 163阅读 0赞

# MySQL数据库锁介绍 #

## 1. 锁的基本概念 ##

**当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。**

锁就是其中的一种机制。

我们可以用商场的试衣间来做个比喻。商场里得每个试衣间都可供多个消费者使用，因此可能出现多个消费者同时试衣服需要使用试衣间。为了避免冲突，试衣间装了锁，某一个试衣服的人在试衣间里把锁锁住了，其他顾客就不能再从外面打开了，只能等待里面的顾客，试完衣服，从里面把锁打开，外面的人才能进去。

## 2. 锁的基本类型 ##

数据库上的操作可以归纳为两种：读和写。

多个事务同时读取一个对象的时候，是不会有冲突的。**同时读和写，或者同时写才会产生冲突。**因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

### 2.1 共享锁（Shared Lock，也叫S锁） ###

共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

产生共享锁的sql：select \* from ad\_plan lock in share mode;

### 2.2 排他锁(Exclusive Lock，也叫X锁) ###

排他锁也叫写锁(X)。

排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了)  
产生排他锁的sql： select \* from ad\_plan for update;

**对于锁，通常会用一个矩阵来描述他们之间的冲突关系。  
      S      X  **

**S    +      –  **

**X    –      –  **

**+ 代表兼容， - 代表不兼容**

<table> 
 <tbody> 
 <tr> 
 <td> 时间\事务 </td> 
 <td> Tx1: </td> 
 <td> Tx2: </td> 
 </tr> 
 <tr> 
 <td> T1 </td> 
 <td> set&nbsp;autocommit=0; </td> 
 <td> set&nbsp;autocommit=0; </td> 
 </tr> 
 <tr> 
 <td> T2 </td> 
 <td> select&nbsp;*&nbsp;from&nbsp;ad_plan&nbsp;lock&nbsp;in&nbsp;share&nbsp;mode; </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> T3 </td> 
 <td> &nbsp; </td> 
 <td> update&nbsp;ad_plan&nbsp;set name=''&nbsp;;&nbsp;blocking </td> 
 </tr> 
 </tbody> 
</table>

执行sql：  select \* from information\_schema.innodb\_locks; 可以查看锁。

## 3. 锁的粒度 ##

就是通常我们所说的锁级别。**MySQL有三种锁的级别：页级、表级、行级。**

相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。

比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。  
MySQL这3种锁的特性可大致归纳如下：  
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。  
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。  
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。

### 3.1 行锁(Row Lock) ###

对一行记录加锁，只影响一条记录。  
通常用在DML语句中，如INSERT, UPDATE, DELETE等。  
**InnoDB行锁是通过给索引上的索引项加锁来实现的**，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：**只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！**

用下面例子来说明一下：

CREATE TABLE test\_index(id int , name VARCHAR(50),age int )engine=innodb ;  
INSERT INTO test\_index values(1,'张一',15);  
INSERT INTO test\_index values(3,'张三',16);  
INSERT INTO test\_index values(4,'张四',17);  
INSERT INTO test\_index values(5,'张五',19);  
INSERT INTO test\_index values(7,'刘琦',19);

不再启用多事务描述了，直接解释执行查询语句

**explain** select \* from test\_index where id = 1;

type: all ，rows: 5 很明显是会使用全表锁。

增加索引，id加唯一索引，age加普通索引。

ALTER TABLE test\_index  
ADD UNIQUE uk\_id(id),

type: const ，key:uk\_id,rows: 1 很明显是会使用行锁，锁定一条记录。

下面做个有趣的实验：两个事务，TX1加共享行锁, 查询age=17的记录， TX2往数据库里插入一条age=18的记录。

TX1:

mysql> set autocommit=0;  
mysql> select \* from test\_index where age=17 lock in share mode;  
\+------+------+------+  
| id   | name | age  |  
\+------+------+------+  
|    4 | 张四 |   17 |  
\+------+------+------+  
1 row in set (0.00 sec)

TX2:

mysql> set autocommit=0;  
mysql> insert test\_index values(8,'test',18);  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

结果是TX2获取锁超时，看来TX1锁定的并不止age=17的记录，不存在的间隙age=18，也被加锁了。

执行select \* from information\_schema.innodb\_locks;可以看到加锁的具体信息

\+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+  
| lock\_id      | lock\_trx\_id | lock\_mode | lock\_type | lock\_table          | lock\_index | lock\_space | lock\_page | lock\_rec | lock\_data          |  
\+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+  
| 45288:57:5:5 | 45288       | X,GAP     | RECORD    | \`test\`.\`test\_index\` | idx\_age    |         57 |         5 |        5 | 19, 0x000000000208 |  
| 45289:57:5:5 | 45289       | S,GAP     | RECORD    | \`test\`.\`test\_index\` | idx\_age    |         57 |         5 |        5 | 19, 0x000000000208 |  
\+--------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+--------------------+

行锁S、X锁上做了一些精确的细分，在代码中称作Precise Mode。这些精确的模式,  使的锁的粒度更细小。可以减少冲突。

A.**间隙锁**(Gap Lock),只锁间隙。

B.**记录锁**(Record Lock) 只锁记录。

C.**Next-Key Lock**(代码中称为Ordinary Lock)，同时锁住记录和间隙。

D.**插入意图锁**(Insert Intention Lock),插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK\_INSERT\_INTENTION的标记。

**行锁兼容矩阵**

\+ 代表兼容， -代表不兼容.

G代表Gap锁，I代表插入意图锁,R代表记录锁，N代表Next-Key锁.

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。

精确模式的检测，用在S、X和X、X之间。

从这个矩阵可以看到几个特点：

A. INSERT操作之间不会有冲突。

B. GAP,Next-Key会阻止Insert。

C. GAP和Record,Next-Key不会冲突

D. Record和Record、Next-Key之间相互冲突。

E. 已有的Insert锁不阻止任何准备加的锁。

Gap lock：  
**间隙锁只会出现在辅助索引（index）上，唯一索引(unique)和主键索引是没有间隙锁。**

间隙锁（无论是S还是X）只会阻塞insert操作。

间隙锁的目的是为了防止幻读（但是需要应用自己加锁，innodb默认不会加锁防止幻读）。

### 3.2 页面锁 ###

### 3.3 表锁(Table Lock) ###

对整个表加锁，影响标准的所有记录。通常用在DDL语句中，如DELETE TABLE,ALTER TABLE等。

很明显，表锁影响整个表的数据，因此并发性不如行锁好。  
在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory等一些非事务性存储引擎。

因为表锁覆盖了行锁的数据，所以表锁和行锁也会产生冲突（商场关门了，试衣间自然也没法使用了）。如:  
A. trx1 BEGIN  
 B. trx1 给 T1 加X锁，修改表结构。  
 C. trx2 BEGIN  
 D. trx2 给 T1 的一行记录加S或X锁（事务被阻塞，等待加锁成功)  
 trx1要操作整个表，锁住了整个表。那么trx2就不能再对T1的单条记录加X或S锁，去读取或修这条记录。

#### 3.3.1 表锁—意向锁 ####

为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁。

A. 意向锁分为意向读锁(IS)和意向写锁(IX)。

B. 意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表。     所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。

C. 在给一行记录加锁前，首先要给该表加意向锁。也就是要同时加表意向锁和行锁。

采用了意向锁后，上面的例子就变成了：

A. trx1 BEGIN

B. trx1 给 T1 加X锁，修改表结构。

C. trx2 BEGIN

D. trx2 给 T1 加IX锁（事务被阻塞，等待加锁成功)

E. trx2 给 T1 的一行记录加S或X锁.

**表锁的兼容性矩阵**