Posted by Erland Sommarskog on 04/24/07 21:56

Igor (jerosimic@gmail.com) writes:
> I currently use READ UNCOMMITTED only for data that is not important,
> like getting lists of referrers or today's top users. But i'm having
> problems with my sp for user login, in combination with some other
> stored procedures i get deadlocks, don't really know of a way to
> resolve those deadlocks other than using NOLOCK on users and
> users_online tables.

Often, but always, deadlocks can be avoided with proper indexes, and also
proper access order.

If the deadlock is between user-interface activities and background tasks,
the command SET DEADLOCK_PRIORITY LOW can be an option, to avoid users
getting deadlock errors slapped in their face.

There are also some "classic" errors you can do if you use the HOLDLOCK
hint.

If you are on SQL 2005, snapshot isolation which comes in two flavours
may be an alternative to READ UNCOMMITTED. With snapshot isolation,
you read consistent committed data - but that may be stale.

> So generally for that kind of situation where you have to update and
> calculate very important data like some money transfers, i should put
> REPEATABLE READ or SERIALIZABLE.
> One more question, why would one use SERIALIZABLE over REPEATABLE
> READ, what are the benefits of SERIALIZABLE?

I would rather ask the question in the other way: in most situations when
READ COMMITTED is not enough, SERIALIZABLE is what you need. That is,
you cannot accept that the rows you have read change, and you cannot
accept that new rows creep in. But if you have read the balance on an
account, and don't want it do change before you update it, REPEATABLE
READ is enough.

But SERIALIZABLE is not good for throughput, and increases the risk for
deadlocks.

You should also be aware of the UPDLOCK hint. An UPDLOCK is a shared
lock that does not block other readers. But only one process can hold
an UPDLOCK, so if two processes try to get an UPDLOCK, one will be
blocked at this point. So going back to the update of the account
balance, UPDLOCK is what you should use, not REPEATABLE READ. If you
use REPEATABLE READ only, you get deadlocks.

You can combine UPDLOCK with SERIALIZABLE, but it seems that SQL Server
sometimes take the range locks in different order, leading to deadlocks
anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]