MySQL’de Transaction Yönetimi ve ACID Uyumluluğu

yazar

Veritabanı yönetiminde en çok göz ardı edilen konulardan biri transaction yönetimidir. Özellikle yoğun yazma operasyonlarında ya da birden fazla tablonun aynı anda güncellenmesi gereken senaryolarda, transaction’ları doğru kullanmamak veri bütünlüğünü ciddi şekilde tehdit eder. MySQL ile çalışırken bu konuyu bir kez iyice kavradığınızda, hem uygulamalarınızın güvenilirliği artar hem de “neden yarım kalan veri var?” sorusunu bir daha sormak zorunda kalmazsınız.

ACID Nedir ve Neden Önemlidir?

ACID, veritabanı işlemlerinin güvenilirliğini garanti altına alan dört temel özelliğin baş harflerinden oluşur. Bu kavramı teorik düzeyde bilmek yetmez; pratikte ne anlama geldiğini de anlamak gerekir.

Atomicity (Atomiklik): Bir transaction içindeki tüm işlemler ya tamamen gerçekleşir ya da hiç gerçekleşmez. Banka havalesi düşünün: hesaptan para çekilir ama karşı hesaba yatırılmadan sistem çökerse ne olur? İşte atomicity tam olarak bu sorunu önler.

Consistency (Tutarlılık): Transaction başlamadan önce ve tamamlandıktan sonra veritabanı tutarlı bir durumda olmalıdır. Tanımlı kısıtlar, foreign key ilişkileri ve veri tipleri her zaman geçerli kalmalıdır.

Isolation (İzolasyon): Eş zamanlı çalışan transaction’lar birbirini görmemelidir. Bir transaction’ın henüz commit edilmemiş değişiklikleri başka bir transaction tarafından okunmamalıdır. Bu özellik, isolation level’lar aracılığıyla kontrol edilir.

Durability (Kalıcılık): Bir transaction başarıyla commit edildikten sonra, sistem çökse bile veriler kaybolmamalıdır. MySQL bunu InnoDB’nin write-ahead logging (WAL) mekanizmasıyla sağlar.

MySQL’de varsayılan depolama motoru olan InnoDB ACID uyumludur. MyISAM ise transaction desteği sunmaz; dolayısıyla üretim ortamlarında kritik veriler için InnoDB tercih edilmelidir.

Temel Transaction Komutları

MySQL’de transaction yönetimi birkaç temel komut üzerine kuruludur.

-- Otomatik commit'i kapat
SET autocommit = 0;

-- Ya da açıkça transaction başlat
START TRANSACTION;

-- İşlemleri gerçekleştir
UPDATE hesaplar SET bakiye = bakiye - 1000 WHERE hesap_id = 101;
UPDATE hesaplar SET bakiye = bakiye + 1000 WHERE hesap_id = 202;

-- Her şey yolundaysa kaydet
COMMIT;

-- Hata varsa geri al
ROLLBACK;

MySQL’de autocommit varsayılan olarak açıktır. Bu durumda her SQL ifadesi otomatik olarak commit edilir yani bir transaction başlatmadan çalıştırdığınız her UPDATE veya DELETE anında kalıcı hale gelir. Bunu kapatmak ya da START TRANSACTION kullanmak zorunludur.

Mevcut autocommit durumunu kontrol etmek için:

SHOW VARIABLES LIKE 'autocommit';

-- Oturum bazında kapatmak için
SET SESSION autocommit = 0;

-- Global olarak kapatmak için (my.cnf üzerinden yapılması önerilir)
SET GLOBAL autocommit = 0;

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

Bir e-ticaret platformunda sipariş oluşturma sürecini ele alalım. Müşteri sipariş verdiğinde şu işlemlerin hepsinin başarılı olması gerekir:

  • Stok miktarı düşülmeli
  • Sipariş kaydı oluşturulmalı
  • Müşteri bakiyesinden tutar kesilmeli
  • Sipariş durumu güncellenmeli

Bu dört işlemden herhangi biri başarısız olursa, geriye kalan tüm işlemlerin de geri alınması gerekir.

START TRANSACTION;

-- Stok kontrolü ve güncelleme
SELECT stok_miktari FROM urunler WHERE urun_id = 55 FOR UPDATE;
-- Uygulama katmanında stok > 0 kontrolü yapıldıktan sonra:
UPDATE urunler SET stok_miktari = stok_miktari - 2 WHERE urun_id = 55;

-- Sipariş kaydı oluştur
INSERT INTO siparisler (musteri_id, urun_id, miktar, toplam_tutar, durum)
VALUES (1001, 55, 2, 299.90, 'beklemede');

