What Are Transactions In SQL Server

What Are Transactions In SQL Server

Transactions are frequently defined as a set of actions that succeed or fail as a whole. To
be more specific, transactions can provide four major functions to the data manipulation
processes that access the database.

1. Atomicity: When two or more pieces of information are involved in a transaction,
either all the pieces are committed or none of them are committed.
2. Consistency: At the end of a transaction, either a new and valid form of the data
exists or the data is returned to its original state. Returning data to its original state is
part of the rollback functionality provided by SQL Server transactions.
3. Isolation: During a transaction (before it is committed or rolled back), the data must
remain in an isolated state and not be accessible to other transactions. In SQL Server, the
isolation level can be controlled for each transaction, as described later in this lesson.
4. Durability: After a transaction is committed, the final state of the data is still
available even if the server fails or is restarted. This functionality is provided through
checkpoints and the database recovery process performed at startup in SQL Server.

By default in SQL Server, each INSERT, UPDATE, or DELETE statement is an individual
transaction that is committed automatically and does not offer rollback functionality.

Transactions are started using the BEGIN TRANSACTION command, which can be shortened to BEGIN TRAN.

----This TRANSACTION has been Committed-----

 BEGIN TRANSACTION
 INSERT INTO Test
 VALUES (1, 'a', 'b')
 COMMIT TRANSACTION

----This TRANSACTION has been rolled back------

 BEGIN TRANSACTION
 INSERT INTO Test
 VALUES (1, 'a', 'b')
 ROLLBACK