Transaction Control · Database Management System

Transaction Control

CSC-265 · Semester IV · Database Management System

intermediate30 minAvailable

SQLin-browser engine · runs locallytransactions

Ctrl Enter to run14 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Manage BEGIN, COMMIT and ROLLBACK to control transaction boundaries and observe atomicity and isolation with concurrent sessions.

Theory & concepts

A transaction groups statements that must all succeed or all fail. BEGIN starts a transaction; COMMIT makes its changes permanent; ROLLBACK undoes them. Two sessions on the same data demonstrate isolation: one session's uncommitted changes are invisible to the other until committed.

Rendering diagram…

Transaction boundaries with COMMIT and ROLLBACK

BEGIN;
UPDATE ACCOUNT SET Balance = Balance - 100 WHERE AccNo = 'A1';
-- session 2 cannot see the new balance yet
COMMIT;

-- on error instead:
ROLLBACK;   -- undoes the UPDATE

Prerequisites

Lesson: Transaction concepts and states
Lesson: The ACID properties

Instructions

1Open two sessions against the ACCOUNT table and note the initial balances.
2In session 1 run BEGIN, then update one account (do not commit).
3Query the same row from session 2 and observe what isolation hides.
4Commit in session 1 and re-query from session 2 to see the change appear.
5Repeat but ROLLBACK instead, and confirm the value reverts.
6Answer the closing questions about atomicity and durability.

Tasks

Atomic update
Transfer between two accounts inside one transaction and commit.
Rollback
Make a change, ROLLBACK, and confirm the data is unchanged.
Isolation check
Show that a second session cannot see uncommitted changes.
Durability proof
Re-query after COMMIT and confirm the change persists across sessions.

Self-check

COMMIT makes changes permanent and visible to other sessions.
ROLLBACK fully undoes the transaction's changes.
The student demonstrates isolation between concurrent sessions.
The student explains each ACID property in the context of the lab.

Environment

two-session consoletransaction timelinecommit/rollback controlsisolation visualization