If you have done any reasonable level of SQL programming you would have been exposed to either NOLOCK or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (the default is READ COMMITTED). I have used these to help perform what is referred to as a ‘dirty read’, that is, ignoring any potential updates and inserts to the tables whilst performing the query. This also prevents the current query from producing and waiting for database locks.
There are a couple of other arguments that change the locking behavior, none of which I have ever had reason to use but I wanted to make a quick note of the them.
REPEATABLE READ
Locks are placed on data that is used in the query, this prevents others from updating the data, but it is possible for new rows to be inserted into the data set by other users. Concurrency is lower and this option should only be used when necessary.
SERIALIZABLE
Places a complete lock on the data set, inhibiting others from updating or inserting rows into the data set until the transaction has been completed. This is the most restrictive of all isolation levels. Concurrency is lowest with this option so this option should only be used where appropriate. This option has the same effect as setting HOLDLOCK (vs NOLOCK) on all tables in the SELECT statement.
Comments are closed.