Let us consider a transaction handled by Query ID 5068.
WORDPRESS MYSQL DEADLOCK UPDATE
After the UPDATE over EO_PK_TABLE it keeps on Updating the columns in EO_PK_TABLE. But at cases the transactions are not committed by the application. I have enabled the query log on this non-prod environment to get the query write pattern. 3)Fetch the next ID 4)Insert into relevant table 5)Commit. 1) Setting the ISOLATION LEVEL 2) Increment the EO_PK_TABLE based on NAME Column. The UNDO entries further for the queries gives the hint that it is not start of the transaction or there is an open transaction. While Updating the records from application perspective, the sequence generation was supposed to be the first step in the transaction. It is noted that the transaction have UNDO log entries. There is a lock over the index, but what is causing the lock and why it happens ? -TRANSACTION 2821458353, ACTIVE 135 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 5455, OS thread handle 139772578862848, query id 590796 10.0.5.128 appuser Searching rows for update UPDATE EO_PK_TABLE SET PK = := PK + 100) WHERE NAME = 'service' - TRX HAS BEEN WAITING 135 SEC FOR THIS LOCK TO BE GRANTED: Let us have a look at the InnoDB Engine status -TRANSACTION 2821395544, ACTIVE 212 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 3397, OS thread handle 139772570343168, query id 195064 10.0.5.128 appuser updating UPDATE EO_PK_TABLE SET PK = := PK + 100) WHERE NAME = 'contact' - TRX HAS BEEN WAITING 212 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1087 page no 3 n bits 248 index PRIMARY of table `ccbuser`.`EO_PK_TABLE` trx id 2821395544 lock_mode X locks rec but not gap waiting error code: 1205 - msg: Lock wait timeout exceeded try restarting transaction"} The table structure is below show create table EO_PK_TABLE\G It was supposed to fix the problem but it does not in the real world test scenario. To get rid of this, the sequence generation were made as direct Update queries and sequence is fetched using Select UPDATE EO_PK_TABLE SET PK = := PK + 100) WHERE NAME = 'dealer' But it tends to cause more locking at high concurrency with their XtraDB Cluster deployment.
WORDPRESS MYSQL DEADLOCK GENERATOR
The application needs a Sequence generator as it was not present inbuilt with MySQL ( MariaDB will have it in 10.3 ) they have made sequence generator function with ‘ select for update‘ statement. I was involved in one of the development support for MySQL.
0 kommentar(er)
