Understanding Transactions and ACID Properties: The Complete Guide

A transaction is a group of SQL statements that succeed or fail together. Either every statement takes effect, or none do. It is the mechanism that keeps a multi-step change from leaving the database in an inconsistent state.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

That is a transfer of 500 from account 1 to account 2. Both updates happen, or neither does. If the second UPDATE fails — say, because account 2 does not exist — the first update is undone automatically. Account 1 does not lose the money with nothing to show for it.

That is why transactions matter. Real systems have thousands of multi-step operations — order placement, payment processing, inventory updates — where a partial result would be worse than no result at all. Transactions give you the guarantee that a step either fully completed or did not happen.

Under the hood, the database achieves this with logging and locking. Every change is written to a log before it is applied. If the transaction rolls back, the log is used to undo the changes. If it commits, the log is used to ensure the changes survive a crash.

ACID is the acronym for the four properties a transaction must guarantee. Every relational database worth trusting implements all four.

Atomicity — all statements in a transaction succeed or none do. If any single statement fails, the whole transaction rolls back. This is the "all or nothing" guarantee.

Consistency — a transaction moves the database from one valid state to another. Constraints — primary keys, foreign keys, unique constraints, check constraints — must hold before and after. A transaction that would violate any rule fails and rolls back.

Isolation — concurrent transactions do not see each other's uncommitted work. Whatever order they actually execute in, the visible outcome is the same as if they had run one after the other.

Durability — once a transaction commits, the changes are permanent. Even a crash immediately after COMMIT does not lose the data. The database achieves this by writing changes to a log on disk before COMMIT returns.

Together they make a database reliable. Without atomicity, a crash mid-transaction leaves partial changes. Without isolation, concurrent transactions see each other's dirty data. Without durability, committed changes vanish on power loss. The four properties cover the essential failure modes.

BEGIN TRANSACTION marks the start of a transaction. Every DML statement after it becomes part of the same unit of work until you either COMMIT or ROLLBACK.

BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_amount, order_status)
VALUES (1008, 'C001', 15000, 'Pending');
COMMIT;

That inserts one order. Without the BEGIN/COMMIT pair, the insert happens in autocommit mode — the statement commits the moment it succeeds, and there is no window to roll it back.

The exact keyword varies by database. SQL Server and PostgreSQL use BEGIN TRANSACTION or just BEGIN. MySQL uses START TRANSACTION. Oracle does not require an explicit BEGIN at all — the first DML statement starts a transaction automatically.

Most databases default to autocommit on, which is why explicit transactions matter. If you want multiple statements to succeed or fail together, you have to wrap them in a transaction. Without the wrapper, each statement commits on its own and there is no way to undo an earlier one when a later one fails.

COMMIT makes every change in the current transaction permanent. After it runs, the changes are visible to other sessions and cannot be rolled back.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

The two updates are now part of the database. Other users see the new balances. The transaction log has the changes on disk. There is no undo.

Before COMMIT, the changes exist only in your session. If another user queries accounts, they see the old values. You can still ROLLBACK. Once COMMIT runs, that window closes.

When to commit: at the end of a logical unit of work, when everything has succeeded and every constraint is satisfied. When not to commit: mid-way through a multi-step operation that could still fail. If a downstream step fails, rolling back is only possible if you have not already committed the earlier steps.

The one anti-pattern to avoid: committing inside a loop. Every COMMIT has real cost — flushing to disk, updating the log. A loop that commits after every row on a million-row operation runs a million commits. Batch the changes and commit once at the end, or in chunks if the batch is very large.

ROLLBACK undoes every uncommitted change in the current transaction. It is the undo button for the duration of the transaction — everything goes back to how it was before BEGIN.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
ROLLBACK;

The two updates are undone. Account 1 has its original balance. Account 2 has its original balance. Nothing changed.

The typical use is error handling. If a statement fails, or a business rule is violated, or the user cancels, you roll back. The database returns to the state it was in before the transaction started.

What gets rolled back: INSERT, UPDATE, DELETE, and MERGE. What does not: DDL statements (they auto-commit on most databases), and sequence values (once NEXTVAL is called, the value is consumed even if you roll back).

One important caveat: autocommit mode makes ROLLBACK useless. If every statement commits itself immediately, there is nothing to roll back. This is why you have to explicitly start a transaction — to have a window where ROLLBACK is meaningful.

SAVEPOINT marks a point inside a transaction you can return to with ROLLBACK TO SAVEPOINT. It lets you undo part of a transaction without discarding the whole thing.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
SAVEPOINT after_credit;

UPDATE audit_log SET note = 'Transfer complete' WHERE transfer_id = 100;
-- Realise the audit update is wrong
ROLLBACK TO SAVEPOINT after_credit;

-- Fix it
UPDATE audit_log SET note = 'Transfer of 500 complete' WHERE transfer_id = 100;
COMMIT;

The final COMMIT keeps the two account updates and the corrected audit update. The first audit update was discarded by the partial rollback.

What SAVEPOINT does not do: it does not commit the work before it (a final COMMIT is still required), and it does not release locks (locks are held until the transaction ends).

When to use SAVEPOINTs: long operations with independent phases. A batch import that processes rows in chunks — one SAVEPOINT per chunk so a bad chunk can be rolled back without losing the earlier chunks. A multi-step workflow where one step is optional and might fail without aborting the rest.

Support is universal — Oracle, PostgreSQL, MySQL, and SQL Server all support SAVEPOINT. SQL Server uses slightly different wording (SAVE TRANSACTION) but the behaviour is the same.

Isolation levels control how much one transaction can see of another's uncommitted work. Stronger isolation means fewer concurrency anomalies but more blocking. Weaker isolation means faster concurrency but more chances of reading inconsistent data.

The four levels are:

• READ UNCOMMITTED — a transaction can see another transaction's uncommitted changes. Called "dirty reads".
• READ COMMITTED — a transaction only sees committed changes. Prevents dirty reads. But the same query run twice can return different values if another transaction committed in between (non-repeatable read).
• REPEATABLE READ — a transaction sees a consistent snapshot of the data from the start of the transaction. The same query returns the same rows every time. But new rows can still appear (phantom read).
• SERIALIZABLE — the strictest level. Every transaction behaves as if it ran completely before or after every other transaction. Prevents all three anomalies, at the cost of concurrency.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- ...
COMMIT;

The default in most databases is READ COMMITTED. Oracle, PostgreSQL, and SQL Server use it by default. MySQL defaults to REPEATABLE READ.

The choice matters most in multi-user environments. A financial application that checks balances before making transfers usually wants REPEATABLE READ or SERIALIZABLE. A read-heavy analytics workload might be fine with READ COMMITTED or even READ UNCOMMITTED.

A lock is a mechanism — a specific resource the database prevents other transactions from touching. An isolation level is a policy — a rule that decides which locks the database takes on your behalf.

When you set an isolation level, the database then takes locks automatically to enforce that level. You rarely write locks explicitly. The database decides when to take a shared lock for a read or an exclusive lock for a write, based on the isolation level and the operation.

-- Setting the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Explicit lock on specific rows
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

The first statement sets the policy. The second takes a specific lock. Both are parts of the same toolkit — one is the automatic level, the other is manual control.

When you actually need to write a lock explicitly: read-then-write patterns where you want to make sure the value does not change between the read and the write. SELECT ... FOR UPDATE takes an exclusive lock and prevents another transaction from updating those rows until you commit.

Most applications never set an isolation level or write a lock. They use the database default and let the automatic locking do its job. When they do need more control, understanding the two concepts separately makes the choice easier.

Durability guarantees that once a transaction commits, the changes are permanent even if the system crashes immediately after. The database achieves this by writing the changes to a transaction log on disk before COMMIT returns.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
COMMIT;   -- returns only after the change is safely on disk

The sequence inside COMMIT: the changes are written to the log, the log is flushed to disk, and only then does COMMIT return success. The actual data files may not be updated yet — that happens later in the background. But the log has everything needed to reconstruct the changes.

