Posted by Igor on 04/26/07 09:22

On Apr 24, 11:56 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Igor (jerosi...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you, you have been great help as always.

Can you give me an example for this "deadlocks can be avoided with
proper indexes, and also
proper access order." or if you have any websites that explain this if
it's not to much trouble.

Unfortunately my hosting is still on MSSQL 2000.

[Back to original message]