SQL Server Performance

locking and design issue

Discussion in 'T-SQL Performance Tuning for Developers' started by sql777, Dec 28, 2002.

  1. sql777 New Member

    My table has a trigger that fires upon insert/update. The trigger calls a extended-stored-proc which calls a ASP page which attempts to retrieve the row that was just inserted. This results in a row lock because the extended-stored-proc does not finish until the page retrieves the row..and the page cannot retrieve the row because the trigger is not complete and the row is locked.

    Is there a clean solution to this without introducing a 'hack'?

  2. Chappy New Member

    try using SELECT x,y,z from MyTable WITH (NOLOCK) in the sql which your ASP page executes
  3. sql777 New Member

    The thing is that the row is already locked...so I don't htink that will work. The nolock will not create a lock on the select statement but the row is previously locked in the trigger.

    Am i wrong?
  4. bradmcgehee New Member

    Give Chappy's suggestion a try. With the NOCLOCK hint, no shared locks are issued and it does not honor exclusive locks. In addition, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible, but shouldn't matter in this case.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page