READLOCK=NOLOCK Option
Setting the lockmode to READLOCK=NOLOCK on a table accessed by a user running a long report allows others users to modify the table data while the report is running. Using READLOCK=NOLOCK affects SELECTs only.
Note: If READLOCK=NOLOCK is set, and rows are changed while a report is being run on the table, the report is not a consistent snapshot of the table. Before using this strategy, consider the importance of the consistency and accuracy of the data.
A table control lock is used to ensure that no reader of any type (including when READLOCK=NOLOCK is set) can look at a table when:
• It is being loaded using the COPY or the CREATE TABLE...AS SELECT statement
• Its schema is being created or changed, using any of the following statements; a READLOCK=NOLOCK reader blocks the following operations:
– CREATE TABLE
– CREATE INDEX
– CREATE VIEW
– CREATE INTEGRITY
– DROP
– MODIFY
Whereas shared locks prevent other users from obtaining write locks and slow down their performance, setting READLOCK=NOLOCK can improve concurrent performance and reduce the possibility of deadlocks.
Set READLOCK to NOLOCK
To set READLOCK to NOLOCK, use this statement:
set lockmode session where readlock = nolock;
When READLOCK=NOLOCK is Beneficial
Setting READLOCK = NOLOCK is beneficial when:
• Running a report to get an overview of the data, and absolute consistency is not essential.
• Updates, inserts, or deletes to a table involve isolated operations on single rows rather than multiple query transactions or iterative operations on multiple rows.
• Reports are needed on tables that are being concurrently updated. Reports slow down the updates and vice versa. Setting READLOCK = NOLOCK on the reporting sessions improves concurrency. (If the report must provide a consistent snapshot, it is preferable to set READLOCK = EXCLUSIVE and get the report done quickly.)
• Running reports “in batch” with a low priority. Running reports this way causes the locking of tables and pages for extended periods because of the lower priority. Setting READLOCK = NOLOCK allows reporting to run at a low priority without disrupting other online users.
When READLOCK=NOLOCK is Undesirable
Setting READLOCK = NOLOCK is undesirable when:
• Users are doing updates that use multiple query transactions or update the whole table (for example, increase all salaries by 10%), but it is necessary that the underlying data does not change, and that a report take a "snapshot" of the table.
• Using multiple query transactions that include updates that reference data from other tables. Here you cannot guarantee the consistency of data between the tables with READLOCK = NOLOCK.