INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

Topic lessonTopic: SQL Server and database foundations

Transactions explained: COMMIT, ROLLBACK and autocommit

A transaction is a unit of work whose changes can be committed or rolled back together. Compare two document-status updates under COMMIT and ROLLBACK. BEGIN alone is not a promise that every error automatically undoes everything.

What you will learn

Predict values after committing or rolling back two updates from the same starting state; distinguish autocommit and error conditions.

Prerequisite

Understand SELECT, UPDATE targets and required permissions. All data is fictional; no real database is changed.

Treat two changes as one unit of work

Start from committed T0 in CorpDocs.app.Documents: document 103 is draft and 104 is open. editor_demo has the required SELECT and UPDATE(status). There are no concurrent writes, triggers or nested transactions, and both updates succeed.

Two independent attempts from the same T0: commit and rollback
T0: 103 = draft / 104 = open

Committed values before each attempt.

Two updates in one explicit transaction →
Pending: 103 = open / 104 = draft

Both updates succeed in the same transaction, before COMMIT.

COMMIT ↓
Committed: 103 = open / 104 = draft

After explicit COMMIT in W-C.

ROLLBACK ↓
Restored: 103 = draft / 104 = open

After explicit ROLLBACK in W-R.

The two branches are separate attempts from the same initial state.

A fictional teaching example. Containment, communication and permission relationships have different meanings.

The branches do not mean COMMIT followed by ROLLBACK. W-C and W-R each begin from a fresh T0. They illustrate grouping two updates in one connection.

COMMIT makes both updates committed

W-C: both updates succeed, followed by explicit commit
BEGIN TRANSACTION;
UPDATE app.Documents SET status = 'open'
WHERE document_id = 103;
UPDATE app.Documents SET status = 'draft'
WHERE document_id = 104;
COMMIT TRANSACTION;
W-C states: distinguish pending from committed
PointDocument 103Document 104State
Before: T0draftopenCommitted
After update 1openopenChanged within this transaction
After update 2opendraftBefore COMMIT
After COMMITopendraftBoth changes committed

Only documents 103 and 104 are targeted; 101, 102 and 105 stay unchanged under these conditions. BEGIN and COMMIT do not supply the UPDATE filters for you.

ROLLBACK undoes the changes in that attempt

W-R: two updates from an independent T0, then explicit rollback
BEGIN TRANSACTION;
UPDATE app.Documents SET status = 'open'
WHERE document_id = 103;
UPDATE app.Documents SET status = 'draft'
WHERE document_id = 104;
ROLLBACK TRANSACTION;
Final values when both attempts start from the same T0
Independent attemptFinal controlDocument 103Document 104
W-CCOMMITopendraft
W-RROLLBACKdraftopen

Separate autocommit from error behavior

The unit in which successful statements commit
ConditionCommit unitImplication
AutocommitEach statementThe first can commit before a later statement fails
Explicit transactionWork enclosed by BEGIN and COMMITCompletion and error handling must be designed
An error occursDepends on error and session conditionsBEGIN alone does not guarantee automatic full rollback

SQL Server behavior depends on settings such as XACT_ABORT and on the error type. Even with XACT_ABORT ON, not every error category behaves identically. Practical handling involves constructs such as TRY/CATCH and transaction-state checks. Our attempts stipulate no errors and explicit completion, so only those outcomes are asserted.

Check commit and rollback

Choose an option to see the result and explanation.

At T1, document 103 becomes open and 104 becomes draft. What is T2 after ROLLBACK?
Read the answer and reason

Answer: 103=draft, 104=open · The example returns to its previously committed T0 state.

What becomes committed if those two changes are COMMITted?
Read the answer and reason

Answer: 103=open, 104=draft · The specified changes to both rows become committed.

Does this case prove that another session always sees uncommitted changes at T1?
Read the answer and reason

Answer: No; isolation conditions are unspecified · Visibility depends on transaction isolation conditions.

Check, then explain in your own words

  1. Fix each attempt’s T0 and the editor’s permissions.
  2. Check both WHERE predicates and assigned values.
  3. Trace COMMIT and ROLLBACK as independent attempts.
  4. Do not infer error behavior or other-session visibility without conditions.
Check your understanding: If statement two fails after BEGIN, is everything always undone?

BEGIN alone does not establish that outcome.

Error type, settings and completion control matter. The fixed outcomes here require both updates to succeed and an explicit COMMIT or ROLLBACK.

Sources and conditions checked

Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.