Thanks to Jeremiah Wilton for the following info:
This wait event indicates that there is a thread which is waiting on an InnoDB record lock. Check your database for conflicting workloads. More information on InnoDB locking can be found here: https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html
In other words, record-level lock conflicts are happening. More than one connection is trying to update the last_login for a particular id in the_table at the same time. Those connections are conflicting and serializing on the record lock for that id. Here’s a query that can help you identify the blocker and waiter for InnoDB record locks in MySQL-family engines. Run this when you see the aurora_lock_thread_slot_futex wait event in Performance Insights. In a future release of Performance Insights, we will automatically generate and display a similar blockers-and-waiters report when Performance Insights detects this event.
select p1.id waiting_thread, p1.user waiting_user, p1.host waiting_host, it1.trx_query waiting_query,
ilw.requesting_trx_id waiting_transaction, ilw.blocking_lock_id blocking_lock, il.lock_mode blocking_mode,
il.lock_type blocking_type, ilw.blocking_trx_id blocking_transaction,
case it.trx_state when 'LOCK WAIT' then it.trx_state else p.state end blocker_state, il.lock_table locked_table,
it.trx_mysql_thread_id blocker_thread, p.user blocker_user, p.host blocker_host
from information_schema.innodb_lock_waits ilw
join information_schema.innodb_locks il on ilw.blocking_lock_id = il.lock_id and ilw.blocking_trx_id = il.lock_trx_id
join information_schema.innodb_trx it on ilw.blocking_trx_id = it.trx_id
join information_schema.processlist p on it.trx_mysql_thread_id = p.id
join information_schema.innodb_trx it1 on ilw.requesting_trx_id = it1.trx_id
join information_schema.processlist p1 on it1.trx_mysql_thread_id = p1.id;
+----------------+--------------+---------------------+---------------------------------------+---------------------+--------------------+---------------+---------------+----------------------+---------------+----------------------+----------------+--------------+---------------------+
| waiting_thread | waiting_user | waiting_host | waiting_query | waiting_transaction | blocking_lock | blocking_mode | blocking_type | blocking_transaction | blocker_state | locked_table | blocker_thread | blocker_user | blocker_host |
+----------------+--------------+---------------------+---------------------------------------+---------------------+--------------------+---------------+---------------+----------------------+---------------+----------------------+----------------+--------------+---------------------+
| 1117 | reinvent | 172.31.51.118:34734 | UPDATE sbtest8 SET k=k+1 WHERE id=125 | 888017450 | 888017113:88:6:17 | X | RECORD | 888017113 | LOCK WAIT | `sysbench`.`sbtest8` | 1196 | reinvent | 172.31.51.118:34888 |
| 1117 | reinvent | 172.31.51.118:34734 | UPDATE sbtest8 SET k=k+1 WHERE id=125 | 888017450 | 888017089:88:6:17 | X | RECORD | 888017089 | LOCK WAIT | `sysbench`.`sbtest8` | 1431 | reinvent | 172.31.51.118:35366 |
| 1117 | reinvent | 172.31.51.118:34734 | UPDATE sbtest8 SET k=k+1 WHERE id=125 | 888017450 | 888015342:88:6:17 | X | RECORD | 888015342 | LOCK WAIT | `sysbench`.`sbtest8` | 1680 | reinvent | 172.31.51.118:35868 |
.
.
+----------------+--------------+---------------------+----------------------------------------+---------------------+-Also the following: https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-examples.html
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+----------------------------------------+-----------------+-----------------+----------------------------------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+----------------------------------------+-----------------+-----------------+----------------------------------------+
| 917169041 | 2822 | UPDATE sbtest5 SET k=k+1 WHERE id=126 | 917169007 | 2296 | UPDATE sbtest5 SET k=k+1 WHERE id=126 |
| 917169041 | 2822 | UPDATE sbtest5 SET k=k+1 WHERE id=126 | 917168488 | 2214 | UPDATE sbtest5 SET k=k+1 WHERE id=126 |
| 917169025 | 3069 | UPDATE sbtest2 SET k=k+1 WHERE id=125 | 917168945 | 2700 | UPDATE sbtest2 SET k=k+1 WHERE id=125 |
.
.
+----------------+----------------+----------------------------------------+-----------------+-----------------+----------------------------------------+see AWS forum post at https://forums.aws.amazon.com/thread.jspa?threadID=289484
Comments