MySQL（9）——表锁和行锁

在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。

如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。

锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

但是加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等 ，都会增加系统的开销。

一、表锁

（一）理论

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

表锁分为读锁和写锁：

• 读锁（read lock），也叫共享锁（shared lock），针对同一份数据，多个读操作可以同时进行而不会互相影响（select）；
• 写锁（write lock），也叫排他锁（exclusive lock），在当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）。

（二）测试

1. 建表

drop table if exists mylock;
CREATE TABLE mylock (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR (20) NOT NULL
) ENGINE MyISAM DEFAULT charset = utf8mb4;
insert into mylock (name) values ('a');
insert into mylock (name) values ('b');
insert into mylock (name) values ('c');

2. 读锁

session1	session2
lock table mylock read; //读锁
select * from mylock; //可以正常读取	select * from mylock; //可以正常读取
update mylock set name=‘aa’ where id=1; //报错	update mylock set name=‘aa’ where id=1; //被阻塞
unlock tables;	被阻塞的更新语句执行成功

3. 写锁

session1	session2
lock table mylock write;// 上写锁
select * from mylock; //可以正常去读	select * from mylock; //被阻塞
update mylock set name=‘aa’ where id=1; //可以正常更新	update mylock set name=‘aa’ where id=1; //被阻塞
unlock tables;	被阻塞的语句执行成功

4. 查看表锁

show open tables

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 472   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 37    |
| Table_open_cache_misses    | 5     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

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

table_locks_immediate
产生表级锁定的次数，表示可立即获取锁的查询次数，每立即获取锁一次该值加1。

（三）小结

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

• 在加读锁并未释放锁时，该进程不能对同一表（mylock）进行写操作，并且也不能对其他表进行操作。

• 对MyISAM表加写锁，会阻塞其他进程对同一表（mylock）的读和写操作，只有当写锁释放后，才会执行其他进程的写操作。

• 在加写锁并未释放锁时，该进程不能对其他表进行操作。

二、行锁

（一）理论

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

（二）测试

1. 建表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
insert into user (name,age) values ('a' ,1);
insert into user (name,age) values ('b' ,2);
insert into user (name,age) values ('c' ,5);

将autocommit关闭：

set global autocommit=0;

2. 读锁

session1	session2
begin;
select * from user where id=3 lock in share mode; //上读锁
	select * from user where id=3 lock in share mode; //可以正常获取数据
update user set age=8 where id=3; //可以更新	update user set age=9 where id=3;//被阻塞
commit; //解除读锁	更新执行

3. 写锁

session1	session2
begin;
select * from user where id=3 for update; //上写锁
	select * from user where id=3; //可以读取
update user set age=6 where id=3; //可以更新	update user set age=7 where id=3; //被阻塞
rollback;	更新执行

上写锁后，其他session可以访问，是由于MySQL的多版本并发控制机制，可以读取快照，因此不会被阻塞。

4. 行锁分析

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

• Innodb_row_lock_current_waits：当前正在等待锁定的数量。

• Innodb_row_lock_time：从系统启动到现在锁定的时长。

• Innodb_row_lock_time_avg：每次等待锁所花平均时间。

• Innodb_row_lock_time_max：从系统启动到现在锁等待最长的一次所花的时间。

• Innodb_row_lock_waits：系统启动后到现在总共等待锁的次数。

5. 行锁升级为表锁

在session1中执行：

select * from user where age=9 for update

其中age列没有索引，导致行锁升级为表锁，在session2中执行：

update user set age=7 where id=5;

该更新语句会被阻塞，当session1使用commit或rollback解锁后，session2中的更新语句得以执行。

6. 间隙锁

user表中数据如下：

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | Castle |   30 |
|  3 | a      |    9 |
|  4 | b      |    2 |
|  5 | c      |    5 |
+----+--------+------+
4 rows in set (0.00 sec)

为age添加索引：

create index idx_age on user(age);

在session1中执行：

update user set name='aa' where age>=9;

在session2中执行:

insert into user (name,age) values ('bb',10);

insert语句被阻塞，在session1解锁后，语句得以执行。

MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

（三）小结

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