Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Unanswered: Locking problems

    Hi,

    I have a trigger for DELETE for table1. This trigger deletes some more rows in table1. After doing that, the trigger calls a stored procedure, SP1, that works with table2, selecting rows from table1 and inserting them in table2.

    The problem is the following: If I run SP1 alone, I can do selects (using with (nolock)) from the table2 while SP1 is still running. But if the trigger for DELETE executes SP1, I can't do any selects on table2 while SP1 is running (and was called by the DELETE trigger).

    I set the lowest isolation level (read uncommited) both in the trigger and in the stored procedure, but I can't still read things from table2 when SP1 is called from the delete trigger. Why is this happening? Is there any way to solve it? I don't care about dirty reads, nor about nonrepeatable reads.

    Thanks,

    Federico

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    For information review this link http://www.sql-server-performance.co...ql_locking.asp about locking.

    And as specified in this KBA http://support.microsoft.com/default...NoWebContent=1 run SP_LOCK2 for addition information on locking.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •