Skip to main content

Mysql lock

索引和执行计划

慢查询设置

设置longquerytime来将执行时间超过设置的SQL记录在slowlog中,单位s。 同时设置logqueriesnotusingindexes=1记录所有没有索引的SQL到slowlog中。
开启slow log需要添加slowquerylog_file参数指定文件位置。
因为slow log会记录重复的SQL,所以一般需要借助pt-query-digest工具来进行分析。
或者在Sql中直接开启:

set global slow_query_log=on;  
set global slow_query_log_file='/home/data/mysql/3306/slow.log';  
set global long_query_time=1;

set global log_queries_not_using_indexes=on;  

分析满日志

分析满日志
pt-query-digest slow.log > report.log  
分析3小时内的满日志
pt-query-digest --since=3h slow.log > report.log  
分析只包含select查询的满日志
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log > report.log  

锁的存在是为了满足ACID和事务隔离级别的要求。

分类

  1. 共享锁(S)
    共享锁允许其它事务可以进行读取,也可以获得其它共享锁,但是不能写入。
    比如select就会上S锁,此时其他事务依然可以读取这条记录,但是并不能对其进行写入操作。

  2. 排它锁(X)
    排它锁防止其它事务锁定同一行。以便进行数据修改。故S和X是冲突的,S是对数据的共享,X是对数据的修改。

意向锁

数据库原理中将表叫做关系,记录叫做元祖。意向锁会在记录上上锁的时候,会在其上一级上一个意向锁。此时,如果有其他操作,比如需要锁表的操作的时候,在查询块的时候就可以知道有锁占用,从而提升上锁检查的效率。即因为表锁覆盖了行锁的数据,所以表锁和行锁也会产生冲突,为了方便检测表级锁和行级锁之间的冲突,就引入了意向锁。
意向锁是放置在资源层次结构的一个级别上的锁,以保护较低级别资源上的共享或排它锁。 1. 意向共享锁(IS)
2. 意向排它锁(IX)

按颗粒度划分

  1. 表级锁
  2. 行级锁

MVCC并发控制

在多版本并发控制中,有两种读操作:
1. 当前读

读取的是最新的版本 需要加锁,取undo的记录。
Delete/update/insert/select xxx for update

  1. 快照读

    读取的是记录的可见版本, 不需要加锁
    Select xxx

锁矩阵

img
Conflict表示冲突

Record Locks

加锁在索引上。

Gap Locks(间隙锁)

只在read-repeatable中出现
间隙锁是在索引记录范围加锁,或者在最后一个索引记录之前或者之后加锁。

Next-key Locks

以上两种锁的组合锁。解决了幻读的问题。

在READ COMMITTED不会产生间隙锁和组合锁

检查锁状态

创造一个锁:  
begin;  
select * from wp_users where `ID`='1' for update;  

此时在Mysql中执行show engine innodb status\G就能看到锁状态了

------------
TRANSACTIONS  
------------
Trx id counter 41488  
Purge done for trx's n:o < 40034 undo n:o < 0 state: running but idle  
History list length 1239  
LIST OF TRANSACTIONS FOR EACH SESSION:  
---TRANSACTION 281479498988216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)  
---TRANSACTION 41487, ACTIVE 123 sec
2 lock struct(s), heap size 1136, 1 row lock(s)  
MySQL thread id 4, OS thread handle 123145349791744, query id 41 localhost root cleaning up  
--------

show process list也能看到:

mysql> show processlist;  
+----+------+-----------+-----------+---------+------+----------+------------------+
| Id | User | Host      | db        | Command | Time | State    | Info             |
+----+------+-----------+-----------+---------+------+----------+------------------+
|  3 | root | localhost | NULL      | Query   |    0 | starting | show processlist |
|  4 | root | localhost | wordpress | Sleep   |  266 |          | NULL             |
|  5 | root | localhost | NULL      | Sleep   |   70 |          | NULL             |
+----+------+-----------+-----------+---------+------+----------+------------------+
3 rows in set (0.01 sec)  

Mysql 查看阻塞语句

Command:

select r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;  

Example:

mysql> select r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread,  
    ->        r.trx_query waiting_query, b.trx_id blocking_trx_id,
    ->        b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query
    ->   from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
    ->   inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;
+----------------+----------------+--------------------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                                    | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+--------------------------------------------------+-----------------+-----------------+----------------+
| 41489          |              4 | select * from wp_users where `ID`='1' for update | 41488           |               5 | NULL           |
+----------------+----------------+--------------------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)  

Mysql 5.7 查看阻塞

Mysql5.7查看阻塞可以直接在informationschema中的Innodblocks表中看到相应的结果。
同时在innodb_trx表中可以看到事务ID和相关状态。

死锁

两个交易都在等待对方资源释放,导致永远都不会释放自己持有的锁。
通过设置innodbprintalldeadlocks,可以记录死锁到错误日志。
同时,如果设置autocommit = 0来拒绝自动提交,设置innodb
table_locks = 1,Innodb可以自动发现死锁。

避免死锁的做法
1. 保持事务尽可能小 2. 降低事务隔离级别 3. 适当使用索引 4. 不要使用for update和lock in share mode

innotop监控工具(Github)

安装

yum install -y epel-release  
yum install -y innotop  

运行

innotop --socket=/mysql/3306/my3306.sock --user=root --password='xiaocan'  

pt-deadlock-logger监控工具

安装

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm  
yum install -y percona-toolkit  

运行

以下命令会在test库中创建deadlocks表记录死锁数据

pt-deadlock-logger --ask-pass --run-time=10 --interval=1 --create-dest-table --dest D=test,t=deadlocks --user=root --socket=/mysql/3306/my3306.sock