SQL locking issue on an update
hi
i have a trigger that does an update at the end of a data pull, but if a user is in the record the trigger halts until timeout or until the lock is manually released or the user exits the record.
is there anyway for me to simply say if the update timeout is > 5secs than continue without update? or any other way to over come the issue?
below is the update part of the trigger
i have a trigger that does an update at the end of a data pull, but if a user is in the record the trigger halts until timeout or until the lock is manually released or the user exits the record.
is there anyway for me to simply say if the update timeout is > 5secs than continue without update? or any other way to over come the issue?
below is the update part of the trigger
update h set h.analysis_c='D/L'
from [line500V7\SAGE].cs3live.scheme.crrethdm h with (rowlock)
join (select returns_log collate Latin1_General_CI_AS as returns_log from dbo.returns_hdr where manifestid=@manifestid) as t1
on h.log_number=t1.returns_log
bull_rider
Isnt the rowlock the issue here?
ASKER
it was "with (nolock)" all along and it was giving the same problem
nolock would not give the same issue as far as i know. Thats the main difference between nolock and rowlock.
Here is a nice article to understand them both:
http://www.developerfusion.com/article/1688/sql-server-locks/4/
Here is a nice article to understand them both:
http://www.developerfusion.com/article/1688/sql-server-locks/4/
You say if "user is in the record"... what do you mean by that? And do you know what kind of lock on which tables that action / state is creating?
ASKER
i simply tried rowlock as an alternative to no lock - as up until now no lock done my job
the issue is that the database IS locking! and how can i avoid the lock upon my update?
the issue is that the database IS locking! and how can i avoid the lock upon my update?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
volox - i mean if someone is using our returns application and accessing info which has the SQL backend i am running my trigger on
i did not note what type of sql lock i was getting - i will get the next time the issue occurs
i did not note what type of sql lock i was getting - i will get the next time the issue occurs
ASKER
in your code volex
WAITFOR DELAY '00:00:05' ---- after this time will the trigger continue?
WAITFOR DELAY '00:00:05' ---- after this time will the trigger continue?
It doesn't matter if you put nolock in the query on your "crrethdm" table, you can't perform an update statement without obtaining a lock. If I recall correctly, at a minimum SQL 2005 needs a shared update lock on the table and an update lock on the row and I believe there is some level of update lock required on any data pages that contain the row and any indexes that the update affects. If you really want to see the locks that are occurring, run SQL profiler with the locking events captured.
Volox I appreciate the code but what I am thinking is nolock would be a better option, because thats the work of nolock. Anyways its on the decision of the author. Hats off to you Volox. :)
Yes, that snippet essentially loops forever waiting for 5 seconds between each attempt / check for locks. The intent was to wait for long running queries on the target table to finish but to do so without creating a tight loop. Depending on how you are using the tables, you may want to decrease the wait time, but if you eliminate it you'll end up spiking a processor in a tight loop until you either succeed or timeout.
ASKER
bull_rider: how would you go about getting around the update lock i am experiencing? it needs to be pretty fast as i am using the data i am pulling from the trigger in another system that is pushing the data over GPRS to handhelds - so obviously timeouts and delays are the last thing i need!
ASKER
Volox: i would not want it to loop forever - it would not be such as hassle if the data update did not happen and simply skiped the record
Have you tried putting nolock on the select statement that you have inside parens (from dbo.returns_hdr)? I am wondering if that is where your lock is occurring?
In that case, you just have to capture the date and time before you go into the loop and make the condition of the while statement a datediff with whatever timeout value you want to use...
ASKER
yea i have no locks on the select
i will try your suggestion volox and will let you know how i get on tomorrow as i am just wrapping up here !
cheers
mal
i will try your suggestion volox and will let you know how i get on tomorrow as i am just wrapping up here !
cheers
mal
ASKER
for got to say thanks volox!
No problem. And thanks for scoring my answer.