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.
Predict values after committing or rolling back two updates from the same starting state; distinguish autocommit and error conditions.
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.
Committed values before each attempt.
Both updates succeed in the same transaction, before COMMIT.
After explicit COMMIT in W-C.
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
BEGIN TRANSACTION;
UPDATE app.Documents SET status = 'open'
WHERE document_id = 103;
UPDATE app.Documents SET status = 'draft'
WHERE document_id = 104;
COMMIT TRANSACTION;| Point | Document 103 | Document 104 | State |
|---|---|---|---|
| Before: T0 | draft | open | Committed |
| After update 1 | open | open | Changed within this transaction |
| After update 2 | open | draft | Before COMMIT |
| After COMMIT | open | draft | Both 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
BEGIN TRANSACTION;
UPDATE app.Documents SET status = 'open'
WHERE document_id = 103;
UPDATE app.Documents SET status = 'draft'
WHERE document_id = 104;
ROLLBACK TRANSACTION;| Independent attempt | Final control | Document 103 | Document 104 |
|---|---|---|---|
| W-C | COMMIT | open | draft |
| W-R | ROLLBACK | draft | open |
Separate autocommit from error behavior
| Condition | Commit unit | Implication |
|---|---|---|
| Autocommit | Each statement | The first can commit before a later statement fails |
| Explicit transaction | Work enclosed by BEGIN and COMMIT | Completion and error handling must be designed |
| An error occurs | Depends on error and session conditions | BEGIN 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.
Check, then explain in your own words
- Fix each attempt’s T0 and the editor’s permissions.
- Check both WHERE predicates and assigned values.
- Trace COMMIT and ROLLBACK as independent attempts.
- 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.
- Transactions (Transact-SQL)Checked:
Scope and conditionsExplicit transactions begin with BEGIN TRANSACTION and end with COMMIT/ROLLBACK. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonTreat two changes as one unit of work COMMIT makes both updates committed ROLLBACK undoes the changes in that attempt Separate autocommit from error behavior Check commit and rollback
- UPDATE (Transact-SQL)Checked:
Scope and conditionsT-SQL UPDATE supports a WHERE condition to narrow targeted rows. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonCOMMIT makes both updates committed ROLLBACK undoes the changes in that attempt
- SET XACT_ABORT (Transact-SQL)Checked:
Scope and conditionsDistinguish autocommit from explicit transactions; runtime error handling depends on settings and the error. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonSeparate autocommit from error behavior Check commit and rollback