mysql锁表处理方法

1.Mysql锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是页面锁,但也支持表级锁;InnoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下是采用行级锁。

2.锁表及解锁

客户端连接Mysql时会产生一个connection,当多个连接(数据库连接)同时对一个表的数据进行更新操作,那么速度将会越来越慢,执行一段时间后将出现数据表被锁的现象,从而影响到其它的查询及更新。尤其当我们使用循环的时候更加容易出现锁表这种情况,导致其它连接无法读写此表。一旦遇到无法对某张表进行读写操作时,而其它表可以正常读写,就要检查是不是该表被锁了,如果被锁住就要解锁,解锁方式有以下两种。

  • show processlist
    该命令是显示用户正在运行的线程,默认只显示前100条数据,如果要显示全部线程,执行show full processlist。需要注意的是只有root用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其它用户正在运行的线程。show processlist 显示的信息都是来自MySQL系统库information_schema中processlist表,执行select * from information_schema.processlist语句也能显示相同的数据。显示结果如图:
    mysql锁表处理方法
    参数解释如下:
    Id:就是这个线程的唯一标识,当我们发现这个线程有问题的时候,可以通过kill命令将这个线程杀掉。
    User: 就是指启动这个线程的用户。
    Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候,我们可以定位到是哪个客户端的哪个进程发送的请求。
    DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库,则该值为NULL。
    Command: 是指此刻该线程正在执行的命令。
    Time: 表示该线程处于当前状态的时间。
    State: 线程的状态。
    Info: 线程执行的语句。
    需要关注的是state列的值,不同值如下:
    Checking table:正在检查数据表。
    Closing tables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。
    Connect Out:从服务器正在连接主服务器。
    Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。
    deleting from main table:服务器正在执行多表删除中的第一部分,刚删除第一个表。
    deleting from reference tables:服务器正在执行多表删除中的第二部分,正在删除其他表的记录。
    Locked:被其他查询锁住了。
    Sending data:正在处理SELECT查询的记录,同时正在把结果发送给客户端。
    Sleeping:正在等待客户端发送新请求。
    如果发现state值为Locked,找到Id值,Kill掉就行。

  • select * from information_schema.innodb_trx
    mysql锁表处理方法
    需要看trx_rows_locked和trx_mysql_thred_id,如果trx_rows_locked里的值不为0,说明有阻塞数据,执行Kill trx_mysql_thred_id。

上一篇:InnoDB 事务可见性


下一篇:性能优化利器:剖析MySQL 5.7新特征 sys schema