Salin dan Bagikan
MySQL Transactions ACID
MySQL Transactions: ACID Properties dengan BEGIN, COMMIT, dan ROLLBACK - Panduan lengkap MySQL transactions dengan ACID properties, BEGIN, COMMIT, ROLLBACK, savepoints, dan …

MySQL Transactions: ACID Properties dengan BEGIN, COMMIT, dan ROLLBACK

MySQL Transactions: ACID Properties dengan BEGIN, COMMIT, dan ROLLBACK

Transactions adalah konsep fundamental untuk data integrity dalam database operations. Setelah mengimplementasikan transaction management untuk sistem finansial, saya memahami critical importance dari proper transaction handling.

ACID Properties

1. Atomicity

Semua operations dalam transaction all-or-nothing. Jika satu gagal, semua dibatalkan.

2. Consistency

Database selalu dalam valid state. Constraints dan rules selalu enforced.

3. Isolation

Concurrent transactions tidak mengganggu satu sama lain. Seolah-olah sequential.

4. Durability

Setelah COMMIT, data persist meskipun system crash.

Basic Transaction Syntax

1. Simple Transaction

-- Start transaction
BEGIN;
-- atau: START TRANSACTION;

-- Operations
INSERT INTO accounts (user_id, balance) VALUES (1, 1000.00);
INSERT INTO accounts (user_id, balance) VALUES (2, 500.00);

-- Commit
COMMIT;

2. Rollback pada Error

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Jika semua berhasil
COMMIT;

-- Jika ada error
ROLLBACK;

3. Complete Transfer Example

-- Transfer 100 dari account 1 ke account 2
BEGIN;

-- Check balance
SELECT balance INTO @sender_balance 
FROM accounts WHERE id = 1 FOR UPDATE;

IF @sender_balance < 100 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Insufficient balance';
END IF;

-- Debit sender
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1;

-- Credit receiver
UPDATE accounts 
SET balance = balance + 100 
WHERE id = 2;

-- Record transaction
INSERT INTO transactions (from_account, to_account, amount, created_at)
VALUES (1, 2, 100, NOW());

COMMIT;

Savepoints

1. Using Savepoints

BEGIN;

-- Step 1
INSERT INTO orders (user_id, total) VALUES (1, 500.00);
SET @order_id = LAST_INSERT_ID();
SAVEPOINT after_order_created;

-- Step 2
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (@order_id, 101, 2);

-- Jika perlu undo sampai savepoint
ROLLBACK TO SAVEPOINT after_order_created;

-- Atau commit semua
COMMIT;

Transaction Isolation Levels

1. READ UNCOMMITTED

-- Bisa read data yang belum di-commit (dirty read)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Lowest isolation, highest performance
-- Hanya untuk read-only scenarios di mana dirty reads acceptable

2. READ COMMITTED

-- Hanya read data yang sudah di-commit
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Default untuk banyak databases (bukan MySQL)
-- No dirty reads, tapi bisa non-repeatable reads

3. REPEATABLE READ (MySQL Default)

-- Default isolation level di MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Same read dalam transaction selalu return same data
-- Phantom reads masih possible

4. SERIALIZABLE

-- Highest isolation
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Complete isolation, no concurrency issues
-- Lowest performance

5. Set Isolation Level

-- Set untuk session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set untuk transaction specific
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... operations
COMMIT;

Locking dalam Transactions

1. FOR UPDATE

BEGIN;

-- Lock rows untuk update
SELECT * FROM accounts 
WHERE id = 1 
FOR UPDATE;

-- Sekarang rows ter-lock, lainnya tidak bisa modify sampai commit
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

2. LOCK IN SHARE MODE

BEGIN;

-- Read lock (shared lock)
SELECT * FROM inventory 
WHERE product_id = 101 
LOCK IN SHARE MODE;

-- Bisa read, tidak bisa write sampai lock released
SELECT stock FROM inventory WHERE product_id = 101;

-- Attempt write akan wait atau timeout
-- UPDATE inventory SET stock = stock - 1 WHERE product_id = 101;

COMMIT;

Transaction Best Practices

1. Keep Transactions Short

-- GOOD: Short transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- BAD: Long transaction dengan operations yang tidak perlu
BEGIN;
SELECT * FROM logs WHERE date > '2024-01-01';  -- Read-only, tidak perlu dalam transaction!
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... user input wait ...
COMMIT;

2. Error Handling

DELIMITER $$

CREATE PROCEDURE SafeTransfer(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts 
    SET balance = balance - p_amount 
    WHERE id = p_from_account;
    
    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Source account not found or insufficient funds';
    END IF;
    
    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE id = p_to_account;
    
    COMMIT;
END$$

DELIMITER ;

3. Deadlock Prevention

-- Access resources dalam consistent order
-- Jika selalu A then B, tidak akan deadlock

BEGIN;
-- Lock A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Lock B
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Kesimpulan

Transactions memastikan:

  • Data integrity dengan ACID properties
  • Concurrent access safety
  • Error recovery dengan ROLLBACK
  • Flexible control dengan SAVEPOINTS

Gunakan selalu untuk multi-operation business processes!

Artikel Terkait

Link Postingan : https://www.tirinfo.com/mysql-transactions-acid-begin-commit-rollback/

Hendra WIjaya
Tirinfo
4 minutes.
3 February 2026