MySQL Deadlock: Deteksi, Analisis, dan Resolusi untuk Production
MySQL Deadlock: Deteksi, Analisis, dan Resolusi untuk Production
Deadlock adalah masalah common di database concurrent systems. Setelah menganalisis dan mengatasi deadlock di production environments, saya akan berbagi strategi untuk deteksi dini dan pencegahan.
Apa itu Deadlock?
Definisi
Deadlock terjadi ketika dua atau lebih transactions saling menunggu satu sama lain untuk release locks.
Contoh Scenario:
Transaction A: Lock row 1, coba lock row 2
Transaction B: Lock row 2, coba lock row 1
Result: DEADLOCK - kedua transaction saling menunggu
Deteksi Deadlock
1. Check Deadlock Log
-- Lihat deadlock terbaru
SHOW ENGINE INNODB STATUS\G
-- Cari bagian "LATEST DETECTED DEADLOCK"
2. Enable Deadlock Logging
# my.cnf
[mysqld]
innodb_print_all_deadlocks = 1
-- Atau runtime
SET GLOBAL innodb_print_all_deadlocks = ON;
3. Monitoring dengan Performance Schema
-- Check deadlock statistics
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ_NORMAL,
COUNT_READ_WITH_SHARED_LOCKS,
COUNT_WRITE_NORMAL,
SUM_TIMER_WAIT
FROM performance_schema.table_lock_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC;
Analisis Deadlock Log
1. Contoh Deadlock Log
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1234, OS thread handle 12345678, query id 123 localhost user
UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 3 n bits 72 index PRIMARY of table `db`.`accounts`
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1235, OS thread handle 12345679, query id 124 localhost user
UPDATE accounts SET balance = balance - 50 WHERE id = 2
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 72 index PRIMARY of table `db`.`accounts`
*** WE ROLL BACK TRANSACTION (1)
2. Analisis Components
- Transaction IDs: Identifikasi transactions yang involved
- Thread IDs: Koneksi mana yang menyebabkan deadlock
- SQL Queries: Apa yang sedang di-execute
- Lock Type: Record locks, gap locks, next-key locks
- Resolution: MySQL akan rollback transaction (1)
Resolusi Deadlock
1. Retry Logic dalam Application
# Python example dengan retry
def execute_with_retry(func, max_retries=3):
for attempt in range(max_retries):
try:
return func()
except mysql.connector.Error as e:
if e.errno == 1213: # Deadlock error code
if attempt < max_retries - 1:
time.sleep(0.1 * (attempt + 1)) # Exponential backoff
continue
raise
# Usage
def transfer_money():
with get_db_connection() as conn:
cursor = conn.cursor()
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
execute_with_retry(transfer_money)
2. Stored Procedure dengan Handler
DELIMITER $$
CREATE PROCEDURE SafeTransfer(
IN p_from_id INT,
IN p_to_id INT,
IN p_amount DECIMAL(10,2)
)
BEGIN
DECLARE retry_count INT DEFAULT 0;
DECLARE max_retries INT DEFAULT 3;
DECLARE deadlock_detected BOOLEAN DEFAULT FALSE;
retry_loop: LOOP
SET deadlock_detected = FALSE;
-- Try transaction
START TRANSACTION;
-- Use consistent order (ascending by ID)
IF p_from_id < p_to_id THEN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
ELSE
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
END IF;
COMMIT;
-- Success
LEAVE retry_loop;
END LOOP;
END$$
DELIMITER ;
Pencegahan Deadlock
1. Consistent Lock Order
-- SELALU lock resources dalam order yang sama
-- Contoh: Berdasarkan ID (ascending)
-- Benar:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Lower ID first
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Higher ID second
COMMIT;
-- Deadlock prone (berbeda order):
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Higher ID first
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Lower ID second
COMMIT;
2. Keep Transactions Short
-- BAD: Long transaction
BEGIN;
SELECT * FROM large_table WHERE status = 'pending'; -- Takes time
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... user input wait ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- GOOD: Short transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
3. Use Lower Isolation Levels
-- Use READ COMMITTED untuk reduce locking
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Atau set global
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
4. Proper Indexing
-- Pastikan WHERE clause columns ter-index
CREATE INDEX idx_account_id ON transactions(account_id);
-- Tanpa index, table scan akan lock lebih banyak rows
Monitoring dan Alerting
1. Deadlock Monitoring Script
#!/bin/bash
# monitor_deadlocks.sh
LOG_FILE="/var/log/mysql_deadlocks.log"
ALERT_EMAIL="dba@example.com"
# Check untuk deadlock messages dalam error log
DEADLOCKS=$(grep -c "Deadlock found" /var/log/mysql/error.log)
if [ $DEADLOCKS -gt 0 ]; then
echo "[$DATE] Detected $DEADLOCKS deadlock(s)" >> $LOG_FILE
grep "Deadlock found" /var/log/mysql/error.log | tail -5 | mail -s "MySQL Deadlock Alert" $ALERT_EMAIL
fi
2. Grafana/Prometheus Monitoring
# Prometheus alert rule
- alert: MySQLDeadlockDetected
expr: rate(mysql_global_status_innodb_row_lock_waits[5m]) > 0
for: 1m
labels:
severity: warning
annotations:
summary: "MySQL deadlock detected"
description: "Deadlocks are occurring in MySQL"
Best Practices Summary
- Consistent lock order: Access resources dalam urutan yang sama
- Short transactions: Minimize lock duration
- Proper indexing: Reduce rows locked
- Retry logic: Handle dengan application-level retry
- Monitor: Track deadlock frequency
- Timeout: Set innodb_lock_wait_timeout reasonable
Kesimpulan
Deadlock adalah normal di concurrent systems:
- Detection: Monitor logs dan performance schema
- Resolution: Application retry dengan backoff
- Prevention: Consistent locking order dan short transactions
Dengan practices yang tepat, deadlock menjadi manageable.
Artikel Terkait
Link Postingan: https://www.tirinfo.com/mysql-deadlock-detection-resolution-production/