URGENT DEADLOCKING ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

URGENT DEADLOCKING ISSUE

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2006-02-12 : 07:52:57

Hi This morning was executing a purge job on a clientvariables database , a database that store cookie sessions. The job deletes from two tables in smalll transactions within a loop, 10,000 recs for each loop. The purge SP essentially deletes from two tables Cdata and cGlobal which are growing by 70-80 K rows a day. However when I ran the Job 4 times I got the following error message on the last part of the SP (I was monitoring this through Profiler:

The error msg being:
Executed as user: NT AUTHORITY\SYSTEM. Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed. The delete statement that got deadlocked:

delete from dbo.cdata where not exists (select 1 from dbo.cglobal where dbo.cdata.cfid = dbo.cglobal.cfid).

Now As I was ruuning profiler i could see that both tables were heavily being accsseed by our coldfusion application with various inserts and selects from the table these are being done in transactions. here are the trace steps from profiler:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select cfid,app,data from CDATA where cfid = @P1 and app = @P2
select data from CGLOBAL where cfid = @P1
IF @@TRANCOUNT > 0
COMMIT TRAN

select cfid,app,data from CDATA where cfid = @P1 and app = @P2
insert into CDATA (cfid,app,data) values (@P1 ,@P2 ,@P3 )
select cfid,data,lvisit from CGLOBAL where cfid = @P1
insert into CGLOBAL (cfid,data,lvisit) values (@P1 ,@P2 ,@P3 )
IF @@TRANCOUNT > 0
COMMIT TRAN

I ran the SP again to check which proceess creates the block by doing SP_who and i could see some select statement was blocking the delete..Can someone please give some advice on how I can change the SP so that the deletion can take place without any deadlocks. (I cant change the Colddusion part as it is out of my sphere)

Here is the SP code:
/*Evaluate expression before delete so that optimizer chooses to use an Index Seek when finding range of entries older than 15 days*/
declare @dt datetime
select @dt = dateadd(d,-15,getdate())

set rowcount 10000

/* 1) Delete first 10,000 CGLOBAL entries older than 15 days*/
delete from dbo.cglobal where lvisit < @dt

/*If delete takes place than loop each time deleting 10,000 recs, loop until there's no record left to delete*/
while @@rowcount > 0
begin

delete from dbo.cglobal
where lvisit < @dt
end

/* 2) Delete CGLOBAL orphans -- Delete first 10,000 CGLOBAL entries*/
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)

-- If delete takes place than loop each time deleting 10,000 recs, loop until there's no record left to delete
while @@rowcount > 0
begin
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
end

/* 3) Delete CDATA orphans -- Delete first 10,000 CDATA recs*/
delete from dbo.cdata where not exists (select 1 from dbo.cglobal where dbo.cdata.cfid = dbo.cglobal.cfid)

/* If delete takes place than loop each time deleting 10,000 recs, loop until there's no record left to delete*/

while @@rowcount > 0
begin
delete from dbo.cdata where not exists (select 1 from dbo.cglobal where dbo.cdata.cfid = dbo.cglobal.cfid)
end

set rowcount 0

YOUR HELP WAOULD BE GREATLY APPRECIATED

barmalej
Starting Member

40 Posts

Posted - 2006-02-12 : 08:53:07

Hi,
My quick advice is:
1. Get rid of SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
This option can put a lot of processing in a queue that you do not need at all. And naturally can lead to plenty of deadlocks. This option perhaps must be used as an exception where there is no other solution.
2. For SELECTs instead use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED especially if you read old data.

Gennadi

miranwar
Posting Yak Master

125 Posts

Posted - 2006-02-12 : 09:11:14

hi the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE is being done in the Coldfusion code...Which i cant change, What can i change in the SP specifically in relation to the delete?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-12 : 09:36:09

Since you can't touch the code that is setting the restrictive isolation level your best bet is to reduce the size of your delete transactions.

Also make sure the code that identifies the rows to delete is optimized. For example, let's say cglobal has an identity column as its PK, you might be able to find the max id that is less older than 15 days and then use "where <id> < @SomeID".

Once you get the old rows cleared out maybe you can schedule a job to run every serveral minutes that maintains the table. It should only have to delete several rows at a time.

Be One with the Optimizer
TG

miranwar
Posting Yak Master

125 Posts

Posted - 2006-02-12 : 09:57:21

Thanks..I will this a go... However just for my undesratnding can you shed some light on why ISOLATION LEVEL SERIALIZABLE is creating problems in relation to this code and why reducing the size of the delete transaction could fix the problem?

barmalej
Starting Member

40 Posts

Posted - 2006-02-12 : 10:15:15

Transaction level SERIALIZABLE makes SQL server to process your queries as if they are coming in a queue and the next one starts only after the previous one ends (if they have common data to work with). So for example not good SELECT that reads too many rows can lock other activity here. TG advice is to try to separate data rows that are in your deletion and Cold Fusion processing so that they can process at the same time or at least produce less problems to each other.

Have a look at indexes in execution plan also once more if they work as you expect. Because one thing is that what you expect (and even write in comment) and another what is happeninng in fact. Especially if statistics is missing...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-12 : 10:17:45

(I was typing this while barmalej was posting but I'll post my response anyway)

If I understand correctly, serializable isolation level places a hold lock on the table. That blocks all users from even reading from the table for the length of the transaction. (Assuming the other users are using the default isolation level). So concurrent operations can't happen.

It looks like you have not posted the entire code for the verndor application because there is not BEGIN tran so I know how long their transactions will last. Since you are deleting many rows per transaction and we (I) don't know how long your delete statement takes, and it looks like the vendor code is just inserting 1 row at a time, your code is really the code that is causeing problems. Who knows how many of the vendor's transactions were rolled back :)

So reducing the size (and time duration) of transactions is a good way to avoid deadlocking. Read about deadlocking (and how to avoid) in Books Online. Lots of good info there.

good luck!

Be One with the Optimizer
TG

miranwar
Posting Yak Master

125 Posts

Posted - 2006-02-12 : 10:27:35

ok , iam just trying to understand how this scenario arises if te vendors code inserts a row into cglobal using serializable isolation level and my sp immediately checks for orphans:

delete from dbo.cdata where not exists (select 1 from dbo.cglobal where dbo.cdata.cfid = dbo.cglobal.cfid)

then my delete opertation would be waiting on the vendor transaction...but what would the vendor transaction be waiting for...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-12 : 10:53:43

I don't know but here is a possibility.

within a transaction the vendor first inserts to cdata then cglobal. Say while the first insert to cdata is happening you begin deleting from cglobal. While your delete is happening the vendor still has a lock on cdata and is waiting for cglobal but you've got it locked by your delete. If your delete takes some time then a deadlock can occur.

Your code doesn't show it but if you are using an explicit transaction and accessing the tables in the opposite order (cglobal then cdata) you could have cglobal locked waiting on cdata and the vendor has cdata locked waiting on cglobal. That is the classic deadlock.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources