ACID Transactions in Databases: A Data Engineer's Guide

Code Example in DuckDB

Copy code
-- First, let's create a sample Accounts table if it doesn't exist
CREATE TABLE IF NOT EXISTS Accounts (
    AccountID INTEGER PRIMARY KEY,
    Balance DECIMAL(10,2)
);

-- Insert sample data if table is empty
INSERT OR IGNORE INTO Accounts VALUES
    (1, 500.00),
    (2, 300.00);

-- Check balances before transfer
SELECT * FROM Accounts WHERE AccountID IN (1, 2);

-- Start the transaction
BEGIN TRANSACTION;

-- Step 1: Debit Account A
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Step 2: Credit Account B
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

-- Commit the transaction to make changes permanent
COMMIT;

-- Check balances after transfer
SELECT * FROM Accounts WHERE AccountID IN (1, 2);

-- Note: If you need to cancel the transaction, you would use:
-- ROLLBACK;
-- instead of COMMIT;

Code Explanation

• BEGIN TRANSACTION; marks the start of our atomic unit.
• The first UPDATE statement debits money from Account A. The second UPDATE statement credits money to Account B.
• COMMIT; finalizes the transaction, making the changes permanent if both operations succeeded.
• ROLLBACK; (when needed) undoes all changes if any error occurs between BEGIN and COMMIT.

Practical Tip: In production systems, it's typical to wrap these operations in TRY...CATCH blocks (or the equivalent in the chosen language/framework) to automatically trigger a ROLLBACK if any error occurs within the transaction.

Code Example (Adding Constraints in DuckDB)

Copy code
-- Example showing constraints in DuckDB
-- Important: DuckDB does not support ADD CONSTRAINT and DROP CONSTRAINT in ALTER TABLE statements

-- Since we can't add constraints after table creation, we need to create the table with all constraints
DROP TABLE IF EXISTS Accounts;

-- Create the table with the CHECK constraint included from the beginning
CREATE TABLE Accounts (
    AccountID INTEGER PRIMARY KEY,
    Balance DECIMAL(10,2) CHECK (Balance >= 0)  -- Constraint defined at table creation time
);

-- Insert sample data
INSERT INTO Accounts VALUES
    (1, 100.00),  -- Account 1 has exactly $100
    (2, 300.00);

-- Show initial account balances
SELECT * FROM Accounts WHERE AccountID IN (1, 2);

-- Now, let's try a transaction that violates this consistency rule
BEGIN TRANSACTION;

-- Try to debit $150 from Account A which only has $100
-- This will fail due to the CHECK constraint
UPDATE Accounts
SET Balance = Balance - 150
WHERE AccountID = 1;

-- The following line won't execute if the UPDATE fails
-- but we include it for completeness
SELECT 'After update attempt (this may not run if constraint fails)' AS Status,
       * FROM Accounts WHERE AccountID = 1;

-- This COMMIT won't happen automatically if the constraint fails
-- DuckDB will roll back automatically on constraint violation
COMMIT;

-- Check final account balances - should be unchanged due to rollback
SELECT 'Final balances after constraint violation' AS Status,
       * FROM Accounts WHERE AccountID IN (1, 2);

-- Let's try a valid transaction now
BEGIN TRANSACTION;

-- Withdraw just $50 instead (which is valid)
UPDATE Accounts
SET Balance = Balance - 50
WHERE AccountID = 1;

-- Show the pending change
SELECT 'Valid update in progress' AS Status,
       * FROM Accounts WHERE AccountID = 1;

-- Commit the valid transaction
COMMIT;

-- Show final state after valid transaction
SELECT 'Final balances after valid transaction' AS Status,
       * FROM Accounts WHERE AccountID IN (1, 2);

Code Explanation

• CREATE TABLE ... CHECK ... defines a rule at the database level when the table is created (unlike some other databases, DuckDB requires constraints to be defined at creation time, not with ALTER TABLE).
• Our TRANSACTION attempts an operation that would result in a negative balance.
• The database engine enforces the CHECK constraint, causing the UPDATE statement to fail.
• Since the transaction cannot complete while satisfying all constraints, it is automatically rolled back by DuckDB.
• A subsequent transaction with a valid withdrawal amount succeeds because it maintains the constraint.

Code Example (Isolation in DuckDB)

Copy code
-- Drop and recreate the Accounts table
DROP TABLE IF EXISTS Accounts;

CREATE TABLE Accounts (
    AccountID INTEGER PRIMARY KEY,
    Balance DECIMAL(10,2) CHECK (Balance >= 0)
);

-- Insert sample data
INSERT INTO Accounts VALUES
    (1, 100.00),
    (2, 300.00);

-- DuckDB uses snapshot isolation by default, which is equivalent to SERIALIZABLE
-- and provides the highest isolation level
-- Start a transaction
BEGIN TRANSACTION;

-- With DuckDB's snapshot isolation, if you read rows,
-- you'll get a consistent snapshot of the database at the start of the transaction
-- regardless of other concurrent transactions
-- Take a snapshot of the total balance at the beginning of the transaction
SELECT 'Initial snapshot of total balance' AS QueryInfo, SUM(Balance) AS TotalBalance
FROM Accounts;

-- Some other operations...
-- Let's simulate some time passing by doing another operation
SELECT COUNT(*) AS TotalAccounts FROM Accounts;

-- Rerun the same query - guaranteed to get the same result
-- even if another transaction added or modified rows in between (due to snapshot)
SELECT 'Second snapshot (should match initial)' AS QueryInfo, SUM(Balance) AS TotalBalance
FROM Accounts;

-- Commit the transaction
COMMIT;

Code Explanation

• In DuckDB, transaction isolation is handled automatically with snapshot isolation (equivalent to SERIALIZABLE in the SQL standard), so there's no need to explicitly set isolation levels.
• When a transaction begins with BEGIN TRANSACTION, DuckDB creates a consistent snapshot of the database that remains stable throughout the transaction's lifetime.
• This snapshot isolation guarantees that all reads within the transaction see a consistent view of the database as it existed at the start of the transaction, regardless of changes made by other concurrent transactions.
• The database enforces these isolation rules automatically to prevent concurrency issues while providing the highest level of transaction integrity.

Code Example

Copy code
BEGIN TRANSACTION;

-- Perform critical updates
-- Deduct $50 from Account 1
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;

-- Add $50 to Account 2
UPDATE Accounts SET Balance = Balance + 50.00 WHERE AccountID = 2;

-- This is the point of no return.
-- Once COMMIT succeeds, the changes are durable.
COMMIT TRANSACTION;

-- If the server crashes after this COMMIT returns success,
-- the changes are guaranteed to persist through recovery.

Code Explanation

• The COMMIT TRANSACTION signals the successful end of the transaction and ensures that all updates — such as transferring funds between accounts — are made durable.
• DuckDB, like other ACID-compliant systems, handles the underlying durability mechanisms (e.g., write-ahead logging) automatically. Once COMMIT completes, the changes are safely persisted and will survive a crash or restart.