TRANSACTION

\\Understanding MySQL Transactions with Row-Level Locking: A Hands-On Guide
When working with relational databases, ensuring data consistency during concurrent operations is critical. MySQL offers powerful transaction control and row-level locking mechanisms that can help developers maintain integrity even in multi-user environments.

In this post, I’ll walk through a few real-world examples using MySQL’s START TRANSACTION, SELECT ... FOR UPDATE, and ROLLBACK commands to demonstrate how locking and rollback work in practice.

Scenario 1: Locking Rows with SELECT ... FOR UPDATE
sql
START TRANSACTION;

-- Lock Alice's row
SELECT * FROM Accounts WHERE name = 'Alice' FOR UPDATE;

-- Update Bob's balance (allowed if not locked by another session)
UPDATE Accounts SET balance = balance + 10 WHERE name = 'Bob';
Insight: SELECT ... FOR UPDATE locks the selected row until the transaction is committed or rolled back. This prevents other sessions from modifying it, ensuring safe concurrent operations.

Scenario 2: Rolling Back a Transaction
sql
START TRANSACTION;

Transfer funds from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';

something went wrong? Roll it back!
ROLLBACK;

Verify balances
SELECT * FROM Accounts;
Insight: ROLLBACK undoes all changes made during the transaction. This is especially useful when an error occurs mid-operation or a condition fails validation.

Scenario 3: Locking Bob, Updating Alice
sql
START TRANSACTION;

Lock Bob's row
SELECT * FROM Accounts WHERE name = 'Bob' FOR UPDATE;

Update Alice's balance
UPDATE Accounts SET balance = balance + 20 WHERE name = 'Alice';

Insight: Locking one row doesn’t block updates to other rows unless explicitly locked. This allows for fine-grained concurrency control.