SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 DELETE Locking question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

miranwar
Posting Yak Master

125 Posts
Posted - 06/26/2007 :  10:40:21  Show Profile  Reply with Quote
Hi I have the following SQL which i am thinking of runing over the course of the week. The SQL is intended to delete approx 100 million rows. I wrote the SQL so that the deletion takes place in small batches to avoid locking. The set rowcount has been set to 5000. However when i ran the sql via the sql agent after 20 mins or so I got a deadlock. I am thinking is there any way to detect locks when running the sql below. If a lock is detectected then kill the session and restart it. Is this Possible some how by querying the sysProcesses table, or is there a best practice to deal with issues like this.

Thanks,


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

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts
Posted - 06/26/2007 :  13:53:37  Show Profile  Reply with Quote
Deadlocked with what kind of process?
Go to Top of Page

Ifor
Constraint Violating Yak Guru

476 Posts
Posted - 06/27/2007 :  05:35:17  Show Profile  Reply with Quote
I always handle deadlocks through an application so do not know any way of handling them in SQL.
You may want to try something like the following terrible looking fiddle to see if you can avoid
deadloacks completely. 

DELETE G
FROM dbo.cglobal G WITH (XLOCK READPAST)
WHERE NOT EXISTS (
        SELECT *
        FROM dbo.cdata D WITH (NOLOCK)
        WHERE D.cfid = G.cfid
    )


If deadlocks cannot be avoided the only options I can think of are either writing a small
application to handle the while loop or working out what is causing the contention on dbo.cglobal.
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 06/27/2007 :  06:52:57  Show Profile  Reply with Quote
"set rowcount 5000
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
while @@rowcount > 0
begin
delete from dbo.cglobal where not exists (select 1 from dbo.cdata where dbo.cdata.cfid = dbo.cglobal.cfid)
end"

If that is your full code its going to be very inefficient.

You will need, at the least, a WAITFOR to allow other processes to run, and Log backup to get a chance to back some stuff up! Need to make sure its going to COMMIT too - no gain if everything is actually in a Transaction (implicit or otherwise).

And then the exists is potentially a pretty huge resource hog each time too. probably better to get all the PKs for everything to delete first (big hit) and then delete in batches from there (joining to that temp table of PKs)

See also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+Records

Kristen
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 06/27/2007 :  07:44:29  Show Profile  Visit nr's Homepage  Reply with Quote
-- might want to batch this first statement
-- and maybe use a permanent work table
-- so that you can restart the process without rerunning the query
select distinct g.cfid
into #a
from dbo.cglobal g
left join dbo.cdata d
on d.cfid = g.cfid
where d.cfid is null

create index ix on #a (cfid)

select * into #b from #a where 1=0

while exists (select * from #a)
begin
insert #b select top 5000 cfid from #a
delete dbo.cglobal where cfid in (select cfid from #b)
delete #a where cfid in (select cfid from #b)
truncate table #b
end

You can now stop and restart this at your leisure.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Edited by - nr on 06/27/2007 07:45:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000