Deadlocks (I think)

Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55 860978 LCK_M_X PAG: 13:1:2573

54 AWAITING COMMAND sleeping sa 1 1499
55 UPDATE sleeping sa 2 1499

respectively. Any help would be welcome.

Thanks in advance,
DonThe queries will most likely not show you a deadlock situation. When SQL server detects a deadlock situation, one of the connections is immediately killed. Try this command, then run the deadlocking process again:

dbcc traceon (-1, 1204, 1205)|||I agree that the queries will only show hanging processes but that's what I found, if I am interpreting the results correctly. I did run the TRACEON command although only for 1204 but I cannot find any output from it in the Error log, in fact the last line in the log is:

2004-02-16 12:22:34.81 spid52 DBCC TRACEON 1204, server process ID (SPID) 52

Am I looking in the wrong spot?|||If I remember correctly, the -1 flag indicates that this flag is to be applied to all spids. If you do not specify the -1, and a deadlock occurs on a separate spid, then the deadlock is not recorded.|||I took your advice, I also discovered that flag 3605 will send that info to the error log. Every five seconds I get an entry to advise me there is no deadlock, but I am still getting the same freezing behaviour in my application.

I would make the assumption that the problem lies in the application however those queries I mentioned before are returning results to indicate my application SPID is waiting on a resource and the same code base operates on Oracle.

Any ideas?|||On the microsoft site there is a sp_blocker procedure which may help you - there is one there for sql 7 and 2000. I don't know how supported it is though!

The procedure will show you the SQL statments causing the blocking lock and the I also think is shows you the blocked SQL statement as well.

You would be wise to test it thoughly though before letting it loose on your prod sys though.|||First, thanks to MCrowley and dbabren. I appreciate your prompt and informative replies. It turns out that my problem was due to a select made just a few statements before the update that was holding a lock on that page.

Please forgive my ignorance in SQLServer but I have run accross many applications running on other DB's that perform a select as a page is entered to display the data to be modified and an update when the changes are submitted. It seems very strange that SQLServer would require me to lace the code with NOLOCK in order to prevent the initial selects from interfering with the subsequent updates. Admittedly the selects are more complicated than necessary due to an antiquated schema but I fail to see how SQLServer could remain competitive in the market with these types of oversights.

I must be missing something!|||I think you need to commit your select statements - that will release the shared locks for you. It sounds like the select and update statements are part of the same transaction - which makes sense if you are selecting for update purposes.

or

set the isolation level for each session to READ UNCOMMITTED which will allow dirty reads - it will also mean that your selects will take no shared lock. This is dangerous though, as data you are updating may be updated by another session at the same time - and one of the updates will be lost.|||This is a common problem in making the transfer from Oracle to SQL Server. In SQL Server, readers block writers. In Oracle, Readers just get outdated information. I am sure you could get a raging flame-war started on which is a better solution to the problem of concurrency.

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible. With both reads, and writes.|||Originally posted by dbabren
set the isolation level for each session to READ UNCOMMITTED

No

it will also mean that your selects will take no shared lock. This is dangerous though

Yes

Basic rule of thumb for all platforms, though, is get in and get out as quick as possible.

Yes Yes