The Symptoms
ER_LOCK_DEADLOCK lock_deadlocks++
sys.metrics
variable_name = 'lock_deadlocks'
event_errors_summary_global_by_error
error_name = 'ER_LOCK_DEADLOCK'
error_number = 1205
SHOW ENGINE INNODB STATE LATEST DETECTED DEADLOCK
The Cause
Deadlocks are caused by locks being obtained in different orders for two or more transactions.
The Investigation
Enable innodb_print_all_deadlocks (OFF by default). See error log for details. Does show which transaction was rolled back.
The Solution
Automatically resolved.
The Prevention
- Smaller transaction
READ COMMITTED- Shorted transaction
- Pessimistic locking