Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
| Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2005, 02:58 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default Continuing DEADLOCK Problem

I continue to receive a DEADLOCK error performing a simple one line update. It only seems to occur when 2 or more records are being updated by different browser windows, but I've verified it's not the same record being updated. I can loop back through and perform the update on a subsequent pass, but that's only masking the problem and degrading performance.

I can find no other code referencing the record being updated. I use a data adapter to retrieve the results to a datatable and then immediately disconnect from the database. I've inserted "with (NO LOCK)" in every Select statement I've got. I'm dumping every database call involving the specifiec table to the debug window to see if there's some overlap between data being read and data being updated and can find no overlap. Could the adapter be locking the records retrieved despite the NOLOCK clause? Is this a case of escalating locks caused by multiple concurrent single record updates? Do data readers lock data?

Does anybody have any ^&%$#$ clue as to how to figure out what's causing this? Is there some tool that can be used to identify the locking call? I'm tearing my hair out trying to find this deadlock and absolutely nothing seems to be helping.
 
Old September 21st, 2005, 02:55 PM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could set your isolation level to Serializable but it will make everything else slower

run a trace on the server and look for the deeadlocks
also look into trace flags 1204 and 1205

also if you have 2 statements in a batch make sure that they are in the same order
tran 1
update table a
update table b

tran 2
update table b
update table a

things like this will cause a deadlock

Inside SQL Server 2000 has an excellent chapter on locking/blocking

http://sqlservercode.blogspot.com/




Similar Threads
Thread Thread Starter Forum Replies Last Post
Continuing Deployment Questions plb BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 5 March 6th, 2007 05:18 PM
Verify completion of Stored Proc before continuing busher .NET Framework 1.x 6 December 15th, 2006 07:58 PM
DEADLOCK issue - URGENT! Ron Howerton SQL Server ASP 3 August 9th, 2005 04:33 PM
Reading one character and then continuing? jacob J2EE 4 February 15th, 2004 05:40 PM
Continuing a line of code in C++ kevin777 C++ Programming 5 January 25th, 2004 05:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.