Consistency is an important part of data storage. That is why READ COMMITTED is the default transaction isolation level in SQL Server. This level prevents you from retrieving values that modified inside a transaction but not committed. However, what is great for consistency is a pain for debugging.
Let us assume you have an integration test that writes to the database, makes some checks and then does a rollback on its transaction. The data is only ever around while the test is running, what is not a problem when the test passes.
If the test fails and you want to see what data was inserted inside the transaction, your SELECT query is blocked until the test and its transaction are done. Before you go and reconfigure your database to use a different transaction isolation level, you can add the WITH (NOLOCK) hint to your SELECT statement:
|
1 2 |
SELECT * FROM [dbo].[Users] WITH (NOLOCK) |
The query does no longer wait on the end of the running transaction and returns the changed data. You now can see what your test gets back and pinpoint the problem that leads to the failure.
This simple little trick can help you to save a lot of time. You do not need to guess about the data, you can simply look at it. Ahmad Yaseen has collected an extensible list of other scenarios where the WITH (NOLOCK) hint is useful. Read it to understand the possible downsides before you use it for something more than debugging.