1. | What is the difference between the READPAST and READUNCOMMITTED table locking hints? |
|
2. | Which transaction isolation level cannot be specified as a table locking hint? |
|
3. | What is the advantage of locking larger resources, such as tables, instead of rows? |
|
4. | What is the main advantage of locking smaller resources, such as rows, instead of tables or pages? |
|
Answers
1. | Both the READPAST and READUNCOMMITTED table locking hints will prevent ELECT (and also UPDATE/DELETE for READPAST) statements from being blocked by resources locked exclusively by other transactions. The difference is that READUNCOMMITTED will return the dirty values for locked resources, while READPAST will simply skip them. (That is, it will not return them at all). |
2. | The snapshot isolation level cannot be specified as a table locking hint. |
3. | The advantage of locking larger resources is that it will reduce the work required by the SQL Server lock manager to allocate locks (because far fewer locks are allocated), and it will also reduce the memory used to maintain locks. |
4. | The main advantage of locking smaller resources is that it greatly reduces the risk of blocking and deadlocks. |