一、背景
近期系统在使用过程中总是出现数据库锁表情况,导致页面报错,经过排查,是由于系统在跑定时任务;将解决过程以及在以后开发过程中该怎样注意,做个总结,避免以后在出现;
二、解决思路
1.查看当前锁表事务
由于出现的是锁表的问题,所以第一步从数据库入手,查看导致锁表的SQL语句是什么;查看是否锁表SQL语句;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
该命令的输出结果包含了当前事务等待的锁资源的相关信息,包括事务ID、锁的类型、锁的模式、被锁定的对象(表、页等)以及锁的状态,而且也能看到具体执行的SQL以及事务的权重,反应一个事务修改和锁定的行数。
2.扩展思路
除了上面查询方式,MySQL还提供了很多查看方式,来查看表是否被锁定。以下是常用的几种方式:
方法一:使用SHOW OPEN TABLES
命令
SHOW OPEN TABLES WHERE `Table` = 'table_name' AND `Database` = 'database_name';
这个命令会返回一个结果集,其中包含了表的一些信息,比如表的状态,使用的存储引擎等等。如果表被锁定,那么状态字段会显示In_use
。
方法二:使用SHOW PROCESSLIST
命令
SHOW PROCESSLIST;
这个命令会返回当前MySQL服务器上所有的活动进程。如果表被锁定,那么可以通过查看这个进程列表来确定是否有进程正在使用该表。你可以检查State
列中的信息,看是否有进程正在锁定该表。
方法三:使用INFORMATION_SCHEMA
系统库
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE `table_name` = 'table_name';
这个查询语句会返回InnoDB引擎的锁信息。如果表被锁定,你可以在结果集中找到相关的行。
方法四:使用SHOW ENGINE INNODB STATUS
命令
SHOW ENGINE INNODB STATUS
这个命令会返回InnoDB引擎的状态信息。你可以在结果中查找TRANSACTIONS
和LOCK WAIT
字段来确定是否有事务正在等待表锁定。
方法五:使用sys.innodb_lock_waits
系统视图(仅适用于MySQL 8.0及以上版本)
SELECT * FROM sys.innodb_lock_waits;
这个查询会返回当前等待锁定的事务信息。如果表被锁定,你可以在结果集中找到相关的行。
3.治本
以上提供了一些查看问题的方式,那么既然出现了问题,就需要彻底根治,避免系统再次出现问题,针对系统本次出现的问题,从一下几个点进行了优化,具体如下:
(1)SQL 本身优化
对跑批SQL进行了执行计划分析,通过分析查看,发现有些关联表进行了全表查询,所以第一步先多查询速度进行优化,从查询时间上入手解决,通过多次执行计划分析,对进行了全表扫描的做关联关系分析,发现部分表有主键,但为未建索引;
因此从SQL做了以下优化:
a.建立索引;
CREATE INDEX index_name ON table_name (column_name);
b.减少子查询
c.添加where条件
d.查询条件避免使用函数
(2)编码层面
由于处理的数据量比较多,数据来源比较复杂,来源多个表,所以将有些能抽出来的表抽出来,尽量放代码层面处理,通过代码逻辑控制;
本次优化只是从这几个方面优化,想有优化sql还是需要从sql的本身进行分析,知道执行顺序以及原理,执行原理可见如下文章:
三、锁的释放
本次问题从sql,系统层面解决了,那么为能临时解决锁表,保证系统正常运行,先对导致锁表的事务进行释放,MySQL中锁的释放是自动进行的,当一个会话执行完相关操作后,所持有的锁会自动释放。不过,有些情况下我们可能需要手动释放锁,比如长事务或者死锁的处理。释放锁SQL语句如下:
1. ROLLBACK
当一个会话执行ROLLBACK语句时,所有该会话持有的锁都会被立即释放。例如:
-
START TRANSACTION;
-
SELECT
*
FROM table1
WHERE id
=
1
FOR
UPDATE;
-
ROLLBACK;
在这个例子中,执行ROLLBACK后,会话所持有的锁会被释放。
2. COMMIT
当一个会话执行COMMIT语句时,所有该会话持有的锁都会被释放。例如:
-
START TRANSACTION;
-
SELECT
*
FROM table1
WHERE id
=
1
FOR
UPDATE;
-
COMMIT;
在这个例子中,执行COMMIT后,会话所持有的锁会被释放。
3. 显式调用UNLOCK TABLES
当一个会话调用UNLOCK TABLES语句时,会释放该会话持有的所有表级锁。例如:
LOCK TABLES table1 WRITE; ... UNLOCK TABLES;
在这个例子中,调用UNLOCK TABLES后,会话所持有的锁会被释放。
4. 长事务的处理
在MySQL中,长事务是指持续运行时间较长的事务。长事务可能导致锁保持的时间较长,从而影响其他会话的并发访问能力。为了释放长事务持有的锁,可以使用以下方法:
- 执行ROLLBACK或者COMMIT语句来结束事务。
- 使用KILL命令终止会话,但这种方法可能会导致事务的回滚。
5. 死锁的处理
当多个会话之间出现循环依赖的锁竞争关系时,就会发生死锁。MySQL会自动检测到死锁,并选择一个会话进行回滚,以解除死锁。
使用SHOW ENGINE INNODB STATUS命令可以查看死锁信息。例如:
SHOW ENGINE INNODB STATUS
在输出结果中的"TRANSACTIONS"部分,可以找到死锁的详细信息;
通过以上方式,从系统本身的编码,SQL语句,数据库表关键字做优化之后,锁表情况也彻底解决;