Questions about DeadLock

Here, I am, with some questions.

I will be fast and simple.

I work with SQL Server 2008 and I've been facing a lot of calls about deadlocks.

Users say they're working and then they get an error

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So I contact the developers and tell them to add WITH (NO LOCK) in queries.

It works 100% of the times.

But, is this right?

Is there something to do instead of it, or is this the only way to get rid of those deadlocks?

Thanks

Upvotes: 0

Answers (4)

sarin

Reputation: 5307

ok. So a deadlock is two processes competing for the same data but waiting for the other to finish before updating it (or releasing the lock). One of the reasons why data is locked is to prevent a 'dirty read'. i.e. reading out-of-date data because it's already been updated

So locking is there for a reason i.e. it's there to protect the integrity of your data. Turning it off WITH (NO LOCK) should only be done if you are sure your system won't suffer as a result. Otherwise you start to get data corruption i.e. bad data.

I would say using WITH (NO LOCK) is safe with procedures that only report i.e. Selects without any following updates and have no bearing on the Operation of the system. For procedures that do selects and then updates not so good.

As per @idstam s answer, if you do need to Select data, make some decisions on it and then do updates, these need to be as short as possible. i.e. the time between the select and update needs to be short because that's the time the lock is present and consequently the window of opportunity for a deadlock. This is more prevalent in a multi-user system where users are looking at the same data.

So, yes you can turn down the locking, but beware it can leave you with bad data. Best thing is to understand what is deadlocking and determine if it can be improved. If you try to minimise deadlocks by lowering your isolation level, you will need to use row versioning i.e. each update checks it is updating the data it last saw and if it isn't (i.e. its been updated in the meantime) it throws an error.

Here is a good MSDN article on how to reduce deadlocks

Upvotes: 1

Spock

Reputation: 4910

NOLOCK and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED can be useful, but it's not always the correct answer. It will depend on the situation. The biggest risk in my opinion would be dirty reads. (Reading data that might be rolled back for some reason.)

You can read more here

http://sqlblog.com/blogs/tamarick_hill/archive/2013/05/06/pros-cons-of-using-read-uncommitted-and-nolock.aspx

Hope that helps

Upvotes: 0

asafm

Reputation: 919

It depends how much do you care about read consistency and how much do you care about performance.

WITH (NO LOCK) - There is no read lock but you are effected by stale, inconsistent data
Row level version - an old version of the row is kept 'on the side'. This also eliminates read lock on the cost of addiional overhead to every DML you are doing.

Upvotes: 0

idstam

Reputation: 2878

Set the database to use row level versioning.

Keep transactions short lived.

Make sure tables are used in the same order in all places.

Getting a write lock up front helps some times.

Upvotes: 0

Questions about DeadLock

Answers (4)

Related Questions