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.

~Matt

it seems only one SP is causing these deadlocks, and it's never chosen as the deadlock victim.

Please can someone tell me how to change the following SP into a Try/Catch to avoid deadlocking?

ALTER PROCEDURE [dbo].[SP_UpdateUserIPandTime](@User_ID INT,@IP nvarchar (16))
AS UPDATE dbo.TABLENAME
SET COLUMNNAME1 = GETDATE(), COLUMNNAME2 = @IP
WHERE (COLUMNNAME3 = @User_ID)


I suppose this may now need to be in the SQL forum, so I'll post there also.

Thanks