SQL Server deadlock

Last Post 11 May 2011 07:02 AM by river1. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
08 May 2011 06:47 AM
Hi Masters,

   I have a big problem...

One off the apps that we have in Visual Basic 6.0 was operating with a database in SQL Server 2000.

This year with changed the app and made it compatible with SQL Server 2005.

Why is for us make it compatible???

Well, we alter the way the app. connects to SQL Server , from MDAC to SQLNCLI and we created a script that converts the database to SQL Server 2005 (changes the compatibility level from 80 to 90, changes the error detectection from torn page to checksum).


When the app was put in production it started to raise an error in one of it's operations....


The error  seams like a normal deadlock ãnd that we need to change the code but i think that this dead lock may be caused because the app. was changed from MDAC to SQLNCLI and they forgot to pass the script that converts the database from 2000 to 2005 (80 to 90 and torn page to checksum).

The error first error that the app displays  is this:

error source = Microsoft SQL Native client
SQL= update t_nseq set last_used =last_used +1 where cod_seq =2
description - timeout expired


the second error is this:

error source = Microsoft SQL Native client
SQL= update t_nseq set last_used =last_used +1 where cod_seq =2
description - transaction (process id .....) was deadlocked on lock resources with aqnother process and has been chosen as the deadlock victim


we have seen the activity monitor and it has this information:

lock type = LCK_M_X command =update  the command is: update t_nseq set last_used =last_used + 1 where cod_seq =2


We have then other  wait type with a insert command the wait type is LCK_M



Do you masters thinh that it is possible that this dead loacks are now appening because we didn't pass the script that converts the database from 80 to 90?



















rm
New Member
New Member

--
08 May 2011 09:23 AM
Changing db option is one time thing, don't have to do it every time app connecting to db. Better to check app code to figure out how it calls those insert/update statements.
river1
New Member
New Member

--
09 May 2011 01:11 AM
the app is not doing that (change db option) every time it connects...

russellb
New Member
New Member

--
09 May 2011 05:15 AM
I think something else is trying to write to the same table at the same time.

What is the Isolation Level?  I'd have a look at changing to READ COMMITTED SNAPSHOT.

How often are you getting these?
rm
New Member
New Member

--
09 May 2011 10:31 AM
Snapshot isolation may not help much to avoid write blocking write.
russellb
New Member
New Member

--
11 May 2011 07:00 AM
True. also I'll rebuild all indexes after migrating from 2000 to 2005
river1
New Member
New Member

--
11 May 2011 07:02 AM
the problem was that they didn't passed the script that converts the database from 2000 to 2005. Now all works fine. Thanks
You are not authorized to post a reply.

Acceptable Use Policy