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 |
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
 Posted - 2007-06-26 : 06:05:38
|
| Hi All,If I KILL a SPID, are any modifications automatically rolled back? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-06-26 : 06:19:24
|
I'd vote yes....if they were "UNcommitted modifications"...bit like a server dying...all o/s work would be rolledback.ps...a small TEST using a QA session with an minor uncommitted UPDATE or INSERT statement would prove it |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-26 : 12:27:02
|
| Yes it rolls back a transaction. You are killing it before it completes, so SQL Server has to roll the uncommitted transaction back for data integrity.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-06-26 : 13:04:00
|
| "If I KILL a SPID, are any modifications automatically rolled back?"As Andrew and Tara have pointed out, rollback of TRANSACTIONS. If you any updates are being done outside transactions, then no - you will have partial logical units of work done, and thus have logical inconsistencies. This is why (a) killing unknown thigns is risky, (b) doing logical units of work in transactions is good.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2007-06-27 : 07:11:00
|
| Thanks. So, just to clarify, if I have a stored proc that contains no explicit transaction, and performs 2 updates, 1st to TableA and 2nd to TableB, and I KILL the SPID whilst it it performing the update to TableB, TableB updates updates will be rolled back whilst TableA will not as they have already been commited? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-27 : 07:31:26
|
| yes_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
Does KILL Rollback?