TRANSACTIONS & Error Handling in Sql Server
Transactions in Sql Server, use to group a set of tasks into a single execution unit.
Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails.
Therefore, a transaction has only two results: success or failure.
A database transaction, by definition, must be atomic, consistent, isolated and durable.
These are popularly known as ACID properties. These properties can ensure the concurrent execution of multiple transactions without conflict.
How to implement Transactions using SQL?
Automatically rollback SQL transactions
if one of the queries in a group of queries executed inside a transaction fails, all the previously executed SQL statements are rollbacked
To start a transaction, the BEGIN TRANSACTION statement is used, followed by the set of queries that you want to execute inside the transaction. To mark the end of a transaction, the COMMIT TRANSACTION statement can be used.
the first query will execute successfully and an error will occur while executing the second query, as we trying to update string into numeric price field. Since the queries are being executed inside a transaction, the failure of the second query will cause all the previously executed queries to rollback.
Manually Rollback SQL transactions
We can rollback a query based on certain conditions as well.
For more details please read the Difference between Commit and Rollback in SQL article.
