Developer ToolsFree referencePDF, Excel, Word exportMulti-database

SQL Isolation Levels and Locking Behavior Reference

Reports that freeze postings, or postings that freeze reports, almost always come down to isolation level. This table compares what each level allows in the four common databases behind ERPs.

Download:

Engine	Isolation level	Default?	Dirty reads	Non-repeatable reads	Phantom rows	ERP note
SQL Server	READ UNCOMMITTED (NOLOCK)	No	Possible	Possible	Possible	Fast and non-blocking but can return uncommitted, duplicated or missing rows. Poor choice for financial or inventory reports.
SQL Server	READ COMMITTED (locking)	Yes (on-premises)	No	Possible	Possible	Readers take shared locks briefly, so long reports and posting jobs can block each other.
SQL Server	READ COMMITTED SNAPSHOT (RCSI)	Default in Azure SQL Database	No	Possible	Possible	Readers use row versions and do not block writers. Needs a database option and more tempdb or version store space. Test the ERP vendor support first.
SQL Server	REPEATABLE READ	No	No	No	Possible	Holds shared locks to the end of the transaction, which raises blocking.
SQL Server	SNAPSHOT	No	No	No	No	Transaction sees one consistent version of the data. Update conflicts raise errors that code must handle.
SQL Server	SERIALIZABLE	No	No	No	No	Uses range locks, so heavy blocking and deadlocks. Use only for short, specific logic.
Oracle	READ COMMITTED	Yes	No	Possible	Possible	Readers never block writers because of multiversion read consistency, so statement-level consistency is built in.
Oracle	SERIALIZABLE	No	No	No	No	Transaction-level snapshot. Updates to rows changed by others after the start fail with ORA-08177.
Oracle	READ ONLY	No	No	No	No	Read-only transaction with a consistent snapshot, useful for multi-query reports.
PostgreSQL	READ COMMITTED	Yes	No	Possible	Possible	Each statement sees data committed before it began. Readers do not block writers.
PostgreSQL	REPEATABLE READ	No	No	No	No	Snapshot for the whole transaction. Concurrent updates cause serialization failures that need a retry.
PostgreSQL	SERIALIZABLE	No	No	No	No	Serializable snapshot isolation. Applications must retry on serialization failure.
MySQL InnoDB	READ COMMITTED	No	No	Possible	Possible	Often chosen to reduce gap locking for write-heavy workloads.
MySQL InnoDB	REPEATABLE READ	Yes	No	No	Not for consistent reads, possible with locking reads	Uses next-key locks, which can cause unexpected lock waits on range updates.
MySQL InnoDB	SERIALIZABLE	No	No	No	No	Plain reads become locking reads, so expect more blocking.
MySQL InnoDB	READ UNCOMMITTED	No	Possible	Possible	Possible	Rarely appropriate for ERP data.

Behaviour summarised from vendor documentation. Defaults and options vary by version and by cloud service, so confirm with SELECT of the current setting on your instance before changing anything.

Optional blocking review

Describe the report and posting that collide and we will suggest what to test.

Optional. The tool and every export work without this. No spam.

How to use this table

Find your database engine, then read the row for the level your ERP session actually uses. On SQL Server check sys.databases for is_read_committed_snapshot_on and the session setting with DBCC USEROPTIONS. For Oracle and PostgreSQL the default read committed level already prevents readers from blocking writers, so blocking complaints usually come from long write transactions instead. Use the ERP note to decide whether a change is worth testing, and keep production changes behind a change request.

The three anomalies in plain words

A dirty read returns data another transaction has not committed yet, which could later roll back. A non-repeatable read means reading the same row twice in one transaction returns different values because someone changed it in between. A phantom means a second query over the same range returns extra or missing rows. Financial reports care most about dirty reads and about consistency across several queries. An open-orders list that tolerates small drift can accept less strict levels.

NOLOCK and read committed snapshot

Adding NOLOCK to ERP report queries is common on SQL Server and it is risky. It can skip or double count rows during page splits and read data that is later rolled back. Read committed snapshot isolation is the safer way to stop readers from blocking writers, at the price of version store storage and a short outage to enable it. Ask your ERP vendor whether it is supported, because some products rely on locking behaviour for their own concurrency control.

Common mistakes

Setting SERIALIZABLE globally to fix a race, enabling snapshot isolation without handling update conflict errors, and treating NOLOCK as free speed are the usual errors. Also remember that isolation settings do not fix long transactions: a posting that holds locks while a user thinks will block regardless of level. Keep transactions short and fetch outside them.

Frequently Asked Questions

Is NOLOCK safe for ERP reports?

Not for anything financial or inventory critical. It can return uncommitted data and skip or duplicate rows. Read committed snapshot is generally a safer way to stop reports blocking postings, if your vendor supports it.

Which isolation level is the default in Oracle?

Read committed. Because of multiversion read consistency, readers do not block writers, and each statement sees data committed as of its start.

What does ORA-08177 mean?

It is raised in Oracle serializable transactions when you try to change data that another transaction changed after yours began. The application must retry the transaction.

Does snapshot isolation use more storage?

Yes. SQL Server keeps row versions in tempdb, or the database version store with accelerated recovery, so long transactions can grow it. Monitor version store size after enabling.

If report blocking is hurting your ERP, we can help read the blocking chains and propose a tested change for your database.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools