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

 
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

Open in new window

LVL 2
meteorelecAsked:
Who is Participating?
 
VoloxConnect With a Mentor Commented:
I tend to agree with bull_rider that the issue here is probably that you have conflicting locks, but if you want SQL code that waits for all the locks on a table to be released, here is a sample.  This code waits until the lock is released, but you could easily alter it to 'timeout' after a period of time.
WHILE 1=1
BEGIN
	Select @LockCount = Count(*) From master..syslockinfo Where rsc_objid  IN (Object_ID('databaseName.dbo.tablename') ) AND db_name([rsc_dbid])='databaseName'
	IF @LockCount = 0
	BEGIN
			BEGIN TRANSACTION
 
			-- Do work here
 
			COMMIT TRANSACTION
 
			BREAK
	END
 
	WAITFOR DELAY '00:00:05'
END

Open in new window

0
 
bull_riderCommented:
Isnt the rowlock the issue here?
0
 
meteorelecAuthor Commented:
it was  "with (nolock)" all along and it was giving the same problem
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bull_riderCommented:
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/
0
 
VoloxCommented:
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?
0
 
meteorelecAuthor Commented:
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?
0
 
meteorelecAuthor Commented:
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
0
 
meteorelecAuthor Commented:
in your code volex

WAITFOR DELAY '00:00:05' ---- after this time will the trigger continue?
0
 
VoloxCommented:
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.
0
 
bull_riderCommented:
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. :)
0
 
VoloxCommented:
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.
0
 
meteorelecAuthor Commented:
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!
0
 
meteorelecAuthor Commented:
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
0
 
VoloxCommented:
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?
0
 
VoloxCommented:
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...
0
 
meteorelecAuthor Commented:
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
0
 
meteorelecAuthor Commented:
for got to say thanks volox!
0
 
VoloxCommented:
No problem. And thanks for scoring my answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.