This is the 17th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Problem description

The error message is simple: an error occurs when the UPDATE statement is executed.

Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock;

MySQL lock is introduced

MySQL has three lock levels: page level, table level, and row level

  1. Row-level locking is used not to directly lock the row, but to lock the index
  2. If a SQL query uses a primary key index, mysql will lock the primary key index.
  3. If a SQL query operates on a non-primary key index,mysql will first lock the non-primary key index and then lock the primary key index.

Cause analysis,

The cause of the error is simple: SQL threw a deadlock error when updating table records by primary key. It is strange that primary key index updates should not generate errors, and The Times we observed errors were exceptions thrown around 3am. At 3 o ‘clock, we will back up the data of this table, delete the expired data and delete the index when the data is deleted.

reference

Finally, I recommend a project that Github maintains to resolve a summary of deadlock cases. Github.com/aneasystone…