Deadlocks due to differences between READ COMMITTED isolation level and READCOMMITTED table hint

• Previous message: Simon Kissane: "What does RECMODEL_70BACKCOMP do?"
• Messages sorted by: [ date ] [ thread ]

Date: 6 Oct 2004 10:25:59 -0700

We have recently being suffering from significant number of deadlocks
between large select statements and transactional update batches
running against SQL Server SP3.

The update batches run out of transactional COM+ using the usual
SERIALIZABLE isolation level.
The select statements run out of non transactional COM+ and therefore
are running under READ COMMITTED (this has been confirmed with lock
checks). ADO Disconnected record sets are used to receive the results
from the queries.

The problem is these select statements place a significant number
shared key locks on many of the tables involved in the join whilst the
select statement is being processed. We are guessing that it is these
shared locks that are causing the deadlocks.

In an attempt to solve the problem we started placing WITH
(READCOMMITTED) table hints on all the tables, views involved in the
join. This change had absolutely no effect and we were left with the
same locking profile.

Now here's the strange thing. If we place a SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE statement before the select statement with the WITH
(READCOMMITTED) table hints we get new good behaviour. As the query
runs and we examine the locks we see that at most there are only two
shared key locks allocated on any one table involved in the join. This
means the database engine is now releasing locks as it cursors along
each table as it performs the select statement. This is exactly the
behaviour we want as now only a few shared locks are ever
simulaneously allocated and consequently the probablity for deadlock
should decrease dramatically.

All this behaviour has been confirmed from both our ADO components
running under COM+ and using SQL Query Analyzer (ISQL) directly. It
has also been confirmed when the select statements are prepared
statements using sp_executesql.

Is this a subtle SQL Server bug/feature. We don't really understand
what is going on here. It looks like when the WITH (READCOMMITTED)
table hint matches the isolation level READ COMMITTED, the table hint
is ignored and therefore we get the usual rather aggressive READ
COMMITTED isolation level behaviour where locks are kept for the
duration of the SELECT statement. When the WITH (READCOMMITTED) table
hint doesn't match the isolation level SERIALIZABLE then the table
hint is implemented and we see the less aggressive lock behaviour
where locks are released as soon as the row in a table has been
finished with.

We have yet to implement this solution on site, but are probably going
to forced to remove the deadlocks. By the way we don't want to use
READUNCOMMITTED as the data we read is acted on in an optimistic
manner using a bespoke versioning technology. Also we don't want to
use UPDLOCK hints as this will reduce all parallelism in the system.

Hope somebody can help clarify are understanding of this issue.

Dave Stringer

• Previous message: Simon Kissane: "What does RECMODEL_70BACKCOMP do?"
• Messages sorted by: [ date ] [ thread ]