MySQL之锁机制（表锁和行锁）

本文主要是介绍MySQL之锁机制（表锁和行锁），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL之锁机制（表锁和行锁）

- 锁的定义
- 锁的分类
- - 表锁
  - - 表锁特点
    - 表锁（读锁）
    - 表锁（写锁)
    - 总结
    - 表锁定分析
  - 行锁（重点）
  - - 行锁特点
    - 并发事务产生的问题
    - 事务隔离级别
    - 模拟数据
    - 行锁基本演示
    - 行锁演示结论
  - 索引失效
  - 间隙锁
  - - 什么是间隙锁
    - 间隙锁演示
    - 间隙锁的危害
  - 面试题：如何锁定一行
  - 行锁分析
  - - InnoDB_row_lock
    - 字段说明
  - 优化建议
  - 总结

锁的定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种共享资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。
锁冲突是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤为重要，而且也更加复杂

锁的分类

从对数据的操作类型分为：读锁（共享锁）和写锁（排他锁）

读锁：针对同一份数据，对该数据的读操作可以同时进行且不受影响。

写锁：写操作未完成前，会阻断其他的读操作和写操作。

从对数据的操作粒度分为：表锁和行锁

表锁

表锁特点

MylSAM引擎使用表锁，开销小，加锁快，无死锁，锁定力度大，发生锁冲突的概率最高。
并发度最低
不支持事务

模拟数据

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;

查看数据库锁

SHOW OPEN TABLES in hanyxx; --查看数据库hanyxx中的表是否加锁
LOCK TABLE book read , phone write -- book表加读锁，phone表加写锁
--全部解锁
UNLOCK TABLES;

表锁（读锁）

主机A给表加上表锁（读锁）

主机A和其他主机都可以读取该表的信息
主机A不能读取其他表的信息，但其他主机可以读取库中其他表的信息
主机A不能对锁住的表进行修改
其他主机对表进行修改，会被阻塞，直到表锁（读锁）被释放

表锁（写锁)

主机A给表加上表锁（写锁）

主机A可以读取该表信息，但其他主机读取时，会进入阻塞状态，直到表锁（写锁）被释放
主机A不能读取其他表的信息，但其他主机不能读取该表信息，但可以读取其他表的信息
主机A可以对该表进行修改
其他主机不能对该表进行修改，会被阻塞，直到表锁（写锁）被释放

总结

在这里插入图片描述

读锁不会阻塞读，只会阻塞写。但是写锁会阻塞读和写。

表锁定分析

在这里插入图片描述

行锁（重点）

行锁特点

开销大，枷锁慢，会出现死锁
锁定粒度最小，发生锁冲突概率最低，并发度最高

InnoDB和MyISAM最大的不同点

支持事务
采用行锁

并发事务产生的问题

脏读
不可重复读
幻读

事务隔离级别

在这里插入图片描述

查看数据库隔离级别

show variables like 'tx_isolation'; -- MySQL 5.7之前的版本
show variables like 'transaction_isolation'; -- MySQL 5.7之后的版本

MySQL数据库默认隔离级别：REPEATABLE-READ

模拟数据

-- 创建表
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);
-- InnnDB事务自动提交，如果需要演示行锁，需要关闭自动提交
SET autocommit=0;

行锁基本演示

在这里插入图片描述

行锁演示结论

如果两个客户端对同一条记录进行修改

客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
客户端A修改后，提交后，客户端B再修改，则不会阻塞
如果两个客户端分别对不同的记录行进行修改，则不会被阻塞

索引失效

索引失效，行锁变表锁（通过varchar类型不加单引号让索引失效）

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态

所以要避免索引失效

为甚么索引失效行锁会变表锁

InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁
否则，InnoDB使用表锁在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

间隙锁

什么是间隙锁

在这里插入图片描述

间隙锁演示

在这里插入图片描述

间隙锁的危害

在这里插入图片描述

面试题：如何锁定一行

在这里插入图片描述

行锁分析

InnoDB_row_lock

通过检查InnoDB_row_lock状态变量分析行锁竞争情况

show STATUS like 'innodb_row_lock%'; -- 查看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`（重要）	从系统启动至今，一共锁定了多少次