MySQL数据库查看是否锁表以及如何解决 |
您所在的位置:网站首页 › 锁的规格怎么看 › MySQL数据库查看是否锁表以及如何解决 |
一、背景
近期系统在使用过程中总是出现数据库锁表情况,导致页面报错,经过排查,是由于系统在跑定时任务;将解决过程以及在以后开发过程中该怎样注意,做个总结,避免以后在出现; 二、解决思路 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语句从开始到结束到底经历了什么? 三、锁的释放本次问题从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语句,数据库表关键字做优化之后,锁表情况也彻底解决; |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |