Deadlock in delete-select

Question

The following SQL statement is occasionally generating deadlocks in my mssqlserver 2000 server

delete from tb_intervaloServico 
where idFeriado in (
    select ints.idIntervalo 
    from tb_periodicidadeServico ps, tb_intervaloServico ints 
    where ints.idPeriodicidadeServico=ps.idPeriodicidadeServico
    and idservicoContrato='7f20b4af-9076-48f9-a861-8b78273eadc3'
    and fromFixa=0)

For some reason, the delete gets a blocking status and doesn't finish(?) The only other process that I find blocked by this, is a maintenance plan that runs on the weekend to recreate the indices, so I have no ideia what could be generating the problem.

This are the locks generated by the delete...

Object                  Lock Type Mode Status Owner
tb_intervaloServico     TAB       IX   GRANT  Xact
tb_periodicidadeServico TAB       IS   GRANT  Xact

Anybody have any pointers on how to get to the root of the problem? I have a suspicion that the table tb_intervaloServico is the root of the blocking because it's being called in the delete and in the select, but I can't reproduce the behavior.

Regards,

Sergio Bastos

Answer 1

Hi,

The following article will guide you through how to troubleshoot deadlocks in SQL Server 2000.

http://msdn.microsoft.com/en-us/library/aa937573(SQL.80).aspx

Let me know if you require specific assistance.

Cheers, John

Answer 2

Looks like your query is self-locking. You might want to read this newsgroup thread (specially the posting of Santeri Voutilainen) about this problem.

It's likely an issue with SP4. You could try to reduce the query parallelism by reducing the numbers of processors used for query execution. For that you might use the query hint OPTION (MAXDOP n) where n is the number of parallel threads. Setting n = 0 means using all available processors.

Alternatively you can also set the option globally: in Enterprise Manager under "Properties - Processor - Parallelism" click the radio button next to "Use" and select 1.

Answer 3

Your deleting from a table your joining to, tb_intervaloServico. Dump your subquery in a temp table and use the temp table in your delete.

select ints.idIntervalo into #deleteMeId
from tb_periodicidadeServico ps, tb_intervaloServico ints 
where ints.idPeriodicidadeServico=ps.idPeriodicidadeServico
and idservicoContrato='7f20b4af-9076-48f9-a861-8b78273eadc3'
and fromFixa=0)

delete from tb_intervaloServico 
where idFeriado in (
select * from #deleteMeId
)

Answer 4

You need to enable some trace flags on your db so you get a printout in your log that explains the deadlock chain.

Try starting SQL server with trace flags 1204,1205 and 1206. Then post the deadlock chain.

You could try escalating the locks on in your sql, its possible this will fix it but without the chain printout its impossible to tell

So perhaps this will help:

delete from tb_intervaloServico 
where idFeriado in (
    select ints.idIntervalo 
    from tb_periodicidadeServico ps, tb_intervaloServico ints 
    with (updlock,serializable)
    where ints.idPeriodicidadeServico=ps.idPeriodicidadeServico
    and idservicoContrato='7f20b4af-9076-48f9-a861-8b78273eadc3'
    and fromFixa=0)

Answer 5

try changing your query to:

delete tis
from 
    tb_intervaloServico tis
where tis.idFeriado in (
    select ints.idIntervalo 
    from tb_periodicidadeServico ps, tb_intervaloServico ints 
    where ints.idPeriodicidadeServico=ps.idPeriodicidadeServico
    and idservicoContrato='7f20b4af-9076-48f9-a861-8b78273eadc3'
    and fromFixa=0)

or maybe:

delete tis
from 
    tb_intervaloServico tis
    inner join tb_intervaloServico ints
     on tis.idFeriado = ints.idIntervalo
    inner join tb_periodicidadeServico ps
     on ints.idPeriodicidadeServico = ps.idPeriodicidadeServico
     and idservicoContrato='7f20b4af-9076-48f9-a861-8b78273eadc3' -- add the correct table prefix for idservicoContrato
     and fromFixa = 0 -- add the correct table prefix for fromFixa

You can test those queries before delete using select * instead of delete