MySQL Lock

发表于 | 分类于 ，

Innodb

锁类型

  • 共享锁(S)

  • 排他锁(X)

  • 意向共享锁(IS)

  • 意向排他锁(IX)

    X IX S IS
    X Conflict Conflict Conflict Conflict
    IX Conflict Compatible Conflict Compatible
    S Conflict Conflict Compatible Compatible
    IS Conflict Compatible Compatible Compatible

Innodb的锁是作用于索引上的，因此理解innodb的锁，要先理解innode的索引实现B+树。

  1. 行级锁（Record Locks）

行级锁只存在于有索引的行上，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 锁住C1等于10的索引，此时不能在插入，修改或删除c1=10的行。

  1. 区间锁（Gap Locks）

区间锁是锁住两个索引的区间，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;加区间锁，锁住这个区间。则C1=15此时插入不进去。

  1. 间隙锁（next-key locks）
  1. 插入意向锁（Insert Intention Lock）
  1. 自增锁

自增锁是表锁。如果一个表有一个自增的列，则插入数据的时候，事务需要获得表锁。

Status报告

高并发关注点：SEMAPHORES

  • RW-shared是共享锁

  • RW-excl是排它锁

1
2
3
4
5
6
7
8
9
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14281934
OS WAIT ARRAY INFO: signal count 10439402
RW-shared spins 0, rounds 69859, OS waits 16343
RW-excl spins 0, rounds 3820844, OS waits 71314
RW-sx spins 15843, rounds 214137, OS waits 1611
Spin rounds per wait: 69859.00 RW-shared, 3820844.00 RW-excl, 13.52 RW-sx
  1. OS WAIT

reservation count表示Innodb产生了多少次OS WAIT, signal count表示，进行OS WAIT的线程，接收到多少次信号（singal）被唤醒。

如果你看到signal的数值很大，通常是几十万，上百万。就表明，可能是很多I/O的等待，或是Innodb争用(contention)问题。关于争用问题，可能与OS的进程调度有关，你可尝试减少innodb_thread_concurrency参数。

  1. Innodb如何处理互斥量(Mutexes)和两步获得锁(two-step approach)

首先进程，试图获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗？”。如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。直到锁被释放后，通过信号(singal)唤醒线程。

  • Mutex spin waits是线程无法获取锁，而进入的spin wait

  • rounds 是spin wait进行轮询检查Mutextes的次数

  • OS waits 是线程放弃spin-wait进入挂起状态

Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间，检查锁的状态，OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。你可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait。

LATEST DETECTED DEADLOCK

当你服务器发生了死锁的情况时，这部分会显示出来。死锁通常的原因很复杂，但是这一部分只会显示最后两个发生死锁的事务，尽管可能也有其它事务也包含在死锁过程中。

TRANSACTIONS

包含Innodb 事务（transactions）的统计信息，还有当前活动的事务列表。

参考链接

http://blog.51cto.com/louisyang/1379813

性能指标

TPS

TPS = (Com_commit + Com_rollback) / seconds

1
2
show global status like 'Com_commit'; 
show global status like 'Com_rollback';

锁定状态

1
2
3
show global status like '%lock%';
or
show status like '%lock%';

  • table_locks_immediate：表示可以立即获取锁的查询次数

  • table_locks_waited：表示不能立即获取锁的次数

  • Innodb_row_lock_current_waits：表示等待行锁定的数量，而与行数无关。

  • Innodb_row_lock_time：系统启动到现在，锁定的总时间长度

  • Innodb_row_lock_time_avg：每次平均锁定的时间

  • Innodb_row_lock_time_max：最长一次锁定时间

  • Innodb_row_lock_waits：系统启动到现在总共锁定的次数

问题：table_locks_immediate和 table_locks_waited，它们保持多大的比例是合适的?

table_locks_waited并不容易触发，除非应用主动作lock tables t read。

Table_locks_waited/Table_locks_immediate=0.3%，如果这个比值比较大的话，说明表锁造成的阻塞比较严重。

若库中都是InnoDB的表，在5.5以后，table_locks_waited这个值应该很小。在mysqldump导出表时，会执行lock table，可能导致此值增加。

其他情况下，若这个值有变，说明应用端主动作了lock table，这个在InnoDB表上是不需要的，需要应用修改。

锁事务

  1. 正在锁
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  1. 等待锁
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  1. 查看锁情况
1
2
3
show status like '%lock%'
or
show status like 'innodb_row_lock_%';

  • Innodb_row_lock_current_waits 当前等待锁的数量

  • Innodb_row_lock_time 系统启动到现在，锁定的总时间长度

  • Innodb_row_lock_time_avg 每次平均锁定的时间

  • Innodb_row_lock_time_max 最长一次锁定时间

  • Innodb_row_lock_waits 系统启动到现在总共锁定的次数

区别是否锁表

  1. 当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重。

锁性能

InnoDB加锁的详细信息，主要用于分析一条语句的详细加锁结构以及加了什么锁。

1
2
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

查看监控信息，Status字段的监控信息

1
show engine innodb status;

查看其中一条事务信息

1
2
3
4
---TRANSACTION 10701389, ACTIVE 0 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 161917, OS thread handle 140218872289024, query id 49510107 cloudsqlproxy~35.198.252.157 accounting cleaning up
Trx read view will not see trx with id >= 10701390, sees < 10701369

参考链接:http://www.ywnds.com/?p=11750

参考链接

https://blog.csdn.net/dc666/article/details/78901341