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.
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. |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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. |
|
|
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 exampleINSERT INTO t1(b, c, d) VALUES(1, 2, 3); -- where a is an identity columnDeclare @a intSELECT @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. |
|
|
|
|
|
|
|