Microsoft SQL Server Getting started with 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.
