June 28, 2006 at 9:34 am
How to select on table without being locked by another transaction that is doing an insert on the same table as is being selected from?
How to avoid deadlock situtation when two transactions update the same table and then select the table?
June 28, 2006 at 10:24 am
You can use the NOLOCK hint in your select to prevent it from being blocked by another transaction that is locking the row; however, this means that you may have dirty reads. If dirty reads are acceptable to the integrity of the operation that you are performing, this is your best bet.
There are many posts out on SSC regarding preventing deadlocking. Some high level pointers are:
1) keep your transactions as short as possible - this not only means to keep your TSQL code within your transaction as brief as possible, but other aspects such as optimal indexes and database design play a factor with transaction duration.
2) access the tables with your UPDATE/SELECT in the same order each time
Search SSC for many more suggestions on preventing deadlocking, also look in BOL.
July 2, 2006 at 9:56 pm
I have found that most transactions are simply not necessary... for example, if your transactions have no ROLLBACK code, there is no need for the transaction. The WITH (NOLOCK) suggestion is a good one but... step back and ask yourself, "is the transaction necessary"?
If the transaction is in relation to the maintenance of a sequence table, please post the code as it is total unnecessary to do an UPDATE/SELECT within a transaction to maintain a sequence table.
If the problem is important to you, you might want to post the code so we can take a look at it.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply