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.

Answer 1

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)