Skip to main content
Group multiple operations into atomic units.

​
Basic Syntax

:BEGIN
:COMMIT
:ROLLBACK

​
Simple Transaction

:BEGIN

:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2

:COMMIT
DatabaseOutput
PostgreSQLBEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
MySQLSTART TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

​
Rollback

Undo all changes in the transaction. 
:BEGIN

:UPDATE Account SET balance = balance - 100 WHERE id = 1
:UPDATE Account SET balance = balance + 100 WHERE id = 2

:ROLLBACK

​
Savepoints

Create checkpoints within a transaction. 
:BEGIN

:INSERT Order WITH user_id = 1, total = 99.99
:SAVEPOINT order_created

:INSERT OrderItem WITH order_id = 1, product_id = 5, quantity = 2
:INSERT OrderItem WITH order_id = 1, product_id = 8, quantity = 1

:COMMIT
DatabaseOutput
PostgreSQLBEGIN; INSERT INTO orders ...; SAVEPOINT order_created; INSERT INTO order_items ...; COMMIT;

​
Rollback to Savepoint

Undo changes back to a savepoint. 
:ROLLBACK TO order_created

​
Release Savepoint

Remove a savepoint (keep the changes). 
:RELEASE SAVEPOINT order_created

​
Isolation Levels

Control how transactions see each other’s changes. Set isolation level before BEGIN. 
:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
:BEGIN

:GET Product WHERE id = 1
:UPDATE Product SET quantity = quantity - 1 WHERE id = 1

:COMMIT
LevelDescription
READ UNCOMMITTEDCan see uncommitted changes from other transactions
READ COMMITTEDOnly sees committed changes (PostgreSQL default)
REPEATABLE READConsistent reads within transaction (MySQL default)
SERIALIZABLEFull isolation, transactions appear sequential
DatabaseOutput
PostgreSQLSET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; ...
MySQLSET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; ...

​
Complete Examples

​
Money Transfer

:BEGIN

:UPDATE Account SET balance = balance - 500 WHERE id = 1 AND balance >= 500
:UPDATE Account SET balance = balance + 500 WHERE id = 2

:INSERT TransactionLog WITH from_account = 1, to_account = 2, amount = 500

:COMMIT

​
Order with Savepoint

:BEGIN

:INSERT Order WITH user_id = 42, status = "pending", total = 0
:SAVEPOINT order_created

:INSERT OrderItem WITH order_id = 1, product_id = 5, quantity = 2, price = 29.99
:INSERT OrderItem WITH order_id = 1, product_id = 8, quantity = 1, price = 49.99

:UPDATE Order SET total = 109.97 WHERE id = 1
:UPDATE Product SET quantity = quantity - 2 WHERE id = 5
:UPDATE Product SET quantity = quantity - 1 WHERE id = 8

:COMMIT

​
High Isolation Transaction

:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
:BEGIN

:GET Account WHERE id = 1
:UPDATE Account SET balance = balance - 100 WHERE id = 1

:COMMIT

​
Database Support

FeaturePostgreSQLMySQLMongoDB
BEGIN/COMMIT/ROLLBACKYesYesYes (sessions)
SAVEPOINTYesYesNo
ROLLBACK TOYesYesNo
RELEASE SAVEPOINTYesYesNo
Isolation LevelsYesYesYes (read concern)

​
MongoDB Note

MongoDB supports multi-document transactions in replica sets and sharded clusters (v4.0+). Savepoints are not supported. 
// MongoDB equivalent
session.startTransaction();
db.accounts.updateOne({ _id: 1 }, { $inc: { balance: -100 } });
db.accounts.updateOne({ _id: 2 }, { $inc: { balance: 100 } });
session.commitTransaction();

​
Limitations

Not currently supported:
  • BEGIN READ ONLY
  • Inline isolation level (BEGIN ISOLATION LEVEL X)
  • Nested transactions

​
Next Steps

Permissions

Control access

PostgreSQL

PostgreSQL specifics