First of all, the WHERE condition in the SQL transaction is hitting the primary key index, and the table isn't large, so we can rule out the possibility that the table is causing slow performance. By checking the output of `SHOW PROCESSLIST;`, we found that only one SQL transaction was running on this table. At first glance, it didn’t look like a deadlock situation.
However, after consulting yellbehuang, I learned that you can’t determine if a deadlock is happening just by looking at `SHOW PROCESSLIST`. Instead, you need to query the InnoDB lock-related tables. There are three main tables involved in tracking InnoDB locks.
By querying these tables, we were able to get more detailed information about the current locking state. The key fields in these tables provide insight into which transactions are waiting for locks and what kind of locks they are holding.
By joining `INNODB_LOCKS` with `INNODB_TRX`, using the thread ID from `SHOW PROCESSLIST`, we could identify that the SQL connection was indeed in a **LOCK WAIT** state.
We then used the `INNODB_LOCK_WAITS` table to find the transaction ID associated with the lock. From there, we queried the `INNODB_TRX` table to get more details about the transaction, including the SQL statement and the thread ID.
Interestingly, the SQL statement was null, but the transaction was still holding a lock. This led us to investigate further. After some research and consulting with Jameszhou, we discovered that this behavior is related to how InnoDB handles write operations. When a write transaction is executed, InnoDB acquires a row lock even before the transaction is committed. If the code has a bug and doesn’t properly commit the transaction, the lock remains held, preventing other connections from writing to the same row.
This explains why the SQL statement appears as null—because the update operation was already written to the buffer, but the commit never occurred. As a result, the lock was not released, and new connections had to wait indefinitely.
So why does InnoDB perform two writes? According to my research, it’s because InnoDB uses logical logging. When inserting a record, multiple physical operations occur on a page, such as updating header values, slot counts, and record data. These are all handled logically in the log to reduce the amount of data logged.
However, if a write fails (e.g., due to an incomplete page write), the data may become corrupted. To prevent this, InnoDB uses a double-write mechanism. It first writes the data to a temporary buffer inside the tablespace, and then writes it to the actual location on disk. This ensures that even in the event of a crash, the data can be recovered correctly.
In summary, understanding InnoDB's locking and write mechanisms is crucial for diagnosing issues like deadlocks or long waits. By using the right system tables and knowing how InnoDB works under the hood, we can better troubleshoot and resolve performance problems.
Copper Tube Terminals Without Checking Hole
Our company specializes in the production and sales of all kinds of terminals, copper terminals, nose wire ears, cold pressed terminals, copper joints, but also according to customer requirements for customization and production, our raw materials are produced and sold by ourselves, we have their own raw materials processing plant, high purity T2 copper, quality and quantity, come to me to order it!
Copper Tube Terminals Without Checking Hole,Cable Lugs Insulating Crimp Terminal,Cable Connector Tinned Copper Ring Terminal,Tubular Cable Lugs Crimp Terminal
Taixing Longyi Terminals Co.,Ltd. , https://www.txlyterminals.com