Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Can Deadlocks Result in Data Corruption?

Author  Topic 

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-02-03 : 23:03:50
hi experts.

Sorry to have to post so many questions about transaction deadlocks this week.

Even though SQl Server 2005 attempts to rollback transactions for the "victim" process that gets killed when a deadlock occurs, is it still possible for some data to be corrupted? For example if a long-running process updates Record A, but it can't get a lock on record B to update B and the SPID eventually gets killed..... does SQL Server always perform a rollback of record A? if it has already been committed, it cant rollback, correct?

Thanks for your opinions. John

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-03 : 23:50:05
If it's already been committed, then there's nothing to rollback (or kill). Short answer is no, a deadlock won't result in corruption.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-04 : 11:20:08
A deadlock involves two sessions that each have an open transaction. Whatever has been committed is not involved in the deadlock. If you are updating two tables and both need to be updated or none, then both would be inside a transaction and both would participate in the deadlock checking. Both are only inside a transaction if it's been coded properly. If it hasn't been coded properly and the query is chosen as the deadlock victim, then data inconsistency is certainly possible.

Data corruption is different, at least in SQL Server terms. Data corruption involves internal system objects.



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-02-04 : 13:04:38
Thanks Russell and tkizer.

Yes, data inconsistency is the term I intended to use.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-04 : 13:35:57
In this case, as long as you code your updates to be ATOMic where necessary, then there's nothing to worry about. If you aren't, then you have what is called a race condition, and it doesn't take a deadlock to end up with inconsistent data. Simple example

INSERT INTO t1(b, c, d) VALUES(1, 2, 3); -- where a is an identity column

Declare @a int
SELECT @a = MAX(a) FROM t1;

INSERT INTO t2 (a) VALUES (@a);

If anything goes wrong with the insert, we just put the wrong value in t2. Or if someone inserts into t1 between our statements we did it.

The above is bad code and is bound to cause trouble eventually.
Go to Top of Page
   

- Advertisement -