MySQL锁，解决锁表的问题

MySQL锁

事务

通常我们需要一组操作，同时成功，或者同时失败的时候需要通过事务来进行保证。

从而保证原子性，一致性，隔离性，和持久性。

事务的隔离级别

• Read Uncommitted 读未提交（最低） 允许一个事务读取另外一个事务未提交的数据，可能造成脏读，不可重复读，幻读。
• Read Committed 读已提交 允许一个事务读取另外一个事务已提交的数据，可以防止脏读。但可能出现不可重复读，幻读。
• Repeatable Read 可重复读 保证同一个事务内，多次读取相同数据的结果是一致的，可以防止脏读和不可重复读。但会出现幻读。
• Serializable 串行（最高） 确保所有事务按照严格的顺序执行，避免了幻读，不可重复度，和幻读，但是会牺牲并发性能。

脏读，不可重复读，幻读

• 脏读：读取一个事务还未提交的数据，作为了当时的数据，但是如果这个事务回滚了，就会导致当时的那个数据是脏的。
• 不可重复读：读取另外一个事务已提交的数据，如果一个事务内有两次读取这个数据去计算，那么这两个计算结果不通。重复读取的数据不一致的问题。
• 幻读：同一个事务类读取相同的数据结果是一致的。那么如果在一个事务内需要读取两次这个数据，那么在第二次结果读取前，我们的数据发生了改变。尽管我们在事务内的数据读取的都是开启事务时的数据，但是实际上数据库的数据就不一致了。那么就会造成幻读的问题。

表锁与行锁

InnoDB 是支持 表锁与行锁的。

MyIsam 只支持行锁。

表锁

用于锁定整个表，阻止其他会话对整个表进行访问

测试准备

创建一个InnoDB引擎的表

drop table if exists InnoDBTest;
CREATE TABLE `InnoDBTest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into InnoDBTest(a,b) values(1,1),(2,2);

测试

# 开启session1
BEGIN;
	# 使用写锁
	select * from InnoDBTest  FOR UPDATE;
# 重新开始一个查询窗口后

# 开启session2
BEGIN;
	# 使用写锁
	select * from InnoDBTest  FOR UPDATE;
# 会发现 session2的selet会被阻塞住，
# 当我们的session1 执行commit后，session2的select才会执行完成。

行锁

用于锁定表中的某行或者某些行（范围），允许其他事务方法未被锁定的行

测试

# 开启session1
BEGIN;
	# 使用写锁
	select * from InnoDBTest where a = 1  FOR UPDATE;
# 重新开始一个查询窗口后

# 开启session2
BEGIN;
	# 使用写锁
	select * from InnoDBTest where a = 1   FOR UPDATE;
	
# 会发现 session2的selet会被阻塞住，

# 开启session3
BEGIN;
	# 使用写锁
	select * from InnoDBTest where a = 2   FOR UPDATE;
# 会发现可以session3的select会执行成功，不会等待session1
# 可以理解会 where a = 1  给a=1的记录加了行锁，只要不访问带有锁的行就能不被阻塞。

读锁与写锁

读锁（共享锁）

普通的select通过加上关键字LOCK IN SHARE MODE可以使对应的行或者表获取到锁。

允许多个事务同时读取一份数据，不会互相干扰。

但读锁会阻止其他事务获得写锁，但不会阻止其他事务获得写锁。

多个事务可以同时获取读锁，称之为共享锁

测试

# 开启session1
BEGIN;
	# 使用读锁
	select * from InnoDBTest where a = 1  LOCK IN SHARE MODE;
	
# 重新开始一个查询窗口后

# 开启session2
BEGIN;
	# 使用读锁
	select * from InnoDBTest where a = 1  LOCK IN SHARE MODE;
	
# 会发现session2能正常获取数据

# 开启session3
BEGIN;
	# 使用写锁
    update InnoDBTest set b = 3 where a = 1;
# session3会被阻塞因为update where a=1 需要获取a=1的记录当时此时，session1给a=1的记录上了读锁，所以session3会阻塞，只到 session1完成。

# 开启session4
BEGIN;
	# 使用写锁
    update InnoDBTest set b = 3 where a = 2;
    
# 会发现session4不会阻塞，因为在innodb中支持行锁，a=2的记录没有被上读锁。

写锁（排他锁）

• 写锁用于防止青铜器会话在同一时间内对相同数据进行读或者写操作。
• 只允许一个事务对数据进行写操作，其他事务不能同时读取或写入。这里也称为排他锁
• 写锁会阻塞其他写锁和读锁，以确保数据的一致性。

通常我们的update、insert、delete都是会获取写锁的。但是普通的select并没有获取锁，需要为其加上 for update才能使普通的select拿到写锁

测试

# 开启session1
BEGIN;
	# 使用读锁
	select * from InnoDBTest for update;

# 开启session2
BEGIN;
	select * from InnoDBTest;
	
# 普通读不会阻塞

# 开启session3
BEGIN;
	# 使用读锁
	select * from InnoDBTest LOCK IN SHARE MODE;
	
# 需要获取InnoDBTest的读锁所以会阻塞

# 开启session4
BEGIN;
	# 使用写锁
	insert into  InnoDBTest value(3,3);
	
# session4执行insert 需要获取写锁，所以 session3会阻塞

元数据锁

表级元数据锁

当一个会话正在修改表结果（新增列，删除列(DDL)等）时，会持有一个表级的元数据锁阻止其他会话对相同的表结果进修改

表级MDL**（Metadata Lock）**

当我们的表被锁住时，我们通过DDL语句去操作表结果的时候，就会触发表级MDL,触发后不管是读锁，写锁，或者是普通的select都会被阻塞

测试

# 开启session1
BEGIN;
  # 获取写锁
  delete from InnoDBTest;
  
# 开启session2
alter table InnoDBTest add COLUMN c VARCHAR(50);
# 因为session1把表的写锁给获取了，所以当我们执行DDL语句的时候，就被阻塞了，需要等待session1执行完成，但是，alter table 又需要获取元数据锁，所以，之后只要操作InnoDBTest的操作都会阻塞，需要等待 seesion1与session2执行完成。

# 开启session3
select * from InnoDBTest

# 普通的select阻塞了。

这里如果session1一直不commit这里就是一个死锁。也就导致了锁表。

如果验证呢？请看锁表章节

锁表

1. 用于查看当前打开的表信息

SHOW OPEN TABLES WHERE In_use >0;

In_use > 0 代表至少被一个会话使用的表才显示。

2.查看当前正在执行的所有数据库连接和查询的详细信息

SHOW FULL PROCESSLIST;

列描述

• Id：连接或线程的唯一标识符。
• User：执行查询的MySQL用户。
• Host：连接的主机名或IP地址。
• db：当前连接的数据库（如果有）。
• Command：正在执行的命令类型，例如 SELECT、INSERT、UPDATE、DELETE 等。
• Time：该查询执行的时间（以秒为单位）。
• State：查询的当前状态，例如正在锁定、发送数据等。
• Info：包含有关查询的更多信息，如查询文本。

可以看见我们这条sql导致了锁表。也就是我们的session使得session2也阻塞了，从而导致table metadata lock一直被占用。

所以，线上环境在高峰期的时候一定要避免去执行DDL,这样会导致非常严重的问题，类似与，数据源连接池全部都阻塞了。导致程序挂掉。有幸遇到过😂

查看正在运行的事务信息

select * from information_schema.innodb_trx;

这个查询包含有关当前正在运行的事务的信息，例如事务的ID、启动时间、事务状态等。
主要关心这两个字段即可。运行过长的事务（知道他是有问题的）可以直接kill 掉

如果解决锁表呢？

如果session1能够被关闭还好了，但是如果通过session1关不掉，不是我们自己的程序去操作的那怎么办？

刚才通过SHOW FULL PROCESSLIST找到了是个主机，及那个sql使得我们的表锁定了。找到该行的**ID**

# 通过线程ID来终止指定的线程
KILL <process_id>;

# 终止对应的进程后
kill 21;

SHOW FULL PROCESSLIST;
# 也没有看到其他的锁表信息了。

# 普通select 可以正常查询了
select * from InnoDBTest

今天遇到一个问题就是 有一个truncate table 在执行，导致锁表了。并且一直没有完成，从而导致线上服务查询基本信息的时候出现大量卡住的问题，导致服务不可用。记录下问题及解决方案，及其他知识点。

以上没有解决的话！

请直接祭出最终办法：万能重启!!!😂