SQL Server Performance Forum – Threads Archive

locking and design issue

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’?

---

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

---

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?

---

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

---

]]>