Understanding the NOLOCK Hint in SQL Server and Its MySQL Equivalent

Shortly after joining Hujiang, I was assigned to a project migrating the group’s SQL Server databases to MySQL and the .NET system to Java. I noticed that almost every legacy .NET SQL query used the keyword nolock , which puzzled me because the literal meaning suggests not using locks.

Example:

SELECT [id]
FROM   [dbo].[foos] WITH(nolock)
WHERE  aField = 42
       AND bField = 1

As a horizontal support engineer, developers asked whether we still need to use nolock after the migration to MySQL and what the MySQL equivalent would be. I had no production experience with SQL Server, so I researched and documented my findings here.

First question: What is nolock ?

nolock is a SQL Server table hint. Hints are directives that start with WITH and can modify the query optimizer’s behavior at runtime. Besides WITH(nolock) , common hints include TABLOCK , INDEX , and ROWLOCK .

According to MSDN, nolock is equivalent to the isolation level READUNCOMMITTED , which allows a query to read data that other transactions have not yet committed (dirty reads).

nolock functions the same as READUNCOMMITTED .

Isolation levels

SQL‑92 defines four isolation levels (from highest to lowest): Serializable, Repeatable Read, Read Committed, and Read Uncommitted. The purpose of isolation levels is to balance data consistency with performance by controlling locking behavior.

Serializable – locks the entire range of rows that satisfy the query.

Repeatable Read – prevents other transactions from modifying rows that have been read.

Read Committed – prevents dirty reads by acquiring shared locks on rows being read.

Read Uncommitted – allows dirty reads; no shared locks are taken.

Using nolock forces the query to operate at the Read Uncommitted level, which can improve performance but risks reading uncommitted (dirty) data.

Why use nolock ?

Developers often enable nolock to avoid lock contention, especially when a table is frequently inserted into while other sessions run full‑table scans. In the default Read Committed mode, an INSERT acquires an exclusive lock that can block concurrent SELECTs.

Tests showed that an uncommitted INSERT blocks a SELECT on the same table, confirming the need for nolock in such scenarios.

Testing nolock

Check the current isolation level (default is Read Committed):

SELECT CASE transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'ReadUncommitted'
 WHEN 2 THEN 'ReadCommitted'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

Create a test table and insert initial rows:

CREATE TABLE foos (
 id BIGINT NOT NULL,
 value NCHAR(10) NULL,
 CONSTRAINT pk PRIMARY KEY CLUSTERED (id)
);
INSERT INTO foos (id, value) VALUES (1, '1'), (2, '2');

Start a transaction that inserts a new row but does not commit:

BEGIN TRANSACTION;
INSERT INTO foos (id, value) VALUES (3, '3');

From another session, run a full‑table SELECT; the query is blocked until the first transaction commits.

MVCC

SQL Server introduced snapshot isolation (MVCC) after 2015, but tests showed that even with snapshot isolation enabled, INSERTs still block concurrent SELECTs on the same table, so nolock remains useful for read‑only workloads.

Do we still need nolock after SQL Server 2005?

Because INSERTs acquire exclusive locks that block full‑table scans, using nolock (or READUNCOMMITTED ) is still required in scenarios such as:

SSIS data extraction where exact data is not critical.

Historical data queries with no concurrent updates.

Microsoft has announced that future versions may deprecate NOLOCK in UPDATE/DELETE statements, recommending its removal.

What is the MySQL equivalent?

MySQL InnoDB uses MVCC and the innodb_autoinc_lock_mode setting to control auto‑increment locking. With innodb_autoinc_lock_mode = 1 (consecutive), INSERTs do not block concurrent SELECTs, so an explicit nolock hint is unnecessary.

Example to check isolation level and lock mode:

SELECT @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

Create a test table in MySQL:

CREATE TABLE `foos` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

Start a transaction that inserts a row, then from another session run a SELECT – the SELECT succeeds without being blocked. However, a second concurrent INSERT will be blocked, demonstrating that MySQL’s lock mode handles read‑write contention without needing a nolock hint.

Recap

Why use nolock ? To avoid lock contention and improve read performance at the cost of possible dirty reads.

Why change isolation levels? To balance consistency and performance based on workload requirements.

Why MySQL does not need an equivalent? Its MVCC implementation and innodb_autoinc_lock_mode already prevent read‑write blocking for most scenarios.

Using the “Five Whys” technique helped uncover the underlying reasons and led to a more informed best‑practice recommendation.

References

Transaction isolation – Wikipedia

Table Hints (Transact‑SQL) – Microsoft Docs

Snapshot Isolation in SQL Server – Microsoft Docs

sys.databases (Transact‑SQL) – Microsoft Docs

MySQL 5.7 Reference Manual – InnoDB Multi‑Versioning