November 18, 2004 at 1:22 pm
Hi,
I recently converted a c# project from mysql to mssql (also switched from odbc to oledb). Everything works fine except transactions that involve multiple database updates. The basic structure is :
1. begin a transaction
2. issues somes selects (with different connection from 1) from table A
3. update table A with transaction connection
4. loop back to 2 for one more iteration
5. commit transaction
Problem is second pass through 2.) causes a timeout because, it appears, the table is locked. I am reading a completly different row. In fact any select on any row in table A fails. I didn't think transactions were supposed to lock the whole table (IsolationLevel is ReadCommited, although others seem to do same thing) only the row involved in the update in step 4. Incidentally, it works fine if a have only one iteration.
Thanks for any help or suggestions,
Bill
November 19, 2004 at 8:35 am
The fact that you are using a different connection means that it is outside the transaction. Once you update a table using a transaction, SQLserver seems to cause all SELECTS from other connections to wait until the transaction is either committed or rolled back.
One solution is to do SELECT ... FROM table1 with (NOLOCK) WHERE ... in step 2. This causes the select to not wait for transactions on other connections to finish.
Regards
Peter
November 19, 2004 at 2:47 pm
Thanks Peter, that fixed it.
Bill
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply