# Transactions

---
# yaml-language-server: $schema=schemas\page.schema.json
Object type:
    - Page
Backlinks:
    - sql
Creation date: "2026-05-26T09:34:43Z"
Created by:
    - thomas
id: bafyreiexau2pcju4y5v62ey66kh2ybaefl4qm3lbt6oppfisir5om2if2i
---
# transatctions   
## PURPOSE   
- data integrity   
- Protect against partial updates   
- concurrent access so multiple users can work
without corrupting data.   
- Support complex operations   
- Enablesafe rollback   
   
## OBJECTIVES   
- Define what a transaction is and explain its ACID
properties.   
- Start, commit, and roll backtransactions using SQL
commands.   
- Use save points to roll back part of a transaction.   
   
## DEFINITION   
- A transaction is a sequence of one or more SQL
statements that are executed as a single unit of work.   
- The transaction must be fully completed(committed) so
all changes become permanent, or fully undone(rolled
back) so the database returns to its original state,
ensuring data integrity.   
- ACID describes the four main promises a database
transaction makes to keep data safe and reliable.   
   
## ACID PROPERTIES   
### A—Atomicity(“All or Nothing”)   
- A transaction’s operations areall completed
successfullyornone at all.   
- If one part fails, the entire transaction isrolled back.   
- Example: In a bank transfer, money isdeducted from
one accountandadded to another—never just one of
them.   
   
### C—Consistency (“Valid State”)   
- A transaction brings the database from onevalid state
to another valid state, following allrules, constraints,
and triggers.   
- No transaction can breakdata integrityrules (like
unique constraints or foreign keys).   
- Example: You can’t insert a row with an invalid foreign
key.   
   
### I—Isolation(“No Interference”)   
- The operations of one transaction areinvisibleto others
until the transaction is committed.   
- Prevents concurrency problems likedirty readsorlost
updates.   
- Example: You can’t see a friend’s half-completed edit in
an online order system.   
   
### D—Durability(“It Sticks”)   
- Once a transaction iscommitted, the changes are
permanent;evenif the system crashes.   
- The database stores committed changes innon-volatile
memory(disk, transaction logs).   
- Example: After hitting “Place Order,” your order remains
recorded even if the server restarts.   
   
## COMMIT AND ROLLBACK   
- Commit: making a transaction permanent   
- Rollback: undoing a transaction
oOnce committed, no rollback is possible!   
- Autocommit:
 *Every SQL statement is a transaction*
 *Every transaction commits automatically if it succeeds*
 Errors roll back that single SQL statement
Autocommit is the default behaviour in PostgreSQL unless you
explicitly start a transaction with START TRANSACTION or BEGIN   
   
## TRANSACTION PROCESSING   
- Implicit start
A new transaction begins automatically after a COMMIT or
ROLLBACK when autocommit mode is enabled.   
- Explicit start
oBEGIN; sql code;COMMIT;—dialect
oBEGIN; sql code;ROLLBACK;—dialect
oSTART TRANSACTION; sql code;COMMIT;
oSTART TRANSACTION; sql code;ROLLBACK;
oAlwaysenda transaction explicitly—don’t leave it open.   
- When does it make sense ?
oIf a particular entry is to be deleted from multiple tables
oIf a user made a mistake in adjustments   
- Possible exceptions (product restrictions) :Some
commands, likecatalogmodifications (e.g., CREATE
TABLE, ALTER TABLE), may be handled outside normal
transaction control.   
   
## SAVEPOINTS   
- Purpose:
oAllow you toundo part of the current transactionwithout
rolling back the entire transaction.
oAct asintermediate checkpoint (snapshot)within a
transaction.   
- You can have multiplesavepointsin a single transaction.   
- Rolling back to a save point does not end the transaction.   
- Example script template:
START TRANSACTION;
UPDATE… ;
INSERT… ;
SAVEPOINTS1;—first checkpoint
INSERT…
SAVEPOINTS2;—second checkpoint
DELETE… ;
ROLLBACK TO SAVEPOINTS2;—undo changes after S2 only