DELETE Locking question - 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

SQL Server Development (2000)

DELETE Locking question

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2007-06-26 : 10:40:21

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
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-26 : 13:53:37

Deadlocked with what kind of process?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-27 : 05:35:17

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.

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 06:52:57

"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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 07:44:29

-- 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources