Hi.
Can't find this one.
I am wondering if a table can be dropped from another user if I am currently inserting lines or updating it.
Thanks.
Not while the transaction in which the insert/update is performed is open. But once that transaction is committed or rolled back, the locks are released and the table can be dropped.
1 Like
Hi.
Will statements that run inside a stored procedure be considered one transaction.
Well not exactly one transaction but statements that need to be completed before the lock is released.
So if a stored procedure have insert and updates inside, then the complete stored procedure (with insert and updates) must be run, before a DROP can be initiated.
Is that the case or if , for example, the insert part is complete inside a stored procedure and an update, in the same sproc, is about to begin, then a DROP might find a time loophole and DROP the table while the stored procedure is not finished yet?
Thanks.
A single (insert/update/delete) statement is a single transaction. Multiple statements inside a stored procedure will not be considered as a single transaction just because they are in a stored procedure. The way I think about it is that if you can put a semi-colon after a statement, that marks the end of the statement. Whatever comes next will be in a different transaction.
If you want all the statements in a stored procedure to run as one unit - i.e., all statements will be successfully executed or none will be - you will need to wrap all those statements in a pair of begin tran and commit tran statements. When you do that, you also have to consider the case where the statements fail and so the transactions cannot be committed. You will have to provide for rolling back the transaction in such cases. There are well-established patterns for doing this. For example, see here.
Sommarskog, as is his usual style tells you everything about the topic, so much so that there is nothing more to be said about that topic. But don't let that scare you. He shows you some simple patterns to use.
1 Like
Hi.
I have done begin tran a couple of time but reading the article raises some questions.
I am not sure if a stored procedure on a begin tran will run in a single transaction.
What I mean is: If I have a stored procedure that insert,update etc and put it in a begin tran, will this be treated as a single transaction.
Also, will it catch errors from the stored procedure in the try catch block?
I have used begn tran, usually in an update statement but now, if i do this:
Begin tran
insert to....
insert to...
update...
update...
delete...
end tran
Will this be considered one transaction or because I am inserting and updating it will not?
Also I suspect it will rollback all the insert and updates, if an error occurs?
I'm asking because, as I've said, I have only used either insert or update, separately in a begin tran and not all together.
Thanks
Also , can I run multiple stored procedures in one transaction and those be considered as one transaction?
begin transaction
exec proc1
exec proc2
exec proc3
commit transaction
By single transaction, it essentially means all or nothing. Either all 5 of those statements commit or none of them. If for example, you can't live with the first 3 statements only committing, then you are on the right track. Yes, it will catch errors in a Catch block. If an error occurs, you must do a Rollback. Otherwise the Stored Procedure will result in an open transaction, which if not handled will be bad.
But! You need Commit Tran or Rollback Tran, not End Tran.
Something like:
BEGIN TRY
BEGIN TRANSACTION
//sql write operations
COMMIT TRANSACTION
END TRY
BEGIN CATCH
//Raise/Log error
ROLLBACK TRANSACTION
END CATCH
1 Like
So. Just to make sure.
Either 3 sprocs in begin tran or all of their code in a begin tran is the same result?
Thanks.
Yes, correct.
Hello.
I'm having an issue using a transaction.
I'm trying to Insert into a temp table from another table.
I deliberately deleted the actual table to see if I can get a ROLLBACK on the transaction but instead I get this error:
"Msg 208, Level 16, State 1, Procedure spVbudgetInsertUpdateXX, Line 16
Invalid object name 'zz_tblBUValue_DROP_334'.
Msg 266, Level 16, State 2, Procedure spVbudgetInsertUpdateXX, Line 16
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
"
I have oversimplified the stored procedure so i can only see the Begin ends and the insert and I do not see any mistake.
Any ideas?
Thanks
ALTER PROCEDURE [dbo].[spVbudgetInsertUpdateXX]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SELECT *
INTO #tempbudgetValue
from zz_tblBUValue_DROP_334 D1
where not exists(select Cinemacode from zz_tblBUValue_DROP_335 D2
where D1.CinemaCode = D2.CinemaCode and D1.CinemaClassCode = D2.CinemaClassCode and D1.FMonth = D2.FMonth and D1.FYear= D2.FYear)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GOT IT!
We need to catch fatal errors (as is the dropped table).
So we use the command SET XACT_ABORT ON
Now it works 