Modelling and data changes
Transactions, ACID and TRY/CATCH
A business operation completes or rolls back.
Understand the concept
A transaction treats multiple changes as one business unit. Atomicity avoids partial completion; consistency preserves constraints; isolation controls concurrency behaviour; durability concerns committed persistence. BEGIN starts, COMMIT commits and ROLLBACK undoes. Long open transactions can increase locking and log usage.
TRY/CATCH handles errors, while SET XACT_ABORT ON affects rollback behaviour for many runtime errors. In CATCH, check XACT_STATE(), roll back when necessary and rethrow with THROW rather than hiding the failure behind a success response.
Use it for related order, stock and financial changes.
Catching an error while leaving a transaction open, or reporting success after failure.
One topic. Three levels of depth.
BEGIN TRANSACTION; SELECT @@TRANCOUNT AS OpenTransactions; ROLLBACK;Shows an open transaction count and rolls back.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Check that Laptop stock retains its original value after rollback.
Show the solution and explanation
SELECT Stock FROM lab.Product WHERE ProductId = 1;The initial value is 8; other persistent changes would alter the result.