October 25, 2017 at 4:35 am
Hi,
I have come across an interesting scenario, but am not sure what the outcome would be.
If have a stored procedure in the form of:
ALTER PROCEDURE [dbo].[MySP(@ID BIGINT)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT dbo.table1(<somefields> ....)
SELECT <somefields> ....
FROM dbo.ATable
WHERE ID =@ID
-- new sp
EXEC dbo.NewSP @ID
COMMIT
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH
END
Then NewSP looks like:
ALTER PROCEDURE [dbo].[NewSP(@ID BIGINT)
AS
BEGIN
INSERT dbo.table5(<somefields> ....)
SELECT <somefields> ....
FROM dbo.AnotherTable
WHERE ID =@ID
As you can see, the first stored proc in within a transaction, does the second stored proc fall within the same transaction, so if the second stored proc failed it would be rolled back along with the first?
This brings me on to another issue as this is all called from a non-transactional entity framework block of code makes other changes. Now as the entity framework code is not transactional, can I safely assume that the this could corrupt data as the transactional stored proc will roll back on an error, but the entity framework code would not?
Thank you
October 25, 2017 at 5:13 am
Paul Giles-Randall - Wednesday, October 25, 2017 4:35 AMIf the second stored proc failed it would be rolled back along with the first?
Splitting hairs this may be, but the first stored procedure wouldn't be rolled back - only the code within the explicit transaction. I think the second stored procedure would be rolled back, though. Don't take my word for it, however - try it out by renaming AnotherTable and then running MySP.
Now as the entity framework code is not transactional, can I safely assume that the this could corrupt data as the transactional stored proc will roll back on an error, but the entity framework code would not?
What might corrupt your data is if your transaction doesn't encompass all statements that need to succeed or fail as a unit. If you have separate statements issued by your entity framework that need to run together then yes, that could be a problem, in which case you will need to reconsider the architecture of your application.
John
October 25, 2017 at 5:20 am
Thanks for the reply.
The application is one I have just inherited as a co-worker left the company, now I know why, so I have taken it over.
My normal process would be to make the EF code transactional to start with, then try to make sense of the stored procedures that are called, adding transactional TSQL when needed.
But as this is an existing application, this is not permitted.
You have confirmed my thoughts, so thank you for that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply