Mysql 锁

数据库的锁机制就是保证数据的一致性。

Innodb 支持行锁，有时会升级为表锁。

MyIsam：支持表锁

× 表锁的特点：

   开销小，加锁块；不会出现死锁，出现锁冲突的概率高，并发度相对低。

× 行锁特点：

开销大，加锁慢，会出现死锁现象；锁力度小，发生冲突的概率低。并发度相对高。

INNODB 的锁类型：

主要有读锁（共享锁）、写锁（排他锁）、意向锁和MDL 锁。

读锁： S锁。

可以同时读取同一行数据。但是在有读锁的情况下，不能进行修改。有读锁就不能获得写锁。

写锁： X锁

一个事务获取了一个数据行的写锁，其他事务就不能获取该行的其他锁，写锁优先级最高。

一些DML语句都会对行记录加写锁。

比较特殊的就是 select for update ，他会对读取的行增加一个写锁，那么其他的任何事物就不能加任何锁。

MDL锁

Mysql 5.5 引入了meta data lock，简称MDL锁，用于保证表中的元数据的信息。

意向锁：

在mysql 存储引擎INNODB中，意向锁是表锁。而且有两种意向锁的类型，分别为意向共享锁和意向排他锁。

意向共享锁（IS）： 是指在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：是指在给一个数据行加排他锁前必须先取得该表的IX锁。

其实意向锁的作用跟MDL锁类似，都是防止数据的不一致。

锁等待和死锁

一个事务需要等待另一个事务释放它的锁后才能对对数据加锁。如果一上一个事务一直不释放，就需要持续等待下去。直到等待超时。

查看锁等的超时时间：

show variables like 'innodb_lock_wait_timeout';

死锁是指两个或两个以上的进程在执行过程中，因为争夺资源而造成一种等待的现象就是所谓的锁等待。即死循环。

 

监控锁状态及查询锁状态

1、看看有没有锁等待 

 show status like ‘inndob_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |  --------> 当前有多少锁等待
| Innodb_row_lock_time          | 0           |   -------->锁等待持续了多场时间   
| Innodb_row_lock_time_avg      | 0     |    ---------> 平均锁等待时间
| Innodb_row_lock_time_max      | 0     |   ----------> 最大锁等待时间
| Innodb_row_lock_waits         | 0     |        ---------->一共发生了多少次锁等待
+-------------------------------+-------+
 

2、查出那个事务在等待（被阻塞了）

use information_schema

select * from information_schema.INNODB_TRX where trx_state='LOCK WAIT';

trx_id: 事务ID

trx_state: 当前事务的状态

trx_mysql_thread_id: 连接层，连接线程ID（show processlist ===》ID 或 trx_id)

trx_query: 当前被阻塞的操作（一般是要丢给开发的）

 

3、查看锁源（看看谁站着茅坑不拉屎）

select × from sys.innodb_lock_waits;   #===========> 被锁的和锁定他的之间的关系

locked_table: 那张出现锁等待

waiting_trx_id:  等待的事务（与上个视图trx_id对应）

waiting_pid: 等待的线程号（与上一个试图trx_mysql_thread_id)

blocking_trx_id: 正在执行锁操作的事务ID

blocking_pid: 正在执行锁操作的线程号（show processlist）

 

4、找到锁源的thread_id (SQL线程）

select × from performance_schema.threads  where processlist_id=8;

===> 33 (SQL 线程ID）

5、找到锁源的SQL语句

select * from performance_schema.`events_statements_current`  where thread_id=33;

 

5.6 与5.7不同（5.6 没有sys库）

 

 

----执行语句的历史

select × from  performance_schema.events_statements_history where thread_id=41;

 

 

优化项目：锁的监控及处理

1、背景：

硬件环境： DELL R720 E系列16核， 48G MEM，SAS×900G ×6， RAID 10

在例行巡检时，发现9-11 点时间段cpu 压力非常高（80--90%）

2、项目的职责

     2.1 通过top 相信排查，发现mysqld 进程占比达到了700-800%

      2.2 其中cpu是的sys和wait比较高，us 处于正常

      2.3 怀疑是mysql 锁或者SQL语句除了问题。

      2.4 经过排查slow log 及锁等待情况，发现有大量锁等待及少量的慢语句。

            （1） pt-query-digest 查看慢查询日志

              （2）有没有锁等待

                       show status like 'innodb_row_lock%';

 

情况一： current_waits 说明当前很多锁等待

情况二： 1000多个lock_waits 说明历史上发生过的锁等很多

            2.5 查看那个事务在等待（谁被阻塞了）

            2.6 查看正在执行加锁的事务（谁锁的）

           2.7 找到锁源的thread_id

           2.8 找到锁源的SQL语句

 3.    找到语句之后与开发人员进行协商找出问题原因

       1、事务挂起导致的

       2、业务逻辑问题导致的死锁，产生了大量锁等待

          临时： kill

         最终： 修改代码中的业务逻辑

项目结果：

      经过排查出来，锁等待的个数减少80%，解决了cpu持续峰值的问题。

锁查询使用的命令：

show status like 'innodb_row_lock%';

select * from information_schema.innodb_trx;

select * from sys.innodb_lock_waits;

select * from performance_schema.threads;

select * from performance_schema.events_statements_cuurent;

select * from performance_schema.events_statements_history;

 

 

 

死锁监控：

show engine innodb status\G

show status like 'innodb_row_lock%';

vim /etc/my.cnf

innodb_print_all_deadlocks =1  #  把所有的死锁信息写入到error 日志里面。

 

死锁会产生严重的锁等待。

查出SQL 语句或者修改程序逻辑。

 

 

 

 

 

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

监控锁状态

1、查看没有锁等待

show status like 'innodb_row_lock%';

2、查看哪个事务在等待（被阻塞了）

use information_schema

select * from information_schema.INNODB_TRX where trx_state='LOCK WAIT';

trx_id:

trx_state:

trx_mysql_thread_id: 连接层的，连接线程id（show processlist ）

trx_query

: