@Transactional
public void createOrder(Order order) {
    orderRepository.save(order);
    paymentGateway.charge(order); // external call inside TX
}

@Transactional
public void createOrderRecord(Order order) {
    orderRepository.save(order);
}

// Outside the transaction
paymentGateway.charge(order);

INSERT INTO orders (id, status)
VALUES (gen_random_uuid(), 'open');

BEGIN;
INSERT INTO orders (id, status)
VALUES (gen_random_uuid(), 'open');
COMMIT;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

import random
import time

def execute_with_retry(conn, txn_logic):
    backoff = 0.1
    while True:
        try:
            with conn.transaction() as txn:
                txn_logic(txn)
            return
        except SerializationFailure:
            time.sleep(backoff + random.uniform(0, 0.1))
            backoff = min(backoff * 2, 2.0)

BEGIN;
SAVEPOINT cockroach_restart;
-- transactional work
RELEASE SAVEPOINT cockroach_restart;
COMMIT;

BEGIN;
SET TRANSACTION READ ONLY;
SELECT * FROM customers WHERE region = 'US-East';
COMMIT;

balance = db.fetch("SELECT balance FROM accounts WHERE id = 123")
balance += 100
db.execute("UPDATE accounts SET balance = %s WHERE id = 123", (balance,))

UPDATE accounts
SET balance = balance + 100
WHERE id = 123;

UPDATE customer_daily_limits
SET used_total = used_total + $2
WHERE customer_id = $1
  AND day = current_date
  AND used_total + $2 <= daily_limit;

WITH limit_row AS (
  SELECT customer_id, day
  FROM customer_daily_limits
  WHERE customer_id = $1 AND day = current_date
  FOR UPDATE
), spend AS (
  UPDATE customer_daily_limits AS l
  SET remaining_limit = l.remaining_limit - $2,
      used_total = l.used_total + $2
  FROM limit_row
  WHERE l.customer_id = limit_row.customer_id
    AND l.day = limit_row.day
    AND l.remaining_limit >= $2
  RETURNING l.customer_id, l.day
), ins AS (
  INSERT INTO transfers (customer_id, amount, direction, created_at)
  SELECT $1, $2, 'debit', now()
  FROM spend
  RETURNING id AS transfer_id
)
SELECT transfer_id FROM ins;

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

for row in rows:
    db.execute(
        "UPDATE accounts SET balance = balance + 10 WHERE id = %s",
        (row.id,)
    )

UPDATE accounts
SET balance = balance + 10
WHERE region = 'US-East';

INSERT INTO trades (id, symbol, price)
VALUES
  (1, 'AAPL', 180),
  (2, 'GOOG', 125),
  (3, 'AMZN', 140);

WITH incoming AS (
  SELECT *
  FROM UNNEST(
    ARRAY['u1', 'u2', 'u3']::STRING[],
    ARRAY['active', 'inactive', 'active']::STRING[]
  ) AS t(id, new_status)
)
UPDATE users AS u
SET status = incoming.new_status,
    updated_at = now()
FROM incoming
WHERE u.id = incoming.id;

DELETE FROM sessions
WHERE expires_at < now()
LIMIT 10000;

ALTER TABLE events
SET (ttl_expiration_expression = 'created_at + INTERVAL ''7 DAY''');

SELECT * FROM orders
AS OF SYSTEM TIME '-5s';

SELECT * FROM inventory
AS OF SYSTEM TIME with_max_staleness(INTERVAL '10s');

SELECT id, order_date, customer_id
FROM orders
ORDER BY id
LIMIT 100 OFFSET 5000;

SELECT id, order_date, customer_id
FROM orders
WHERE id > 5000
ORDER BY id
LIMIT 100;

SELECT id, created_at, customer_id
FROM orders
WHERE (created_at, id) > ('2025-01-01 00:00:00', 5000)
ORDER BY created_at, id
LIMIT 100;

query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)

cursor.execute("SELECT * FROM users WHERE username = %s;", (user_input,))

PREPARE get_balance AS
SELECT balance FROM accounts WHERE id = $1;

EXECUTE get_balance(1001);
EXECUTE get_balance(2002);

-- Avoid
SELECT * FROM users WHERE id = 101;

-- Preferred
SELECT name, email FROM users WHERE id = 101;

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id UUID,
  region STRING
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID,
  region STRING
);

CREATE INDEX orders_by_id_hash
ON orders (id)
USING HASH SHARDED WITH BUCKET_COUNT = 16;

CREATE TABLE sales (
  region_id STRING,
  order_id UUID DEFAULT gen_random_uuid(),
  PRIMARY KEY (region_id, order_id)
);

SET CLUSTER SETTING sql.defaults.require_explicit_primary_keys.enabled = true;

maximumPoolSize: (vCPUs * 4) / number_of_pool_instances
minimumIdle: equal to maximumPoolSize
maxLifetime: 30 min (add jitter +/- 5 min)
idleTimeout: 5-10 min typical
keepaliveTime: slightly shorter than infrastructure timeout (~5 min)
connectionTimeout: 10-30 s typical
autoCommit: true unless using explicit transactions only

maximum-pool-size: 12
minimum-idle: 12
max-lifetime: 1800000
idle-timeout: 600000
keepalive-time: 300000
connection-timeout: 10000
auto-commit: true
pool-name: ingestionPool

def handle_order(order):
    db.execute("INSERT INTO orders (id, status) VALUES (%s, %s)", (order.id, 'PENDING'))
    publish_event('process_order', {'order_id': order.id})

SET transaction_rows_read_err = 10000;
SET transaction_rows_written_err = 1000;

cockroach workload init bank 'postgresql://root@localhost:26257?sslmode=disable'
cockroach workload run bank --concurrency=64 --duration=10m