March 6, 2020 at 9:12 am
when I check the deadlock in the database, there are some deadlocks caused by the delete or update or insert sql statement, if I kill the spid which executing delete or update or insert sql statement, does it make data loss or data wrong or data inconsistency ? thanks!
March 6, 2020 at 10:58 am
normally you will get a rollback transaction that puts everything back into place, but if you have for example a sql agent job with 5 steps and you kill it mid ways through then step 1,2,3 have performed, 4 is rolled back, 5 doesn't execute
this also occurs in stored procedures if you have lots of try catch blocks with begin tran/commit/rollback
it's really hard to say, you have to look at the code and figure out on a case by case basis
MVDBA
March 6, 2020 at 5:29 pm
If a deadlock occurs, a rollback happens like Mike says. If it is still rolling back, you won't be able to kill the rollback, you will just have to wait for it. Once the rollback completes you won't have any data corruption or inconsistency. The only loss is that the INSERT, UPDATE, or DELETE will not have been performed.
March 7, 2020 at 3:27 am
Thank you Mike and Chris. but I have one question about sql server process (SPID) .
there are two stored procedures.one is SP1 the other is sp2. sp1 and sp2 has many lines sql statments, and sp2 is called in sp1.
but I'm confused the process(spid) of SQL server when I execute it under a new query of SSMS or under the front end.
Question 1.
If I execute sp1 under a new query of SSMS, it will generate 1 sql server process(spid), is it right ?
Question 2.
If I execute sp1 under front end (the front end is encoded by C#) , it will generate 1 sql server process(spid) in the back end, is it right. ?
Question 3.
if I encode many lines (maybe has many line select or insert or update or delete) using a sql statment block( don't create a stored procedure) and if I execute these codes under a new query of SSMS, it will generate 1 sql serer process(spid) or more than 1 spid in the sql server database ?
Question 4.
If I enocde many lines (maybe has many line select or insert or update or delete) under c# winform program, and use a button click event to execute these codes, when I click this button, after these sql statements are called, how many processes will be generated in the sql server database ?
March 8, 2020 at 8:26 am
Dear All,
for the said question1 to question4, I encoded some source code to test and I already understand, for the case 1 to case 4, there is only one spid for them.
but I have a question about the operation of killing process if cause any data corruption or inconsistency .
for example, the following stored procedure has 2 sql statement blocks, sql statement block1 and sql statement block2;
when I kill the spid generated by this stored procedure, and the sql statement block1 has just been finished , and the sql statement block2 is being performed when I kill this process. so sql server will only roll back the data of sql statement block2 and the updated data of sql statement block1 won't be rolled back, is it right ? thanks!
create proc sp1
as
begin
----SQL Statement Block 1
BEGIN TRY
BEgIN TRAN
delete from table1 where f1='a';
insert into table2 select * from table3 where f1='a';
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
----SQL Statement Block 2
BEGIN TRY
BEgIN TRAN
delete from table111 where f1='a';
insert into table222 select * from table3 where f1='a';
update table3 set f1=getdate() ;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
end
March 8, 2020 at 2:45 pm
Correct. But, be really careful about killing SPIDs... kill the wrong one and your server server could serve as a boat anchor for hours. You should also plan on finding and fixing code that creates reasons for you to kill a SPID.
You should also read about SET XACT_ABORT ON. And, no... IMHO and contrary to what some people state, TRY/CATCH does NOT make the use of SET XACT_ABORT ON obsolete. Quite the opposite, really, because a lot of people simple don't rethrow the error in the CATCH block correctly. If you're not going to take the time to do that correctly, then it's a waste of time, energy, and code to use TRY/CATCH. IMHO, SET XACT_ABORT ON should be used even when TRY/CATCH is present.
One of the worst problems people have with transaction blocks is that they also don't write an exit into the TRY/CATCH block when they might actually need to. If we use your code as an example, do you REALLY want the 2nd block of code to execute if the first block has failed?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2020 at 2:37 am
Dear Jeff,
Thank you for kind help!
I quite agree with you!
my said procedure, I just want to verify how the transaction(rollback) works on the said scripts, actually, when the 1st block fails, we need to break and give up executing the other script.
thanks again!
March 9, 2020 at 9:06 am
im sure you can google this
but
begin tran
Begin try
--do some sql stuff
End Try
begin catch
--raise error or rollback transaction
--do some other stuff
End catch
MVDBA
March 9, 2020 at 2:10 pm
Dear Jeff,
Thank you for kind help!
I quite agree with you!
my said procedure, I just want to verify how the transaction(rollback) works on the said scripts, actually, when the 1st block fails, we need to break and give up executing the other script.
thanks again!
So add a RETURN to the CATCH block to exit the current stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2020 at 12:29 am
Yes, thank you Mike and Jeff!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply