This content originally appeared on DEV Community and was authored by Kamwemba Tinashe C
1. Create the Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
2. Insert Initial Data
INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);
Transaction
Start the Transaction (implicitly starts with the first DML):
- No explicit START TRANSACTION needed; the transaction begins with the first UPDATE.
Debit from Alice
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
credit to Bob
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
Rollback the Transaction:
ROLLBACK;
Check Balances to Confirm No Changes
SELECT * FROM Accounts;
OUTPUT
Deadlock Simulation
- Oracle supports row-level locking, which we can use to simulate a deadlock. Open two SQL*Plus sessions. Session 1 (Window 1):
Lock Alice's account:
UPDATE Accounts SET balance = balance WHERE name = 'Alice';
-- Don't commit yet; leave it locked
Try to update Bob's account (run this after starting Session 2):
UPDATE Accounts SET balance = balance WHERE name = 'Bob';
Session 2 (Window 2):
Lock Bob's account:
UPDATE Accounts SET balance = balance WHERE name = 'Bob';
-- Don't commit yet; leave it locked
Try to update Alice's account:
UPDATE Accounts SET balance = balance WHERE name = 'Alice';
- At this point, Session 1 waits for Session 2's lock on Bob, and Session 2 waits for Session 1's lock on Alice, causing a deadlock. Oracle will detect this and roll back one transaction, raising an error like:
Log-Based Recovery
Step 1: Enable Logging
- Logging is enabled by default in Oracle via redo logs and undo tablespaces. You don’t need to enable it manually unless it’s been disabled (unlikely in a standard setup). To confirm an undo tablespace exists (without DBA_TABLESPACES), try:
SELECT tablespace_name FROM USER_TABLESPACES WHERE tablespace_name LIKE '%UNDO%';
Step 2: Start a Transaction and Update a Record
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Charlie';
-- Transaction starts implicitly
Step 3: Rollback the Transaction
ROLLBACK;
Step 4: Check the Log to Confirm Undo Operation
SELECT * FROM Accounts WHERE name = 'Charlie';
This content originally appeared on DEV Community and was authored by Kamwemba Tinashe C
Kamwemba Tinashe C | Sciencx (2025-10-07T16:42:41+00:00) Transactions, Deadlocks & Log Based Recovery. Retrieved from https://www.scien.cx/2025/10/07/transactions-deadlocks-log-based-recovery-5/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.