MySQL--InnoDB锁机制（13-2）

只爱喝胡辣汤
79 阅读8分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第45天，点击查看活动详情

根据加锁的范围,MySQL中的锁大致可以分为: 全局锁、表级锁和行锁三类.

1 全局锁

  • 什么是全局锁

    全局锁是对整个数据库实例加锁，添加全局锁后,以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（建表、修改表结构等）和更新类事务的提交语句。

    全局锁命令(FTWRL):

    flush tables with read lock

  • 全局锁使用场景

    全局锁的典型使用场景是，做全库逻辑备份（mysqldump）. 重新做主从时候也就是把整库每个表都 select 出来存成文本. 通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份.在备份过程中整个库完全处于只读状态。

    值得注意的是当使用MySQL的逻辑备份工具mysqldump进行备份时.

    添加参数 --single-transaction ，会在导数据之前就启动一个事务，来确保拿到一致性快照视图。由于 MVCC 的支持，这个过程中数据是可以正常更新的.

    但是**--single-transaction** 方式只适用于所有的表都使用InnoDB引擎的情况,如果有表没有使用事务引擎,那么备份就需要使用FTWRL命令

  • 数据库全局锁的两种方法

    方式一

    flush tables with read lock

    方式二(不建议)

    set global readonly = true

    执行FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态,而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

  • 全局锁示例

    1. 使用数据库 test_lock, 创建 test1表
    create table test1(
  id int primary key,
  name varchar(32)
);
    1. 插入数据
    insert into test1 values(1,'A'),(2,'B'),(3,'C');
    1. 查看表数据
    mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
    1. 加全局锁
    mysql> flush tables with read lock;
    1. 执行insert操作 报错
    mysql> insert into test1 values(4,'D');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
    1. 执行查询可以正常返回结果
    mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
    1. 释放锁,添加成功
    mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
​
mysql> insert into test1 values(4,'D');
Query OK, 1 row affected (0.10 sec)
2 表级锁 (偏读)

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。

  • 表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
  • 表级锁特点: 开销小,加锁快.出现死锁的概率比较低;锁定的粒度比较大,发生锁冲突的概率比较高,并发度比较低.MyISAM引擎默认使用表级锁

1) 数据准备

-- 创建表选择 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;
​
insert into mylock01(title) values('a1'),('b1'),('c1'),('d1'),('e1');
insert into mylock02(title) values('a2'),('b2'),('c2'),('d2'),('e2');

2) 加锁语法

  1. 查询表中加过的锁
-- 0表示没有加锁,当前的所有数据库表都没有加锁
SHOW OPEN TABLES;
​
-- 查询加锁的表,条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;
  1. 手动加表锁

语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;

-- 为mylock01加读锁(共享锁),给mylock02加写锁(排它锁)
mysql> lock table mylock01 read,mylock02 write;
Query OK, 0 rows affected (0.00 sec)
​
mysql> show open tables where In_use > 0;
+-----------+----------+--------+-------------+
| Database  | Table    | In_use | Name_locked |
+-----------+----------+--------+-------------+
| test_lock | mylock01 |      1 |           0 |
| test_lock | mylock02 |      1 |           0 |
+-----------+----------+--------+-------------+
  1. 释放锁,解除锁定
unlock tables;

3) 加读锁测试

开启session1和session2两个会话窗口

  1. 在session1中对mylock01表加读锁
lock table mylock01 read;
  1. 对mylock01进行读操作, 两个窗口都可以读.
select * from mylock01;
  1. 在session1进行写操作,失败
mysql> update mylock set title='a123' where id=1;
ERROR 1100 (HY000): Table 'mylock' was not locked with LOCK TABLES
  1. 在session1中 读取其他表,比如读取mylock02表,读取失败.不能读取未锁定的表
mysql> select * from mylock02;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES
  1. 在session2中 对 mylock01表 进行写操作, 执行后一直阻塞
update mylock01 set title='a123' where id = 1;
  1. session1解除 mylock01 的锁定,session2 的修改执行.
unlock tables;
​
mysql> update mylock01 set title='a123' where id = 1;
Query OK, 1 row affected (47.83 sec)

总结:

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

4) 加写锁测试

  1. 在session1中 对mylock01表加写
 lock table mylock01 write;
  1. 在session1中, 对 mylock01 进行读写操作, 都是可以进行的
select * from mylock01 where id = 1;
update mylock01 set title = 'a123' where id = 1;
  1. 在session1中读其他表, 还是不允许
mysql> select * from mylock02 where id = 1;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES
  1. 在session2 中读mylock01 表, 读操作被阻塞
select * from mylock01;
  1. 在session2 中 对mylock01表进行写操作, 仍然被阻塞
update mylock01 set title = 'a456' where id = 1;
  1. 释放锁, session2操作执行执行.
unlock tables;

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

3 行级锁 (偏写)

1) 行级锁介绍

行锁的是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;

锁兼容

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

image.png

2) 行锁测试

  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,'a',23);
insert into innodb_lock values(null,'a',33);
insert into innodb_lock values(null,'a',43);
insert into innodb_lock values(null,'a',43);
insert into innodb_lock values(null,'b',53);
insert into innodb_lock values(null,'c',63);
insert into innodb_lock values(null,'d',73);
  1. 打开两个窗口,并开启手动提交事务(提交或者回滚事务就会释放锁)
#开启MySQL数据库手动提交
SET autocommit=0;
  1. 窗口1中, 对id为1的数据进行更新操作,但是不commit. 执行之后,在当前窗口查看表数据,发现被修改了.
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
​
mysql> update innodb_lock set name = 'aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | aaa  |   13 |
|  2 | b    |   23 |
|  3 | c    |   33 |
|  4 | d    |   43 |
+----+------+------+
  1. 在窗口2 查看表信息, 无法看到更新的内容
mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | a    |   13 |
|  2 | b    |   23 |
|  3 | c    |   33 |
|  4 | d    |   43 |
+----+------+------+
4 rows in set (0.00 sec)

总结: 在有写锁的情况下,一个事务不允许读取到另一个事务没有提交的内容. 避免了脏读的发生.

  1. 窗口1开启事务, 对innodb_lock表 id=1的这一行进行读取.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
​
mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | aaa  |   13 |
+----+------+------+
  1. 窗口2开启事务, 对id=1的数据进行修改,然后提交事务
begin;
update innodb_lock set name = 'a' where id=1;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
​
mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | a    |   13 |
|  2 | b    |   23 |
|  3 | c    |   33 |
|  4 | d    |   43 |
+----+------+------+
4 rows in set (0.00 sec)
  1. 窗口2提交事务后,窗口1再次查询,还是之前的查询结果
mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | aaa  |   13 |
+----+------+------+
​

总结: 在有写锁的情况下，一个事务内多次读取同一数据的结果始终保持一致,避免了不可重复读的问题.

avatar
文章
阅读
粉丝