Tell us about transactions in databases and locking. How would you handle concurrent debit and credit operations on an account balance?

Database transactions are a set of operations that are executed as a single unit. They ensure the properties of ACID:

• Atomicity — all operations are either completed or not performed at all.
• Consistency — the database transitions from one correct state to another.
• Isolation — concurrent transactions do not affect each other.
• Durability — after a transaction is committed, changes are saved.

Locks are used to ensure isolation and prevent conflicts during concurrent access.

To handle simultaneous debit and credit operations on a balance, it is important to:

1. Use a transaction to make debit and credit operations atomic.
2. Apply row-level locks (e.g., SELECT ... FOR UPDATE) to prevent race conditions during balance read/write.

Example in SQL:

BEGIN;
-- Lock the balance row
SELECT balance FROM accounts WHERE id = :account_id FOR UPDATE;
-- Check and update the balance
UPDATE accounts SET balance = balance - :amount WHERE id = :account_id;
-- Similarly for crediting another account
COMMIT;

In PHP, this can be implemented using PDO with transactions:

$pdo->beginTransaction();
// SELECT FOR UPDATE
$stmt = $pdo->prepare('SELECT balance FROM accounts WHERE id = ? FOR UPDATE');
$stmt->execute([$accountId]);
$balance = $stmt->fetchColumn();

if ($balance >= $amount) {
    // debit
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([$amount, $accountId]);
    // credit
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$amount, $otherAccountId]);
    $pdo->commit();
} else {
    $pdo->rollBack();
    throw new Exception('Insufficient funds');
}

This approach guarantees the correctness of the balance during concurrent operations.