Using WITH (NOLOCK)

Epicor ERP 10
1

When writing a query in BAQ, Epicor always uses the WITH (NOLOCK) table hint.

Is this a good practice to do if writing a SELECT query using SSMS?

I know some developers are lazy and don’t want to type the extra characters. Are there any good reasons not to use it, especially as it relates to the Epicor databases?

2

Meh, Kinda I don’t particularly like it, you can read dirty (uncommitted data that way)

" The default behaviour in SQL Server is for every query to acquire its own shared lock prior to reading data from a given table. This behaviour ensures that you are only reading committed data. However, the NOLOCK table hint allows you to instruct the query optimiser to read a given table without obtaining an exclusive or shared lock. The benefits of querying data using the NOLOCK table hint is that it requires less memory and prevents deadlocks from occurring with any other queries that may be reading similar data. The only drawback is that using the NOLOCK table hint may accidentally result into reading uncommitted “dirty” data."

https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/

3

Pros for NOLOCK:

  • Better memory usage
  • Prevention of deadlocks

Cons:

  • Might read dirty data

I guess it depends on use cases. In our case I would argue in favor of overall server health instead of accidentally reading dirty data. Our users know that Epicor data is not to be trusted 100% anyhow. But YMMV I guess.

4

One more thing to keep in mind is that Epicor (by Default) has Snapshot Isolation turned on:

“The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.”

This basically means that the NO-LOCK is essentially pointless in an Epicor Environment (with rare exceptions)