Solved

SQL locking issue on an update

Posted on 2009-05-13
18
174 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:meteorelec
  • 8
  • 7
  • 3
18 Comments
 
LVL 6

Expert Comment

by:bull_rider
ID: 24376300
Isnt the rowlock the issue here?
0
 
LVL 2

Author Comment

by:meteorelec
ID: 24376376
it was  "with (nolock)" all along and it was giving the same problem
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24376661
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Expert Comment

by:Volox
ID: 24376732
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24376785
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
 
LVL 8

Accepted Solution

by:
Volox earned 500 total points
ID: 24376832
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24376840
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24376865
in your code volex

WAITFOR DELAY '00:00:05' ---- after this time will the trigger continue?
0
 
LVL 8

Expert Comment

by:Volox
ID: 24376873
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
 
LVL 6

Expert Comment

by:bull_rider
ID: 24376883
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
 
LVL 8

Expert Comment

by:Volox
ID: 24376909
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24376957
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24376971
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
 
LVL 8

Expert Comment

by:Volox
ID: 24377017
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
 
LVL 8

Expert Comment

by:Volox
ID: 24377028
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24377059
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
 
LVL 2

Author Comment

by:meteorelec
ID: 24393837
for got to say thanks volox!
0
 
LVL 8

Expert Comment

by:Volox
ID: 24396437
No problem. And thanks for scoring my answer.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question