If a row inserted in a SQL console is visible on that connection but not another, the transaction may still be open. Results also depend on the tool's autocommit setting, so start with an explicit transaction. The following example uses a separate practice table.
INSERT makes a change within a transaction
With autocommit disabled, the inserted row with ID 101 is visible to the current session. Choosing COMMIT confirms the change; choosing ROLLBACK before committing cancels it.
CREATE TABLE todo (
id INTEGER PRIMARY KEY,
title VARCHAR(100) NOT NULL,
done CHAR(1) NOT NULL
);
BEGIN TRANSACTION;
INSERT INTO todo (id, title, done)
VALUES (101, 'Transaction check', 'N');
SELECT id, title FROM todo WHERE id = 101;The same session can query row 101 immediately. Executing COMMIT at this point makes the change durable. Exactly when another connection sees it also depends on database isolation settings and the state of any transaction already reading there.
COMMIT;Think of commit as confirming the change, not as an undo button. After a commit, a later ROLLBACK generally cannot cancel just that committed change. You would need separate deletion or correction SQL. Especially for production data, check the target rows again before committing.
ROLLBACK cancels uncommitted changes
For test data, you can inspect the result and then roll it back.
BEGIN TRANSACTION;
INSERT INTO todo (id, title, done)
VALUES (102, 'Temporary row', 'N');
ROLLBACK;
SELECT id, title FROM todo ORDER BY id;Only row 101 should remain. Row 102 was never committed, so the rollback cancels it. If several INSERT and UPDATE statements form one business operation, rolling them all back after a failed check prevents a partially changed dataset. Verify that the conditions in your check query are correct before COMMIT.
Check autocommit settings first
A tool or driver may enable autocommit. Without an explicit transaction, each statement may be committed immediately and a later ROLLBACK cannot undo it. If a console example rolls back but the same SQL in an application does not, inspect the connection settings and the transaction's start and end before changing the SQL itself.
The principle also applies in frameworks: handle multiple changes within a service method's transaction boundary, and define which exceptions trigger a rollback. Catching an exception and returning as though nothing failed may prevent the intended rollback.
Treat DDL as a separate boundary
DDL such as creating or altering tables can have different transaction behavior from data changes. This example creates the table first, then starts a separate transaction for the INSERT. Before trusting a ROLLBACK after CREATE TABLE, check whether your database implicitly commits around DDL. Manage schema changes through a dedicated migration process.
Key takeaways
An INSERT usually remains part of the current transaction until COMMIT, and it can be canceled with ROLLBACK before then. Autocommit changes that assumption. Check the target and result before committing, and group related changes into one business transaction to avoid partial data.