If the server crashes right after COMMIT, the log is on disk. On restart, the database reads the log and replays the committed transactions that had not yet made it into the data files. The changes are not lost.

The flush is the reason COMMIT is not instant. Fast SSDs make it fast — often under a millisecond — but the cost is real. On a system committing thousands of transactions per second, the cost adds up.

Some databases let you loosen durability for speed. PostgreSQL has synchronous_commit = off. MySQL has innodb_flush_log_at_trx_commit = 2. Those settings trade durability for throughput — committed changes might be lost if the server crashes in a small window. Acceptable for logs and metrics; not for financial data.

Six habits cover most of what matters. None of them are complicated, and together they prevent the majority of transactional bugs.

Keep transactions short. The longer a transaction runs, the longer it holds locks and the more it blocks other work. Anything that does not need to be inside the transaction — file reads, API calls, user interaction — should happen before BEGIN.

Choose the right isolation level. Default to the database default. Override only when you have a specific reason. Serializable for financial correctness, read-committed for most workloads.

Always handle errors and roll back on failure. A failed statement leaves the transaction open and holding locks. Any code path that might fail needs a rollback.

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    -- Log the error
END CATCH;

Do not wait for user input inside a transaction. A transaction waiting on a user click holds locks the whole time. If the user goes to lunch, the transaction stays open, blocking other work. Collect input first, then start the transaction.

Use SAVEPOINTs for long operations with independent phases. When a long transaction has several distinct steps and one might fail, SAVEPOINTs let you roll back just the failed part without losing the earlier work.

Monitor long-running transactions. Every production database exposes open transactions through a system view. Check regularly for transactions that have been open for minutes or hours — those are usually bugs where a transaction was started and never committed or rolled back.

A deadlock happens when two transactions each hold a lock the other needs. Neither can proceed, and both wait forever. Something has to break the cycle, and the database does it automatically.

-- Transaction A
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Now waiting for account 2

-- Transaction B (concurrent)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
-- Now waiting for account 1

-- A holds 1, wants 2. B holds 2, wants 1. Deadlock.

The database runs a deadlock detector — a background process that scans the lock graph looking for cycles. When it finds one, it picks a victim transaction (usually the one with the least work done) and rolls it back. The victim gets an error; the other transaction proceeds as if nothing happened.

Common error messages: "deadlock detected" (PostgreSQL), "transaction was deadlocked" (SQL Server), "ORA-00060: deadlock detected while waiting for resource" (Oracle).

Preventing deadlocks: access resources in the same order across transactions (always update account 1 before account 2), keep transactions short, use lower isolation levels where possible.

Handling deadlocks in application code: catch the error and retry. A small random delay between retries prevents two transactions from deadlocking again the same way.

-- Pseudocode for a retry loop
attempt = 0
while attempt < 3:
    try:
        BEGIN TRANSACTION;
        -- do work
        COMMIT;
        break;
    except DeadlockError:
        ROLLBACK;
        attempt += 1
        if attempt == 3: raise
        sleep(random_delay)

Deadlocks are not a bug in your query — they are a fact of concurrent access. Every production database has them. What matters is that the application knows how to handle them gracefully.

Three specific anomalies the isolation levels are designed to prevent. Every interview that covers transactions asks about them.

Dirty read. Transaction A reads data that Transaction B has written but not committed. If B then rolls back, A has read data that never existed. This is the worst anomaly because it produces data that is simply wrong.

-- Transaction B
BEGIN TRANSACTION;
UPDATE accounts SET balance = 6000 WHERE account_id = 1;
-- not committed yet

-- Transaction A (concurrent)
SELECT balance FROM accounts WHERE account_id = 1;
-- With READ UNCOMMITTED: sees 6000 (dirty read)
-- With READ COMMITTED or higher: sees the old value

-- Transaction B
ROLLBACK;
-- The 6000 never existed

Non-repeatable read. Transaction A reads a row, Transaction B updates and commits it, Transaction A reads the same row again and gets a different value. The two reads in the same transaction disagree.

-- Transaction A
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;   -- reads 10000

-- Transaction B (concurrent)
BEGIN TRANSACTION;
UPDATE accounts SET balance = 6000 WHERE account_id = 1;
COMMIT;

-- Transaction A
SELECT balance FROM accounts WHERE account_id = 1;   -- reads 6000
-- Two reads, different values — non-repeatable read

Phantom read. Transaction A runs a query that returns five rows, Transaction B inserts a row that matches the same filter and commits, Transaction A runs the query again and gets six rows. A new row has appeared "out of nowhere".

-- Transaction A
BEGIN TRANSACTION;
SELECT COUNT(*) FROM orders WHERE order_status = 'Pending';   -- returns 5

-- Transaction B (concurrent)
INSERT INTO orders (order_id, order_status) VALUES (9999, 'Pending');
COMMIT;

-- Transaction A
SELECT COUNT(*) FROM orders WHERE order_status = 'Pending';   -- returns 6
-- A new row appeared — phantom read

The isolation levels prevent these anomalies in a specific order. READ COMMITTED prevents dirty reads. REPEATABLE READ prevents dirty reads and non-repeatable reads. SERIALIZABLE prevents all three. Higher isolation means fewer anomalies but more blocking.

A lock is a mechanism the database uses to enforce isolation. Different types of locks allow different levels of concurrent access.

Shared lock (S) — taken for reads. Multiple transactions can hold shared locks on the same row, and any of them can read. But a shared lock blocks any exclusive lock on the same row, which means it prevents writes.

Exclusive lock (X) — taken for writes. Only one transaction can hold it, and it blocks both reads and writes from other transactions. When you UPDATE a row, the database takes an exclusive lock on that row until you commit or roll back.

Update lock (U) — an intermediate mode used when a transaction intends to update a row but has not yet. It is a common in-between state to prevent two transactions from both holding a shared lock and then both trying to upgrade to exclusive, which would deadlock.

Locks can be taken at different granularities. Row-level locks are the most granular and give the best concurrency. Page-level and table-level locks cover more data but are cheaper to track. The database chooses based on the operation.

-- Explicit exclusive lock for a specific row
BEGIN TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Now no other transaction can modify account 1
-- Update, then commit
COMMIT;

Locks are held until the transaction ends. Commit or rollback releases them. That is why short transactions are important — a long-running transaction holds locks that block everyone else.

When you see a query blocked for a long time, the reason is almost always a lock held by another session. Finding and resolving that session is the standard troubleshooting step.

MVCC stands for Multiversion Concurrency Control. It is the technique modern databases use to let readers and writers work without blocking each other.

Without MVCC, a reader would need to take a shared lock on every row it reads. That lock would block any writer trying to update the row, and the reader would hold the lock for the duration of the transaction. Long reads block writes.

MVCC solves this by keeping multiple versions of each row. When a transaction reads, it sees the version that was current when the transaction started. When a writer updates a row, it creates a new version rather than overwriting the old one. The old version remains visible to transactions that started before the update.

-- Transaction A (started at time T1)
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;   -- sees version at T1

-- Transaction B (started at time T2 > T1)
BEGIN TRANSACTION;
UPDATE accounts SET balance = 6000 WHERE account_id = 1;
COMMIT;

-- Transaction A
SELECT balance FROM accounts WHERE account_id = 1;   -- still sees version at T1
COMMIT;

Transaction A sees the same value twice because MVCC gives it a consistent snapshot. Transaction B's update does not block A's read, and A's read does not block B's write. Both run at the same time without interfering.

MVCC is used by PostgreSQL, Oracle, MySQL (InnoDB storage engine), and SQL Server (with snapshot isolation enabled). It is the main reason modern databases handle concurrency much better than older designs.

The cost is storage. Old versions of rows have to be kept until no transaction needs to see them. Databases run background "vacuum" processes to clean up old versions. On a heavily-updated table, the cleanup has to keep up or the storage grows.

Usually not. Most databases auto-commit DDL statements — CREATE, ALTER, DROP, TRUNCATE. Once they run, they are permanent and cannot be rolled back as part of the transaction that issued them.

