How to use transaction and select together, with sql2008

Question

Hi, we have a ASP.NET site with a SQL2008 DB.

I'm trying to implement transactions in our current system.

We have a method that is inserting a new row to a table (Table_A), and it works fine with transaction. If an exception is throwed, it will do the rollback.

My problem is, that another user can't do anything that invole with Table_A in the same time - probably because it's locked.

I can understand why the transaction have to lock the new row, but I don't want it to lock the whole table - forcing user_B to wait for user_A to finish.

I have tryed to set isolationlevel ReadUncommitted and Serializable on the transaction, but it didn't work.

So i'm wondering if I also need to rewrite all my select methods, so they are able to select all the rows except the new row there is in the making?

Example:

• Table T has 10 rows(records?)
• User A is inserting a new row into table T. This take some time.
• At the same time, User B want to select the 10 rows, and dosn't care about the new row.

So I guess I somehow need to rewrite user B's select query from a "select all query" to a "select all rows that isnt bind to a transaction query". :)

Any tips is much appriciated, thanks.

Answer 1

Probably what you need is to set your isolation level to Read Committed Snapshot.

Although it needs more space for the tempdb it is great when you don't want your selects to lock the tables.

Beware of posible problems when using this isolation level.

Answer 2

You'll need to make changes to your selects, not to the transaction that is doing the insert.

You can

• set isolation level read uncommitted for the connection doing the select, or
• specify the WITH (NOLOCK) hint against the table that is being locked, or
• specify the WITH (READPAST) hint against the table being locked

As I say, all 3 of these options are things that you'd apply to the SELECT, not the INSERT.

A final option may be to enable SNAPSHOT isolation, and change the database default to use that instead (There are probably many warnings I should include here, if the application hasn't been built/tested with snapshot isolation turned on)