Database Deadlock Cases and Their Resolutions

Database deadlocks manifest when a process attempts to access a row or field that is locked by another transaction, causing the waiting process to eventually time out and raise a system error.

During routine monitoring, several deadlock errors appeared in the logs, including both INSERT and SELECT statements, prompting a detailed case‑by‑case investigation.

java.lang.RuntimeException: org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve defaultParameterMap

Case 1: INSERT with a missing unique key leading to deadlock

Log excerpt:

2017-06-21.10:52:15.490 ERROR [http-bio-8080-exec-51] d.s.Statement.statementLogError:149 [] {conn-10039, pstmt-7757099} execute error. insert into tb_call_index_record (business_no,mall_user_id,status) values ( ?,?,?) com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

The INSERT omitted the task_no field, which is defined as a UNIQUE KEY ( UNIQUE KEY `uniq_taskno` (`task_no`) ). Inserting a NULL value created a duplicate entry in the unique index, causing the deadlock. The resolution was to filter out records with an empty task_no before insertion, after which the deadlock ceased.

Case 2: Concurrent INSERTs with the same unique key causing deadlock

Log excerpt:

2017-06-22.11:15:10.903 ERROR [http-bio-8080-exec-67] c.q.i.n.w.a.c.c.CallCenterController.action:198 java.lang.RuntimeException: org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve defaultParameterMap ### SQL: insert into tb_call_out_record(task_no,sn,ctime) values (?,?,?)

Two nearly simultaneous requests with identical task_no values were logged within milliseconds of each other. The first thread succeeded, while the second encountered a duplicate‑key situation, acquiring a shared (S) lock and then waiting for an exclusive (X) lock, resulting in a deadlock. The fix involved adding request de‑duplication logic at the controller layer (or implementing idempotency checks) to prevent identical concurrent inserts.

Case 3: SELECT deadlock caused by a JDBC driver parameter bug

Log excerpt:

java.lang.RuntimeException: org.springframework.dao.DeadlockLoserDataAccessException: ### Error querying database. Deadlock found when trying to get lock; try restarting transaction ### The error may involve defaultParameterMap ### SQL: select * from tb_allocation where user = ?

Investigation revealed that the MySQL JDBC driver was configured with useServerPrepStmts=true , a known bug in MySQL 5.6 that can cause deadlocks even for simple SELECT statements. Removing this parameter from the connection URL eliminated the SELECT deadlocks.

Summary

When encountering deadlocks, verify the presence of transactions, concurrent operations, and the table schema—especially UNIQUE indexes, which can be a hidden source of deadlocks.

Analyze production logs to identify duplicate or near‑duplicate requests that may trigger lock contention.

Avoid using useServerPrepStmts=true with MySQL 5.6, as it is an acknowledged driver bug that can cause deadlocks.