InnoDB Deadlock Tespiti ve Çözümü için SQL Sorguları

Veritabanı yönetiminin en sinir bozucu anlarından biri, prodüksiyonda bir deadlock (kilitlenme) patlamasıyla karşılaşmaktır. Uygulamalar askıda kalır, loglar hata mesajlarıyla dolar, kullanıcılar şikayet etmeye başlar ve sen de hangi sorgunun hangisini beklediğini çözmeye çalışırsın. InnoDB’nin bu konuda oldukça iyi araçları var, ama bunları nasıl kullanacağını bilmezsen çok değerli zaman kaybedersin. Bu yazıda gerçek dünya senaryolarıyla deadlock tespiti, analizi ve çözümünü adım adım ele alacağız.

Deadlock Nedir ve Neden Olur?

InnoDB satır seviyesinde kilitleme (row-level locking) kullanır. Bu genellikle harika bir şeydir çünkü eşzamanlılığı artırır. Ancak iki ya da daha fazla transaction birbirinin tuttuğu kilitleri beklemeye başladığında ortaya deadlock çıkar.

Klasik senaryo şöyle işler:

• Transaction A tablo1’deki satır 1’i kilitler, ardından tablo2’deki satır 2’yi kilitlemek ister
• Transaction B tablo2’deki satır 2’yi kilitler, ardından tablo1’deki satır 1’i kilitlemek ister
• İkisi de birbirini sonsuza kadar bekler

InnoDB bu durumu tespit ettiğinde transaction’lardan birini kurban seçip geri alır (rollback) ve “ERROR 1213: Deadlock found when trying to get lock” hatasını döndürür. Aslında bu otomatik çözüm bir nimettir, en azından sistem tamamen kilitlenmez.

InnoDB Status ile İlk Tanı

Deadlock yaşandığında yapacağın ilk şey InnoDB’nin dahili durum raporuna bakmaktır. Bu rapor son yaşanan deadlock hakkında çok detaylı bilgi verir.

mysql -u root -p -e "SHOW ENGINE INNODB STATUSG"

Bu komut tonlarca çıktı üretir. Deadlock ile ilgili kısım LATEST DETECTED DEADLOCK başlığı altındadır. Grep ile direkt oraya atlayabilirsin:

mysql -u root -p -e "SHOW ENGINE INNODB STATUSG" 2>/dev/null | 
  awk '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/'

Çıktı şuna benzer bir şey olacaktır:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-15 14:23:45 0x7f8a1c003700
*** (1) TRANSACTION:
TRANSACTION 421893, ACTIVE 0 sec starting index read
MySQL thread id 1542, OS thread handle 140234567890, query id 98432 localhost app_user
UPDATE orders SET status='processing' WHERE id=1001
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 5 n bits 72 index PRIMARY of table `shopdb`.`orders`
*** (2) TRANSACTION:
TRANSACTION 421894, ACTIVE 0 sec starting index read
MySQL thread id 1543, OS thread handle 140234567891, query id 98433 localhost app_user
UPDATE order_items SET quantity=5 WHERE order_id=1001
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 234 page no 5 n bits 72 index PRIMARY of table `shopdb`.`orders`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 235 page no 3 n bits 64 index PRIMARY of table `shopdb`.`order_items`
*** WE ROLL BACK TRANSACTION (1)

Bu çıktıyı okumak başlangıçta korkutucu görünebilir ama aslında çok net bir bilgi sunar: hangi transaction hangi kilidi tutuyor, hangisini bekliyor ve hangisi rollback edildi.

Performance Schema ile Detaylı Kilit Analizi

InnoDB Status anlık bir fotoğraf sunar ve sadece son deadlock’u gösterir. Daha sistematik bir analiz için Performance Schema kullanmak gerekir. Önce etkin olduğunu doğrula:

SELECT * FROM performance_schema.setup_instruments 
WHERE name LIKE '%lock%' AND enabled = 'YES';

SELECT * FROM performance_schema.setup_consumers 
WHERE name LIKE '%lock%';

Aktif değilse etkinleştir:

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE name LIKE 'wait/lock/metadata/sql/mdl';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE name = 'events_waits_current';

Şu anda bekleyen kilitleri görmek için şu sorguyu kullan. Bu özellikle “deadlock olmadı ama sorgular çok yavaş” durumlarında hayat kurtarır:

SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    b.trx_started AS blocking_trx_started,
    TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_seconds
FROM 
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Bu sorgu sana şu anda kimin kimi beklediğini, ne kadar süredir beklediğini ve sorguların ne olduğunu gösterir. Prodüksiyonda yavaşlık yaşandığında bu sorguyu çalıştırmak refleks haline gelmelidir.

MariaDB 10.5+ ve MySQL 8.0 için Güncel Sorgular

information_schema.innodb_lock_waits ve innodb_locks tabloları MySQL 8.0’da kaldırıldı. Yerini performance_schema.data_lock_waits ve performance_schema.data_locks aldı. MariaDB 10.5+ için de benzer durum söz konusu. Her iki sürümle çalışan güncel sorgu şöyle olmalı:

-- MySQL 8.0+ ve MariaDB 10.5+ için
SELECT 
    dlw.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    dlw.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
    dl_req.THREAD_ID AS waiting_thread_id,
    dl_req.OBJECT_SCHEMA AS schema_name,
    dl_req.OBJECT_NAME AS table_name,
    dl_req.LOCK_TYPE AS waiting_lock_type,
    dl_req.LOCK_MODE AS waiting_lock_mode,
    dl_req.LOCK_STATUS AS lock_status,
    dl_blk.THREAD_ID AS blocking_thread_id,
    dl_blk.LOCK_TYPE AS blocking_lock_type,
    dl_blk.LOCK_MODE AS blocking_lock_mode
FROM 
    performance_schema.data_lock_waits dlw
    INNER JOIN performance_schema.data_locks dl_req 
        ON dlw.REQUESTING_ENGINE_LOCK_ID = dl_req.ENGINE_LOCK_ID
    INNER JOIN performance_schema.data_locks dl_blk 
        ON dlw.BLOCKING_ENGINE_LOCK_ID = dl_blk.ENGINE_LOCK_ID;

Deadlock Loglamasını Kalıcı Hale Getirmek

Varsayılan olarak InnoDB sadece son deadlock’u hafızada tutar. Prodüksiyonda bu yeterli değildir. innodb_print_all_deadlocks parametresini aktif ederek tüm deadlock’ların error log’a yazılmasını sağla:

# my.cnf veya my.ini dosyasına ekle
[mysqld]
innodb_print_all_deadlocks = 1
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON

Ardından servisi yeniden başlat:

# Restart olmadan da çalışma zamanında aktif edebilirsin
mysql -u root -p -e "SET GLOBAL innodb_print_all_deadlocks = 1;"

Deadlock loglarını error log’dan filtreleyerek izlemek için:

# Error log konumunu öğren
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

# Log'u canlı izle ve sadece deadlock satırlarını göster
tail -f /var/log/mysql/error.log | grep -i "deadlock|TRANSACTION|WAITING FOR"

Gerçek Dünya Senaryosu: E-ticaret Sipariş Sistemi

Diyelim ki bir e-ticaret platformu yönetiyorsun. Sipariş onaylandığında şu iki işlem eşzamanlı tetikleniyor:

İşlem 1 (Ödeme Servisi):

START TRANSACTION;
UPDATE orders SET payment_status = 'paid' WHERE id = 5001;
UPDATE inventory SET reserved = reserved - 1 WHERE product_id = 200;
COMMIT;

İşlem 2 (Stok Servisi):

START TRANSACTION;
UPDATE inventory SET reserved = reserved - 1 WHERE product_id = 200;
UPDATE orders SET fulfillment_status = 'ready' WHERE id = 5001;
COMMIT;

Görüyor musun? İki transaction da aynı tabloları ama ters sırada güncelliyor. Bu klasik bir deadlock reçetesidir.

Tespit için şu sorguyu çalıştır:

-- Transaction geçmişini ve kilit bilgilerini birleştir
SELECT 
    t.trx_id,
    t.trx_state,
    t.trx_started,
    t.trx_wait_started,
    t.trx_mysql_thread_id,
    t.trx_query,
    t.trx_rows_locked,
    t.trx_rows_modified,
    t.trx_lock_structs,
    TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_seconds
FROM 
    information_schema.innodb_trx t
WHERE 
    t.trx_state = 'LOCK WAIT'
ORDER BY 
    trx_age_seconds DESC;

Çözüm: Her iki transaction’ın da tabloları aynı sırada güncellemesini sağla. Uygulama kodunda bu sırayı standartlaştır. orders tablosu her zaman önce, inventory her zaman sonra güncellensin.

Hangi Sorgular En Çok Kilide Neden Oluyor?

Kronik deadlock sorunlarını çözmek için hangi sorgu veya uygulama kullanıcısının en çok kilite yol açtığını bulmak gerekir:

-- Uzun süredir açık transaction'ları bul
SELECT 
    trx.trx_id,
    trx.trx_state,
    trx.trx_started,
    TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS open_seconds,
    trx.trx_mysql_thread_id AS thread_id,
    trx.trx_query AS current_query,
    trx.trx_tables_in_use,
    trx.trx_tables_locked,
    trx.trx_lock_structs,
    proc.user AS db_user,
    proc.host AS client_host,
    proc.db AS database_name,
    proc.command AS thread_command,
    proc.time AS thread_time,
    proc.info AS full_query
FROM 
    information_schema.innodb_trx trx
    LEFT JOIN information_schema.processlist proc 
        ON trx.trx_mysql_thread_id = proc.id
WHERE 
    TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) > 30
ORDER BY 
    open_seconds DESC;

30 saniyeden uzun açık kalan transaction’lar kırmızı alarm işaretidir. Bunlar genellikle uygulama hatalarından (commit edilmeyen transaction’lar) ya da uzun süren batch işlemlerden kaynaklanır.

Sorunlu Thread’i Sonlandırmak

Kritik bir deadlock veya kilit bekleme durumunda, bloke eden thread’i öldürmek gerekebilir:

# Önce processlist'i kontrol et
mysql -u root -p -e "SHOW FULL PROCESSLISTG" | grep -A 5 "Lock|lock"

# Belirli bir thread'i sonlandır
mysql -u root -p -e "KILL 1542;"

# Sadece sorguyu sonlandır, bağlantıyı kesme
mysql -u root -p -e "KILL QUERY 1542;"

Thread’i kill etmeden önce ne yaptığını iyice anla. Yarı kalmış bir transaction veri tutarsızlığına yol açabilir. InnoDB rollback yapacaktır ama bunu bilerek ve kontrollü yapman gerekir.

İndeks Eksikliği ve Deadlock İlişkisi

Deadlock’ların göz ardı edilen en yaygın nedenlerinden biri eksik indekslerdir. InnoDB, WHERE koşulundaki sütunda indeks yoksa tablo taraması (full table scan) yapar ve bu süreçte çok daha fazla satırı kilitler.

-- İndeks kullanımını kontrol et
EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]';

-- Eğer type = 'ALL' görüyorsan tam tablo taraması yapıyor demektir
-- Bu durumda çok fazla satır kilitlenir

-- İndeks ekle
ALTER TABLE orders ADD INDEX idx_customer_email (customer_email);

-- Sonucu doğrula
EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]';
-- type = 'ref' veya 'const' görmelisin

Bir diğer yaygın tuzak ise gap lock (aralık kilidi) sorunudur. InnoDB, REPEATABLE READ izolasyon seviyesinde aralıkları da kilitler. Bunu anlamak için:

-- Mevcut izolasyon seviyesini kontrol et
SELECT @@global.transaction_isolation, @@session.transaction_isolation;

-- READ COMMITTED'a geçmek gap lock sorunlarını azaltır
-- ama phantom read sorununa yol açabilir, dikkatli değerlendir
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- Ya da my.cnf'e ekle
-- transaction_isolation = READ-COMMITTED

Deadlock Önleme Stratejileri

Tespit önemlidir ama asıl hedef deadlock’ları önlemektir. İşte kanıtlanmış yöntemler:

• Tutarlı sıralama: Birden fazla tabloyu güncelleyen her işlemde tabloları her zaman aynı sırada kilitle. Alfabetik ya da ID bazlı sabit bir sıra belirle.

• Transaction süresini kısa tut: Transaction içinde ağ çağrısı, dosya okuma gibi yavaş işlemler yapma. Veriyi önce çek, işle, sonra kısa bir transaction ile yaz.

• SELECT FOR UPDATE kullanımına dikkat et: Gerçekten güncelleme yapacaksan kullan. Sadece okuma amaçlıysa LOCK IN SHARE MODE ya da düz SELECT tercih et.

• Toplu güncellemelerde sıralama: Birden fazla satırı güncellerken satırları ID’ye göre sıralı güncelle. Böylece farklı transaction’lar aynı sırayla ilerlediğinden çakışma azalır.

• innodb_lock_wait_timeout değerini ayarla: Varsayılan 50 saniyedir. Çoğu uygulama için bu çok uzun. 10-15 saniyeye indirmek daha hızlı hata tespiti sağlar.

-- Mevcut değeri kontrol et
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Çalışma zamanında değiştir
SET GLOBAL innodb_lock_wait_timeout = 15;

-- Kalıcı yapmak için my.cnf'e ekle
-- innodb_lock_wait_timeout = 15

Otomatik İzleme Scripti

Prodüksiyonda deadlock’ları sürekli izlemek için basit bir bash scripti işini görür:

#!/bin/bash
# deadlock_monitor.sh
# Cron ile her dakika çalıştır: * * * * * /opt/scripts/deadlock_monitor.sh

DB_USER="monitor_user"
DB_PASS="gizli_sifre"
LOG_FILE="/var/log/mysql/deadlock_monitor.log"
ALERT_EMAIL="[email protected]"
THRESHOLD_SECONDS=60

timestamp=$(date '+%Y-%m-%d %H:%M:%S')

# Uzun süren lock wait'leri kontrol et
long_waits=$(mysql -u "$DB_USER" -p"$DB_PASS" -N -e "
    SELECT COUNT(*) 
    FROM information_schema.innodb_trx 
    WHERE trx_state = 'LOCK WAIT' 
    AND TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) > $THRESHOLD_SECONDS;
" 2>/dev/null)

if [ "$long_waits" -gt 0 ]; then
    echo "[$timestamp] UYARI: $long_waits adet uzun sureli lock wait tespit edildi" >> "$LOG_FILE"
    
    # Detayları al
    details=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "
        SELECT trx_id, trx_mysql_thread_id, 
               TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) AS wait_sec,
               trx_query 
        FROM information_schema.innodb_trx 
        WHERE trx_state = 'LOCK WAIT';" 2>/dev/null)
    
    echo "$details" >> "$LOG_FILE"
    
    # Mail gonder
    echo -e "Sunucu: $(hostname)nZaman: $timestampnn$details" | 
        mail -s "MySQL Deadlock Alarmi" "$ALERT_EMAIL"
fi

# Son deadlock bilgisini logla
mysql -u "$DB_USER" -p"$DB_PASS" -e "SHOW ENGINE INNODB STATUSG" 2>/dev/null | 
    awk '/LATEST DETECTED DEADLOCK/,/--------/' >> "$LOG_FILE"

Bu scripti çalıştırmak için gerekli izinleri ver:

chmod +x /opt/scripts/deadlock_monitor.sh

# Monitor kullanıcısı için minimum izinler
mysql -u root -p -e "
CREATE USER 'monitor_user'@'localhost' IDENTIFIED BY 'gizli_sifre';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'localhost';
GRANT SELECT ON information_schema.* TO 'monitor_user'@'localhost';
FLUSH PRIVILEGES;"

Sys Schema ile Hazır Görünümler

MySQL 5.7+ ve MariaDB 10.6+ ile gelen sys schema, deadlock analizi için harika kısayollar sunar:

-- InnoDB kilit beklentilerini özet olarak göster
SELECT * FROM sys.innodb_lock_waits;

-- En çok kilit üreten tablolar
SELECT 
    object_schema,
    object_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM 
    sys.schema_table_statistics
ORDER BY 
    count_write DESC 
LIMIT 20;

-- Uzun süredir açık olan session'lar
SELECT * FROM sys.session 
WHERE conn_id != CONNECTION_ID() 
AND time > 30
ORDER BY time DESC;

Sonuç

Deadlock yönetimi tek seferlik bir müdahale değil, sürekli bir izleme ve iyileştirme döngüsüdür. Özetlemek gerekirse:

• Tespit için SHOW ENGINE INNODB STATUS ve information_schema / performance_schema sorgularını kullan
• Kronik sorunlar için innodb_print_all_deadlocks parametresini aktif et ve logları düzenli incele
• Önleme için tutarlı kilit sırası, kısa transaction’lar ve doğru indeksler sağla
• İzleme için otomatik scriptler veya Percona Monitoring and Management (PMM) gibi araçlar kullan
• Acil müdahale için KILL komutunu nasıl ve ne zaman kullanacağını bil

İndeks eksikliği, tutarsız kilit sıralaması ve uzun açık kalan transaction’lar deadlock’ların yüzde sekseninden fazlasının nedenidir. Bu üç konuya odaklanırsan çoğu sorunu kökten çözebilirsin. Gerisi zaten kendiliğinden netleşir.