Non-locking insert w/ trigger

[acidkewpie]

Hi,

I have a table which has an insert trigger. This trigger isn't concerned with data validation or anything. Instead it is used to run some system calls via xp_cmdshell(). These commands take a while, and the point of them being there is they will update the rows that were inserted to call them. The data is being inserted by an addnew() call via and adodb connection in ASP.

Concurrently with this triggers execution i want to be reading the values that the trigger is updating on this particular rows.

So the problem seems to be that all the while this trigger is running i'm unable to read that particular row, which is causing my end ASP script to hang until the trigger completes.

What can i do to get around this? I've tried adding (NOLOCK) in whatever places i can find, but i'm unable to find much online about it.

Cheers

Chris

 

[ClayG]

NOLOCK only applies to select - it doesnt apply to writing data, which must always at least lock the row while it is being written. Nolock ensures a reading process will not lock data from access by other processes. Your problem is different. You want to bypass the locks held by the writing process.

If your trigger is calling things via xp_cmdshell, its not directly the trigger thats locking, the shelled command will have its own connection and thats what has a write lock.

If you REALLY want to read inconsistent data while it is being concurrently upadted, you can include this statement:

Set Transaction Isolation Level Read Uncommitted

Although I would question:
- why you want to read data that is in the processes of changing, instead of waiting for it, as locking is intended to do;
- maybe that updating thingy is too slow or is doing too much in a single transaction, could it break its updates into multiple commits ?
- if the update is supposed to be asynchronous then perhaps your trigger should write to a sort of log table and a background process (a Job) could process the updates.

 

[acidkewpie]

i want to read the changing data as it's essentially used to give feedback to the webpage. The trigger is actually pinging a certain IP until it responds. back in my application i wish to monitor the data once a second, and until it does connect and the trigger ends, to be able to write a "progress" dot to the html page.

I'm quite new to triggers, but if the trigger were to write to another log table, would that not imply that the main trigger would still not actually complete until the whole process ended anyway?

I think i've been a bit confused in that rather than the insert in ASP causing a lock, it simply doesn't return from the suroutine until everything is committed. that seems more likely actually. Maybe what i actually want is to just be able to just have the trigger run in the background, and not stop an original insert happen.

Cheers