《MySQL中级系列教程-第九章-MySQL锁机制详解-上篇》

刺骨的言语ヽ痛彻心扉 2023-01-02 12:21 154阅读 0赞

# 《MySQL中级系列教程-第九章-MySQL锁机制详解-上篇》 #

**锁是计算机协调多个进程或线程并发访问某一资源的机制。** 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要。

MySQL用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为**悲观锁(Pessimistic Lock)。**

MySQL中的锁其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

**表级锁：** 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

**行级锁：** 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

**页面锁：** 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

## 1.1 锁的分类 ##

### 1.1.1 按操作分 ###

*  读锁(共享锁)：`Shared Locks（S）`
 *  写锁(排它锁)：`Exclusive Locks（X）`
 *  意向共享锁：`Intention Shared Locks（IS）`
 *  意向排他锁：`Intention Exclusive Locks（IX）`
 *  自增锁：`（AUTO-INC Locks）`

### 1.1.2 按粒度分 ###

*  表锁：`Table Locks`
 *  行锁：`Row Locks`
 *  页锁：`Page Locks`

### 1.1.3 按算法划分 ###

*  记录锁：`Record Locks`
 *  间隙锁：`Gap Locks`
 *  临键锁：`Next-key Locks`

## 1.2 MyIsam引擎锁 ##

MyIsam引擎默认使用的是表锁，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

MySQL的表级锁有两种模式：

*  **表共享读锁（读锁）（Table Read Lock）**：允许本线程/进程读、不允许本线程写，其他线程可读，不可写
 *  **表独占写锁（写锁）（Table Write Lock）**：允许本线程读、写操作，其他线程不可读、不可写

### 1.2.1 准备数据 ###

建立一张使用MyIsam引擎的数据库表

CREATE TABLE `student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = MyISAM ;
    INSERT INTO student(NAME) VALUES('小明'),('小刚');

查看表是否被锁过

show open tables;

对表加锁（读锁/写锁）

lock table student read;
    lock table student write;

对表解锁

unlock tables;

### 1.2.2 MySIAM引擎写锁 ###

在MySIAM引擎中，当某个线程获取到表的写锁时，只有持有该锁的线程才可以对表进行进行操作，其他线程对表进行读、写操作时都会被等待，直到锁被释放。

*  示例

给student表加写锁

lock table student write;

当前线程可以执行update、delete、insert、query等操作

其他线程以上操作均不能执行，需要等到线程释放锁

### 1.2.3 MySIAM引擎读锁 ###

在MySIAM引擎中，当某个线程获取到表的读锁时，当前线程除了读取数据之外，不可进行其他操作，如（insert、update、delete）等操作，其他线程可以进行读的操作，不可进行写的操作。

*  示例

给t\_user表加上读锁

lock table student read;

当前线程只能执行query操作，但是只能查询本表，其他表不能查询，不能执行insert、update、delete操作

其他线程可以读取该表数据，但是insert、update、delete会出现阻塞状态。

### 1.2.4 小结 ###

在MySIAM引擎中，对表进行query、insert、update、delete等操作都会自动加上表锁，其中query操作加上的是读锁，insert等操作加上的是写锁，整个过程不需要用户干预，因此，用户一般不需要直接用lock table命令给MyISAM表显式加锁。

当使用LOCK TABLE时，不仅需要一次锁定用到的所有表，而且，同一个表取过多少别名，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！

-- 给student表加读锁
    lock table student read;
    
    -- Table 'a' was not locked with LOCK TABLES 提示表别名"a"没有被锁住
    select * from student a where a.id=1;
    
    -- 释放锁
    unlock tables;
    
    -- 取别名
    lock table student as a read;
    select * from student a where a.id=10;		-- 一切正常
    unlock tables;
    
    --取多个别名
    lock table student as a read,student b read;

### 1.2.5 表锁应用场景 ###

给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

*  SQL语句：

CREATE TABLE `t_orders`  (
      `id` int(11) NOT NULL COMMENT '订单id',
      `order_name` varchar(255) COMMENT '订单名称',
      `total` varchar(255) COMMENT '订单总金额',
      PRIMARY KEY (`id`) USING BTREE
    );
    
    CREATE TABLE `t_order_detail`  (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单详情id',
      `order_name` varchar(255) COMMENT '订单详情名称',
      `subtotal` double(255, 0)  COMMENT '订单详情金额',
      `order_id` int(11)  COMMENT '所属订单',
      PRIMARY KEY (`id`) USING BTREE
    );
    
    -- 插入订单数据
    insert into t_orders values(1,'双十一购物',0);
    insert into t_orders values(2,'618购物',0);
    -- 在订单详情插入一条数据
    insert into t_order_detail values(1,'神舟笔记本',4999,1);
    
    -- 同时更改订单表的订单金额
    update t_orders set total=total+4999 where id=1;
    
    -- 又插入一条记录
    insert into t_order_detail values(2,'华为手机',2888,1);
    
    -- 更改订单表中的订单金额
    update t_orders set total=total+2888 where id=1;

有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order\_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条

*  SQL语句：

--统计订单表金额
    select sum(total) from t_orders;
    --统计订单详情表金额
    select sum(subtotal) from t_order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order\_detail表可能已经发生了改变。因此，正确的方法应该是：

-- 给两张表都加上读锁
    Lock tables t_orders read, t_order_detail read;
    select sum(total) from t_orders;
    select sum(subtotal) from t_order_detail;
    Unlock tables;

### 1.2.6 MyISAM 的并发插入 ###

MyISAM表的读写操作之间是串行的，在一定的条件下，MyISAM表也支持查询和插入操作的并发进行，在MyISAM引擎中有一个系统变量`concurrent_insert`，专门用于控制器并发插入的行为

*  查询当前`concurrent_insert`的值：

show variables like 'concurrent_insert';

*  concurrent\_insert为0时，为NEVER状态，不允许并发插入
 *  concurrent\_insert为1时，为AUTO状态，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
 *  concurrent\_insert为2时，为ALWAYS状态，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center]

### 1.2.7 MyISAM的锁调度 ###

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

*  通过指定启动参数`low_priority_updates`，使MyISAM引擎默认给予读请求以优先的权利。
 *  通过执行命令`set low_priority_updates=1`，使该连接发出的更新请求优先级降低。
 *  通过指定INSERT、UPDATE、DELETE语句的LOW\_PRIORITY属性，降低该语句的优先级。

**low\_priority\_updates**

*  0：默认值，状态为OFF，代表写优先
 *  1：状态为NO，代表读优先

查看MyISAM的读/写优先级：

show variables like 'low_priority_updates';

更改`low_priority_updates`

set low_priority_updates=1;
    
    update low_priority userinfo set username='1' where id=1;

虽然上面的方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数`max_write_lock_count`设置一个合适的值，当一个表的写锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

show variables like 'max_write_lock_count';

看到上面的值就知道了，在读写竞争时，读锁毫无优势。

## 1.3 InnoDB 引擎锁 ##

InnoDB与MyISAM的最大不同有两点：

一是支持事务、外键；

二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。

*  **共享锁（S）：又称读锁。** 允许一个事务去读一行，阻止其他事务获取该行的排它锁
 *  **排他锁（Ｘ）：又称写锁。** 允许获取排他锁的事务更新数据，阻止其他事务获取排它锁、共享锁

InnoDB引擎加锁：

*  共享锁（S）：`SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE`
 *  排他锁（X）：`SELECT * FROM table_name WHERE ... FOR UPDATE`

### 1.3.1 InnoDB行锁实现方式 ###

#### 如何锁一行的数据 ####

如何在操作时锁定一行操作

*  在查询之后添加`for update`，其它操作会被阻塞，直到锁定的行提交commit;

查看行锁的使用信息：

show status like 'innodb_row_lock%';

*  **`Innodb_row_lock_current_waits`：当前有多少线程正在等待行锁**
 *  **`Innodb_row_lock_time`：行锁等待时间**
 *  **`Innodb_row_lock_time_avg`：行锁平均等待时间**
 *  **`Innodb_row_lock_time_max`：行锁最大等待时间**
 *  **`Innodb_row_lock_waits`：总的行锁等待数**

### 1.3.2 InnoDB写锁 ###

在InnoDB中，排它锁允许当前排它锁事务更新数据，**阻止其他事务获取排它锁、共享锁**，获取排它锁可以在查询语句后面显示的加上`for update`，来获取排它锁，触发任何修改（update/delete/insert）操作也会获取该行的排它锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，**否则，InnoDB将使用表锁！**  
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

修改自动提交为0（手动提交）

set autocommit=0;		-- 设置本次自动提交为false(重启后不生效)
    
    set global autocommit=0;	-- 设置全局的自动提交为false(关闭自动提交,需要重启客户端)

查询提交状态，0为OFF（手动提交）,1为NO（自动提交，默认值）

show variables like "autocommit";

获取排它锁，测试表锁

select * from t_orders for update;

在另一个事务中，查询某条数据，获取单条数据的排它锁，发现不能获取，**InnoDB如果没有用到索引则默认使用表锁**

select * from t_orders where id=1 for update;

#### 测试排它锁机制–阻止其他事务获取排它锁 ####

在InnoDB引擎中，获取到排它锁的事务将会阻止其他事务获取排它锁、共享锁

1、在事务中获取某条记录的排它锁

select * from t_orders where id=1 for update;

2、在其他事务中再次获取排它锁，发现出现等待现象，必须要第一事务进行提交

#### 测试排它锁机制–阻止其他事务获取共享锁 ####

1、在事务中获取某条记录的排它锁

select * from t_orders where id=1 for update;

2、在其他事务中获取共享锁，发现还是出现等待现象，必须要第一个事务进行提交

select * from t_orders where id=1 lock in share mode;

**思考：当某一行已经被排它锁锁上时，其他事务能否修改该条数据？？**

### 1.3.3 InnoDB读锁 ###

在InnoDB引擎中，某个事务获取共享锁之后，会阻止其他事务获取排它锁，但是其他事务可以获取共享锁，值得注意的是，在InnoDB引擎中，进行普通的查询操作是不会触发共享锁的，必须显示的加上`lock in share mode`，才会加上共享锁。

1、给某一行数据加上排它锁

select * from t_orders where id=1 for update;

2、在其他事务中读取

select * from t_orders where id=1;

#### 测试共享锁机制–允许其他事务读取 ####

1、先给某一行加上共享锁

select * from t_orders where id=1 lock in share mode;

2、在其他事务中读取该行数据，可正常读取

select * from t_orders where id=1 lock in share mode;

#### 测试共享锁机制–阻止其他事务获取排它锁 ####

1、先给某一行加上共享锁

select * from t_orders where id=1 lock in share mode;

2、在其他事务中进行排它锁操作（update/insert/delete/for update），发现处于等待现象

select * from t_orders where id=1 for update;

### 1.3.4 InnoDB死锁 ###

如果一个事务中请求了某表的读锁，另一个事务请求了某表的写锁，势必会被阻塞，于此同时在第一个事务中（请求读锁的事务）再次请求写锁，那么这样一来两个客户端都在等待对方的锁释放，造成死锁；

<table> 
 <thead> 
  <tr> 
   <th>Client-01</th> 
   <th>Client-02</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin; – 开启事务</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin – 开启事务</td> 
  </tr> 
  <tr> 
   <td>select * from t_orders where id=1 lock in share mode; – 共享锁</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>select * from t_orders where id=1 for update; – 排它锁</td> 
  </tr> 
  <tr> 
   <td>select * from t_orders where id=1 for update; – 排它锁</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

## 1.4 锁小结 ##

### 1.4.1 MySIAM引擎 ###

1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。

2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用对同一表查询和插入的锁争用问题。

3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置`LOW_PRIORITY_UPDATES`参数，或在INSERT、UPDATE、DELETE语句中指定`LOW_PRIORITY`选项来调节读写锁的争用。

4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

### 1.4.2 InnoDB 引擎 ###

（1）**InnoDB的行锁是基于索引实现的**，如果不通过索引访问数据，InnoDB会**默认使用表锁**。

（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

*  尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
 *  给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，**最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center]: /images/20221119/94092ce986dc47c7be1d6ecd9a135220.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221119/a043750b020c44ce93aeec84fc05b169.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221119/1ce81cf424f741ff839a50d68e4c2d1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221119/28e48ea4046940f2a0192ef57c1de0da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA57eR5rC06ZW35rWBKno_size_18_color_FFFFFF_t_70_g_se_x_16_pic_center_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221119/4ebcf7bc57b04e85b8d40d08993b7c0e.png