ACID Guarantees
Full ACID compliance across all data models with strict serializable isolation
Overview
entropyDB provides full ACID (Atomicity, Consistency, Isolation, Durability) guarantees for all operations, even across multiple data models. This ensures data integrity and consistency in all scenarios.
Atomicity
All operations in a transaction complete successfully or none do. No partial updates.
Consistency
Database maintains all defined rules, constraints, and triggers at all times.
Isolation
Concurrent transactions don't interfere with each other. Serializable by default.
Durability
Committed data survives crashes and power failures through write-ahead logging.
Isolation Levels
entropyDB supports all standard SQL isolation levels, with Serializable as the default.
-- Set isolation level for session SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Set isolation level for single transaction BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Your queries here COMMIT; -- Available levels: -- READ UNCOMMITTED (not recommended) -- READ COMMITTED -- REPEATABLE READ -- SERIALIZABLE (default)
READ UNCOMMITTED
Allows dirty reads. Not recommended for production use.
⚠️ May read uncommitted changes from other transactions
READ COMMITTED
Prevents dirty reads. Good for most applications.
⚠️ May see different data in repeated reads (non-repeatable reads)
REPEATABLE READ
Prevents non-repeatable reads. Same snapshot throughout transaction.
⚠️ May see phantom rows (new rows appearing in range queries)
SERIALIZABLE (Default)
Strongest isolation. Prevents all anomalies.
✓ Full isolation. Transactions appear to execute serially.
Transaction Examples
Basic Transaction
-- Start transaction
BEGIN;
-- Transfer money between accounts
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Insert audit log
INSERT INTO audit_log (action, user_id, timestamp)
VALUES ('transfer', 1, NOW());
-- Commit if everything succeeded
COMMIT;
-- Or rollback on error
-- ROLLBACK;Savepoints
BEGIN; -- Initial operations UPDATE users SET last_login = NOW() WHERE id = 1; -- Create savepoint SAVEPOINT sp1; -- More operations INSERT INTO orders (user_id, total) VALUES (1, 99.99); UPDATE inventory SET stock = stock - 1 WHERE product_id = 5; -- If something goes wrong, rollback to savepoint -- ROLLBACK TO SAVEPOINT sp1; -- Or release savepoint and continue RELEASE SAVEPOINT sp1; COMMIT;
Optimistic Locking
-- Using version numbers for optimistic concurrency
BEGIN;
-- Read current version
SELECT * FROM documents WHERE id = 1 FOR UPDATE;
-- Result: id=1, content='...', version=5
-- Update with version check
UPDATE documents
SET content = 'new content',
version = version + 1,
updated_at = NOW()
WHERE id = 1 AND version = 5;
-- Check if update succeeded
-- If 0 rows affected, another transaction updated it first
-- Application should retry
COMMIT;Pessimistic Locking
BEGIN; -- Lock rows for update (blocks other transactions) SELECT * FROM inventory WHERE product_id = 5 FOR UPDATE; -- Exclusive lock acquired, safe to update UPDATE inventory SET stock = stock - 1 WHERE product_id = 5 AND stock > 0; COMMIT; -- Releases lock -- Other lock modes: -- FOR UPDATE: Exclusive lock for updating -- FOR SHARE: Shared lock for reading (blocks updates) -- FOR NO KEY UPDATE: Less restrictive update lock -- SKIP LOCKED: Skip locked rows instead of waiting -- NOWAIT: Fail immediately if lock unavailable
Consistency Checks
Constraints
-- Primary key ensures uniqueness CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL ); -- Foreign key ensures referential integrity CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT NOT NULL REFERENCES users(id), total DECIMAL(10,2) CHECK (total >= 0) ); -- Complex constraints ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= overdraft_limit); -- Deferred constraints (checked at commit) CREATE TABLE parent ( id INT PRIMARY KEY DEFERRABLE INITIALLY DEFERRED );
Triggers for Consistency
-- Maintain aggregate consistency
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
UPDATE orders
SET total = (
SELECT SUM(quantity * price)
FROM order_items
WHERE order_id = NEW.order_id
)
WHERE id = NEW.order_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total();Durability
entropyDB ensures durability through write-ahead logging (WAL) and configurable sync modes.
-- Synchronous commit modes SET synchronous_commit = on; -- Wait for WAL to be written to disk (default) -- Options: -- on: Maximum durability (default) -- remote_apply: Wait for replication -- remote_write: Wait for replica to write WAL -- local: Wait for local WAL write only -- off: Don't wait (faster but may lose recent commits on crash)
Recovery Process
- After crash, WAL is replayed automatically
- All committed transactions are recovered
- Uncommitted transactions are rolled back
- Database returns to consistent state
Deadlock Handling
entropyDB automatically detects and resolves deadlocks by aborting one transaction.
-- Example: Two transactions waiting for each other -- Transaction 1: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Now waiting for lock on id = 2... UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- Transaction 2 (concurrent): BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- Now waiting for lock on id = 1... DEADLOCK! UPDATE accounts SET balance = balance + 50 WHERE id = 1; COMMIT; -- entropyDB will abort one transaction with error: -- ERROR: deadlock detected -- Application should retry the aborted transaction
Preventing Deadlocks
- Always acquire locks in the same order
- Keep transactions short
- Use appropriate isolation levels
- Consider SKIP LOCKED or NOWAIT for high contention
Best Practices
1. Use Transactions for Multi-Step Operations
Wrap related operations in BEGIN/COMMIT to ensure atomicity.
2. Keep Transactions Short
Long transactions hold locks and increase contention. Do heavy computation outside transactions.
3. Handle Serialization Failures
In SERIALIZABLE mode, retry transactions that fail with serialization errors.
4. Use Read Committed for Read-Heavy Workloads
SERIALIZABLE adds overhead. Use READ COMMITTED if you don't need strict isolation.
5. Always Handle Errors
Wrap transactions in try/catch and rollback on errors to avoid leaving transactions open.