Ch 15: Case Study: Record Level Locks

The Symptoms

• Severe: timeoute; deadlocks
• Mild: slow down

Query Analysis in MySQL Enterprise Monitor.

sys.innodb_lock_waits

The Cause

• DML
• SELECT FOR SHARE
• SELECT FOR UPDATE

The Investigation

For MySQL 8+

• data_locks
• data_lock_waits

The Solution

Be wary of killing transaction as rollbacks have a large impacts.

The Prevention

READ COMMITTED