本文详细介绍了MySQL数据库的锁机制，包括读锁、写锁、表锁、行锁的概念及区别。通过实例展示了表锁的创建、分析方法，以及行锁的创建和分析，强调了无索引行锁可能导致的表锁升级问题。此外，还解释了间隙锁的作用和避免锁等待的优化策略。
9. 数据库锁理论
-
根据操作类型，锁可分为
- 读锁(共享锁)： 针对同一份数据，多个读操作可以同时进行而并不相互影响。
- 写锁(互斥锁)： 当前写操作在没有完成之前，它会阻断其他写锁和读锁。
-
根据数据操作的粒度划分，锁可分为
- 表锁(偏读)：
- 行锁(偏写)：
- 页锁：
1. 创建表锁演示实例
-
建表 SQL：引擎选择 myisam
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; -
mylock 表中的测试数据
mysql> select * from mylock;
-
查看当前数据库中表的上锁情况：
show open tables;，In_user 的值0 表示未上锁。
-
添加读/写锁
使用命令：lock table 表名1 read(write) [,表名2 read(write)]
为mylock表添加读锁，即lock table mylock read;
添加之后，我们发现，依然可以读取mylock的数据。
虽然可以读取数据，但是修改数据是不允许的。修改数据时，光标不断的在闪烁。
接着，我们将读锁改为写锁，即 lock table mylock write；
添加之后，我们发现，读取数据时会被阻塞，光标不断在闪烁。
最后，解锁，我们可以使用命令： unlock tables;
2. 表锁案例总结1：
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）
- 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对用一表的写请求，当前进程写操作时会报错。只有当读锁释放后，才会执行其他进程的写操作。
- 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
简而言之，就是读写锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
=
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6VMkQOnR-1606020997384)(…/…/AppData/Roaming/Typora/typora-user-images/image-20201121153502148.png)]
3. 如何分析表锁？
分析表锁，我们可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定：
我们可以通过命令: show status like ‘table_lock%’; 查看状态变量。
上图中的两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
-
Table_locks_immediate：产生表级锁定的次数，标识可以立即获取锁的查询次数，每立即获取锁值加1；
-
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次，锁值加1），此值高则说明存在着较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也就是 MyISAM 不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成永远阻塞。
4. 创建行锁演示实例
-
建表 SQL：**引擎选择 InnoDB **
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); -
查询 test_innodb_lock 表中的测试数据
-
因为我们的数据库使用的是版本 Server version: 8.0.18 MySQL Community Server - GPL。 MySQL版本在5以上默认使用的是innodb引擎，该引擎是支持事务的，每一个分号的输入即自动提交事务。因此我们要关闭自动提交，自己手动提交数据，即输入命令：
set autocommit=0;。 -
会话1 进行修改数据，并暂时不提交事务。
update test_innodb_lock set b='40001' where a=4; -
会话1 查询数据，发现数据库数据发生了变化， a=4的值为4001
-
会话2 查询数据，发现数据并没有修改。
-
此时会话1还未提交，会话2想要修改a=4,即第4行数据，希望修改为4002
update test_innodb_lock set b='4002' where a=4;。
光标在闪烁，说明该修改命令被阻塞了，会话2无法修改。
-
此时会话1 进行 commit，提交数据，并查询结果。
会话1的第4行的b已经修改为40001，会话2的修改语句执行完成。但会话2查询到的结果为会话2修改的值4002.
-
会话2也继续进行提交数据。
-
会话1为再次提交数据，读取到的仍然为旧的值。因为MySQL数据库的默认隔离级别为可重复读。
-
会话1提交数据，即可读到最新的数据了。
-
会话1修改第4行的数据，修改为4001；会话2修改第5行的数据，修改为5001；
-
我们可以发现，两个会话是可以同时提交成功的，说明会话1修改的行对会话2修改是没有影响的。这就是行锁的效率体现。
5. 无索引行锁升级为表锁
这个问题发生往往都很隐蔽，难以发现该错误的发生：
-
首先，我们查看我们的表的字段类型
-
在学索引的时候，我们都字段自动类型转换会导致索引失效。除次之外，还会导致行锁变为表锁。
-
举个栗子，我们在会话1中b的值为4001的记录修改a字段为401，
即 update test_innodb_lock set a=401 where b=‘4001’;
对于varchar类型的数据，我们必须加上引号，否则会产生神秘的问题。
-
如果有引号时，结果是很正常的
-
当我们把引号去掉时，再看看结果如何吧！！！
我们可以发现，会话1秒出结果，但是会话2的光标不断的在闪烁。。。。说明行锁已经升级为表锁了。
-
当会话1提交时，会话2将不再阻塞。
6. 间隙锁
MySQL InnoDB支持三种行锁定方式：
- 行锁（Record Lock）：锁直接加在索引记录上面。
- 间隙锁（Gap Lock）：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
- Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。
-
查询 test_innodb_lock 的数据，查看存在哪些记录
-
我们很容易可以发现，缺少a为2的记录项。当我们的会话1需要修改 1<a<5的记录，b值修改为fafu时。会话2插入1条a=2,b=’2b’;
# 会话1 update test_innodb_lock set b='fafu' where a<5 and a>1; # 会话2 insert into test_innodb_lock values(2,'2b');
通过图，我们很容易看出问题了。 a=2的记录项原本就不能存在，但最终还是被锁住了，而如果插入的数据项a 的范围不在1~5之间的话，此时就不会被锁住。
-
所以 这 重分的说明了，如果是范围查询，会存在间隙锁。
-
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要
小结：
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。
间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。
要禁止间隙锁的话，可以把隔离级别降为***读已提交***，或者开启参数***innodb_locks_unsafe_for_binlog***。
7. 如何锁定具体的某一行数据
大致可分为下面4个步骤
# 步骤1，开启
begin;
# 步骤2，锁定行
select * from test_innodb_lock where a=8 for update;
# 步骤3，进行数据修改
update ......
# 步骤4，提交
commit;
在会话1中锁定一行数据时，其他会话无法对该行记录继续修改操作。如果修改其他记录，则不会被阻塞
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uk9QH7Y7-1606020997403)(…/…/AppData/Roaming/Typora/typora-user-images/image-20201121185401229.png)]
8. 如何分析行锁？
通过检查 innoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：
使用命令： show status like 'innodb_row_lock%';
对各个状态量的说明如下：
| 状态 | 描述 |
|---|---|
| Innodb_row_lock_current_waits | 当前正在等待锁定的数量 |
| Innodb_row_lock_time | 从系统启动到现在锁定总时间长度 |
| Innodb_row_lock_time_avg | 每次等待所花的平均时间 |
| Innodb_row_lock_time_max | 从系统启动到现在等待最长的一次所花的时间 |
| Innodb_row_lock_waits | 系统启动后到现在总共等待的次数 |
在这5个状态量中，我们最主要关注 Innodb_row_lock_time_avg（平均时间），Innodb_row_lock_waits（等待总次数），Innodb_row_lock_time（等待总时间） 三个状态即可
5185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
