MySQL Tablo Kilitleme Sorunları: Lock Wait Timeout Hatası Nasıl Çözülür

Tarafındankomut
0 Comments

Prodüksiyonda bir sabah geliyorsun, ekranda kırmızı alarmlar yanıyor, uygulama logları Lock wait timeout exceeded; try restarting transaction hataları fırlatıyor ve kullanıcılar işlem yapamıyor. MySQL’in tablo kilitleme sorunları, deneyimli sysadminlerin bile terini akıtan türden problemlerdir. Bu yazıda bu hatanın ne anlama geldiğini, nasıl tespit edileceğini ve kalıcı olarak nasıl çözüleceğini gerçek dünya senaryolarıyla anlatacağım.

Lock Wait Timeout Nedir?

MySQL’de birden fazla transaction aynı anda çalışırken, bir transaction başka bir transaction tarafından kilitlenmiş bir satıra veya tabloya erişmeye çalışırsa beklemek zorunda kalır. Bu bekleme süresi innodb_lock_wait_timeout parametresiyle belirlenir ve varsayılan değeri 50 saniyedir. Bu süre dolduğunda MySQL şu hatayı fırlatır:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Bu hata genellikle şu senaryolarda ortaya çıkar:

  • Uzun süren bir transaction tabloyu kilitliyor ve diğerleri sıra bekliyor
  • Toplu veri güncelleme işlemleri (bulk UPDATE/DELETE) sırasında
  • ORM kütüphanelerinin gereksiz yere transaction açık tutması
  • Uygulama katmanında hata sonrası rollback yapılmaması
  • Deadlock durumları (iki transaction birbirini bekliyor)

InnoDB engine kullandığınızda satır bazlı kilitleme (row-level locking) devreye girer. Bu teoride çok daha iyi bir seçenektir, ancak yanlış kullanıldığında yine de büyük sorunlara yol açabilir.

Sorunu Anlık Olarak Tespit Etmek

Prodüksiyonda bir sorun yaşandığında ilk yapılacak şey mevcut durumu anlamak. MySQL’e bağlanıp şu sorguları çalıştır:

-- Aktif processleri listele
SHOW FULL PROCESSLIST;

-- InnoDB engine durumunu göster (en önemli komut)
SHOW ENGINE INNODB STATUSG

-- Aktif transactionları göster
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

SHOW ENGINE INNODB STATUS komutu sana çok değerli bilgi verir. Çıktının TRANSACTIONS bölümünde hangi transaction’ların birbirini beklediğini, LATEST DETECTED DEADLOCK bölümünde ise son deadlock detaylarını görebilirsin.

Kilitleri Detaylı İncelemek

MySQL 5.7+ ve 8.0 sürümlerinde performance_schema ve information_schema üzerinden kilitleri çok daha detaylı inceleyebilirsin:

-- Hangi transaction hangi kilidi bekliyor?
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,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_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;

MySQL 8.0’da INNODB_LOCK_WAITS view’i performance_schema.data_lock_waits olarak taşındı:

-- MySQL 8.0 için güncel sorgu
SELECT 
    dlw.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    dlw.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
    dl.OBJECT_NAME AS locked_table,
    dl.LOCK_TYPE,
    dl.LOCK_MODE,
    dl.LOCK_STATUS,
    it.trx_query AS blocking_query,
    it.trx_started AS blocking_started,
    TIMESTAMPDIFF(SECOND, it.trx_started, NOW()) AS blocking_duration_sec
FROM performance_schema.data_lock_waits dlw
JOIN performance_schema.data_locks dl ON dl.ENGINE_LOCK_ID = dlw.BLOCKING_ENGINE_LOCK_ID
JOIN information_schema.INNODB_TRX it ON it.trx_id = dlw.BLOCKING_ENGINE_TRANSACTION_ID;

Bu sorgu sana tam olarak hangi thread’in hangi tabloyu ne kadar süredir kilitlediğini gösterir. Prodüksiyonda bir krizde bu sorgunun çıktısı paha biçilemez.

Sorunlu Thread’i Öldürmek

Kilitleyen thread’i tespit ettikten sonra onu öldürmek gerekebilir. Bunu yaparken dikkatli ol, yanlış thread’i kill edersen başka sorunlar çıkabilir:

# MySQL'e bağlan ve kilitleyen thread'i bul
mysql -u root -p -e "SHOW FULL PROCESSLISTG" | grep -A 10 "State: Waiting"

# Belirli bir thread'i öldür (thread_id'yi değiştir)
mysql -u root -p -e "KILL 1234;"

# Sadece sorguyu öldür, transaction'ı değil
mysql -u root -p -e "KILL QUERY 1234;"

Burada önemli bir ayrım var: KILL 1234 hem sorguyu hem de bağlantıyı sonlandırır. KILL QUERY 1234 ise sadece çalışan sorguyu durdurur, bağlantı açık kalır. Genellikle önce KILL QUERY dene, eğer transaction açık kalmaya devam ederse o zaman KILL kullan.

Gerçek Dünya Senaryosu 1: Yarım Kalan Migration

Bir e-ticaret projesinde şöyle bir durum yaşadım. Geliştirici yeni bir kolon eklemek için migration çalıştırmış, ancak ALTER TABLE komutu büyük bir tabloda (15 milyon satır) takılmış. Migration scriptini durdurmadan tarayıcıyı kapatan geliştirici farkında olmadan o transaction’ı açık bırakmış. 2 saat boyunca hiç kimse o tabloya yazamaz hale gelmiş.

-- Uzun süren ALTER TABLE veya DDL operasyonlarını tespit et
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 200) AS query_snippet
FROM information_schema.PROCESSLIST
WHERE time > 300  -- 5 dakikadan uzun çalışanlar
    AND command != 'Sleep'
ORDER BY time DESC;

Bu tür durumlarda pt-online-schema-change veya MySQL 8.0’ın ALGORITHM=INSTANT özelliğini kullanmak çok daha güvenli.

Gerçek Dünya Senaryosu 2: ORM’nin Açık Bıraktığı Transaction’lar

Laravel, Django veya Rails kullanan projelerde sık karşılaşılan bir durum: ORM bir transaction açıyor, bir exception fırlatılıyor, ama rollback çağrılmıyor. Bu sayede onlarca “zombie” transaction birikip kilitleri tutmaya devam ediyor.

# Zombie transaction'ları tespit et
mysql -u root -p << 'EOF'
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS duration_minutes,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
    AND trx_query IS NULL  -- Sorgu çalışmıyor ama transaction açık
    AND TIMESTAMPDIFF(MINUTE, trx_started, NOW()) > 5;
EOF

Sorgu çalışmayan ama trx_state = 'RUNNING' olan ve trx_query IS NULL olan transaction’lar tam anlamıyla zombie. Bunlar genellikle uygulama katmanındaki bug’lardan kaynaklanır.

MySQL Konfigürasyonunu Optimize Etmek

Uzun vadeli çözüm için MySQL konfigürasyonunda bazı değişiklikler yapman gerekir:

# /etc/mysql/mysql.conf.d/mysqld.cnf veya /etc/my.cnf dosyasını düzenle
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Şu parametreleri ayarla:

[mysqld]
# Lock wait timeout - varsayılan 50, prodüksiyonda 30 daha uygun
innodb_lock_wait_timeout = 30

# Deadlock tespiti - aktif olmalı
innodb_deadlock_detect = ON

# Her deadlock bilgisini error log'a yaz
innodb_print_all_deadlocks = 1

# Transaction izolasyon seviyesi
transaction_isolation = READ-COMMITTED

# Uzun sorguları logla
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Lock bilgilerini performance schema'ya kaydet
performance_schema = ON

innodb_lock_wait_timeout: Saniye cinsinden bekleme süresi. 50 saniye genellikle çok yüksek, uygulamanın kullanıcıya 50 saniye bekletmesi kabul edilemez.

innodb_deadlock_detect: MySQL’in deadlock’ları otomatik olarak tespit edip çözmesini sağlar. Kapalıysa sadece timeout ile çözülür.

innodb_print_all_deadlocks: Her deadlock’ı error log’a yazar. Debugging için çok değerli.

transaction_isolation = READ-COMMITTED: Bu izolasyon seviyesi gap lock kullanımını azaltır ve lock çakışmalarını minimize eder.

Değişiklikleri uyguladıktan sonra:

# MySQL'i yeniden başlat
sudo systemctl restart mysql

# Değişiklikleri doğrula
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';"

Monitoring Script’i Yazmak

Prodüksiyonda lock sorunlarını proaktif olarak izlemek için basit bir monitoring script’i işe yarar:

#!/bin/bash
# /usr/local/bin/mysql_lock_monitor.sh

MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
MYSQL_HOST="localhost"
ALERT_THRESHOLD=30  # saniye
LOG_FILE="/var/log/mysql_lock_monitor.log"
ALERT_EMAIL="[email protected]"

check_locks() {
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    
    # Uzun süren lock wait'leri kontrol et
    local long_waits=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" 
        --skip-column-names -e "
        SELECT COUNT(*) 
        FROM information_schema.INNODB_TRX 
        WHERE trx_state = 'LOCK WAIT' 
            AND TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) > $ALERT_THRESHOLD;
    " 2>/dev/null)
    
    if [ "$long_waits" -gt 0 ]; then
        echo "[$timestamp] UYARI: $long_waits adet transaction ${ALERT_THRESHOLD}s+ beklemiş durumda" | tee -a "$LOG_FILE"
        
        # Detaylı bilgiyi logla
        mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" -e "
            SELECT 
                trx_id,
                trx_state,
                trx_mysql_thread_id AS thread_id,
                TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) AS wait_sec,
                LEFT(trx_query, 100) AS query
            FROM information_schema.INNODB_TRX
            WHERE trx_state = 'LOCK WAIT'
                AND TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) > $ALERT_THRESHOLD;
        " 2>/dev/null | tee -a "$LOG_FILE"
        
        # E-posta gönder (mail komutu kurulu olmalı)
        echo "MySQL lock wait uyarısı: $long_waits transaction ${ALERT_THRESHOLD}s+ beklemiş" | 
            mail -s "ALARM: MySQL Lock Wait" "$ALERT_EMAIL"
    fi
}

check_locks
# Script'i çalıştırılabilir yap ve cron'a ekle
chmod +x /usr/local/bin/mysql_lock_monitor.sh

# Her dakika çalıştır
echo "* * * * * root /usr/local/bin/mysql_lock_monitor.sh" >> /etc/cron.d/mysql_monitor

Index Eksikliğinin Lock Sorunlarına Etkisi

Pek çok kişinin gözden kaçırdığı bir nokta: Index eksikliği lock sorunlarını katlar. Düşün, bir UPDATE sorgusu WHERE koşulunda index’lenmiş bir kolonu kullanmıyorsa, InnoDB tüm tabloyu taramak ve potansiyel olarak çok daha fazla satırı kilitlemek zorunda kalır.

-- Index kullanımını kontrol et
EXPLAIN SELECT * FROM siparisler WHERE durum = 'beklemede' AND guncelleme_tarihi < '2024-01-01';

-- Full table scan yapan sorguları tespit et
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,
    SUM_NO_INDEX_USED,
    SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY COUNT_STAR DESC
LIMIT 20;

Eğer bir UPDATE veya DELETE sorgusu full table scan yapıyorsa, bu sorgu çalışırken teorik olarak tablodaki çok sayıda satırı kilitleyebilir. İlgili kolonlara index eklemek bazen lock sorunlarını tamamen ortadan kaldırır.

-- Eksik index'leri ekle
ALTER TABLE siparisler ADD INDEX idx_durum_tarih (durum, guncelleme_tarihi);

-- Mevcut index kullanımını kontrol et
SELECT 
    table_name,
    index_name,
    column_name,
    seq_in_index
FROM information_schema.STATISTICS
WHERE table_schema = 'veritabani_adi'
ORDER BY table_name, index_name, seq_in_index;

Deadlock Loglarını Analiz Etmek

innodb_print_all_deadlocks = 1 aktifse, deadlock bilgileri MySQL error log’una yazılır. Bu logları analiz etmek çok önemli:

# MySQL error log'unu filtrele
grep -A 30 "LATEST DETECTED DEADLOCK" /var/log/mysql/error.log | tail -100

# Son 24 saatteki deadlock sayısını kontrol et
grep -c "TRANSACTION.*DEADLOCK" /var/log/mysql/error.log

# pt-deadlock-logger ile sürekli izle (Percona Toolkit gerekli)
pt-deadlock-logger --user=root --password=sifre --host=localhost 
    --dest=deadlock_log_db.deadlocks 
    --interval=10 
    --daemonize 
    --log=/var/log/pt-deadlock-logger.log

Deadlock loglarında genellikle şu pattern’i görürsün: Transaction A, Row X’i kilitleyip Row Y’yi bekliyor. Transaction B ise Row Y’yi kilitleyip Row X’i bekliyor. Bu durumda MySQL birisini kurban seçip rollback yapar.

Çözüm genellikle transaction’ların tabloları hep aynı sırayla kilitlemesini sağlamaktır. Örneğin her zaman kullanici tablosunu güncelledikten sonra siparis tablosunu güncelliyorsan, ters sırada çalışan başka bir kod parçacığı varsa onu da aynı sıraya çek.

