ROHINI, DELHI · LIVE ONLINE & CLASSROOM

SQL transaction practice: commit and rollback

Two database windows can disagree without either being broken. One connection may be looking at its own unfinished change while the other still sees committed data. This exercise makes that difference visible before you use transactions in a larger project.

Use PostgreSQL in a disposable local practice database. Open two separate connections, labelled A and B, to that same database. Two editor tabs that share one connection are not enough. Keep each connection in its normal autocommit mode except where the instructions explicitly start a transaction. Do not run the setup or cleanup against work, customer or production data.

Prepare a small, visible starting point

The quantities below are invented practice data, not a stock-control design. In A, run this setup outside an explicit transaction. If the table already exists, stop and choose a fresh practice database instead of deleting an unfamiliar table.

CREATE TABLE practice_stock (
  item text PRIMARY KEY,
  quantity integer NOT NULL
);
INSERT INTO practice_stock VALUES ('notebook', 12);
SELECT * FROM practice_stock;

In B, run the same SELECT. Both results should show notebook with quantity 12. Also run SELECT pg_backend_pid(); in each connection: different backend identifiers help confirm that A and B are separate sessions. Save the starting results before moving on.

Round one: observe, then roll back

In A, run these statements together and then stop. Keep the transaction open while you inspect B.

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE practice_stock
SET quantity = quantity - 3
WHERE item = 'notebook';
SELECT * FROM practice_stock;

A should show 9. In B, run SELECT * FROM practice_stock; as a fresh statement. It should still show 12. PostgreSQL's Read Committed documentation explains that a query sees committed data as of the query's start, plus changes made earlier in its own transaction. An unfinished change in A is therefore not the same as a committed result visible to B.

Now run ROLLBACK; in A. Query the table again in both connections. Each should show 12. The PostgreSQL transaction tutorial describes rollback as cancelling the updates in the open transaction. It does not undo a transaction that has already committed.

Round two: commit the same change

Repeat A's BEGIN, UPDATE and SELECT block. Before finishing A, query B again and record its result. Then run COMMIT; in A, followed by a new SELECT in B. B should now show 9. The order matters: a screenshot taken before commit is evidence about that earlier moment.

CheckpointExpected quantity in AExpected quantity in B
After setup1212
First update still open912
After rollback1212
Second update still open912
After commit and fresh queries99

Turn the exercise into a worksheet

Copy the checkpoint names and add four columns in your own notes: prediction, actual result, exact statement and explanation. Leave actual results blank until you run the statement. Record the connection label and the order of operations; do not replace an unexpected observation with the expected answer.

  • If both windows show 9 before commit: check whether they share a connection, whether COMMIT was included in the executed selection, or whether your client executed the block differently.
  • If B still shows 12 after commit: check that it queried the same database and table after A finished. Look for an older transaction with a different isolation level.
  • If A reports an aborted transaction: save the original error and use ROLLBACK before restarting. Do not keep pasting updates into an error state.

Finish by closing any open practice transaction. When you have retained your notes and confirmed that this is your disposable table, run DROP TABLE practice_stock; outside a transaction. Keep the results, not an unnecessary practice table.

Common questions

Why only one row?

A small result makes session visibility easier to explain. This worksheet deliberately does not test concurrent writers, constraints or a production inventory workflow.

Will the same script behave identically in every database?

No. This exercise specifies PostgreSQL and Read Committed. Client transaction settings and other database engines need their own checks.

What should I show a trainer?

Show the ordered observations and explain the moment when B's result changes. Then discuss a broader learning path through the database course. You can also ask about a free demo; this worksheet does not promise a particular lab in every batch.

Admissions

Request a call back

Tell us which course you are interested in and the counselling desk will call you about fees, batches and a free demo class.

18008913982 (toll free) · +91 9899-355-335
[email protected]

Message us on WhatsApp

Open the enquiry form in a separate window ↗

Your enquiry goes to Coding Bytes through ViaCRM so the counselling team can respond. Please share only the details needed for your question, not passwords, payment details or sensitive documents. Privacy & enquiries · Before enrolling

Call WhatsApp Enquire