本文主要是介绍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 锁实现的。
（三）小结
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能较少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
这篇关于MySQL（9）——表锁和行锁的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
