March 28, 2009 at 6:20 pm
If a user cancels a stored procedure running in a query window, and the SP was in the middle of running:
update table_A set run_date = getdate() where run_date is NULL
I assume that some records will be updated and some won't ? There is no begin tran or commit that would do a rollback.
Would he get a message saying "n records affected" to reflect how many updates were done before it got cancelled ? (Yes, this is a simplified realworld example :angry:)
March 29, 2009 at 5:41 am
A single statement will not update just some of the records that it would otherwise have updated if it were not cancelled, regardless of whether the sproc uses transactions. Either all of the specified records are updated or none.
March 29, 2009 at 7:23 am
homebrew01 (3/28/2009)
I assume that some records will be updated and some won't ? There is no begin tran or commit that would do a rollback.
Not at all. Even thought you don't explicitly start and commit a transaction, the update will run in the context of one. Thus the update will complete entirely or not at all.
This is assuming that it's a single-statement update. If there are multiple updates in a proc then, if the execution is cancelled and there's no transaction, some of the updates may have completes and some not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 11:20 am
GilaMonster (3/29/2009)
homebrew01 (3/28/2009)
I assume that some records will be updated and some won't ? There is no begin tran or commit that would do a rollback.Not at all. Even thought you don't explicitly start and commit a transaction, the update will run in the context of one. Thus the update will complete entirely or not at all.
This is assuming that it's a single-statement update. If there are multiple updates in a proc then, if the execution is cancelled and there's no transaction, some of the updates may have completes and some not.
Yes, there were multiple updates. The user took a screen shot and had 3 "n row(s) affected" results, but there are 10 update statements. I'm trying to figure out if the 4th update completed partially, or if none of the records got updated. So can I assume that the 4th update did not occur because there was no 4th "n row(s) affected" result ?
create procedure Update_Dates as
update table_A set run_date = getdate() where run_date is NULL
update table_B set run_date = getdate() where run_date is NULL
update table_C set run_date = getdate() where run_date is NULL
update table_D set run_date = getdate() where run_date is NULL
update table_E set run_date = getdate() where run_date is NULL
.
.
exec Update_Dates
.
.
(1111 row(s) affected)
(2222 row(s) affected)
(3333 row(s) affected)
The statement has been terminated.
Query was cancelled by user
March 29, 2009 at 11:56 am
homebrew01 (3/29/2009)
I'm trying to figure out if the 4th update completed partially, or if none of the records got updated.
It would have been all or nothing, as a single update cannot partially complete.
So can I assume that the 4th update did not occur because there was no 4th "n row(s) affected" result ?
Providing there were no triggers on any of the tables, yes. If there were triggers one or more of the 'rows affected' could have come from the triggers and then it's a questions of whether update 2 and 3 ran or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 12:18 pm
Hi
If you are executing several update statements the previous data will be written before you get the error. To handle this you should use a transaction and a TRY-CATCH block.
Greets
Flo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply