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