mac vs pc - Page 410 - Developer IT

How to nest transactions nicely - "begin transaction" vs "save transaction" and SQL Server

- by Brian Biales

Do you write stored procedures that might be used by others? And those others may or may not have already started a transaction? And your SP does several things, but if any of them fail, you have to undo them all and return with a code indicating it failed? Well, I have written such code, and it wasn’t working right until I finally figured out how to handle the case when we are already in a transaction, as well as the case where the caller did not start a transaction. When a problem occurred, my “ROLLBACK TRANSACTION” would roll back not just my nested transaction, but the caller’s transaction as well. So when I tested the procedure stand-alone, it seemed to work fine, but when others used it, it would cause a problem if it had to rollback. When something went wrong in my procedure, their entire transaction was rolled back. This was not appreciated. Now, I knew one could "nest" transactions, but the technical documentation was very confusing. And I still have not found the approach below documented anywhere. So here is a very brief description of how I got it to work, I hope you find this helpful. My example is a stored procedure that must figure out on its own if the caller has started a transaction or not. This can be done in SQL Server by checking the @@TRANCOUNT value. If no BEGIN TRANSACTION has occurred yet, this will have a value of 0. Any number greater than zero means that a transaction is in progress. If there is no current transaction, my SP begins a transaction. But if a transaction is already in progress, my SP uses SAVE TRANSACTION and gives it a name. SAVE TRANSACTION creates a “save point”. Note that creating a save point has no effect on @@TRANCOUNT. So my SP starts with something like this: DECLARE @startingTranCount int SET @startingTranCount = @@TRANCOUNT IF @startingTranCount > 0 SAVE TRANSACTION mySavePointName ELSE BEGIN TRANSACTION -- … Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves: IF @startingTranCount = 0 COMMIT TRANSACTION And finally, to roll back just your changes so far: -- Roll back changes... IF @startingTranCount > 0 ROLLBACK TRANSACTION MySavePointName ELSE ROLLBACK TRANSACTION Here is some code that you can try that will demonstrate how the save points work inside a transaction. This sample code creates a temporary table, then executes selects and updates, documenting what is going on, then deletes the temporary table. if running in SQL Management Studio, set Query Results to: Text for best readability of the results. -- Create a temporary table to test with, we'll drop it at the end. CREATE TABLE #ATable( [Column_A] [varchar](5) NULL ) ON [PRIMARY] GO SET NOCOUNT ON -- Ensure just one row - delete all rows, add one DELETE #ATable -- Insert just one row INSERT INTO #ATable VALUES('000') SELECT 'Before TRANSACTION starts, value in table is: ' AS Note, * FROM #ATable SELECT @@trancount AS CurrentTrancount --insert into a values ('abc') UPDATE #ATable SET Column_A = 'abc' SELECT 'UPDATED without a TRANSACTION, value in table is: ' AS Note, * FROM #ATable BEGIN TRANSACTION SELECT 'BEGIN TRANSACTION, trancount is now ' AS Note, @@TRANCOUNT AS TranCount UPDATE #ATable SET Column_A = '123' SELECT 'Row updated inside TRANSACTION, value in table is: ' AS Note, * FROM #ATable SAVE TRANSACTION MySavepoint SELECT 'Save point MySavepoint created, transaction count now:' as Note, @@TRANCOUNT AS TranCount UPDATE #ATable SET Column_A = '456' SELECT 'Updated after MySavepoint created, value in table is: ' AS Note, * FROM #ATable SAVE TRANSACTION point2 SELECT 'Save point point2 created, transaction count now:' as Note, @@TRANCOUNT AS TranCount UPDATE #ATable SET Column_A = '789' SELECT 'Updated after point2 savepoint created, value in table is: ' AS Note, * FROM #ATable ROLLBACK TRANSACTION point2 SELECT 'Just rolled back savepoint "point2", value in table is: ' AS Note, * FROM #ATable ROLLBACK TRANSACTION MySavepoint SELECT 'Just rolled back savepoint "MySavepoint", value in table is: ' AS Note, * FROM #ATable SELECT 'Both save points were rolled back, transaction count still:' as Note, @@TRANCOUNT AS TranCount ROLLBACK TRANSACTION SELECT 'Just rolled back the entire transaction..., value in table is: ' AS Note, * FROM #ATable DROP TABLE #ATable The output should look like this: Note Column_A ---------------------------------------------- -------- Before TRANSACTION starts, value in table is: 000 CurrentTrancount ---------------- 0 Note Column_A -------------------------------------------------- -------- UPDATED without a TRANSACTION, value in table is: abc Note TranCount ------------------------------------ ----------- BEGIN TRANSACTION, trancount is now 1 Note Column_A --------------------------------------------------- -------- Row updated inside TRANSACTION, value in table is: 123 Note TranCount ------------------------------------------------------ ----------- Save point MySavepoint created, transaction count now: 1 Note Column_A ------------------------------------------------------ -------- Updated after MySavepoint created, value in table is: 456 Note TranCount ------------------------------------------------- ----------- Save point point2 created, transaction count now: 1 Note Column_A ----------------------------------------------------------- -------- Updated after point2 savepoint created, value in table is: 789 Note Column_A -------------------------------------------------------- -------- Just rolled back savepoint "point2", value in table is: 456 Note Column_A ------------------------------------------------------------- -------- Just rolled back savepoint "MySavepoint", value in table is: 123 Note TranCount ----------------------------------------------------------- ----------- Both save points were rolled back, transaction count still: 1 Note Column_A --------------------------------------------------------------- -------- Just rolled back the entire transaction..., value in table is: abc

Search Results

Search found 25856 results on 1035 pages for 'mac vs pc'.

Page 410/1035 | < Previous Page | 406 407 408 409 410 411 412 413 414 415 416 417 | Next Page >

- by Brian Biales

- by Alexander Kuznetsov

- by EpsilonVector

- by CraigG

- by Sam Patrick

- by AKE

- by Charles Cline

- by ArtDeineka

- by Alex Bixel

- by Tarun Arora

- by T

- by gablin

- by user1879097

- by Orgad Kimchi

- by BastiBechtold

- by Murphy

- by Manesh Karunakaran

- by Thomas

- by j-g-faustus

- by alessio

- by Nicolás Salvarrey

- by john

< Previous Page | 406 407 408 409 410 411 412 413 414 415 416 417 | Next Page >