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