I have WebService(.Net framework 2.0) worked with SQL2005 server. Now and then I get an error about deadlock (10 times at day), but I can not reproduce error by myself. I already use SQL Server Profile to get information about deadlock, nothing new and also notnig found on internet (anyone have reasons).
Error description: "Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.."
Here is pseudo code:
BEGIN TRAN ONE
SELECT.... (can be T1 and T2)
UPDATE TABLE1 SET....
INSERT INTO TABLE2...
COMMIT TRAN ONE
BEGIN TRAN TWO
SELECT * FROM TABLE1 T1 WITH(nolock)
INNER JOIN TABLE2 T2 with(nolock)
COMMIT TRAN TWO
and I get deadlock on CONNECTION2?!?!?!?!!?
I just have select statement(just one select) and I'm 100% sure there is no INSERT/DELETE/UPDATE and there is not store procedure call or trigger doing write in table1,2. And second, there is NOLOCK parameters!!!
I'm confused, I thought that deadlock is about two transaction lock same object used in differend order.
How can TRAN TWO lock something or have can be deadlocak victim????
Does anyone have any ideas what is this about???
Thansk's for help, tips etc...