mysql deadlock specific lines

Sometimes, the scope operation may cause the lock. At this time, we need to know which line causes the lock https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/ In this article, I mentioned how to view specific lines, but it doesn't match my test environment. In this article, 0 refers to the primary key, but 0 refers to the first field of the unique index. oracle official website gives a way to view https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html In this way, you can view specific records. The output of show engine innodb status of my test environment is as follows:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42405 page no 35836 n bits 736 index `idx_code_date` of table `wdw`.`lock_t1` trx id 351077568 lock_mode X locks rec but not gap waiting
Record lock, heap no 164 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 9; hex 3030333633312e4f46; asc 003631.OF;;
 1: len 5; hex 999b260000; asc   &  ;;
 2: len 4; hex 811eca73; asc    s;;

*** (2) TRANSACTION:
TRANSACTION 351077561, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
191 lock struct(s), heap size 30248, 465 row lock(s), undo log entries 232
MySQL thread id 1817997, OS thread handle 0x7fc90858a700, query id 38786558 localhost root updating
delete from lock_t1 WHERE fund_code='003630.OF' and trade_date >= '2016-12-19 00:00:00'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 42405 page no 35836 n bits 736 index `idx_code_date` of table `wdw`.`lock_t1` trx id 351077561 lock_mode X locks rec but not gap
Record lock, heap no 164 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 9; hex 3030333633312e4f46; asc 003631.OF;;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 351081879:42405:35836:164
lock_trx_id: 351081879
  lock_mode: X
  lock_type: RECORD
 lock_table: `wdw`.`lock_t1`
 lock_index: idx_code_date
 lock_space: 42405
  lock_page: 35836
   lock_rec: 164
  lock_data: '003631.OF', 0x999B260000
*************************** 2. row ***************************
    lock_id: 351081874:42405:35836:164
lock_trx_id: 351081874
  lock_mode: X
  lock_type: RECORD
 lock_table: `wdw`.`lock_t1`
 lock_index: idx_code_date
 lock_space: 42405
  lock_page: 35836
   lock_rec: 164
  lock_data: '003631.OF', 0x999B260000
2 rows in set (0.00 sec)

However, it is not clear what value 0x999B260000 refers to. The storage articles about datetime are as follows: http://m.blog.itpub.net/703656/viewspace-1018371/

Tags: MySQL Oracle

Posted on Sun, 03 May 2020 23:37:05 -0400 by Nickosssss