view+event to log innodb lock blocks

log_innodb_locks

CREATE VIEW percona.innodb_lock_blocks AS
   SELECT ilw.requesting_trx_id AS requesting_trx_id,
              ilw.blocking_trx_id AS blocking_trx_id,
              itrxr.trx_started AS requesting_trx_started,
              itrxb.trx_started AS blocking_trx_started,
              itrxr.trx_mysql_thread_id AS requesting_mysql_thread_id,
              itrxb.trx_mysql_thread_id AS blocking_mysql_thread_id,
              itrxr.trx_query AS requesting_query,
              itrxb.trx_query AS blocking_query,
              itrxr.trx_started AS requesting_trx_started,
              itrxb.trx_started AS blocking_trx_started,
              itrxr.trx_wait_started AS requesting_wait_started,
              itrxb.trx_wait_started AS blocking_wait_started
       FROM information_schema.innodb_lock_waits ilw
       INNER JOIN information_schema.innodb_trx itrxr
           ON ilw.requesting_trx_id=itrxr.trx_id
       INNER JOIN information_schema.innodb_trx itrxb
           ON ilw.blocking_trx_id=itrxb.trx_id;

CREATE EVENT lockhist_record
   ON SCHEDULE
       EVERY 1 SECOND STARTS NOW()
   COMMENT 'records innodb lock statistics'
       DO INSERT INTO innodb_lock_history SELECT * FROM innodb_lock_blocks;

CREATE TABLE innodb_lock_history (
   requesting_trx_id varchar(18),
   blocking_trx_id varchar(18),
   requesting_trx_started datetime,
   blocking_trx_started datetime,
   requesting_mysql_thread_id bigint unsigned,
   blocking_mysql_thread_id bigint unsigned,
   requesting_query varchar(1000),
   blocking_query varchar(1000),
   requesting_trx_started datetime,
   blocking_trx_started datetime,
   requesting_wait_started datetime,
   blocking_wait_started datetime
) engine=InnoDB;