Microsoft SQL Server Using Transactions to change data safely


Example

Whenever you change data, in a Data Manipulation Language(DML) command, you can wrap your changes in a transaction. DML includes UPDATE, TRUNCATE, INSERT and DELETE. One of the ways that you can make sure that you're changing the right data would be to use a transaction.

DML changes will take a lock on the rows affected. When you begin a transaction, you must end the transaction or all objects being changed in the DML will remain locked by whoever began the transaction. You can end your transaction with either ROLLBACK or COMMIT. ROLLBACK returns everything within the transaction to its original state. COMMIT places the data into a final state where you cannot undo your changes without another DML statement.

Example:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Notes:

  • This is a simplified example which does not include error handling. But any database operation can fail and hence throw an exception. Here is an example how such a required error handling might look like. You should never use transactions without an error handler, otherwise you might leave the transaction in an unknown state.
  • Depending on the isolation level, transactions are putting locks on the data being queried or changed. You need to ensure that transactions are not running for a long time, because they will lock records in a database and can lead to deadlocks with other parallel running transactions. Keep the operations encapsulated in transactions as short as possible and minimize the impact with the amount of data you're locking.