Microsoft SQL Server: SAVE TRANSACTION
Today somebody asked me a question concerning SAVE TRANSACTION, so I decided to write a small blog post about the basic concept. Consider a database test that resides on our SQL Server instance. In this database we have a table called Item which has the columns ItemNo and Description:
We assume that our table is empty and we execute the following T-SQL code:
BEGIN TRANSACTION INSERT INTO Item Values (1, 'Computer') INSERT INTO Item Values (2, 'Printer') DELETE FROM Item Where ItemNo = 1 SAVE TRANSACTION SaveA INSERT INTO Item Values (3, 'Router') INSERT INTO Item Values (4, 'Monitor') SAVE TRANSACTION SaveB INSERT INTO Item Values (5, 'Keyboard') UPDATE Item SET Description = 'Wireless Keyboard' WHERE ItemNo = 5 ROLLBACK TRANSACTION SaveA UPDATE Item SET Description = 'Mouse' WHERE ItemNo = 4 COMMIT TRANSACTION
Question: Which item or items are there in the Item table after executing the T-SQL?
In this example you executed Transact-SQL code that uses transactional savepoints. These savepoints allow you to roll back portions of the transaction as needed. To create a savepoint to which you can roll back you use the SAVE TRANSACTION statement and specify a savepoint name.
When you issue a ROLLBACK statement you can specify the safepoint to which you want to roll back. In this example we issued two INSERT statements to insert the first two rows and a DELETE statement that deletes the first row. Then we created a savepoint named SaveA. Next we inserted row 3 and row 4 and created another savepoint named SaveB. By having multiple savepoints we can roll back to different places in the transaction.
Next we insert another row in the table and edit the description of item 5. Then we issue a rollback to savepoint SaveA. This rolls back all changes made since creating that savepoint. After this rollback we update item 4. However since the INSERT for this row has been rolled back, no update occurred. Therefore, after we commit the transaction, all we have left is the second row (containing ‘Printer’).
You should note that when rolling back to a savepoint, SQL Server holds resources until the entire transaction is either commited or rolled back. Therefore you should always issue a COMMIT or a complete ROLLBACK, even if you have previously rolled back part of the transaction.
We’ll explore transactions more in detail in a later post.