This content originally appeared on DEV Community and was authored by Balaji
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.
This content originally appeared on DEV Community and was authored by Balaji
Balaji | Sciencx (2025-10-08T04:34:10+00:00) HASHING. Retrieved from https://www.scien.cx/2025/10/08/hashing-5/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.