Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics 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 February 15th, 2009, 05:16 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 198
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via MSN to itHighway
Default Avoiding SQL Deadlocks in ASP/VBScript

Hi everyone,

I'm really in need of some assistance with a deadlock issue on my medium sized website.

I've found one example cause of the many deadlock issues I'm getting and the 2 queries are as follows...

Query 1) Updating a user's "Last Accessed" time and "Last IP Used" fields in the database. (Stored procedure)
Query 2) Trying to select the user's table and another table in a join. (Direct T-SQL query)

My questions are...
1) Why does SQL not figure out that query 1 is making an update and to wait a few seconds until running query 2?
2) How can I avoid these in ASP/VBScript? Is there a Try>Catch statement I can write? Is there some additional parameters I can add to the T-SQL or SP that will make the SQL server wait if the table is locked?

1 server is running both MSSQL 2005 and IIS 6. Windows 2003.

Many thanks in advance for any help you can give.
 
Old February 15th, 2009, 07:25 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Originally Posted by itHighway View Post
My questions are...
1) Why does SQL not figure out that query 1 is making an update and to wait a few seconds until running query 2?
2) How can I avoid these in ASP/VBScript? Is there a Try>Catch statement I can write? Is there some additional parameters I can add to the T-SQL or SP that will make the SQL server wait if the table is locked?
(1)
(a) Because that is the responsibility of the user. How can SQL Server tell if user 1 is going to actually *DO* anything more with in "a few seconds"??
(b) "a few seconds" is an ETERNITY in computer time frames! Maybe a few *nanoseconds* AT MOST would make sense.

(2) Several ways.
(a) do everything inside of a transaction. ADO supports transactions. You can do a ROLLBACK if the sequence does not succeed.
(b) Do everything inside a transaction inside of a Stored Procedure. *MUCH* the better route. If you can put your query 1 in a SP, why can't you put query 2 into a different SP and have it invoke SP1 first??
(c) It's a horribly ugly solution, and I wouldn't use it on ANY really busy site, but... ASP provides a way to do this that doesn't involve a DB at all (which is of course why it was created...for non-DB deadlock problems):
Code:
<%
...
Application.Lock
    ... do anything ...
Applcation.Unlock
...
%>
So long as all users of the same resource use Application locks, this is a guaranteed way of "serializing" access to the Application object and hence to any code at all between the lock and unlock.





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Express User Instance...avoiding aspdewd SQL Server 2005 1 October 27th, 2007 10:12 PM
Sql server deadlocks and RAID manupuri SQL Server 2000 0 June 28th, 2004 01:27 AM
Deadlocks in WebService performance adi@ecbp.co.il .NET Web Services 0 October 16th, 2003 01:26 PM
SQL Server deadlocks ak SQL Server 2000 4 September 12th, 2003 06:27 AM
Inserting "Double Quotes" ASP?VBScript/SQL Server craigcsb Classic ASP Databases 1 July 4th, 2003 10:37 PM





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