1

I need to perform this SQL very frequently 

UPDATE Users SET UserPoint=UserPoint+(@UserPoint) WHERE UserID=@UserID

I can allow dirty-read or dirty-write but I do not want to see deadlock, is there a way to max the chance to avoid deadlock?

EDIT

OK, @Tomtome this might not be a deadlock, thats good news for me.

Here I follow up a new question, hope you can help.

I also need to read the data, so I use

SELECT UserPoint FROM [Users] WITH (NOLOCK) WHERE UserID=@UserID

It's not a transaction, just a simple one line SQL, and I already use the nolock, do I need to use SET TRANSACTION ISOLATION LEVEL to avoid deadlock, if I allow dirty-read.

EIDT AGAIN

I think SET ISOLATION LEVEL to READ UNCOMMITTED and WITH NOLOCK are same thing. so Done. Thanks everyone,

Eric Yin
  • 8,737
  • 19
  • 77
  • 118
  • Why would you deadlock? Aren't you only updating one row? Is there likely to be something else updating the same row? – Simon Dec 23 '11 at 10:19
  • Don't want to assume so: Does userID have a unique or primary index? AND is it part of a clustered index? [One Table Deadlocks](http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx) – xQbert Dec 23 '11 at 10:25
  • Yes, UserID is in a clustered index. And the chance of everyone is updating the same row is very big. – Eric Yin Dec 23 '11 at 10:29
  • Eric, if you have a separate follow up question, please ask it in a separate question. Otherwise it might not get much attention as this one has already been answered. – Stephen Turner Dec 23 '11 at 10:38
  • 1
    Excerpt from article above: If you have a clustered index on the table then instead of a ROW lock you have a KEY lock. Locking on the lower level increases concurrency, but if a lot of locks are taken consumes more memory and vice versa for the higher levels. So granularity simply means the level at which the SQL Server locks data. Also note that the more restricted isolation level we choose, the higher the locking level to keep data in correct state. and related [article](http://www.mssqltips.com/sqlservertip/2517/using-a-clustered-index-to-solve-a-sql-server-deadlock-issue/). – xQbert Dec 23 '11 at 10:43

1 Answers1

3

is there a way to max the chance to avoid deadlock?

No, because this statement CAN NOT DEADLOCK.

A Deadlock requires two locks to be hold (a and b) as minimum. So one process gets lock A, waits for B, another gets B, waits for A - and nothing can be resolved by waiting. If the above statement encounters a lock it will wait (and possibly timeout), but not deadlock.

UNLESS it is part of a larger transaction with more statements.

So, the problem does not exist to start with ;)

TomTom
  • 61,059
  • 10
  • 88
  • 148