AWS Aurora环境下插入触发MySQL Lock wait timeout异常排查求助

Hey there, let's dig into why you're hitting a lock wait timeout on a straightforward INSERT in AWS Aurora MySQL—this is definitely unexpected since you'd usually see this with updates or deletes. Below are the most probable causes and steps to track down which transaction is holding the lock:

Possible Causes of Lock Wait Timeout on INSERT #

• Gap/Next-Key Locks from InnoDB: Even INSERTs can trigger these locks if there's an ongoing transaction holding locks on adjacent rows (like a DELETE or UPDATE on a row near where your INSERT would land). InnoDB uses these by default in the repeatable-read isolation level to prevent phantom reads.
• Long-Running Transactions: Any open transaction (even a read-only one) that's scanned the Document table might be holding shared locks that conflict with your INSERT. For example, a transaction that ran SELECT * FROM Document WHERE page_number = 12 and hasn't committed yet could block your insert if there's an index on page_number.
• Foreign Key Constraint Checks: If the Document table has a foreign key linked to another table, a long-running transaction on that parent table could be holding locks your INSERT needs to validate the constraint.
• DDL Operations: Even if you didn't manually lock the table, someone might be running a DDL command (like ALTER TABLE) on Document—these take exclusive table locks that block all writes, including INSERTs.
• Aurora Cluster-Specific Issues: Rarely, replication lag on read replicas or temporary writer node issues could lead to unexpected lock contention, though this is less likely for simple inserts.

How to Track Down the Lock Holder #

• Inspect InnoDB Transaction & Lock Details: Run these commands on your Aurora writer node:
  • SHOW ENGINE INNODB STATUS;: Head to the TRANSACTIONS section. Look for entries marked WAITING FOR THIS LOCK TO BE GRANTED—you'll find the corresponding transaction that holds the blocking lock nearby.
  • SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;: Identify the waiting transaction by looking for trx_state = 'LOCK WAIT', note its trx_id.
  • SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;: Match the waiting trx_id to find the blocking_trx_id, then cross-reference that ID in INNODB_TRX to see what the blocking transaction is doing.
• Spot Long-Running Queries: Use SHOW PROCESSLIST; or SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'; to find queries that have been active for an unusually long time—these are often the ones holding locks.
• Leverage Aurora's Built-In Tools: Check the AWS RDS Console's "Events" tab for ongoing DDL operations, and use Performance Insights to visualize lock contention trends over time. This can help you spot patterns you might miss with raw queries.
• Verify Isolation Level: Run SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation; to confirm if you're using the default repeatable-read level. If your application doesn't require this consistency, switching to read-committed can reduce the occurrence of gap locks.

If you can share the output from SHOW ENGINE INNODB STATUS; or details of any long-running transactions, we can narrow this down even further.

内容的提问来源于stack exchange，提问作者Lohith MV