READLOCK Option
Unless the lock level is MVCC or TABLE, pages are locked for reading in shared mode. A shared lock on a page does not prevent multiple users from concurrently reading that data.
However, a user trying to change data on the locked page must wait for all shared locks to be released, because changing data requires exclusive locks.
This can be a problem if one user is running a long report that accesses a table with a shared lock. No users can make changes to the locked table data until the report is complete.
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.
READLOCK=EXCLUSIVE Option
A READLOCK option is to set READLOCK to EXCLUSIVE.
Here is an example where controlling the shared lock locking level is necessary.
• User1 submits a multiple query transaction that retrieves data into a table field that the user is allowed to change before writing changes back into the table.
• At the same time, User2 submits a multiple query transaction to retrieve the same set of data into his table field, makes changes to the data, and writes the changes back to the table.
• User1 and User2 will deadlock. Each is waiting for the other to finish and release the shared lock, so that each one can get an exclusive lock to make changes.
If the retrievals and changes had not been done with a multiple query transaction, no deadlock has occurred, because the shared locks are released before the requests for exclusive locks are made. But the exclusive lock transaction is necessary to prevent data from changing between the times you read the data and write to it.
It is preferable to exclusively lock the data when reading it into the table field, so that no other user can also retrieve the same set of data until the first user is finished.
If it is likely that User1 will hold locks for a long time before committing changes, then alternative methods of "optimistic locking" should be investigated.
Set READLOCK=EXCLUSIVE
To set READLOCK to EXCLUSIVE, use the following statement:
set lockmode session where readlock = exclusive;