-- Müşteri bakiyesinden düş
UPDATE musteriler SET bakiye = bakiye - 299.90 WHERE musteri_id = 1001;

-- Hata yoksa commit
COMMIT;

-- Hata durumunda uygulama katmanından ROLLBACK çağrılır

FOR UPDATE ifadesine dikkat edin. Bu, seçilen satırı lock’layarak başka transaction’ların aynı satırı aynı anda güncellemesini engeller. Stok yönetimi gibi race condition’a açık senaryolarda bu kritik öneme sahiptir.

SAVEPOINT Kullanımı

Bazen bir transaction içinde belirli noktalara geri dönmek istersiniz; tüm işlemi geri almak istemezsiniz. İşte bu noktada SAVEPOINT devreye girer.

START TRANSACTION;

INSERT INTO log_kayitlari (mesaj) VALUES ('İşlem başladı');
SAVEPOINT baslangic_noktasi;

UPDATE urunler SET fiyat = fiyat * 1.10 WHERE kategori_id = 3;
SAVEPOINT fiyat_guncelleme;

-- Bu kısım hata verdi diyelim
UPDATE kampanyalar SET aktif = 0 WHERE bitis_tarihi < NOW();

-- Sadece son işlemi geri al, fiyat güncellemesini koru
ROLLBACK TO SAVEPOINT fiyat_guncelleme;

-- Devam et
UPDATE kampanyalar SET aktif = 0 WHERE bitis_tarihi < CURDATE();

COMMIT;

SAVEPOINT’ler özellikle karmaşık iş mantığı içeren uzun transaction’larda çok işe yarar. Ancak aşırıya kaçmamak gerekir; çok sayıda SAVEPOINT, transaction yönetimini karmaşıklaştırır.

İzolasyon Seviyeleri

MySQL’de dört farklı isolation level bulunur ve her biri farklı okuma sorunlarına karşı farklı düzeyde koruma sağlar.

READ UNCOMMITTED: En düşük izolasyon seviyesidir. Bir transaction, başka bir transaction’ın henüz commit etmediği değişiklikleri okuyabilir. Buna “dirty read” denir. Üretimde kullanılmaması önerilir.

READ COMMITTED: Commit edilmiş veriler okunabilir. Dirty read sorunu yoktur ancak “non-repeatable read” sorunu olabilir; yani aynı transaction içinde aynı sorgu farklı sonuçlar döndürebilir.

REPEATABLE READ: MySQL InnoDB’nin varsayılan seviyesidir. Aynı transaction içinde aynı sorgu her zaman aynı sonucu döndürür. Ancak “phantom read” problemi teorik olarak mümkündür; InnoDB bunu gap locking ile büyük ölçüde çözmüştür.

SERIALIZABLE: En yüksek izolasyon seviyesidir. Transaction’lar adeta sırayla çalışıyormuş gibi davranır. Yüksek güvenlik ama düşük performans demektir.

-- Mevcut izolasyon seviyesini görüntüle
SELECT @@transaction_isolation;

-- Oturum bazında değiştir
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Sonraki transaction için değiştir
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Global olarak değiştir
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Hangi seviyeyi seçeceğiniz uygulamanızın ihtiyacına göre değişir. Çoğu OLTP uygulaması için REPEATABLE READ yeterlidir. Raporlama sorgularında READ COMMITTED kullanmak, uzun okuma transaction’larının diğer işlemleri bloklamasını önler.

Deadlock Yönetimi

Deadlock, iki transaction’ın birbirinin beklediği kaynakları lock’laması sonucu oluşan kilitlenme durumudur. MySQL bu durumu otomatik olarak tespit eder ve transaction’lardan birini otomatik rollback yaparak çözer.

-- Deadlock bilgisini görüntüle
SHOW ENGINE INNODB STATUSG

-- Son deadlock detaylarını filtrele
SHOW ENGINE INNODB STATUSG | grep -A 50 "LATEST DETECTED DEADLOCK"

-- Deadlock sayacını izle
SHOW STATUS LIKE 'Innodb_deadlocks';

Deadlock’ları minimize etmek için bazı pratik kurallar vardır:

  • Transaction’ları mümkün olduğunca kısa tutun
  • Birden fazla tablo güncelliyorsanız her zaman aynı sırada güncelleyin
  • Büyük toplu güncellemeler yerine küçük batch’ler kullanın
  • İndeksleri doğru kullanın; tam tablo lock’u yerine satır lock’u kullanıldığından emin olun
-- Uzun süren transaction'ları tespit et
SELECT 
    trx_id,
    trx_started,
    trx_state,
    trx_rows_locked,
    trx_rows_modified,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sure_saniye
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY sure_saniye DESC;

