MySQL高级 - 锁机制

RG
136 阅读9分钟

「这是我参与2022首次更文挑战的第4天，活动详情查看：2022首次更文挑战」

MySQL锁概述

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

MySQL的锁分类

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，MySQL中不同的存储引擎支持不同的锁机制。

  • MyISAM和MEMORY存储引擎采用的表级锁
  • InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
  • BDB采用的是页面锁，也支持表级锁

按照数据操作的类型分

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
  • 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分

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

按照操作性能可分为乐观锁和悲观锁

  • 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
  • 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

表级锁(偏读)

  • 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
  • 表级锁定分为：表共享读锁（共享锁）与表独占写锁（排他锁）。
  • 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

数据准备

-- 创建数据库
CREATE DATABASE test_lock CHARACTER SET 'utf8';

-- 创建表,选择 MYISAM存储引擎
CREATE TABLE mylock01(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

-- 创建表
CREATE TABLE mylock02(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

-- mylock01表中向插入数据
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');

-- mylock02表中向插入数据
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');

SELECT * FROM mylock01;

加锁语法

查看表中加过的锁

-- 0表示没有加锁,当前的所有数据库表都没有加锁
SHOW OPEN TABLES;

-- 查询加锁的表,条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;

手动增加表锁

-- 语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;
-- 为mylock01加读锁(共享锁) , 给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write;

SHOW OPEN TABLES WHERE In_use > 0;

释放锁，解除锁定

-- 方式1
unlock tables;

-- 方式2 找到锁进程,得到id
SHOW PROCESSLIST;
kill id

加读锁测试

  • MySQL的表级锁有两种模式:
    • 表共享读锁(Table Read Lock)
    • 表独占写锁(Table Write Lock)。
  1. 对mylock01表加读锁
lock table mylock01 read;
  1. 开启两个窗口，对mylock01进行读操作，两个窗口都可以读
select * from mylock01;

image.png

  1. 在1窗口进行写操作(update)，失败
update mylock01 set title='a123' where id = 1;
  1. 在1窗口中读取其他的表，比如读取mylock 02表。读取失败
select * from mylock02;

错误提示:表“mylock02”未用锁表锁定

  1. 在2窗口中对mylock01表进行写操作
update mylock01 set title='a123' where id = 1;

执行后一直阻塞

  1. 解除mylock01的锁定，窗口2的修改执行。
unlock tables;

总结:

  • 对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但是会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。

加写锁测试

  1. 在窗口1中对mylock01表加写
lock table mylock01 write;
  1. 在窗口1中，对mylock01进行读写操作，都是可以进行的
select * from mylock01 where id = 1;
update mylock01 set title = 'a123' where id = 1;

  1. 在窗口1中读其他表，还是不允许

  2. 在窗口2中读mylock01表，读操作被阻塞

select * from mylock01;
  1. 在窗口2中对mylock01表进行写操作，仍然被阻塞
update mylock01 set title = 'a456' where id = 1;
  1. 释放锁，窗口2操作执行执行。
unlock tables;

总结：对MyISAM表加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的操作

行级锁(偏写)

行级锁介绍

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

  • 使用MySQL行级锁的两个前提

    • 使用innoDB 引擎
    • 开启事务(隔离级别为Repeatable Read)

  • InnoDB行锁的类型

    • 共享锁（S）：当事务对数据加上共享锁后，其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

    • 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

  • 加锁的方式

    • InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果要加可以使用下面的方式:
    • 加共享锁（S）：select * from table_name where ... lock in share mode;
    • 加排他锁（x）：select * from table_name where ... for update;

  • 锁兼容

    • 共享锁只能兼容共享锁，不兼容排它锁
    • 排它锁互斥共享锁和其它排它锁

  • 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住，这点需要咱们格外的注意

行锁测试

1.更新时的行锁测试

1.数据准备

#创建表
CREATE TABLE innodb_lock(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    INDEX idx_name(NAME)
);

# 插入数据
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);

2.打开两个窗口，都开启手动提交事务 ( 提交事务或回滚事务就会释放锁 )

#开启MySQL数据库手动提交
SET autocommit=0;

3.执行不同会话修改操作，窗口1读，窗口2写

  • 窗口1进行，对id为1的数据进行更新操作，但是不进行commit。
  • 执行之后，在当前窗口查看表数据，发现被修改了。
update innodb_lock set name = 'aaa' where id=1;
select * from innodb_lock;
  1. 在窗口2 查看表信息，无法看到更新的内容
select * from innodb_lock;

总结：行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

  1. 窗口1对innodb_lock表的id=1的这一行，进行写操作，但是不要commit
begin;
update innodb_lock set name = 'abc' where id=1;
  1. 接下来窗口2也对innodb_lock表的id=1的这一行，进行写操作，发现发生了阻塞
begin;
update innodb_lock set name = 'a123' where id=1;
  1. 等窗口1执行commit语句之后，窗口2的SQL就会执行了

总结：在有写锁的情况下，其他事务不能再对当前数据添加写锁，从而保证数据的一致性，从而避免了不可重复读的问题。

2.查询时的排他锁测试

  1. select语句加排他锁方式：select * from table_name where ... for update
  • for update的作用
    • for update是在数据库中上锁用的，可以为数据库中的行上一个排他锁。
  • for update 的应用场景
    • 存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。
  • for update的注意点
    • for update仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
  1. 在窗口1中，首先开启事务，然后对id为1的数据进行排他查询
begin;
select * from innodb_lock where id = 1 for update;
  1. 在窗口2中，对同一数据分别使用排他查和共享锁两种方式查询
-- 排他锁查询
select * from innodb_lock where id = 1 for update;

-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;

我们看到开了排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。

  1. 如果只是使用普通查询，我们发现是可以的
select * from innodb_lock where id = 1;

3.查询时的共享锁测试

  • 添加共享锁：select * from table_name where ... lock in share mode
  • 事务获取了共享锁，在其他查询中也只能加共享锁,但是不能加排它锁。
  1. 窗口1开启事务,使用共享锁查询id = 2的数据,但是不要提交事务
begin;
select * from innodb_lock where id = 2 lock in share mode;
  1. 窗口2开启事务，使用普通查询和共享锁查询id = 2的数据，是可以的
select * from innodb_lock where id = 2 lock in share mode;
select * from innodb_lock where id = 2;
  1. 加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。
select * from innodb_lock where id = 2 for update;

行锁分析

  1. 执行下面的命令,可以获取行锁锁信息
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 51265 |
| Innodb_row_lock_time_avg      | 51265 |
| Innodb_row_lock_time_max      | 51265 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
  1. 参数说明
Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。

当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。

avatar
文章
阅读
粉丝