Solved

SQL locking issue on an update

Posted on 2009-05-13
18
169 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now