This content originally appeared on DEV Community and was authored by Vishnupriya K
Database reliability is critical for any application. In this tutorial, we’ll explore Transactions, Deadlocks, and Log-Based Recovery using a simple Accounts table.
1. Setup: Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);
INSERT INTO Accounts VALUES (1, 'Alice', 1000);
INSERT INTO Accounts VALUES (2, 'Bob', 1500);
INSERT INTO Accounts VALUES (3, 'Charlie', 2000);
COMMIT;
This table will be used to simulate transactions, deadlocks, and recovery.
2. Transaction – Atomicity & Rollback
Goal: Transfer 500 from Alice to Bob, then rollback to demonstrate atomicity.
-- Start transaction
BEGIN
-- Deduct 500 from Alice
UPDATE Accounts
SET balance = balance - 500
WHERE acc_no = 1;
-- Add 500 to Bob
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 2;
-- Simulate a rollback
ROLLBACK;
END;
/
✅ Check balances:
SELECT * FROM Accounts;
Result: Balances remain unchanged. No partial update occurred — atomicity is preserved.
3. Deadlock Simulation
Goal: Demonstrate a deadlock scenario with two sessions.
Session 1:
-- Lock Alice’s account
UPDATE Accounts
SET balance = balance + 100
WHERE acc_no = 1;
-- Pause before committing (wait)
Session 2 (simultaneously):
-- Lock Bob’s account
UPDATE Accounts
SET balance = balance - 50
WHERE acc_no = 2;
-- Now try to update Alice’s account
UPDATE Accounts
SET balance = balance - 100
WHERE acc_no = 1;
Both sessions are waiting for the other to release locks → deadlock occurs.
Most DBMS detect deadlocks and abort one transaction to resolve it.
Key takeaway: Always acquire locks in the same order to avoid deadlocks in multi-session environments.
4. Log-Based Recovery
Goal: Ensure database can undo changes after rollback using logs.
In Oracle, all transactions are automatically logged in the Redo Log.
In MySQL/PostgreSQL, binary logs / WAL (Write-Ahead Log) track all changes.
Example:
BEGIN
UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 3;
ROLLBACK; -- Undo the update
END;
/
Check logs:
- Oracle: V$LOGMNR_CONTENTS or redo logs capture the undo operation.
- MySQL: Binary log will record both the original update and rollback action .
Result: Log ensures database can recover from failures without losing consistency.
5. Summary
Concept Explanation
- Transaction (Atomicity) Either all operations succeed or none; rollback prevents partial updates.
- Deadlock Two sessions waiting for each other’s locks; resolved by DBMS aborting one transaction.
- Log-Based Recovery Logs record changes so rollback and crash recovery are possible.
Conclusion
By understanding transactions, deadlocks, and log-based recovery, you can ensure your database remains reliable, consistent, and recoverable. Always test transactions carefully, handle deadlocks gracefully, and leverage logging for safety.
💡 Tip: Simulate deadlocks in a controlled environment to learn how your DBMS resolves them. This is essential for multi-user applications.
This content originally appeared on DEV Community and was authored by Vishnupriya K
Vishnupriya K | Sciencx (2025-10-05T20:43:04+00:00) Understanding Transactions, Deadlocks & Log-Based Recovery in SQL. Retrieved from https://www.scien.cx/2025/10/05/understanding-transactions-deadlocks-log-based-recovery-in-sql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.