BEGIN TRANSACTION;
UPDATE accounts SET balance = 0 WHERE account_id = 1;   -- DML, can be rolled back
DROP TABLE accounts;                                     -- DDL, auto-committed
ROLLBACK;   -- undoes the UPDATE, but the DROP already happened

After that transaction, the UPDATE is rolled back, but the table is still dropped. The ROLLBACK does not undo the DDL.

The exceptions are PostgreSQL and some configurations of other databases. PostgreSQL supports transactional DDL — you can wrap CREATE TABLE, ALTER TABLE, and DROP TABLE in a transaction and roll them back. That is a real advantage for migrations, where a failed DDL can leave the schema in an inconsistent state on other databases.

-- PostgreSQL: this works
BEGIN;
CREATE TABLE new_feature (...);
-- Something goes wrong
ROLLBACK;
-- The new table is gone

On Oracle, SQL Server, and MySQL, the same thing does not work. The CREATE TABLE commits immediately, and the ROLLBACK only undoes the DML. This is one of the reasons database migration tools are careful about DDL — they know it cannot be undone and handle it accordingly.

The practical implication for developers: be extra careful with DDL statements in production. A DROP TABLE inside a transaction on Oracle does not become safely reversible just because a transaction is open. The drop commits itself.

A distributed transaction spans multiple databases or multiple resources — say updating a row in database A and a row in database B as one atomic unit. It is harder than a local transaction because the resources are independent and no single process owns both.

The standard protocol is two-phase commit (2PC). One coordinator process manages the transaction across resources. It works in two phases:

• Prepare phase. The coordinator asks each resource to prepare to commit. Each resource locks the resources it needs and writes the changes to its log but does not commit. It responds with "ready" or "abort".
• Commit phase. If every resource responded "ready", the coordinator tells all of them to commit. If any resource responded "abort", the coordinator tells all of them to roll back.

-- Conceptual two-phase commit
Coordinator: PREPARE to Database A
Database A: READY (locked, logged, not committed)
Coordinator: PREPARE to Database B
Database B: READY (locked, logged, not committed)
Coordinator: COMMIT to Database A
Database A: committed
Coordinator: COMMIT to Database B
Database B: committed

Two-phase commit gives distributed transactions ACID properties in the normal case. The problem is when the coordinator crashes after asking for prepare but before sending commit. The resources are stuck — they have the work logged and locked but do not know whether to commit or abort. The transaction waits until the coordinator recovers.

That is the practical cost of distributed transactions. They are slower, more fragile, and often avoided in modern architectures in favour of sagas — a pattern where each step has a compensating action that undoes it if a later step fails.

Real-world distributed transactions appear in Oracle's distributed database features, XA transactions in Java, and MSDTC on Windows. Modern microservices architectures usually avoid them and use eventual consistency with sagas instead.

When a session disconnects — network drop, application crash, connection killed by an administrator — the transaction is rolled back automatically. Whatever work the session had done but not committed is discarded.

The database detects the disconnect, rolls back any open transactions that session had, and releases the locks. Other sessions that were waiting on those locks proceed as if the transaction had never started.

That behaviour is what makes long transactions dangerous on unreliable networks. A transaction that has been running for thirty minutes and holds thousands of locks can lose all that work with a single dropped connection. On a network that occasionally drops connections, this is a real risk.

Practical mitigations. First, keep transactions short — the shorter they are, the less work is lost on disconnect. Second, use SAVEPOINTs for long operations to reduce the blast radius of a failure. Third, use explicit connection management with reconnection logic so a transient network issue does not kill a large batch.

Two-phase commit and distributed transactions have similar issues. If the coordinator disconnects mid-transaction, resources can be left in a prepared but not committed state until the coordinator recovers.

From the application's perspective, a disconnect that rolls back work looks the same as an explicit ROLLBACK. The client sees an error, the database is back to its pre-transaction state, and the application can retry.

A session is a connection to the database. A transaction is a unit of work inside a session. One session can have many transactions over its lifetime — one after another — but a session has at most one open transaction at a time.

-- One session, three transactions
BEGIN TRANSACTION;
INSERT INTO customers (...) VALUES (...);
COMMIT;

BEGIN TRANSACTION;
UPDATE customers SET ... WHERE ...;
COMMIT;

BEGIN TRANSACTION;
DELETE FROM customers WHERE ...;
COMMIT;

That is one session running three transactions sequentially. The session holds the connection; the transactions are the work done through it.

Session-level settings apply for the life of the connection. If you set the isolation level in a session, it applies to every transaction in that session. If you set a session variable, it persists until the connection closes.

Transaction-level settings apply only for that one transaction. You can override the session's isolation level for a single transaction without affecting the rest of the session.

-- Set session default
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Override for one transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- ...
COMMIT;

The distinction matters when tuning. Session-level settings affect every query and transaction that runs through the connection. Transaction-level settings affect one unit of work. For a connection pool where sessions are reused across many operations, session-level settings should be set carefully — they can affect unrelated code that happens to use the same connection.

A long-running transaction is any transaction that stays open for minutes or hours. It usually indicates a bug — a transaction was started and never committed or rolled back. Sometimes it is intentional — a long batch job. Either way, it causes problems.

The main problem is locks. Every write in a transaction holds an exclusive lock until the transaction ends. A long-running transaction that has updated a few thousand rows holds locks on all of them for the whole duration. Any other session trying to update those rows blocks. The blocking cascades — session B blocks on A's lock, session C blocks on B, and soon the whole system is waiting.

The second problem is undo storage. In databases that use MVCC, old versions of rows have to be kept until no transaction needs to see them. A long-running transaction forces old versions to be retained for the entire duration. On a high-churn table, that can grow the storage dramatically.

Third, MVCC cleanup stalls. PostgreSQL's VACUUM process, MySQL's purge thread, and equivalent processes in other databases cannot clean up old row versions while a long-running transaction still needs them. The database accumulates garbage that would normally be cleaned up.

To find long-running transactions, every database exposes them through a system view:

-- PostgreSQL
SELECT pid, state, query, now() - xact_start AS duration
FROM   pg_stat_activity
WHERE  state != 'idle'
ORDER BY duration DESC;

When you find one that should not be running, the standard response is to kill the session. That rolls the transaction back and releases the locks. The application that owned the session sees an error and can retry.

Testing transactions is trickier than testing most SQL because the interesting cases involve concurrent sessions. Three techniques cover most scenarios.

Single-session testing for atomicity. Open a transaction, run your statements, then deliberately cause a failure (raise an error, violate a constraint, disconnect the session). Verify that the database is unchanged after the failure.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Deliberately cause a failure
INSERT INTO accounts (account_id, holder_name, balance)
VALUES (1, 'Duplicate', 0);   -- primary key violation
ROLLBACK;
-- Verify: account 1's balance is unchanged

Two-session testing for isolation. Open two connections to the same database. From session A, start a transaction and update a row without committing. From session B, try to read or update the same row. The behaviour you see (blocking, seeing the old value, seeing the new value) tells you how the isolation level is working.

-- Session A
BEGIN TRANSACTION;
UPDATE accounts SET balance = 6000 WHERE account_id = 1;
-- Don't commit yet

-- Session B (concurrent)
SELECT balance FROM accounts WHERE account_id = 1;
-- Under READ COMMITTED: sees the old value
-- Under READ UNCOMMITTED: sees 6000
-- Blocks if a lock is held and isolation requires it

Load testing for deadlocks. Deadlocks only appear under real concurrency. To test them, run many concurrent sessions doing the same kind of transaction and see whether deadlocks appear in the database log. Fixing deadlocks usually means changing the order of access or adding retry logic, and neither shows up without real load.

Automated test frameworks for transactions are limited. Most teams write custom scripts that open multiple connections and exercise the concurrency cases directly. The important thing is to have those tests, because the bugs they catch (deadlocks, isolation violations, lost updates) are almost invisible in single-user testing.