How SQL Server handles transactional consistency

SQL Server transactions let an application treat several statements as one unit of work. When money moves, stock changes and an audit record is written, a partial failure can leave the business in a state nobody intended. Transaction boundaries and isolation levels determine whether that state can occur and what concurrent users are allowed to see.

Understanding these controls requires more than memorising the names of isolation levels. It also means recognising how shared locks, row versions, update conflicts and deadlocks affect an application running in a busy Australian market.

Transactions define the unit of reliable work

A transaction groups database operations that must succeed or fail together. BEGIN TRANSACTION marks its starting point, while COMMIT TRANSACTION makes the changes permanent. ROLLBACK TRANSACTION discards uncommitted work and restores the database to its previous consistent state. Without an explicit boundary, each statement may commit independently, leaving only part of a business operation protected.

A production transaction should also handle errors. TRY...CATCH provides a place to roll back after a failed statement, while XACT_ABORT ON makes many runtime errors terminate the transaction automatically:

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.AccountBalance
    SET Balance = Balance - @Amount
    WHERE AccountId = @FromAccount;

    INSERT INTO dbo.TransferAudit
        (FromAccount, Amount, CreatedAtUtc)
    VALUES
        (@FromAccount, @Amount, SYSUTCDATETIME());

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH

SQL Server uses @@TRANCOUNT to track nested transaction requests. An inner COMMIT does not create a separately durable transaction; it reduces the count, while the outer transaction still controls the final commit. Savepoints can support partial rollback, although careless use can make recovery behaviour difficult to reason about. Keep transactions short and include only the statements required for the same consistency rule.

ACID guarantees describe observable behaviour

Atomicity guarantees that all changes within a transaction succeed or that none are retained. If a transfer debits an account but cannot insert its audit entry, rollback removes the debit. SQL Server uses transaction-log records and undo information to maintain that guarantee during normal operation and recovery.

Consistency means the committed data satisfies the rules defined by the application and database. Primary keys, foreign keys, unique indexes, check constraints and NOT NULL columns enforce part of that contract. The remaining rules, such as preventing an account from going below zero, often belong in application logic or carefully designed conditional updates.

Isolation determines what one transaction can observe while another is active. The selected level balances correctness against concurrency; it does not automatically make every business rule safe. A transaction can avoid reading uncommitted data and still create an invalid result when two users modify related records at the same time.

Durability means committed work remains available after the transaction completes. The transaction log provides the foundation for this protection, with the database recovery model, log flushing configuration and backup strategy determining how that durability is maintained.

Isolation levels control what concurrent transactions can see

SQL Server provides several isolation levels, each offering different guarantees and concurrency costs. The setting applies to the current session until it is changed:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

READ UNCOMMITTED permits dirty reads, including values that another transaction may later roll back. It reduces blocking but should be reserved for data that tolerates approximation, such as an optional display counter. READ COMMITTED prevents dirty reads and is SQL Server’s default locking behaviour. It can still produce non-repeatable reads because a second read may encounter data changed by another committed transaction.

REPEATABLE READ keeps shared locks on rows read by the transaction until it ends. It prevents another transaction from changing or deleting those rows, although inserted rows can still create phantom results. SERIALIZABLE adds key-range protection, preventing qualifying rows from being inserted into the range being read. This gives the strongest locking guarantee but increases blocking and the likelihood of deadlocks.

SNAPSHOT provides a transaction-level view of committed data. The snapshot is established when the transaction performs its first read, and subsequent reads continue to use that view. Updates can fail when the row being changed has changed since the snapshot was taken. Snapshot isolation must be enabled for the database with ALLOW_SNAPSHOT_ISOLATION, and it depends on row-version storage.

Locking and row versions explain the differences

Traditional SQL Server isolation uses shared, exclusive, update and intent locks. Under locking READ COMMITTED, a shared lock is released after a row has been read. This allows many readers but permits a writer to block a reader, or a reader to delay a writer. Indexes with useful access paths reduce the number and duration of these locks.

Databases configured with READ_COMMITTED_SNAPSHOT use row versioning for ordinary readers. A reader obtains the last committed version of a row from version storage instead of waiting for a shared lock. Writers still lock the current row, so blocking and update conflicts remain possible. Enabling the option can release substantial reader-writer blocking, but it changes application behaviour and increases work in tempdb.

Lock-based REPEATABLE READ retains shared locks, while SERIALIZABLE adds range locks over index keys. These protections can become expensive on large scans. Snapshot transactions avoid many locks, but they can still suffer from write skew, where two transactions each read an old state and then update different rows in a way that breaks a shared business invariant.

Deadlocks occur when two transactions wait for resources held by each other. SQL Server detects many deadlocks and rolls back one victim automatically, but applications should still handle that error. Short transactions, sensible indexes and consistent access orders reduce the risk. A finite LOCK_TIMEOUT can also prevent a request from waiting indefinitely.

Choose consistency to match the business rule

Start with the rule that must remain true, then choose the smallest level of protection that enforces it. A reporting screen may be satisfied by READ COMMITTED or snapshot behaviour. A process that reserves the final item in stock needs stronger protection, either through serialisable semantics, an atomic conditional update or an explicit concurrency token.

In a Sydney payments system, two requests must not spend the same available balance. An atomic statement can protect the debit without holding a broad serialisable transaction across several tables. A Melbourne marketplace can apply the same pattern to product inventory, combining a conditional update with an order-status check. These designs make the invariant visible in one operation instead of depending on timing.

Australian operational details also influence the design. Amounts may be stored and reported in AUD while transactions include 10% GST. Overnight batch windows can shift when daylight saving moves between AEST and AEDT, particularly for teams coordinating Perth with eastern states. Data handling must also respect the Privacy Act 1988 and the Australian Privacy Principles when customer records are read during reconciliation.

Optimistic concurrency is often preferable to escalating every transaction to SERIALIZABLE. It allows concurrent work to proceed and rejects only the operation that used stale data. Explicit isolation then protects a small critical section, while application validation handles rules that database constraints cannot express.

Patterns for safer application transactions

Keep the transaction close to the data access operation. Network calls, user-interface waits and remote API requests should occur before or after the transaction, not while database locks are being held. A service that opens a transaction, calls an external payment gateway and then updates SQL Server may hold locks for several seconds without gaining additional correctness.

Use concurrency tokens for update-heavy workflows. SQL Server’s ROWVERSION column changes whenever a row is modified, allowing an application to include the previously observed value in its update:

UPDATE dbo.Stock
SET Quantity = @NewQuantity,
    LastUpdatedUtc = SYSUTCDATETIME()
WHERE ProductId = @ProductId
  AND RowVersion = @ExpectedRowVersion;

IF @@ROWCOUNT = 0
    THROW 50001, 'The stock record changed before it could be updated.', 1;

The application can reload the row and retry when the conflict is safe to retry. Deadlock victims and transient failures may also need a bounded retry policy with fresh data. Retrying an entire transaction blindly can duplicate work unless each operation is idempotent or protected by a unique business identifier.

Test concurrency deliberately rather than relying on sequential execution. A useful test starts two transactions, pauses them at controlled points and verifies the final balances, quantities or audit rows. Database tooling such as Extended Events or SQL Server Profiler can reveal blocking chains, deadlocks and long-running transactions. Monitoring transaction duration and lock waits often identifies consistency problems before they appear as customer-facing errors.


Start with one important workflow and map every statement that must succeed together. Write down the business invariant, identify the exact rows involved and select an isolation level that protects it without creating unnecessary serialisation.

Open a test copy of the database, reproduce two concurrent attempts and confirm that the result remains correct. Record the chosen level, timeout, retry behaviour and concurrency strategy beside the service so future developers can preserve the same guarantees.