We all experienced blocking one way or the other, in the old days, the only method is wait J

This is not entirely true you might say, as you can always change the code to include nolock table hint so that it can perform a dirty read. Yes, that seems to the be only way at the time but you are facing with two issues: First is that you have to change the code (to include the table hint), if the application is in-house developed that might be easier, but if it’s a vendor application, you might not be able to do that. The other is that you are risking yourself to read something that is not committed (dirty read), it might be fine with some system depends on its usage, but it’s a risk that the application will need to take.

Back in SQL 2005, there is a new feature call snapshot isolation, simply put is that SQL server will start to use versioning on the record to prevent blocking. This is one of the feature that is helpful to many of the applications but not used, either in-house build or vendor apps. It does also provide a more consistent data read as it no longer read uncommitted data. However, there is a catch, as SQL server require to use versioning to keep track of the old and new data, it using tempdb for this task, hence there is extra load on tempdb which you might have to look out for, but the benefit does out-weight the downside and I would say this should be turn on by default for most user DBs.

To illustrate the issue, let take a very simple test, assume you create a DB call TestDB with all default setting, and you create a table call TestTable with 2 columns – ColA of int and ColB of varchar(10). Then we insert just one record to the table:

You can see that a record can be selected by a simple select statement. Now open another connection to the same database, and do an update with begin transaction:

Without a commit or rollback trans and just keep the transaction open. Go back to the original connection and perform the same select again, you will notice nothing got return and blocking occurs! This is a very standard blocking that we might face everyday. Like we discussed above, we can try to work around it by using a nolock table hint:

Then you can get the value of “G” which is uncommitted!

Let move on to test snapshot isolation, change the same DB to enable this feature

Once you have done that, let's try to run the same query again, reset the record to have the value of A, then on a new connection run the same update statement we used above.

Now let's run the select statement again and you can see that there is no blocking occurs, however, your result-set is getting a value of “A”, which is what we expected as the updated value of “G” is not committed yet! You can confirm that by trying to run select with a table hint of nolock, where it will give you a value of “G”.

From the above simple test, you can see there was no change from the code and you can get away with a very simple blocking, if you still haven't enable it, it's worth to give it a test and it might help you with the concurrent issue that you might be facing today!