概述

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

锁的分类

从对数据操作的类型分

读锁(共享锁)

1、定义:针对同一份数据,多个读操作可以同时进行并且不会互相影响。

写锁(排它锁)

1、定义:当前写操作没有完成前,它会阻塞其它写锁和读锁。

从对数据操作的粒度分

表锁(偏读)

特点

偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

案例分析

1、用到的表结构及数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create table mylock(
`id` int(11) not null primary key auto_increment,
`name` varchar(20)
)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');

2、加读锁

session1 session2
session1加读锁image-20200228170427817 连接就行,不做操作
session1可以读取加锁的表image-20200228170538824 session2也可以读取加锁的表image-20200228170615269
session1不能读取其他没有锁定的表image-20200228170643539 session2可以读取其他表image-20200228170707313
session1不能更新其他没有加锁的表image-20200228171106582 session2可以更新其他没有加锁的表image-20200228171148114
session1不能更新当前锁定的表image-20200228170744640 session2更新锁定的表会处于阻塞状态,直到session1释放锁image-20200228171641518
释放锁image-20200228171430670 获得锁,更新成功image-20200228171400381

3、加写锁

session1 session1
加写锁image-20200228174144762 连接就行,不做操作
可以读锁定的表image-20200228174226221 读锁定的表会阻塞,直到锁释放image-20200228174318626
可以更新锁定的表image-20200228174342547 更新锁定的表会阻塞,直到锁释放image-20200228174431024
不能读未锁定的表image-20200228174510747 可以读未锁定的表image-20200228174559564
不能更新未锁定的表image-20200228174704617 可以更新未锁定的表image-20200228174726810
释放锁image-20200228174949078
案例结论

1、MyISAM 在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
MySQL 的表级锁有两种模式:
表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)

锁类型 可否兼容 读锁 写锁
读锁
写锁

结论:结合上表,所以对 MyISAM 表进行操作,会有以下情况。
①. 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的写操作。
②. 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。
简而言之,就是读锁会阻塞写,但是不会阻塞读,而写锁会把读和写都阻塞。

表锁分析

1、哪些表被加锁了

1
2
3
4
5
6
mysql> show open tables where In_use>0;
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| study    | mylock |      1 |           0 |
+----------+--------+--------+-------------+

2、如何分析表锁定
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 229   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 15    |
| Table_open_cache_misses    | 10    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+

日常主要注意两个变量:
①. Table_locks_immediate: 产生表级锁定的次数,表示可以立即获取锁定的查询次数,每次立即获取锁,这个值就加1;
②. Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁,值加1),此值高则说明存在着较严重的表级锁争用情况;

此外,MyISAM的读写锁调度是写优先,这就是MyISAM不适合做为写为主的表的引擎的原因。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而产生阻塞。

行锁(偏写)

特点

偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的不同点:一是支持事务(transaction);二是采用了行级锁。

案例分析

1、用到的表结构及数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
create table test_lock(
`a` int(11),
`b` varchar(16)
) engine innodb;

insert into test_lock values (1,'b2');
insert into test_lock values (3,'3');
insert into test_lock values (4,'4000');
insert into test_lock values (5,'5000');
insert into test_lock values (6,'6000');
insert into test_lock values (7,'7000');
insert into test_lock values (8,'8000');
insert into test_lock values (9,'9000');
insert into test_lock values (1,'b1');
insert into test_lock values (2,'2000');

create index idx_a on test_lock(a);
create index idx_b on test_lock(b);

2、行级锁基本演示

session1 session2
关闭自动提交image-20200301230332412 关闭自动提交image-20200301230356588
更新但是不提交image-20200301230427836 更新同一条记录时阻塞image-20200301230505163
提交更新image-20200301230535600 解除阻塞,更新成功image-20200301230600438
更新 a=4的记录image-20200301230829659 更新 a=9的记录,不会被阻塞image-20200301230854433

3、无索引行锁升级为表锁 image-20200301232116430 表中的字段 b 的类型是 varchar,而上面的 session1中b没有加引号,所以导致类型转换,索引失效。在 session1提交之前,虽然 session2操作的记录跟 session1 不是同一条,但是仍然产生了阻塞, 所以是 session1 产生了表锁。当 session1 提交以后,session2立即更新成功。 image-20200301232015857

4、间隙锁的危害 image-20200301232836658 image-20200301232919448 跟图中一样(表里没有a=2这条数据),当我们使用范围条件而不是相等条件检索数据,并请求共享或者排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但是并不存在的记录,叫做间隙(GAP)
因为 query 执行过程中通过范围查找,所以它会锁定整个范围内所有的索引键值,即使这个值并不存在。因此间隙锁有一个致命的弱点,就是当锁定一个范围阀值之后, 即使某些不存在的键值也会被无辜的锁定,造成锁定的时候无法插入键值范围内的任何数据。就像图中的 session2插入 a 为2的记录时,会被阻塞, 直到 session1 提交以后才会插入成功。

5、如何锁定一行

1
2
begin;
select * from tbl_name where column = 'xxx' for update;

案例截图 image-20200301234445848

案例结论

InnoDB存储引擎由于实现了行级锁定,虽然在行级锁定机制的实现方面所带来的性能损耗可能比表级锁定要高一些, 但是在整体并发处理能力方面要远远低于 MyISAM 的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM 相比就会有比较明显的优势了。
但是,InnoDB的行级锁定同样也有其弱的一项,当使用不当的时候,可能会让InnoDB的整体性能表现比MyISAM差。

行锁分析
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 248529 |
| Innodb_row_lock_time_avg      | 24852  |
| Innodb_row_lock_time_max      | 51006  |
| Innodb_row_lock_waits         | 10     |
+-------------------------------+--------+

各个状态的说明:
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:系统启动到现在总共等待的次数。

优化建议

1、尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁。
2、合理设计索引,尽量缩小锁的范围。
3、尽可能较少检索条件,避免间隙锁。
4、尽量控制事务大小,较少锁定资源量和时间长度。
5、尽可能低级别事务隔离(这个需要慎重吧,InnoDB默认的是可重复读)。

页锁

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。这个先了解一下吧。

涉及到的命令

1、加锁,mylock 表加的是读锁,book 表加的是写锁。

1
lock table mylock read,book write;

2、查看哪些表处于加锁状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql> show open tables;
+--------------------+-------------------+--------+-------------+
| Database           | Table             | In_use | Name_locked |
+--------------------+-------------------+--------+-------------+
| study              | emp               |      0 |           0 |
| study              | tbl_user          |      0 |           0 |
| study              | test001           |      0 |           0 |
| study              | dept              |      0 |           0 |
| study              | class             |      0 |           0 |
| study              | mylock            |      1 |           0 |
| study              | article           |      0 |           0 |
| study              | staffs            |      0 |           0 |
| study              | phone             |      0 |           0 |
| study              | book              |      1 |           0 |
+--------------------+-------------------+--------+-------------+

In_use为1的表示现在处于锁定状态。

3、释放表锁

1
mysql> unlock tables;