锁机制

lienhui68 收录于技术

2020-06-04 约 5898 字预计阅读 12 分钟次阅读

概述

锁的定义

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

锁的分类

从对数据操作的类型（读、写）分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响，但是会阻断写
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写操作和读操作。
从对数据操作的粒度
- 表锁
- 行锁

表锁

表锁特点：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

表锁案例

数据准备

建表 SQL：引擎选择 myisam

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

手动加锁和释放锁

查看当前数据库中表的上锁情况：show open tables;，0 表示未上锁

1

show open tables from 具体数据库 查看某个数据库中的表锁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


mysql> show open tables from eden;
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| eden     | t4     |      0 |           0 |
| eden     | t2     |      0 |           0 |
| eden     | dept   |      0 |           0 |
| eden     | t_dept |      0 |           0 |
| eden     | t3     |      0 |           0 |
| eden     | t1     |      0 |           0 |
| eden     | t_emp  |      0 |           0 |
| eden     | emp    |      0 |           0 |
| eden     | e      |      0 |           0 |
+----------+--------+--------+-------------+
9 rows in set (0.04 sec)

添加锁

1

lock table 表名1 read(write), 表名2 read(write), ...;

给mylock表上读锁，给book表上写锁

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


# 给mylock表上读锁，给book表上写锁
LOCK TABLE `mylock` READ, `book` WRITE;
  
# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis       | book                                                 |      1 |           0 |
| sql_analysis       | mylock                                               |      1 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+

释放表锁

1

unlock tables;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


# 释放给表添加的锁
UNLOCK TABLES;
  
# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis       | book                                                 |      0 |           0 |
| sql_analysis       | mylock                                               |      0 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+

读锁案例

打开两个会话，SESSION1为mylock表添加读锁。

1
2


# 为mylock表添加读锁
LOCK TABLE `mylock` READ;

打开两个会话

SESSION1是否可以读自己锁的表是
是否可以修改自己锁的表否
是否可以读其他的表否
SESSION2是否可以读SESSION1锁住的表是

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39


# SESSION1
   
# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)
   
# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and cannot be updated
   
# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
   
   
# SESSION2
# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)
   
# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
# 在这里阻塞

写锁案例

打开两个会话，SESSION1为mylock表添加写锁。

打开两个会话

SESSION1是否可以读自己锁的表是
是否可以修改自己锁的表是
是否可以读其他的表否
SESSION2是否可以读SESSION1锁住的表否

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


# SESSION1
   
# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)
   
# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
   
# SESSION2
   
# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
# 阻塞
   
# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
# 阻塞

案例结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）

结论：结合上表，所以对MyISAM表进行操作，会有以下情况：

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把其他session的读和写都堵塞。

表锁分析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 173   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

行锁

行锁特点：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁。

事务复习

并发事务处理带来的问题

更新丢失：最后的更新覆盖了由其他事务所做的更新
脏读：读已修改但尚未提交
不可重复读：读已修改并且提交的数据
幻读：读已增加并且提交的数据

事务的隔离级别

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
查看当前数据库的事务隔离级别：show variables like 'tx_isolation'; mysql 默认是可重复读

行锁案例

数据准备

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


# 建表语句
CREATE TABLE `test_innodb_lock`(
`a` INT,
`b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁'; 

# 插入数据
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1, 'b2');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2, '3');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3, '4000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4, '5000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5, '6000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6, '7000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7, '8000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8, '9000');

# 创建索引
CREATE INDEX idx_test_a ON `test_innodb_lock`(a);
CREATE INDEX idx_test_b ON `test_innodb_lock`(b);

打开SESSION1和SESSION2两个会话，都开启手动提交

1
2
3


# 开启MySQL数据库的手动提交
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

读己之所写

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41


# SESSION1 
   
# SESSION1対test_innodb_lock表做写操作，但是没有commit。
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)
   
# SESSION2 
   
# SESSION2这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

行锁两个SESSION同时対一条记录进行写操作

1
2
3
4
5
6
7
8
9


# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
# SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！
# 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了
mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

行锁两个SESSION同时对不同记录进行写操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
   
# SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！
# SESSION1和SESSION2同时对不同的行进行写操作互不影响
mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

set autocommit和begin/start transaction

MYSQL的事务处理主要有两种方法。

用begin,rollback,commit来实现
- begin 开始一个事务
- rollback 事务回滚
- commit 事务确认
直接用set来改变mysql的自动提交模式

MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
- set autocommit=0 禁止自动提交
- set autocommit=1 开启自动提交
来实现事务的处理。但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！个人推荐使用第一种方法！ MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！（切记！）
set autocommit=0与start transaction区别
1. 不管autocommit 是1还是0，START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。
2. 当autocommit 为 0 时，不管有没有START TRANSACTION，只有当commit数据才会生效，ROLLBACK后就会回滚。

没有命中索引（无索引或索引失效）导致行锁升级为表锁

索引失效演示

session1 开启事务，修改 test_innodb_lock 中的数据，varchar 不用 ’ ’ ，导致系统自动转换类型，导致索引失效

1
2
3
4
5
6


mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
  
mysql> update test_innodb_lock set a=44 where b=4000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2 开启事务，修改 test_innodb_lock 中不同行的数据

1
2
3
4
5


mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
  
mysql> update test_innodb_lock set b='9001' where a=9;
# 阻塞...

由于发生了自动类型转换，索引失效，导致行锁变为表锁

间隙锁

是什么

查询条件命中非唯一索引或者是唯一索引的范围查询时，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制是所谓的间隙锁（Next-Key锁）。

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


-- test_innodb_lock 表中的数据
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

-- 查看索引
show index from test_innodb_lock;
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | idx_test_a |            1 | a           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| test_innodb_lock |          1 | idx_test_b |            1 | b           | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

-- session1, session1 开启事务，执行修改 a > 1 and a < 6 的数据，这会导致 mysql 将 a = 2 的数据行锁住（虽然表中并没有这行数据）
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set b='Heygo' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- session2 开启事务，此时插入(如果不存在)或者删除或者更新a=2到5之间的数据都会阻塞
mysql> delete from test_innodb_lock where a = 2;
-- 阻塞直至超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

注意事项

间隙锁是锁一个范围的键值，如果范围选择不当，会导致某些键值被无辜锁定，影响性能。

行锁分析

案例结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候（索引失效，导致行锁变表锁），可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

工具分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 249144 |
| Innodb_row_lock_time_avg      | 49828  |
| Innodb_row_lock_time_max      | 51110  |
| Innodb_row_lock_waits         | 5      |
+-------------------------------+--------+
5 rows in set (0.01 sec)

对各个状态量的说明如下：
1. Innodb_row_lock_current_waits：当前正在等待锁定的数量；
2. Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
3. Innodb_row_lock_time_avg：每次等待所花平均时间；
4. Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
5. Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
1. Innodb_row_lock_time_avg（等待平均时长）
2. Innodb_row_lock_waits（等待总次数）
3. Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

行锁优化

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

页锁

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

了解即可，用的不多。

目录

锁机制

概述