Transaction và Concurrency Control

ACID Properties

Atomicity

Transaction hoàn thành hoàn toàn hoặc không hoàn thành.

Consistency

Transaction chuyển database từ state consistent sang state consistent khác.

Isolation

Transactions thực hiện đồng thời không ảnh hưởng lẫn nhau.

Durability

Khi transaction committed, changes được lưu vĩnh viễn.

Transaction Syntax

BEGIN, COMMIT, ROLLBACK

BEGIN TRANSACTION;

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

-- Nếu thành công
COMMIT;

-- Nếu có lỗi
ROLLBACK;

SAVEPOINT

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Nếu có lỗi ở bước này
ROLLBACK TO SAVEPOINT after_debit;

COMMIT;

Isolation Levels

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE account_id = 1;

READ COMMITTED (Mặc định)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE account_id = 1;

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1;

SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 1;

Concurrency Problems

Dirty Read

Đọc dữ liệu từ transaction chưa committed.

Non-repeatable Read

Một transaction đọc cùng row hai lần và nhận kết quả khác nhau.

Phantom Read

Một transaction đọc tập hợp rows hai lần và thấy số lượng rows khác nhau.

Lost Update

Hai transactions cùng update một row, update sau ghi đè update trước.

Locking Mechanisms

Row-level Locking

-- SELECT FOR UPDATE (pessimistic locking)
BEGIN TRANSACTION;
SELECT * FROM accounts 
WHERE account_id = 1 
FOR UPDATE;

UPDATE accounts SET balance = balance - 100 
WHERE account_id = 1;
COMMIT;

Optimistic Locking

-- Sử dụng version column
UPDATE products 
SET stock_quantity = stock_quantity - 1,
    version = version + 1
WHERE product_id = 123 
AND version = 5; -- Version hiện tại

-- Kiểm tra nếu rows affected = 0 thì có conflict

Deadlock Handling

Deadlock Detection

-- Transaction 1
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- Chờ
COMMIT;

-- Transaction 2  
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1; -- Deadlock!
COMMIT;

Deadlock Prevention

-- Luôn update accounts theo cùng thứ tự (ví dụ: từ id nhỏ đến lớn)
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Transaction khác cũng phải theo thứ tự tương tự
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1; 
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;

Practical Examples

Bank Transfer Transaction

CREATE PROCEDURE transfer_funds(
  IN from_account INT,
  IN to_account INT, 
  IN amount DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  -- Kiểm tra số dư
  SELECT balance INTO @current_balance 
  FROM accounts 
  WHERE account_id = from_account 
  FOR UPDATE;
  
  IF @current_balance < amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  -- Trừ tiền từ account nguồn
  UPDATE accounts 
  SET balance = balance - amount 
  WHERE account_id = from_account;
  
  -- Cộng tiền vào account đích
  UPDATE accounts 
  SET balance = balance + amount 
  WHERE account_id = to_account;
  
  -- Ghi log transaction
  INSERT INTO transactions (from_account, to_account, amount, transaction_date)
  VALUES (from_account, to_account, amount, NOW());
  
  COMMIT;
END;