MySQL Deadlock ve Tablo Kilitleme Sorunları Çözümü

Veritabanı yönetiminde en sinir bozucu anlardan biri, gece yarısı gelen bir alarm ve ardından gelen “uygulama dondu” telefonu. Çoğu zaman bunun arkasında tablo kilitleme sorunları veya deadlock’lar yatar. MySQL ve MariaDB üzerinde çalışan sistemlerde bu problem o kadar yaygın ki, deneyimli bir DBA’nın ilk bakacağı yerlerden biri her zaman kilit durumudur. Bu yazıda hem teorik altyapıyı hem de gerçek hayatta karşılaştığım senaryoları ele alacağım.

Kilit Mekanizması Nasıl Çalışır?

MySQL/MariaDB’de kilitleme birkaç farklı katmanda gerçekleşir. InnoDB storage engine kullandığınızda row-level locking devreye girer, bu da tablo seviyesi kilitlemeye göre çok daha granüler bir yapı sunar. Ancak bu granülerlik, beraberinde deadlock riskini de artırır.

Kilit türleri şunlardır:

Shared Lock (S Lock): Okuma işlemleri için alınır. Birden fazla transaction aynı anda shared lock alabilir.
Exclusive Lock (X Lock): Yazma işlemleri için alınır. Sadece bir transaction tutabilir, diğerleri bekler.
Intention Shared (IS): Tablonun bir satırına shared lock almak niyetinde olduğunu belirtir.
Intention Exclusive (IX): Tablonun bir satırına exclusive lock almak niyetinde olduğunu belirtir.
Gap Lock: Bir index aralığına kilit koyar, phantom read’leri önler.
Next-Key Lock: Row lock ile gap lock kombinasyonu, InnoDB’nin default davranışı.

MyISAM kullanıyorsanız işler biraz farklı. MyISAM sadece tablo seviyesi kilit destekler, bu yüzden bir yazma işlemi başladığında tüm tablo kilitlenir. Yüksek trafikli sistemlerde MyISAM kullanmak bu yüzden ciddi performans sorunlarına yol açar.

Mevcut Kilitleri Tespit Etmek

Sorun yaşandığında ilk yapmanız gereken şey mevcut durumu anlamak. Bunun için birkaç farklı sorgu kullanabilirsiniz.

# MySQL'e bağlanıp kilit durumunu kontrol et
mysql -u root -p -e "SHOW ENGINE INNODB STATUSG" | grep -A 30 "TRANSACTIONS"

Bu komut çok uzun bir çıktı verir. Sadece lock bilgilerine bakmak istiyorsanız information_schema tablolarını kullanmak çok daha temiz:

-- Aktif kilitleri listele
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
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;

MariaDB 10.x ve MySQL 8.x’te bu sorgular biraz farklı çalışır. MySQL 8’de performance_schema kullanmak daha doğru:

-- MySQL 8 için performance_schema ile kilit analizi
SELECT 
    psce.sql_text,
    psew.object_name,
    psew.index_name,
    psew.object_type,
    psew.lock_type,
    psew.lock_status,
    ps.processlist_id,
    ps.processlist_time
FROM performance_schema.events_statements_current psce
JOIN performance_schema.threads ps ON ps.thread_id = psce.thread_id
JOIN performance_schema.data_lock_waits dlw ON dlw.requesting_thread_id = ps.thread_id
JOIN performance_schema.data_locks psew ON psew.thread_id = dlw.blocking_thread_id
ORDER BY psew.lock_status, psew.lock_type;

Uzun süredir bekleyen sorguları bulmak için ise PROCESSLIST oldukça kullanışlı:

-- 30 saniyeden uzun süredir çalışan sorguları bul
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as QUERY
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND TIME > 30
ORDER BY TIME DESC;

Gerçek Hayat Senaryosu: E-Ticaret Sitesinde Deadlock

Bir e-ticaret projesinde stok yönetimi modülü ciddi deadlock sorunları yaşıyordu. Senaryo şuydu: Kampanya başladığında yüzlerce kullanıcı aynı anda aynı ürünleri sepete eklemeye çalışıyordu.

Uygulama tarafında iki farklı transaction akışı vardı:

Transaction A sırası: orders tablosuna yaz, ardından stock tablosunu güncelle. Transaction B sırası: stock tablosunu kontrol et, ardından orders tablosuna yaz.

Bu iki farklı sıralama, klasik deadlock durumunu oluşturuyordu. Transaction A, orders’ı kilitleyip stock’u bekliyordu. Transaction B ise stock’u kilitleyip orders’ı bekliyordu. Sistem çıkmaza giriyordu.

MySQL bu durumu otomatik olarak tespit eder ve bir transaction’ı rollback eder. Ancak hangi transaction’ın kurban seçileceğini siz belirleyebilirsiniz:

-- Deadlock kurban seçimini ayarla (düşük değer = kolay kurban)
SET innodb_deadlock_detect = ON;

-- Transaction önceliğini düşür (bu transaction daha kolay kurban seçilir)
SET innodb_lock_wait_timeout = 5;

-- Transaction başına deadlock ağırlığı
SET @deadlock_weight = 1; -- düşük değer = daha kolay kurban

Çözüm olarak transaction sıralamasını standardize ettik. Tüm kod tabanında önce stock, sonra orders güncellenecek şekilde refactor yapıldı. Bu değişiklik deadlock sayısını sıfıra indirdi.

Deadlock Loglarını Analiz Etmek

MySQL error log’unda ve InnoDB status çıktısında deadlock bilgisi bulunur. Bunu düzenli izlemek için bir script yazabilirsiniz:

#!/bin/bash
# deadlock_monitor.sh - Deadlock tespiti ve loglama

MYSQL_USER="monitor_user"
MYSQL_PASS="gizli_sifre"
LOG_FILE="/var/log/mysql/deadlock_monitor.log"
ALERT_THRESHOLD=5

# Son 1 dakikadaki deadlock sayısını al
DEADLOCK_COUNT=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "
SELECT COUNT(*) 
FROM information_schema.INNODB_METRICS 
WHERE NAME = 'lock_deadlocks' 
AND ENABLED = 1;" 2>/dev/null)

# InnoDB status'dan deadlock bilgisi çek
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW ENGINE INNODB STATUSG" 2>/dev/null | 
    awk '/LATEST DETECTED DEADLOCK/,/WE ROLL BACK/' >> ${LOG_FILE}

# Eşik aşıldıysa alarm gönder
if [ "${DEADLOCK_COUNT}" -gt "${ALERT_THRESHOLD}" ]; then
    echo "UYARI: Son dakikada ${DEADLOCK_COUNT} deadlock tespit edildi!" | 
    mail -s "MySQL Deadlock Alarmı - $(hostname)" [email protected]
fi

echo "$(date): Deadlock kontrolü tamamlandı. Sayı: ${DEADLOCK_COUNT}" >> ${LOG_FILE}

Bu scripti cron’a ekleyerek her dakika çalıştırabilirsiniz:

# crontab -e ile ekle
* * * * * /usr/local/bin/deadlock_monitor.sh

Tablo Kilidi Sorunları: LOCK TABLES ve MyISAM

MyISAM kullanan eski uygulamalarda explicit tablo kilitleri sık görülür. Bir uygulama LOCK TABLES çekip düzgün unlock yapmadan çökerse, kilit askıda kalabilir.

Mevcut tablo kilitlerini görmek için:

-- Tablo kilitlerini göster
SHOW OPEN TABLES WHERE In_use > 0;

-- Tüm processlist'i göster
SHOW FULL PROCESSLIST;

-- Sorunlu process'i sonlandır (ID'yi processlist'ten al)
KILL 12345;

Bazen KILL komutu da yeterli olmaz. Transaction tamamen temizlenmeden önce birkaç saniye beklemek gerekebilir. Bu tür durumlarda şunu kullanabilirsiniz:

-- Kilit wait timeout'unu geçici olarak düşür
SET GLOBAL innodb_lock_wait_timeout = 10;

-- Sadece bu session için de ayarlayabilirsiniz
SET SESSION innodb_lock_wait_timeout = 5;

InnoDB Optimizasyon Parametreleri

Kilitleme sorunlarını minimize etmek için my.cnf üzerinde yapabileceğiniz ayarlar oldukça etkilidir:

# /etc/mysql/my.cnf veya /etc/my.cnf dosyasına eklenecekler

[mysqld]
# Deadlock tespitini aktif tut
innodb_deadlock_detect = ON

# Kilit bekleme timeout'u (saniye)
innodb_lock_wait_timeout = 30

# Row lock yerine daha az gap lock kullanmak için
# REPEATABLE READ yerine READ COMMITTED izolasyon seviyesi
# (dikkatli kullanın, uygulama davranışını etkileyebilir)
transaction_isolation = READ-COMMITTED

# InnoDB buffer pool boyutu - büyük buffer = daha az disk I/O = daha hızlı lock release
innodb_buffer_pool_size = 4G

# Concurrent I/O thread sayısı
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Her tablonun kendi dosyasında olması yönetimi kolaylaştırır
innodb_file_per_table = ON

Bu değişiklikleri yaptıktan sonra MySQL’i yeniden başlatmak gerekir:

# Servisi yeniden başlat
systemctl restart mysql

# Veya MariaDB için
systemctl restart mariadb

# Config'in doğru yüklendiğini doğrula
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'transaction_isolation';"

Index Eksikliği ve Kilit Yayılması

Deadlock ve kilit sorunlarının en sık görülen nedenlerinden biri index eksikliğidir. Index olmayan bir kolon üzerinden WHERE koşuluyla güncelleme yapıldığında, InnoDB sadece ilgili satırları değil tüm tabloyu veya büyük bir range’i kilitlemek zorunda kalır.

-- Kötü örnek: Indexsiz kolona WHERE
UPDATE orders SET status = 'shipped' WHERE customer_name = 'Ali Yilmaz';

-- Bu sorgu explain ile analiz edildiğinde full table scan göreceksiniz
EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_name = 'Ali Yilmaz'G

-- Çözüm: Index ekle
ALTER TABLE orders ADD INDEX idx_customer_name (customer_name);

-- Veya daha iyi: customer_id ile çalış (zaten primary key veya indexed)
UPDATE orders SET status = 'shipped' WHERE customer_id = 12345;

Hangi sorgularınızın index kullanmadığını bulmak için slow query log’u aktif etmek çok faydalı:

# my.cnf'e ekle
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

# Slow query logunu analiz et
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Veya pt-query-digest ile (Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log | head -100

SELECT FOR UPDATE Kullanımı

Optimistic locking yerine pessimistic locking tercih ettiğinizde SELECT FOR UPDATE kullanırsınız. Bu doğru kullanılmazsa ciddi sorunlara yol açar.

-- Kötü kullanım: Uzun süre kilit tutmak
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Burada uygulama tarafında çok uzun işlem yapılıyor
-- Kilit dakikalarca tutuluyor
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- İyi kullanım: Transaction'ı kısa tut
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- Sadece gerekli kontrolü yap
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- Uygulama mantığını transaction dışına taşı

Bazen SELECT FOR UPDATE yerine SELECT FOR SHARE (MySQL 8) veya LOCK IN SHARE MODE kullanmak daha uygun olabilir. Okuma işlemi yapıp sadece veriyi başkasının değiştirmesini engellemek istiyorsanız exclusive lock’a gerek yok.

Prometheus ve Grafana ile Kilit Metrikleri İzleme

Production ortamda reactive değil proactive olmak gerekir. mysqld_exporter ile Prometheus’a kilit metriklerini çekip Grafana’da görselleştirebilirsiniz:

# mysqld_exporter kurulumu (Debian/Ubuntu)
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.15.0.linux-amd64.tar.gz
mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

# MySQL'de monitoring kullanıcısı oluştur
mysql -u root -p -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'guclu_sifre' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;"

# Credentials dosyası oluştur
cat > /etc/mysql/mysqld_exporter.cnf << 'EOF'
[client]
user=exporter
password=guclu_sifre
host=localhost
port=3306
EOF

# Systemd service dosyası
cat > /etc/systemd/system/mysqld_exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
User=prometheus
ExecStart=/usr/local/bin/mysqld_exporter 
  --config.my-cnf=/etc/mysql/mysqld_exporter.cnf 
  --collect.info_schema.innodb_metrics 
  --collect.info_schema.processlist 
  --collect.engine_innodb_status
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable --now mysqld_exporter

Grafana’da izlemeniz gereken başlıca metrikler:

mysql_global_status_innodb_row_lock_waits: Kilit bekleme sayısı
mysql_global_status_innodb_row_lock_time_avg: Ortalama kilit bekleme süresi
mysql_global_status_innodb_deadlocks: Toplam deadlock sayısı
mysql_info_schema_processlist_seconds: Uzun çalışan sorgular
mysql_global_status_threads_running: Aktif thread sayısı

Uygulama Seviyesinde Retry Mekanizması

Deadlock’lar tamamen engellenemez, bu yüzden uygulama tarafında retry mantığı şart. Örnek bir Python implementasyonu:

import mysql.connector
import time
import logging

def execute_with_retry(connection, query, params=None, max_retries=3, retry_delay=0.5):
    """
    Deadlock durumunda otomatik retry yapan sorgu çalıştırıcı
    """
    for attempt in range(max_retries):
        try:
            cursor = connection.cursor()
            cursor.execute(query, params)
            connection.commit()
            return cursor.fetchall()
            
        except mysql.connector.errors.DatabaseError as e:
            # MySQL error code 1213: Deadlock found
            # MySQL error code 1205: Lock wait timeout exceeded
            if e.errno in (1213, 1205) and attempt < max_retries - 1:
                connection.rollback()
                wait_time = retry_delay * (2 ** attempt)  # Exponential backoff
                logging.warning(
                    f"Deadlock/Lock timeout tespit edildi. "
                    f"Deneme {attempt + 1}/{max_retries}. "
                    f"{wait_time}s sonra tekrar deneniyor."
                )
                time.sleep(wait_time)
                continue
            else:
                connection.rollback()
                logging.error(f"Sorgu başarısız oldu: {e}")
                raise
        finally:
            cursor.close()

Pratik Deadlock Önleme Kuralları

Yıllar içinde edindiğim deneyimden çıkardığım temel kuralları şöyle sıralayabilirim:

Tutarlı sıralama: Her zaman aynı sırada tablolara ve satırlara erişin. Transaction A önce users, sonra orders; Transaction B de aynı sırayla erişmeli.
Transaction’ları kısa tut: Bir transaction ne kadar uzun süre açık kalırsa, kilit çakışma ihtimali o kadar artar. Kullanıcı inputu bekliyorsanız, transaction’ı kapatın.
Doğru izolasyon seviyesi: READ COMMITTED çoğu uygulama için yeterlidir ve gap lock kullanımını azaltır.
Index her şeydir: Yazma sorgularında mutlaka uygun index olsun. Indexsiz sorgular tablo taraması yapar ve geniş lock alır.
Büyük batch işlemlerini böl: 100.000 satır güncellemek yerine 1.000’er satırlık parçalara bölün, aralarında kısa sleep ekleyin.
SELECT FOR UPDATE’i minimize edin: Gerçekten gerektiğinde kullanın, varsayılan olarak değil.
Deadlock loglarını düzenli inceleyin: Her deadlock bir hikaye anlatır. Örüntüyü görmek için logları takip edin.

Sonuç

Tablo kilitleme ve deadlock sorunları, MySQL/MariaDB yönetiminin ayrılmaz bir parçası. Tamamen ortadan kaldırmak mümkün olmasa da doğru yaklaşımlarla minimize edilebilir. İlk adım her zaman mevcut durumu doğru anlamak: SHOW ENGINE INNODB STATUS, PROCESSLIST ve information_schema tabloları bize çok şey söyler.

Monitoring tarafını ihmal etmeyin. Prodüksiyon’da kör uçmak yerine Prometheus/Grafana ile metrikleri izlemek, sorunu alarm geldiğinde değil daha erken tespit etmenizi sağlar. Uygulama tarafında retry mekanizması kurmak ise kaçınılmaz deadlock’lara karşı en iyi savunmadır.

En önemli nokta ise proaktif olmak. Index analizleri, slow query log takibi ve düzenli EXPLAIN kullanımı, sorun oluşmadan önce potansiyel kilit problemlerini ortaya koyar. Gece yarısı telefonu almak yerine gündüz sakin kafayla bu analizleri yapmak, hem sizin hem de kullanıcılarınızın sinirlerini korur.

MySQL ve MariaDB’de Tablo Kilitleme Sorunları ve Deadlock Çözümleri