Bu sorgu 30 saniyeden uzun süredir açık olan transaction’ları listeler. Üretim ortamında bu sorguyu periyodik çalıştırmak ya da bir monitoring aracına entegre etmek iyi bir alışkanlıktır.

Uygulama Katmanından Transaction Yönetimi

MySQL transaction’larını genellikle doğrudan SQL konsolundan değil, bir programlama dili üzerinden yönetirsiniz. PHP ile örnek vermek gerekirse:

-- MySQL'de saklı yordam olarak transaction yönetimi örneği
DELIMITER //

CREATE PROCEDURE siparis_olustur(
    IN p_musteri_id INT,
    IN p_urun_id INT,
    IN p_miktar INT
)
BEGIN
    DECLARE v_stok INT;
    DECLARE v_fiyat DECIMAL(10,2);
    DECLARE v_toplam DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sipariş oluşturulamadı';
    END;

    START TRANSACTION;

    -- Stok kontrolü
    SELECT stok_miktari, birim_fiyat 
    INTO v_stok, v_fiyat
    FROM urunler 
    WHERE urun_id = p_urun_id 
    FOR UPDATE;

    IF v_stok < p_miktar THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Yetersiz stok';
    END IF;

    SET v_toplam = v_fiyat * p_miktar;

    UPDATE urunler 
    SET stok_miktari = stok_miktari - p_miktar 
    WHERE urun_id = p_urun_id;

    INSERT INTO siparisler (musteri_id, urun_id, miktar, toplam_tutar)
    VALUES (p_musteri_id, p_urun_id, p_miktar, v_toplam);

    COMMIT;
END //

DELIMITER ;

Saklı yordamlar içinde EXIT HANDLER FOR SQLEXCEPTION kullanmak, herhangi bir SQL hatasında otomatik rollback yapılmasını sağlar. Bu, transaction güvenliğini uygulama katmanından bağımsız hale getirir.

InnoDB Redo Log ve Undo Log

ACID uyumluluğunun arka planında ne olduğunu anlamak, sorunları çözmek için önemlidir.

Redo Log: Transaction commit edildiğinde veriler diske yazılmadan önce redo log’a kaydedilir. Sistem çökerse, MySQL yeniden başlarken redo log’u okuyarak commit edilmiş ama henüz veri dosyasına yazılmamış değişiklikleri uygular. Bu durability özelliğini garanti eder.

Undo Log: Transaction rollback edildiğinde ya da diğer transaction’ların eski veri versiyonlarını okuması gerektiğinde undo log kullanılır. Bu yapı MVCC (Multi-Version Concurrency Control) mekanizmasının temelidir.

-- InnoDB redo log boyutunu kontrol et
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';

-- Undo tablespace bilgisi
SELECT TABLESPACE_NAME, FILE_NAME, TOTAL_EXTENTS, EXTENT_SIZE
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

-- InnoDB durumunu izle
SHOW STATUS LIKE 'Innodb_log_%';

innodb_log_file_size değeri yüksek yazma yükü olan sistemlerde büyük tutulmalıdır. Küçük redo log dosyaları sık checkpoint oluşturulmasına yol açar ve bu da performansı olumsuz etkiler.

Transaction ile İlgili Yaygın Hatalar

Sistem yöneticisi ve geliştirici olarak sıkça karşılaşılan hataları bilmek, sorunları hızlı çözmenizi sağlar.

Uzun açık kalan transaction’lar: Bir transaction başlatıp commit veya rollback yapmadan uygulamanın başka işlemler yapması ya da beklemesi, lock birikmesine yol açar. Bu özellikle web uygulamalarında her HTTP isteğinin bağımsız yönetilmesi gereken durumlarda kritiktir.

Nested transaction yanılgısı: MySQL iç içe transaction’ları desteklemez. START TRANSACTION çağrıldığında mevcut transaction otomatik commit edilir. Bu davranışı bilmeden kod yazmak veri tutarsızlığına yol açabilir.

-- Nested transaction davranışını test et
START TRANSACTION;
INSERT INTO test_tablo VALUES (1, 'ilk kayit');

-- Bu, önceki transaction'ı otomatik commit eder!
START TRANSACTION;
INSERT INTO test_tablo VALUES (2, 'ikinci kayit');
ROLLBACK; -- Sadece ikinci insert geri alınır, birinci kalıcıdır

-- Doğru yaklaşım: SAVEPOINT kullan
START TRANSACTION;
INSERT INTO test_tablo VALUES (1, 'ilk kayit');
SAVEPOINT sp1;
INSERT INTO test_tablo VALUES (2, 'ikinci kayit');
ROLLBACK TO SAVEPOINT sp1; -- Sadece ikinci geri alınır
COMMIT; -- İlk kayit kalıcı olur

DDL ifadelerinin transaction’ı commit etmesi: MySQL’de CREATE TABLE, ALTER TABLE, DROP TABLE gibi DDL ifadeleri mevcut transaction’ı otomatik commit eder. Bu nedenle DDL ifadelerini transaction içinde kullanmaktan kaçınılmalıdır.

Performans İzleme ve Optimizasyon

Transaction yönetiminde performans sorunlarını erken tespit etmek için bazı sorgu ve araçlardan yararlanılabilir.

-- Aktif lock'ları görüntüle
SELECT 
    r.trx_id AS bekleven_trx_id,
    r.trx_mysql_thread_id AS bekleyen_thread,
    r.trx_query AS bekleyen_sorgu,
    b.trx_id AS bloklayan_trx_id,
    b.trx_mysql_thread_id AS bloklayan_thread,
    b.trx_query AS bloklayan_sorgu
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Transaction istatistikleri
SELECT 
    COUNT(*) AS toplam_trx,
    SUM(trx_rows_locked) AS toplam_kilitli_satir,
    SUM(trx_rows_modified) AS toplam_degistirilen_satir,
    MAX(TIMESTAMPDIFF(SECOND, trx_started, NOW())) AS en_uzun_sure
FROM information_schema.innodb_trx;

Bu sorgular monitoring script’lerinize entegre edilebilir. Zabbix veya Prometheus gibi araçlarla bu metrikleri toplamak, proaktif müdahale imkanı sunar.

my.cnf dosyasında transaction performansını etkileyen önemli parametreler:

  • innodb_flush_log_at_trx_commit: Her transaction’da log flush davranışını kontrol eder. Değer 1 tam ACID uyumu sağlar ama daha yavaştır. Değer 2 biraz daha hızlıdır ama sistem çökmesinde son saniyenin transaction’ları kaybolabilir.
  • innodb_lock_wait_timeout: Bir transaction lock beklemek için kaç saniye bekleyeceğini belirler. Varsayılan 50 saniyedir.
  • innodb_deadlock_detect: Deadlock tespitini etkinleştirir veya devre dışı bırakır. Yüksek eş zamanlılık senaryolarında bu mekanizmanın kendisi de kaynak tüketebilir.
  • innodb_rollback_on_timeout: Lock timeout durumunda sadece son ifadeyi mi yoksa tüm transaction’ı mı rollback edeceğini belirler.
-- my.cnf örnek yapılandırması
[mysqld]
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 30
innodb_deadlock_detect = ON
innodb_rollback_on_timeout = ON
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

MariaDB ile Farklılıklar

MariaDB, MySQL ile büyük ölçüde uyumlu olmasına rağmen transaction yönetiminde bazı ince farklar bulunur.

MariaDB 10.2 ile birlikte gelen SEQUENCE nesneleri ve RETURNING clause gibi özellikler transaction’lar içinde kullanılabilir. Ayrıca MariaDB, BEGIN yerine START TRANSACTION kullanımını önerir; her ne kadar ikisi de çalışsa da.

MariaDB’de Galera Cluster kullanıyorsanız transaction yönetimi çok daha kritik hale gelir. Galera, optimistic locking kullanır; yani bir transaction commit aşamasında cluster genelinde çakışma kontrolü yapılır ve çakışma varsa transaction rollback edilir. Bu durum uygulama katmanında retry mekanizmasının kurulmasını zorunlu kılar.

Sonuç

MySQL’de transaction yönetimi ve ACID uyumluluğu, sağlam bir veritabanı altyapısının temel taşlarından biridir. Sadece COMMIT ve ROLLBACK komutlarını bilmek yeterli değildir; isolation level’ları, lock mekanizmalarını, deadlock yönetimini ve InnoDB’nin iç çalışma prensiplerini anlamak gerekmektedir.

Pratikte en sık yapılan hata, transaction’ları çok uzun açık bırakmak ya da hiç kullanmamaktır. Her iki uç da sorunludur. Transaction’ları ihtiyaç duyulan minimum süre açık tutun, hata durumlarını her zaman ele alın ve ROLLBACK’i asla atlamamaya özen gösterin.

Üretim ortamında information_schema.innodb_trx tablosunu düzenli izlemek, lock bekleme sürelerini takip etmek ve deadlock loglarını analiz etmek proaktif bir DBA yaklaşımının gereğidir. Bu metrikleri monitoring sisteminize entegre ettiğinizde, veri tutarlılığını tehdit eden durumları kullanıcılar fark etmeden tespit edip müdahale edebilirsiniz.

Yorum yapın