In many situations, data modification requires several steps. For example, you may need to change the values in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that most databases must be compatible with many operations simultaneously. You have several options to configure how data is locked while it is being modified by a user.
Checkout the part-1 of my two part article series on SQL Server Transactions and Locking here, in which you will learn fundamentals about SQL Server transactions, how they work and how to manage them, and how to write procedures that use SQL Server transactions.
This article is published on SQL-SERVER-PERFORMANCE.COM.