First of all, the where condition of the sql transaction has hit the primary key index, and the table is not large, so you can rule out the reason why the table is too slow. Through the show processlist; found that only the sql transaction in the operation of this table, at first glance does not seem to be the cause of deadlock:
But after consulting yellbehuang, it is found that whether the sql transaction is deadlock can not be judged by the show processlist, but by querying the related table of the innodb lock, there are mainly three tables related to the innodb lock.
The meanings of the fields in the above table are as follows:
The lock state of the sql can be obtained by selecting * from INNODB_LOCKS a inner join INNODB_TRX b on a.lock_trx_id=b.trx_id and trx_mysql_thread_id=thread id, and the thread id can be obtained by the above show processlist, and the execution result is as follows:
At this point, it is found that the sql connection is indeed in the LOCK WAIT lock wait state.
You can get the transaction ID 75CB26AE of the current lock by selecting * from innodb_lock_waits where requesTIng_trx_id=75CB26E5 (that is, the lock_trx_id obtained from the above query).
Then get the sql statement and thread id by select * from innodb_trx where lock_trx_id=75CB26AE
As seen from the above results, the transaction is in the running state, but the sql is null. The thread id is the connection to the 30764 port of the 206 machine of the show processlist above, and the connection is in the sleep state. Why is sql still null but still possessing locks? After querying relevant information and consulting jameszhou, I know that this is actually related to the writing mechanism of the innodb engine. When innodb performs a write transaction operation, it actually obtains the row lock of the row in the index (even if there is no index on the table, then innodb Will create a hidden aggregate primary key index in the background, and then write in the cache, the last transaction commit is officially written to the DB and release the lock. The reason why sql is null is because the connection has already written the sql update operation into the cache, but because the code bug has no final commit, it always takes up the row lock. The subsequent new connection wants to write this row of data because it always takes Not waiting for a row lock and waiting for a long time.
So why does innodb need to write twice? The following is the conclusion I got from querying relevant information:
Because the log in innodb is logical, the so-called logic is that when inserting a record, it may cause a value of a certain length to be written at multiple offset positions of a certain page (where the record is finally inserted). For example, the number of records in the header, the number of slots, the data in the page slot, the value of the record in the page, etc. These are some physical operations, and innodb is designed to be logically processed in order to save log volume and other reasons. That is, it will insert a record on the basis of a page, then the content recorded in the log record is the table space number, the page number, the value of each column of the record, etc., and is internally converted into the above physical operation.
But one problem here is that if the page itself is wrong, this error may be due to a write break (1 page is 16K, multiple writes, there may be no write success, resulting in incomplete pages) Caused by this, then this logical operation can not be completed, because its premise is that the page is still correct, complete, because if the page is not correct, the data in this page is invalid, it may produce various Unpredictable problems.
Well, it is precisely because of this problem, so we must first ensure that this page is correct, the method is to write twice, its idea is ultimately a backup idea, that is, a mirror.
The process of writing innodb twice:
Think of two writes as a short-term log file that is allocated inside the Innodb tablespace, which contains 100 data pages. When Innodb writes out the data pages in the buffer, it uses the method of writing multiple pages at a time, so that multiple pages can be written to the write buffer twice and call fsync() to ensure that the data is written out. Disk, then the data pages are set to their actual storage location and fsync() is called again. In the case of failure recovery, Innodb checks the contents of the doublewrite buffer and the original storage location of the data page. If the data page is in an inconsistent state in the two write buffers, it will be simply discarded. If it is inconsistent in the original storage location, it will be from the two write buffers. reduction.
Shenzhen Ousida Technology Co., Ltd , https://www.osdvape.com