mysql中的一些锁问题

参考文档：

http://blog.csdn.net/zyz511919766/article/details/50147283

http://narcissusoyf.iteye.com/blog/1637309

mysql　version: mysql.5.6.34

| tx_isolation  | READ-COMMITTED |

| binlog_format | ROW   |

１.表如下:

CREATE TABLE `device_install` (
  `enter_time` datetime DEFAULT NULL COMMENT '安装时间',
  `customer_name` varchar(32) DEFAULT NULL COMMENT '客户姓名',
  `province` varchar(20) DEFAULT NULL COMMENT '省市',
  `city_name` varchar(50) DEFAULT NULL COMMENT '城市',
  `address` varchar(100) DEFAULT NULL COMMENT '安装地址',
  `mobile` varchar(20) DEFAULT NULL COMMENT '客户电话',
  `product_no` varchar(50) DEFAULT NULL COMMENT '机编',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '变更时间',
  `datadate` int(11) DEFAULT NULL COMMENT '数据写入日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备安装表'

２.现象：主库在进行如下删除操作时，从库中出现了持续的同步延时，而且在从库中使用show engine innodb status\G和show processlist均没有任何锁情况的出现。

DELETE FROM device_install WHERE ENTER_TIME>= DATE_FORMAT(DATE_SUB(NOW(),interval 3 day),'%Y-%m-%d');

在从库中查INNODB_TRX表时，没有出现表，但是出现一个正在运行的锁。

                    trx_id: 11977921084
                 trx_state: RUNNING
               trx_started: 2017-06-01 16:56:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 7726
       trx_mysql_thread_id: 2147701
                 trx_query: NULL
       trx_operation_state: fetching rows
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 6923
     trx_lock_memory_bytes: 685608
           trx_rows_locked: 629122
         trx_rows_modified: 803
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

３.在从库上执行如下sql，开启lock锁的监控：

在test库中创建如下有：

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

关闭时，只需要drop table innodb_lock_monitor;即可。

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

4.打开从库日志，会打印出相关的锁信息，如下所示：

------------
TRANSACTIONS
------------
Trx id counter 11977907630
Purge done for trx's n:o < 11977907627 undo n:o < 0 state: running but idle
History list length 6919
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 11977907629, ACTIVE 4896 sec fetching rows
mysql tables in use 1, locked 1
6821 lock struct(s), heap size 669224, 619247 row lock(s), undo log entries 14773
MySQL thread id 38228, OS thread handle 0x7f4c7a0c0700, query id 34359337 Reading event from the relay log
TABLE LOCK table `exchange`.`device_install` trx id 11977907629 lock mode IX
RECORD LOCKS space id 660 page no 4 n bits 168 index `GEN_CLUST_INDEX` of table `exchange`.`device_install` trx id 11977907629 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

....

RECORD LOCKS space id 660 page no 5 n bits 168 index `GEN_CLUST_INDEX` of table `exchange`.`device_install` trx id 11977907629 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

....

RECORD LOCKS space id 660 page no 12 n bits 176 index `GEN_CLUST_INDEX` of table `exchange`.`device_install` trx id 11977907629 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

原因分析：表 `exchange`.`device_install` 发生了锁表情况。

主库中执行的sql语句中，在从库中执行时被分解成了一行一行执行的情况，由于此表中没有主键的原因，不能获得X锁，导致删除数据时，直接获得IX锁，而当其它session想获得X锁时，其实已经无法正常获得这个锁.