SQL Server Transaction creates a wrapper around the set of SQL Statements.
\n Converts multiple SQL statements into a single unit of work.
\n The single unit of work is entirely completed or completely undone.<\/p>\n
The stored procedure execution doesn\u2019t 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.<\/p>\n
Account 1
\n Account 2<\/p>\n
500
\n 0<\/p>\n
-100
\n 0<\/p>\n
400
\n +100<\/p>\n
Bal: 400
\n Bal: 100<\/p>\n
In this bad situation, the data will be in an inconsistency state.
\nEx: Account 1 will be debited with 100, but Account 2 doesn\u2019t receive it; hence, the total won\u2019t tally.<\/p>\n
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.<\/p>\n
The transaction completion is called COMMIT, and the new values will be permanently written to disk.<\/p>\n
If any SQL statement fails, then both the statements are undone. The debit and credit changes will be reverted back to the previous state.<\/p>\n
The process of undoing all the actions performed within the transaction is called rollback.<\/p>\n
The wrapping of SQL statements within the transaction helps to ensure the data remains consistent and the trustworthy.<\/p>\n
BEGIN TRANSACTION or BEGIN TRAN to begin the transaction.<\/p>\n
You can provide a name for the transaction, and that\u2019s optional.<\/p>\n
BEGIN TRANSACTION Ex: BEGIN TRANSACTION Transaction1.<\/p>\n
WITH MARK (optional) — When you add a mark to the transaction, then the record of the transaction\u2019s name is added to the database transaction log, which is helpful when you want to restore the database to the prior state.<\/p>\n
Mark helps to identify the exact date and time the transaction is completed successfully.<\/p>\n
\n Fig 1: Create a transaction<\/p>\n
Group of statements inside the transaction.<\/p>\n
\n Fig 2: Group of SQL statements<\/p>\n
Before inserting the values into the IncomeSourceType table.<\/p>\n
\n Fig 3: Table values<\/p>\n
After INSERT statement execution<\/p>\n
\n Fig 4: After Insert statement execution<\/p>\n
After the INSERT statement, execute the IncomeSourceType table.<\/p>\n
\n Fig 5: After Insert statement execution — select table<\/p>\n
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.<\/p>\n
Execute the select statement in the new window, and the results are<\/p>\n
\n Fig 6: Select table in new session<\/p>\n
Rollback the transaction.<\/p>\n
\n Fig 7: Rollback transaction<\/p>\n
Newly inserted 2 records are roll back.<\/p>\n
\n Fig 8: Select table after Rollback<\/p>\n
What if you \u2018COMMIT\u2019 the transaction instead of rolling it back? Then the results are<\/p>\n
\n Fig 9: Commit transaction<\/p>\n
Results after \u2018COMMIT\u2019 transaction<\/p>\n
\n Fig 10: Results after Commit transaction<\/p>","protected":false},"excerpt":{"rendered":"
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\u2019t always complete as expected. … <\/p>\n