SQL Server Transaction basics

1. Microsoft SQL Server Transaction

• SQL Server Transaction creates a wrapper around the set of SQL Statements.
• Converts multiple SQL statements into a single unit of work.
• The single unit of work is entirely completed or completely undone.

2. Data Inconsistency

• The stored procedure execution doesn’t always complete as expected. There could be unforeseen scenarios like network error interruption, memory module corruption, natural disasters in data center locations and any number of things that may happen during the process that cause one activity to finish successfully and the other activity to fail.

Account 1	Account 2
500	0
-100	0
400	+100
Bal: 400	Bal: 100

• In this bad situation, the data will be in an inconsistency state. Ex: Account 1 will be debited with 100, but Account 2 doesn’t receive it; hence, the total won’t tally.

• The solution to the problem is to wrap the debit and credit process into a single unit of work so that both operation will be completed successfully.

• The transaction completion is called COMMIT, and the new values will be permanently written to disk.

• If any SQL statement fails, then both the statements are undone. The debit and credit changes will be reverted back to the previous state.

• The process of undoing all the actions performed within the transaction is called rollback.

• The wrapping of SQL statements within the transaction helps to ensure the data remains consistent and the trustworthy.

3. Create a Transaction

• BEGIN TRANSACTION or BEGIN TRAN to begin the transaction.

• You can provide a name for the transaction, and that’s optional.

• BEGIN TRANSACTION Ex: BEGIN TRANSACTION Transaction1.

• WITH MARK (optional) - When you add a mark to the transaction, then the record of the transaction’s name is added to the database transaction log, which is helpful when you want to restore the database to the prior state.

• Mark helps to identify the exact date and time the transaction is completed successfully.

Fig 1: Create a transaction

4. Group of SQL statements inside Transaction

• Group of statements inside the transaction.

  
  

  Fig 2: Group of SQL statements

• Before inserting the values into the IncomeSourceType table.

  
  

  Fig 3: Table values

• After INSERT statement execution

  
  

  Fig 4: After Insert statement execution

• After the INSERT statement, execute the IncomeSourceType table.

  
  

  Fig 5: After Insert statement execution - select table

• It looks like 2 rows were newly added to the table. It is still inside the transaction, and the data is not yet finalized on the server.

• Execute the select statement in the new window, and the results are

  
  

  Fig 6: Select table in new session

5. Rollback

• Rollback the transaction.

Fig 7: Rollback transaction

• Newly inserted 2 records are roll back.

  
  

  Fig 8: Select table after Rollback

6. Commit

• What if you ‘COMMIT’ the transaction instead of rolling it back? Then the results are

  
  

  Fig 9: Commit transaction

• Results after ‘COMMIT’ transaction

  
  

  Fig 10: Results after Commit transaction