Sql-server – Deadlock Error from SQL Server 2000 to SQL Server 2008

deadlocksql-server-2008transactiontrigger

After migrating data from SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on the database, that's gives me error.

 Transaction (Process ID 59) was deadlocked on lock resources with
 another process and has been chosen as the deadlock victim. 
 Rerun the transaction.

There are 3 triggers on the table (Insert,Update,Delete). How can I solve this problem?

Best Answer

After upgrading to SQL Server 2008, did you run the below?

DBCC UPDATEUSGAE
UPDATE STATS WITH FULLSCAN
REBUILD ALL INDEXES

The statistics blob is changed significantly from 2000 to SQL Server 2008 and the above steps are very important after the upgrade. I have seen many ppl run into performance problems but not deadlocks. I would try doing all of the above if you haven't so far and try to reproduce the problem afterwards.

That said, what is the results of SELECT @@VERSION on the 2008 box? Are you updating one row or several rows?

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Sql-server – Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

No, this is happening because you have a deadlocked resource. What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.

This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim to be chosen (which is why you see the error you posted).

Deadlocks happen. If they are extremely infrequent then it's probably not an issue to worry about. But if they are happening all the time, then you need to drill down into what query(ies) is(are) causing them. You may need to rework your DML, or alter your transaction isolation level if it is appropriate (allowing dirty reads is not the answer), or even go with an optimistic isolation level like Snapshot Isolation or Read Committed Snapshot Isolation. But great consideration needs to be made here, as those optimistic isolation levels utilize row-versioning which will hammer tempdb, so if you don't have an optimial configuration for tempdb you could bring your performance to its knees.

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Sql-server – Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

Related Question