前提条件

1.需要数据库的DBA权限 2.需要开启数据库的定时策略 3.建立一张表用于记录长时间等待行锁的事务信息

整体步骤

1.创建存储记录的表 2.开启数据库的定时策略 3.编写存储过程 4.创建事件定时调用存储过程

1.创建存储记录的表

DDL:

CREATE TABLE LOCK_RECORD (
  BLOCKING_TIME varchar(100) DEFAULT NULL,
  BLOCKING_QUERY varchar(100) DEFAULT NULL,
  BLOCKING_THREAD varchar(100) DEFAULT NULL,
  BLOCKING_TRX_ID varchar(100) DEFAULT NULL,
  WAITING_TIME varchar(100) DEFAULT NULL,
  WAITING_STATE varchar(100) DEFAULT NULL,
  WAITING_QUERY varchar(1024) DEFAULT NULL,
  WAITING_THREAD varchar(100) DEFAULT NULL,
  WAITING_TRX_ID varchar(100) DEFAULT NULL,
  BLOCKING_STATE varchar(100) DEFAULT NULL,
  RE_TIME datetime DEFAULT NULL,
  id bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

2.开启数据库的定时策略

首先执行 show variables like '%event_sche%'; 查询定时策略是否开启，如果是关闭的话，需要先开启。

开启执行语句：set global event_scheduler=1;

3.编写存储过程

存储过程可以理解为一个是一个函数，此时我们需要编写插入指定表指定数据的逻辑

存储过程代码如下： ``

CREATE PROCEDURE record_lock()
begin
	INSERT INTO lock_record (WAITING_TRX_ID,WAITING_THREAD,WAITING_QUERY,WAITING_STATE,WAITING_TIME,BLOCKING_TRX_ID,BLOCKING_THREAD,BLOCKING_QUERY,BLOCKING_STATE,BLOCKING_TIME,RE_TIME) 
(SELECT r.trx_id WAITING_TRX_ID,
       r.trx_mysql_thread_id WAITING_THREAD,
       r.trx_query WAITING_QUERY,
       p2.STATE WAITING_STATE,
       p2.`TIME` WAITING_TIME,
       b.trx_id BLOCKING_TRX_ID,
       b.trx_mysql_thread_id BLOCKING_THREAD,
       b.trx_query BLOCKING_QUERY,
       p1.STATE BLOCKING_STATE,
       p1.`TIME` BLOCKING_TIME,
       (select now()) RE_TIME
  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
         join information_schema.processlist p1 
        on p1.id = b.trx_mysql_thread_id 
       join information_schema.PROCESSLIST p2
      on p2.id = r.trx_mysql_thread_id 
     order by p1.`TIME` desc);
END

4.创建事件定时调用存储过程

创建事件

CREATE EVENT lock_record //创建事件
on schedule every 10 second //事件的调度频率
DISABLE ON SLAVE
do call record_lock(); //调用创建的存储过程

常用定时执行计划

-- 表示创建后并不开始生效
on completion preserve disable

on schedule every 1 second //每秒执行1次
on schedule every 2 minute //每两分钟执行1次
on schedule every 3 day //每3天执行1次

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2016-10-01 21:50:00' //在2016年10月1日，晚上9点50执行

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天

开启事件

alter event lock_record on completion preserve enable;

实战演示

事务1

select * from dam_element de where id = 1 for update

事务2

update dam_element set description = '2' where id = 1

表中数据

Mysql定时记录数据库中等待行锁的事务信息