Uygulama Katmanında Alınacak Önlemler

Sysadmin olarak MySQL tarafında yapabileceklerinin sınırı var. Asıl çözümün büyük bölümü uygulama geliştirme pratiklerinde:

  • Transaction’ları kısa tut: Transaction başladıktan sonra network çağrısı, dosya okuma gibi I/O işlemleri yapma. Bu işlemleri transaction dışında gerçekleştir.
  • SELECT FOR UPDATE’i dikkatli kullan: Gerçekten lock’a ihtiyacın var mı diye sorgula. Çoğu durumda optimistic locking daha iyi çalışır.
  • Bulk işlemleri parçalara böl: 1 milyon satırı tek UPDATE’te değiştirmek yerine 1000’erlik batch’lere böl.
  • Retry logic ekle: Lock wait timeout aldığında uygulama otomatik olarak retry yapmalı.
  • Connection pool boyutunu optimize et: Çok fazla açık connection, çok fazla potansiyel transaction demektir.

Percona Toolkit ile Detaylı Analiz

Eğer ciddi bir MySQL ortamı yönetiyorsan Percona Toolkit muhtemelen zaten kurulu olmalı:

# Percona Toolkit kur
sudo apt-get install percona-toolkit  # Debian/Ubuntu
sudo yum install percona-toolkit      # RHEL/CentOS

# pt-query-digest ile slow log'u analiz et
pt-query-digest /var/log/mysql/slow.log 
    --filter '$event->{Lock_time} > 1' 
    --report-format profile,query_report 
    --limit 10

# pt-stalk ile sorun anında otomatik veri topla
pt-stalk --function=status --variable=Threads_running 
    --threshold=50 
    --mysql-user=root 
    --mysql-password=sifre 
    --dest=/var/lib/pt-stalk 
    --daemonize

pt-stalk özellikle değerli bir araç: Belirlediğin bir eşiği aştığında (örneğin çok fazla aktif thread) otomatik olarak SHOW ENGINE INNODB STATUS, processlist ve diğer diagnostic bilgileri toplayıp kaydeder. Sorun zaten geçtikten sonra ne olduğunu anlamaya çalışırken bu kayıtlar hayat kurtarır.

Önleyici Tedbirler Özeti

Lock wait timeout sorunlarını kalıcı olarak minimize etmek için yapman gerekenleri özetleyecek olursam:

  • innodb_lock_wait_timeout değerini uygulamanın kabul edebileceği makul bir süreye (10-30 saniye) ayarla
  • innodb_print_all_deadlocks = 1 ile tüm deadlock’ları logla
  • transaction_isolation = READ-COMMITTED ile gereksiz gap lock’ları önle
  • Tüm WHERE koşullarında kullanılan kolonların index’li olduğundan emin ol
  • Uzun süren işlemler için pt-online-schema-change kullan
  • Monitoring script’i ile proaktif uyarı al
  • Uygulama ekibiyle birlikte transaction süreleri ve retry logic konusunda çalış
  • Periyodik olarak SHOW ENGINE INNODB STATUS çıktısını incele, sorun çıkmadan önce erken uyarı işaretlerini yakala

Sonuç

Lock wait timeout hataları korkutucu görünse de sistematik bir yaklaşımla hem tespit etmek hem de kalıcı olarak çözmek mümkün. En önemli adım doğru araçları kullanmak: SHOW ENGINE INNODB STATUS, information_schema.INNODB_TRX ve performance_schema sana ihtiyaç duyduğun tüm bilgiyi verir.

Prodüksiyonda kriz anında önce kilitleyen thread’i tespit et, gerekirse kill et, servisi ayağa kaldır. Sonra sakin bir ortamda kök nedeni analiz et: Uzun transaction mı, eksik index mi, uygulama bug’ı mı? Kalıcı çözüm genellikle hem MySQL konfigürasyonu hem de uygulama kod değişikliği gerektirir.

Son olarak şunu vurgulamak isterim: Bu sorunlar çoğunlukla tek seferlik değildir. Bir kez yaşadıysan, monitoring eklemezsen, kök nedeni çözmezsen tekrar yaşarsın. Monitoring script’ini kur, slow query log’unu etkinleştir, deadlock loglarını izle. Proaktif olmak, gece 3’te alarmla uyanmaktan her zaman daha iyidir.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir