Correlating OS Thread IDs from SEMAPHORES Section to TRANSACTIONS Section

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549 
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do you match them up?

SELECT LOWER(HEX(1079654736));

This returns:


Now, examine the TRANSACTIONS section, and look at the value for “OS thread handle”.

Here is the corresponding entry in the TRANSACTIONS section (note “OS thread handle 0x405a3950”) for the semaphore wait:

---TRANSACTION 99FAB3092, ACTIVE 283 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x405a3950, query id 3546555576 update
REPLACE INTO `t1` (id,`date`,`col3`,`col4`,`col5`,`col6`,`col7`,col8)
VALUES (352937385,'2012-08-27 06:16:58','1056665','3755','0','0','0',101)
TABLE LOCK table `db1`.`t1` trx id 99FAB3092 lock mode IX
RECORD LOCKS space id 3680 page no 156295 n bits 456 index `PRIMARY` of table `db1`.`t1` 
trx id 99FAB3092 lock_mode X locks rec but not gap

Since we see “0x405a3950”, we know this is the transaction waiting for the semaphore.

Hope this helps.