1. 概述
锁是计算机协调多个进程或线程并发访问某一种资源的机制。
在数据库中,除去计算机硬件资源(CPU、RAM、I/O等)的争用外,数据也是一种供许多用户共享的资源。如何保证并发访问数据的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,因此锁对于数据库而言尤其重要,也更加复杂。
比如我们在淘宝抢购商品时,当两个客户同时抢购一个商品时,后台的数据库中必然用到事务和锁,通过对整个下单流程进行统一的事务交和数据库加锁机制,保证商品不会被超卖。
1.1 锁的分类
- 数据库操作类型分类(读/写)
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响;
- 写锁(排他锁):当前操作没有完成前,会阻断其他的写锁和读锁;
- 对数据的操作粒度:
- 表锁:对数据表上锁
- 行锁:对操作的数据行上锁
开销、加锁速度、死锁、粒度、并发性能只能根据具体应用的特点来说明哪种锁更合适。
这里推荐一篇关于 MySQL 锁相关的博客 MySQL的锁机制和加锁原理
2. 表锁(偏向读)
2.1 特点
偏向读操作,偏向 MYISAM
存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低
2.2 示例
2.2.1 建立数据库
use big_data;
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');
查看创建的数据库
mysql> use base_crud;
Database changed
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
数据库锁的基本操作
手动添加表锁:
lock table 表名1 read(write), 表名2 read(write), 其他;
查看上锁的表:
show open tabels
手动解锁:
unlock tables
2.2.2 添加读锁
mylock
表添加读取锁,同时打开两个 session 窗口
session1 窗口
# 为 mylock 表添加读锁
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)
# 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 更新 mylock 表
mysql> update mylock set name = 'a' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
# 查询 book 表
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# 更新 book 表
mysql> update book set card = 10 where bookid = 1;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# 释放锁
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)
session2窗口
# 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 4 | 2 |
| 25 | 2 |
| 36 | 2 |
| 23 | 3 |
| 38 | 4 |
| 15 | 6 |
| 16 | 6 |
| 24 | 6 |
| 39 | 6 |
| 34 | 20 |
+--------+------+
10 rows in set (0.00 sec)
# 更新 book表
mysql> update book set card = 10 where bookid = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 更新 mylock 表
mysql> update mylock set name = 'a3' where id = 1;
Query OK, 1 row affected (28.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由于session1 在
mylock
表加上读锁,导致队列阻塞,只有当session1 释放读锁时session2 才能对mylock
表进行更新操作,因此更新操作等待时间很长。
加读锁后不同 session 对表操作总结
表操作 | 当前 session | 其他 session |
---|---|---|
读取加了读锁的表 | YES | YES |
读取没有加读锁的表 | NO | YES |
更新/插入加了读锁的表 | NO | 阻塞等待 |
更新/插入没有加读锁的表 | NO | YES |
删除加了读锁操作的表 | NO | 阻塞等待 |
删除没有加读锁的表 | NO | YES |
2.2.3 添加写锁
mylock
表添加写入锁,同时打开两个 session 窗口
session1 窗口
# 1.1 给 mylock 表添加写锁
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)
# 1.2 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a3 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.01 sec)
# 1.2 查询 book 表
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# 2.1 更新 mylock 表
mysql> update mylock set name = 'b1' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 2.2 更新后查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a3 |
| 2 | b1 |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 3.1 更新 book 表
mysql> update book set card = 22 where bookid = 1;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# 3.2 释放写锁
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)
session2 窗口
# 1.1 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a3 |
| 2 | b1 |
| 3 | c3 |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (12.18 sec)
# 1.2 查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 4 | 2 |
| 25 | 2 |
| 36 | 2 |
| 23 | 3 |
| 38 | 4 |
| 15 | 6 |
| 16 | 6 |
| 24 | 6 |
| 39 | 6 |
| 34 | 20 |
+--------+------+
10 rows in set (0.00 sec)
# 1.3 更新 mylock 表
mysql> update mylock set name = 'c3' where id = 3;
Query OK, 1 row affected (10.42 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 1.4 更新 book 表
mysql> update book set card = 2222 where bookid = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 1.5 更新后查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 25 | 2 |
| 36 | 2 |
| 23 | 3 |
| 38 | 4 |
| 15 | 6 |
| 16 | 6 |
| 24 | 6 |
| 39 | 6 |
| 34 | 20 |
| 4 | 2222 |
+--------+------+
10 rows in set (0.00 sec)
由于session1 在
mylock
表加上写锁,导致队列阻塞,只有当session1 释放读锁时session2 才能对mylock
表进行查询、更新、插入、删除操作,阻塞等待时间很长。
加写锁后不同 session 对表操作总结
表操作 | 当前 session | 其他 session |
---|---|---|
读取加了写锁的表 | YES | 阻塞等待 |
读取没有加写锁的表 | NO | YES |
更新/插入加了写锁的表 | YES | 阻塞等待 |
更新/插入没有加写锁的表 | NO | YES |
删除加了写锁的表 | YES | 阻塞等待 |
删除没有加写锁的表 | NO | YES |
2.2.4 示例总结
MYISAM
在执行查询语句 SELECT
前,会自动给涉及到的所有表加读锁;在执行增删改操作前,会自动给涉及到的所有表加写锁。
MySQL 的表级锁有两种模式:
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)
对使用 MyISAM
文件存储引擎的表进行操作会出现以下情况:
- 对
MyISAM
表的读操作(加读锁)不会阻塞其他进程对同一表的读请求,但会阻塞同一表的写请求。当读锁释放后才会执行其他进程的写操作; - 对
MyISAM
表的写操作(加写锁)会阻塞其他进程对同一表的读和写操作,只有写锁释放后,才能执行其他进程的读写操作; - 简而言之,就是读锁会阻塞写操作,但是不会阻塞读操作。而写锁则会把读操作和写操作都阻塞;
2.3 表锁分析
查看数据库中那些表加锁:show open tables;
1:表示加锁
0:表示没有加锁
分析表锁定
使用命令:show status like 'table%'
mysql> show status like 'table%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Table_locks_immediate | 510836 |
| Table_locks_waited | 5 |
+-----------------------+--------+
2 rows in set (0.00 sec)
参数说明:
Table_locks_immediate
:产生表级锁定的次数,表示可以立即获取锁查询的次数,每立即获取锁值加 1;Table_locks_waited
:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加 1)数值高说明存在严重的表级锁争用情况;
MyISAM
的读写锁调度是写优先,因此不适合做写为主表的引擎。因为加锁后,其他线程不能做任何操作,大量的更新会使得查询很难获得锁,进而造成查询阻塞。
3. 行锁(偏向写)
3.1 特点
偏向 InnoDB
存储引擎,开销大,加锁慢;会出现死锁问题,锁的粒度小,发生锁冲突的概率最低,并发度是最高的。
InnoDB
引擎和MyISAM
引擎最大的不同:InnoDB
支持事务(transaction)和行级锁。
3.2 事务相关
3.2.1 事务及其 ACID 属性
事务是由一组 SQL 语句组成的逻辑处理单元,事物具有以下四个属性,通常简称为事务的 ACID 属性
原子性(Atomicity):事务是一个原子操作,对数据的修改,要么全都执行,要么全都不执行;
一致性(Consistent):在事务开始和完成时,数据必须保持一致状态。这也就意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有内部数据结构(如:B 树索引或双向链表)也都必须是正确的;
隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务不受外部并发操作影响的**“独立环境”**执行。这意味着事务处理的中间状态对外部是不可见的,反之亦然;
持久性(Durable):事务完成后,对于数据的修改是永久性的,即使出现系统故障也能够保持;
3.2.2 并发带来的事务问题
更新丢失(Lost Update)
当两个或多个事务选择同一行,并基于最初选定的值更行当前行是,由于每个事务不知道其他事务的存在,最后的更新覆盖了由其他事务所作的更新,产生丢失更新的问题。
时间片 | 事务 A | 事务 B |
---|---|---|
T1 | 开始事务 | |
T2 | 开始事务 | |
T3 | 查询账户余额 300 元 | |
T4 | 查询账户余额 300 元 | |
T5 | 账户充值 200 元,余额 500 元 | |
T6 | 提交事务 | |
T7 | 消费 100 元,余额 200 元 | |
T8 | 撤销事务 | |
T9 | 余额 300 元 |
事务 A 最后提交事务,导致事务 B 的事务提交被覆盖,造成事务 B 更新的数据丢失
脏读(Dirty Reads)
事务 A 正在对一条记录做修改,这个事务完成提交前,这条记录的数据就处于待定状态(可能提交也可能回滚);此时,事务 B 也来读取这条待定状态的数据,并对数据做进一步的处理,就会产生数据依赖关系,这种现象叫做**“脏读”**。
简而言之,就是事务 B 读取到了事务 A 已修改但没有提交的数据,还在这个数据的基础上做了操作。此时事务 A 回滚,事务 B 读取到的数据无效,不符合一致性要求。
不可重复读(Non-Repeatable Reads)
一个事务先后读取同一条记录,但在事务的两次读取之间该数据被其他事务所修改或删除,两次读取到的数据不一致或不能读取,这种现象叫做**“不可重复读”**。
简而言之,就是事务 A 读取到了事务 B 已经提交的修改数据,不符合隔离性。
幻读(Phantom Reads)
一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象称为**“幻读”**
简而言之,事务A读取到了事务B提交的新增数据,不符合隔离性。
幻读和脏读比较:
- 脏读是事务B里面修改了数据;
- 幻读是事务B里面新增了数据;
幻读和不可重复读比较:
- 不可重复读的重点是修改:同样的条件,两次读发现值不一样;
- 幻读的重点在于新增或者删除:同样的条件,两次读发现得到的记录数不一样;
3.3.3 事务的隔离级别
① DEFAULT
:
默认级别,由 DBA 默认的设置来决定隔离级别,归属下列某一种:
② READ_UNCOMMITTED
:
就是一个事务可以读取另一个未提交事务的数据。
会出现脏读、不可重复读、幻读(隔离级别最低,但并发性高)
最低级别,只能保证不读取物理上损坏的数据;
③ READ_COMMITTED
:
就是一个事务要等另一个事务提交后才能读取数据,解决脏读问题。
会出现不可重复读、幻读问题(锁定正在读取的行,适用于大多数系统,Oracle默认级别)
语句级别;
④ REPEATABLE_READ
:
就是在开始读取数据(事务开启)时,不再允许修改操作,解决不可重复读问题。
会出现幻读问题(锁定所读的所有行,MYSQL默认级别)
事务级别;
⑤ SERALZABLE
:
是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。
但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。(锁整表)
最高级别,事务级别;
事务隔离级别由上到下依次提升,隔离级别越高,越能保证数据的完整性和一致性。但对数据库性能的消耗依次增加,并发执行效率依次下降。
事务隔离实质上就是使得事务在一定程度上**“串行”,实际上是与并发相矛盾的。同时,不同的应用对读一致性和事务的隔离级别是不同的,有些应用可能对“不可重复读”和“幻读”**不敏感,更关心数据的并发处理能力。
查看当前数据库的事务隔离级别:
show variables like 'tx_isolation'
大多数的数据库默认隔离级别为
Read Commited
,比如SqlServer
、Oracle
少数数据库默认隔离级别为:
Repeatable Read
比如:MySQL InnoDB
事务的隔离级别和并发出现的问题之间的关联
Dirty reads | non-repeatable reads | phantom reads | |
---|---|---|---|
READ_UNCOMMITTED | Y | Y | Y |
READ_COMMITTED | N | Y | Y |
REPEATABLE_READ | N | N | Y |
SERALZABLE | N | N | N |
3.3 示例
3.3.1 建表语句
-- 创建数据库
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
-- 创建索引
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- 关闭自动提交
SET autocommit=0;
查看创建的数据库
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
3.3.2 行锁示例
session 1 窗口
# 1.1更新数据
mysql> update test_innodb_lock set b='b3' where a = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 1.2查询数据
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 1.3提交查询
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 2.1 更新行数据
mysql> update test_innodb_lock set b='3000' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 2.2 更新后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | 3000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 2.3 更新后提交
mysql> commit;
# 2.4提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 2.5 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 2.6 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 3.1 更新a=5
mysql> update test_innodb_lock set b='5005' where a=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9009 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
session 2 窗口
# 1.1 session 1 未提交 session 2 查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 1.2 session 1提交后session 2提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 1.3 session 1和 session 2都提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 2.1 更新行数据,session 1尚未提交,此时会出现阻塞当session 1提交后session 2才能更新
mysql> update test_innodb_lock set b='b4' where a=4;
Query OK, 1 row affected (15.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 2.2 session 1提交事务后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 2.3 查询数据
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 3.1 更新a=9
mysql> update test_innodb_lock set b='9009' where a=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9009 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
3.3.3 索引失效行锁升级为表锁
session 1 窗口
# 1.1 查询当前数据
mysql> select * from test_innodb_lock;
+------+-------+
| a | b |
+------+-------+
| 1 | 10010 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7007 |
| 8 | 8008 |
| 9 | 10010 |
| 1 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
# 1.2 更新b=10010 所在行数据
mysql> update test_innodb_lock set a=20 where b=10010;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# 1.3 提交事务
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
# 1.4 提交后查询
mysql> select * from test_innodb_lock;
+------+-------+
| a | b |
+------+-------+
| 20 | 10010 |
| 3 | 3000 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7007 |
| 8 | 8008 |
| 20 | 10010 |
| 20 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
session 2 窗口
# 1.1 查询当前数据
mysql> select * from test_innodb_lock;
+------+-------+
| a | b |
+------+-------+
| 1 | 10010 |
| 3 | b3 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7007 |
| 8 | 8008 |
| 9 | 10010 |
| 1 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
# 1.2 session 1更新后未提交,由于session 1索引失效导致行锁失效变成表锁,session 2更新不同行时被阻塞
mysql> update test_innodb_lock set b='3000' where a=3;
Query OK, 1 row affected (9.93 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# session 1提交后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 提交后查询
mysql> select * from test_innodb_lock;
+------+-------+
| a | b |
+------+-------+
| 20 | 10010 |
| 3 | 3000 |
| 4 | b4 |
| 5 | 5005 |
| 6 | 6000 |
| 7 | 7007 |
| 8 | 8008 |
| 20 | 10010 |
| 20 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
行锁示例总结:
- 当前session 更新数据后不提交,其他session 不能看到更新后的数据;
- 当前session 更新一行数据未提交,其他 session 更新同一行数据时会阻塞;
- 当前 session 和其他 session 更新不同的数据行时,不会发生阻塞;
- 索引失效会导致行锁变成表锁;
3.3.4 间隙锁
当我们用范围查询而不是相等条件检索数据,并请求共享锁或排他锁时,InnoDB 会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做**“间隙(GAP)”,InnoDB 也会对“间隙”加锁,这种锁机制就叫做“间隙锁(Next-Key 锁)”**。
间隙锁的危害:
Query 执行过程中通过范围查找的话,会锁住整个范围内所有的索引键值,即使这个值不存在。
间隙锁的一个致命弱点,就是当锁定一个范围值后,即使某些不存在的键值也会被锁定,会造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下可能会对性能造成很大影响。
session 1窗口
# 1.1 更新 1 < a < 6 的行 b 为 test
mysql> update test_innodb_lock set b='test' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# 1.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 1.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | test |
| 4 | test |
| 5 | test |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 2.1 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 2.2 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | test |
| 4 | test |
| 5 | test |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
# 3.锁定一行
# 3.1 打上起始点
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 3.2 查询锁定
mysql> select * from test_innodb_lock where a=8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)
# 3.3 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.4 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.5 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | test |
| 4 | test |
| 5 | test |
| 6 | 6000 |
| 7 | 7000 |
| 8 | xxxx |
| 9 | 9000 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
session 2窗口
# 1.1 session 1更新范围数据,产生间隙锁造成阻塞,session 1提交后执行
mysql> insert into test_innodb_lock values(2,'2000');
Query OK, 1 row affected (7.61 sec)
# 1.2 session 1提交后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 1.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | test |
| 4 | test |
| 5 | test |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
# 3.锁定一行
# 3.1 更新一行,由于session 1查询时锁定行,导致执行阻塞。session 1提交后才能执行
mysql> update test_innodb_lock set b='xxxx' where a=8;
Query OK, 1 row affected (7.90 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | test |
| 4 | test |
| 5 | test |
| 6 | 6000 |
| 7 | 7000 |
| 8 | xxxx |
| 9 | 9000 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
面试问题:如何锁定行?
select xxxx... for update
锁定某一行后,其他的操作会被阻塞,直到锁定行的会话提交。
3.3.5 总结
InnoDB
存储引擎由于实现了行级锁定,虽然在锁定机制实现方面所带来的性能损耗比表级锁更多,但是在整体并发处理能力方面要远优于 MyISAM
的表级锁定的。当系统并发量比较高的时候,InnoDB
的整体性能和 MyISAM
相比会有比较明显的优势。
InnoDB
的行级锁同样也存在问题,当我们不当使用时,可能会使 InnoDB
的整体性能表现比 MyISAM
更差。
3.4 行锁分析
通过检查 InnoDB_row_lock
状态变量来分析系统上的行锁争夺情况
查看命令 show status like '%innodb_row_lock%'
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 91944 |
| Innodb_row_lock_time_avg | 18388 |
| Innodb_row_lock_time_max | 51296 |
| Innodb_row_lock_waits | 5 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
状态变量说明:
Innodb_row_lock_current_waits
:当前正在等待锁定的数量;
Innodb_row_lock_time
:从系统启动到现在锁定总时间;
Innodb_row_lock_time_avg
:每次平均等待时间;
Innodb_row_lock_time_max
:从系统启动到现在等待最长的一次所花的时间;
Innodb_row_lock_waits
:系统启动后到现在总共等待的次数;五个状态变量中比较重要的:
Innodb_row_lock_time_avg
:每次平均等待时间;
Innodb_row_lock_waits
:系统启动后到现在总共等待的次数;
Innodb_row_lock_time
:从系统启动到现在锁定总时间;尤其 是当前等待次数很高,而且每次等待时长也很长的时候,需要分析系统中出现多次等待的原因,并根据分析结果指定优化计划。
3.5 优化建议
① 尽可能让所有数据检索都通过索引完成,避免无索引行锁升级为表锁;
② 合理设计索引,尽量缩小锁的范围;
③ 尽可能减少检索条件,避免间隙锁;
④ 尽量控制事务大小,减少锁定资源量和时间长度;
⑤ 尽可能使用低级别的事务隔离;
4. 页锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB支持页级锁。