Transaction

DEFINITION

A set of actions that will succeed or fail as a batch.

FUNCTIONS (ACID)

Atomicity - nothing or all,
means, a set of actions that will succeed/fail as a batch.
means, Either all actions within a transaction will be succeeded or all actions within a transaction will be failed 

Consistency - At the end of a transaction, either a new state of data is available or original data will be available.

Isolation - During transaction (before rollback/commit), data will be hidden from any other transaction

Durability - After a transaction is committed, a final state of data will be available forever even server fails/restarts

TYPE OF TRANSACTION

Implicit - While dealing with ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, SELECT, INSERT, UPDATE, TRUNCATE, OPEN, REVOKE SQL server applies transaction

• By default IMPLICIT Transactions will be off means auto committed, you can make it on using following command. If you do so, you have to explicitly COMMIT/ROLLBACK after all above commands

SET IMPLICIT_TRANSACTIONS ON

Explicit -

• You can have your own transaction within a stored procedure.
• Each transaction will have 2 ends. Either Rollback or Commit
• Transaction can be started using BEGIN TRANSACTION command
• Transaction can be ended using COMMIT TRANSACTION / ROLLBACK TRANSACTION command
• It is a good practice to use transaction whenever multiple DML operations are there. For single DML operation, no transaction is required which will be taken care by IMPLICIT transaction
• If transaction is open on Object A, no other DML operation will be allowed on Object A from another session. Even SELECT query won’t work until WITH NOLOCK specified
• When huge number of records are getting operated, it is not good practice to use transaction because
1. It will give a big time performance hit
2. All the other users will be blocked
3. Transaction log will grow exponentially which will consume disk space like anything

Alternate solution is – perform all the operations on temp table “without” using transaction and then make temporary table and actual table in sync using transaction.

• By default all the queries are auto committed in SQL Server
• COMMIT will commit only last (latest) open transaction
• ROLLBACK will roll back ALL active transactions. Any further COMMIT/ROLLBACK will throw an error saying “no corresponding BEGIN TRANSACTION”

BEGIN TRAN
    UPDATE ABC SET A = 2
    BEGIN TRAN
 UPDATE ABC SET A = 3
    ROLLBACK
    UPDATE ABC SET A = 4
COMMIT

Above code won’t work and throw an error “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.”

• Transactions are not applicable on variables, neither on single value variable nor on table variables.
• So it is a good practice to check the TranCount before hitting COMMIT or ROLLBACK

IF (@@TRANCOUNT > 0)

COMMIT

• ROLLBACK will not reset identity back to its original stage.
• You can give the name also to Transaction