Dead end in delete-select

Question

The following SQL statement sometimes creates locks on 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 deletion gets a lock status and does not end (?). The only other process that I think is blocked by this is a maintenance plan that works on the weekend to recreate indexes, so I have no idea what could cause the problem.

These are the locks generated by the deletion ...

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

Does anyone have pointers on how to get to the root of the problem? I have a suspicion that the tb_intervaloServico table is the root of the lock, because it is called in delete and in select, but I cannot reproduce the behavior.

Answer 1

db, , .

SQL- 1204, 1205 1206. .

sql, , ,

, :

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 2

, . , ( Santeri Voutilainen) .

, SP4. parallelism, , . OPTION (MAXDOP n), n - . n = 0 .

: Enterprise Manager " - - Parallelism" "" 1.

Answer 3

:

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)

:

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

, select *

Answer 4

, SQL Server 2000.

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

, .

,

Answer 5

Your removal from the table you joined is tb_intervaloServico. Dump your subquery in the temp table and use the temporary 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
)