[SOLVED] How to write SQL to Update and void Deadlock?

How to write SQL to Update and void Deadlock?

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,

Solution

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 ;)