如何根據十六進制轉儲找到誰持有鎖？

我一直在閱讀診斷 MySQL InnoDB 鎖文章。Karl E. Jørgensen 在 2008 年寫道，所以我很困惑它是否有效。

我想提供一個片段SHOW ENGINE INNODB STATUS：

---TRANSACTION 20532F16, ACTIVE 386 sec starting index read
mysql tables in use 6, locked 6
LOCK WAIT 2 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 96238, query id 81681916 192.168.6.31 thanhnt updating
DELETE FROM `v3_zone_date`  
   WHERE `dt` = NAME_CONST('_currDate',_latin1'2012-03-02' COLLATE 'latin1_swedish_ci')
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 482988 page no 6 n bits 360 index `GEN_CLUST_INDEX` of table `reportingdb`.`v3_zone_date` /* Partition `pcurrent_201232` */ trx id 20532F16 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 6; hex 000237440e77; asc   7D w;;
1: len 6; hex 0000204f2acb; asc    O* ;;
2: len 7; hex e6000480120110; asc        ;;
3: len 3; hex 8f5340; asc  S@;;
4: len 2; hex 83d4; asc   ;;
5: len 3; hex 814f42; asc  OB;;
6: len 3; hex 000000; asc    ;;
7: len 3; hex 000000; asc    ;;
8: len 3; hex 800000; asc    ;;
9: len 3; hex 000001; asc    ;;
10: len 3; hex 000000; asc    ;;
11: len 3; hex 8fb862; asc   b;;
12: len 2; hex 0000; asc   ;;

------------------
---TRANSACTION 20532EE8, ACTIVE 437 sec fetching rows, thread declared inside InnoDB 236
mysql tables in use 22, locked 22
24944 lock struct(s), heap size 3586488, 11457529 row lock(s)
MySQL thread id 97447, query id 81504647 event_scheduler Copying to tmp table

查詢被切斷，所以我從SHOW FULL PROCESSLIST輸出中得到它：

*************************** 18. row ***************************
    Id: 97447
  User: thanhnt
  Host: 192.168.6.31
    db: reportingdb
Command: Connect
  Time: 423
 State: Copying to tmp table
  Info: UPDATE `selfserving_banner_zone` A,( SELECT B.`bannerid`,C.`zoneid`,ROUND(SUM(C.`realclick`)*100/SUM(C.`totalview`),5) CTR
       FROM `ox_campaigns` A 
       INNER JOIN `ox_banners` B ON B.`campaignid`= A.`campaignid`
       INNER JOIN `v3_zone_date` C ON C.`campaignid` = B.`campaignid` AND B.`bannerid` = C.bannerid 
       WHERE A.`revenue_type` = 5 AND C.`dt` BETWEEN DATE_SUB( NAME_CONST('_currdate',_latin1'2012-03-02' COLLATE 'latin1_swedish_ci'),INTERVAL 1 DAY) AND  NAME_CONST('_currdate',_latin1'2012-03-02' COLLATE 'latin1_swedish_ci')  AND C.totalview >0 AND A.isExpired NOT IN (1,2)
       AND A.deleted = 0 AND B.deleted = 0 AND  CURRENT_DATE BETWEEN B.`activate` AND B.`expire`  AND A.status = 1 AND B.status = 1 AND C.`realclick` > 0
       GROUP BY B.`bannerid`,C.`zoneid`) B
       SET A.`ctr` = B.CTR WHERE A.`bannerid` = B.bannerid AND A.`zoneid` = B.zoneid

根據上面的文章，TRANSACTION 20532F16 正在等待一個鎖。但正如你所看到的，這裡有一些十六進制轉儲。哪一個可以用來判斷持有鎖的事務？此外，我看到交易號已經是十六進制（例如：20532EE8）

本文沒有解釋有關十六進制的足夠詳細資訊。

PS：我已經嘗試了所有上述十六進制轉儲（十六進制和十進制）但沒有運氣。

---

回复 RolandoMySQLDBA：

我最近寫了一篇關於如何在一個表上鎖定 100,000 行需要 44KB

11457529 行鎖剛剛接管… 5MB。

如果您的 InnoDB 緩衝池不夠大，您可能沒有足夠的資源來定義所需的行鎖。因此，我建議增加您的 innodb_buffer_pool_size。

這是我的緩衝池和記憶體：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 2636907
Buffer pool size   1310712
Free buffers       704307
Database pages     589697
Old database pages 217517
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 361757, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 365924, created 666845, written 1151187
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 589697, unzip_LRU len: 0
I/O sum[32]:cur[0], unzip sum[0]:cur[0]

如您所見，我有很多可用頁面（704307）。你還有其他建議嗎？

PS：innotop顯示關於 InnoDB Locks 的空結果：

_________________________________ InnoDB Locks __________________________________
CXN  ID  Type  Waiting  Wait  Active  Mode  DB  Table  Index  Ins Intent  Special

---

更新 2012 年 3 月 6 日星期二 00:16:41 ICT

PS：我已經嘗試了所有上述十六進制轉儲（十六進制和十進制）但沒有運氣。

我的 : 中沒有與上述十六進制的交易SHOW ENGINE INNODB STATUS;：

$ egrep -i '8f5340|814f42|8fb862' innodb.status_2012-03-02
3: len 3; hex 8f5340; asc  S@;;
5: len 3; hex 814f42; asc  OB;;
11: len 3; hex 8fb862; asc   b;;

現在這真的很容易。不要使用SHOW ENGINE INNODB STATUS，使用 information_schema.innodb_locks。這是一個我用外鍵寫的部落格文章的例子：

http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/

對於使用 MySQL >= 8.0 的使用者，information_schema.innodb_locks現在移至performance_schema.data_locks.

參考這篇博文：https ://dev.mysql.com/doc/refman/8.0/en/innodb-locks-table.html

引用自：https://dba.stackexchange.com/questions/14257