Every language
11 lenguajes, copy-ready. One at a time with syntax highlighting, or all inline.
SQLSQLrunnable
-- success twin: both updates are ONE unit — all commit or none do
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SELECT txid_current(); -- one tx id for BOTH updates — proof they are a unit
COMMIT;
-- failure twin: the RAISE aborts the tx; the first UPDATE rolls back with it
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
DO $$ BEGIN RAISE EXCEPTION 'account 2 not found'; END $$;
COMMIT; -- never reached: the tx is poisoned, only ROLLBACK ends it now
ROLLBACK;
-- the isolation edge from the explanation: readers queue on the row lock
-- SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;Caveat, stated honestly: the sql-playground may autocommit each statement, so the failure twin there rolls back only the DO block — run it in a real psql session to watch the whole BEGIN..COMMIT unit abort. The Postgres rule the demo leans on: an error inside a tx poisons it — every later statement, including COMMIT, fails until ROLLBACK.
JSJavaScript
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function transfer(from, to, amount) {
const client = await pool.connect(); // one client = one session = one tx
try {
await client.query('BEGIN');
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release(); // skip this and the pool drains dry
}
}There is no transaction object in node-postgres — BEGIN, COMMIT and ROLLBACK are ordinary SQL strings, and each query() is a network round trip, so a 'transaction' is really five awaited statements on the same client. The finally release() is part of the idiom: a client left inside a BEGIN never goes back to the pool.
TSTypeScript
import type { Pool, PoolClient } from 'pg';
export async function transaction<T>(
pool: Pool,
fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await fn(client); // typed work, typed result
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
// usage:
// await transaction(pool, async (c) => {
// await c.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
// await c.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
// });The generic folds the ceremony into one reusable helper — callers write only the typed body. The trap is a try/catch INSIDE fn: a swallowed error never reaches the helper's catch, so the half-done unit COMMITS. Never rescue inside the callback unless you rethrow.
GoGo
import (
"context"
"database/sql"
)
func transfer(ctx context.Context, db *sql.DB, from, to, amount int) error {
tx, err := db.BeginTx(ctx, nil) // BEGIN
if err != nil {
return err
}
defer tx.Rollback() // no-op after Commit — this line IS the error path
if _, err := tx.ExecContext(ctx,
"UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from); err != nil {
return err // deferred ROLLBACK fires here
}
if _, err := tx.ExecContext(ctx,
"UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to); err != nil {
return err
}
return tx.Commit() // the only exit that keeps the work
}defer tx.Rollback() immediately after BeginTx is the idiom: every early return rolls back, and after Commit the Rollback returns sql.ErrTxDone and is harmlessly ignored. Without it, the forgotten error path holds the row lock until the connection dies.
RsRust
use sqlx::PgPool;
async fn transfer(
pool: &PgPool,
from: i64,
to: i64,
amount: i64,
) -> Result<(), sqlx::Error> {
let mut tx = pool.begin().await?; // BEGIN
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
.bind(amount)
.bind(from)
.execute(&mut *tx)
.await?;
sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
.bind(amount)
.bind(to)
.execute(&mut *tx)
.await?;
tx.commit().await // nothing is durable before this line
}RAII writes the error path: any early ? return Drops tx un-committed, and the Drop rolls the transaction back — there is no explicit rollback branch to forget. The flip side: a tx alive across an .await that hangs holds the row lock for exactly as long.
PHPPHP
$pdo = new PDO($dsn, $user, $pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
try {
$pdo->beginTransaction();
$pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
->execute([$amount, $fromId]);
$pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
->execute([$amount, $toId]);
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack(); // already aborted server-side? rollBack would THROW
}
throw $e;
}rollBack() on a connection whose transaction already aborted throws a second PDOException ('There is no active transaction') — inTransaction() is the guard that keeps the original error visible. On MySQL, DDL inside a transaction (ALTER TABLE, CREATE INDEX…) silently commits everything before it.
PyPython
import psycopg
with psycopg.connect(ENV['DATABASE_URL']) as conn: # BEGIN at entry
with conn.cursor() as cur: # the cursor is its OWN with
cur.execute(
'UPDATE accounts SET balance = balance - %s WHERE id = %s',
(amount, from_id),
)
cur.execute(
'UPDATE accounts SET balance = balance + %s WHERE id = %s',
(amount, to_id),
)
# clean exit → COMMIT; an exception inside → ROLLBACKwith conn: is the transaction — commit on clean exit, rollback on exception — and psycopg leaves the connection OPEN after the block (conn stays reusable; conn.close() is separate). The nested with conn.cursor() is the other half of the idiom: cursors close, connections commit.
C#C#
using var conn = new SqlConnection(connectionString);
conn.Open();
using var tx = conn.BeginTransaction(); // BEGIN — disposed AFTER the commands
try
{
using (var debit = new SqlCommand(
"UPDATE accounts SET balance = balance - @amount WHERE id = @id", conn, tx))
{
debit.Parameters.AddWithValue("@amount", amount);
debit.Parameters.AddWithValue("@id", fromId);
debit.ExecuteNonQuery();
}
using (var credit = new SqlCommand(
"UPDATE accounts SET balance = balance + @amount WHERE id = @id", conn, tx))
{
credit.Parameters.AddWithValue("@amount", amount);
credit.Parameters.AddWithValue("@id", toId);
credit.ExecuteNonQuery();
}
tx.Commit();
}
catch
{
tx.Rollback();
throw;
}Enrollment is the trap: every SqlCommand must take tx in its constructor — create one without it and that statement silently runs OUTSIDE the transaction. The using order matters too: declare the transaction before the commands, so disposal runs commands-first (disposing a live SqlTransaction rolls it back).
JvJava
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false); // BEGIN — from here on, manual
try (PreparedStatement debit = conn.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ?");
PreparedStatement credit = conn.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
debit.setInt(1, amount);
debit.setInt(2, fromId);
debit.executeUpdate();
credit.setInt(1, amount);
credit.setInt(2, toId);
credit.executeUpdate();
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true); // the pool hands this conn to the next borrower
}
}The pool is the trap: the connection goes back SHARED, and a borrower that finds autoCommit still false runs a one-statement 'transaction' that never commits. finally { setAutoCommit(true); } is not optional cleanup — it is correctness.
KtKotlin
dataSource.connection.use { conn ->
conn.autoCommit = false // BEGIN
try {
conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?")
.use { ps ->
ps.setInt(1, amount); ps.setInt(2, fromId)
ps.executeUpdate()
}
conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")
.use { ps ->
ps.setInt(1, amount); ps.setInt(2, toId)
ps.executeUpdate()
}
conn.commit()
} catch (e: SQLException) {
conn.rollback()
throw e
} finally {
conn.autoCommit = true // same pool trap as Java
}
}use{} is Kotlin's try-with-resources — close on any exit, so the statements never leak. Same JDBC rules as Java and the same pool trap: restore autoCommit in finally or the next borrower inherits a half-open transaction.
RbRuby
require 'sequel'
DB = Sequel.connect(ENV['DATABASE_URL'])
DB.transaction do
DB[:accounts].where(id: from_id)
.update(balance: Sequel[:balance] - amount)
DB[:accounts].where(id: to_id)
.update(balance: Sequel[:balance] + amount)
end # raise inside → ROLLBACK; reaching the end → COMMIT
# abort WITHOUT bubbling an error:
# DB.transaction { raise Sequel::Rollback } # rolls back, returns nil
# generic DBI-style, for drivers without a block helper:
# db['BEGIN']; begin ... ; db['COMMIT']; rescue; db['ROLLBACK']; raise; endThe block form (Sequel, and ActiveRecord::Base.transaction in Rails) maps exception → ROLLBACK and normal exit → COMMIT — a break or early return still COMMITS; only a raise undoes. Sequel::Rollback is the escape hatch: abort cleanly with no error surfaced.