Definition:
In SQL Server, a transaction is a logical unit of work that consists of one or more database operations that must be executed as a single, indivisible unit. Transactions ensure that all operations within the transaction are either completed successfully or rolled back if an error occurs, so that the database remains in a consistent state.
- Transactions in SQL Server consist of a group of queries or SQL statements that execute as a single unit.
- Transactions ensure that all commands within the transaction are executed or none of them are executed.
- If any command encounters an error, the transaction fails and all data modifications are rolled back, erasing any changes made.
- If there are no errors, the transaction is successful, and all modifications are committed and become a part of the database.
In SQL Server there are following modes of transactions:
| Auto- commit Transactions | Explicit Transaction | Implicit Transaction |
| It is a default transaction in SQL Server. SQL Server evaluated each T-Sql statement as a transaction. It ensures that statements are committed or rolled-back according their results. | In this Mode we can define a transaction with the starting and ending points of the transaction. In this each transection started with BEGIN TRANSACTION statement and ends with COMMIT or ROLLBACK statement. | In this Mode a new transactions starts implicitly for every statements but we need to use COMMIT or ROLLBACK for complete the transaction. |
ACID Properties: Ensuring Data Integrity and Reliability in SQL Databases
ACID properties are fundamental principles in database management systems that guarantee data integrity and reliability. Let’s delve into each of these properties and understand their significance:
- Atomicity: Atomicity ensures that a transaction is treated as an indivisible unit of work. It means that either all the operations within a transaction are successfully completed, or none of them take effect. If any part of the transaction fails, the entire transaction is rolled back, and the database is restored to its previous state. Atomicity prevents data inconsistencies and ensures that data changes are not left in an incomplete or invalid state.
Example: Consider a banking application where a transfer of funds involves deducting an amount from one account and adding it to another. With atomicity, if any step fails (e.g., deducting from one account but failing to add to another), the entire transaction is rolled back, preventing money from being lost or left in an inconsistent state.
- Consistency: Consistency ensures that the database transitions from one valid state to another valid state after a transaction is completed. It enforces integrity constraints, such as referential integrity, unique key constraints, or any custom business rules defined for the database. If a transaction violates any of these constraints, the changes are rolled back, preserving the overall consistency and correctness of the data.
Example: Suppose an e-commerce application processes an order by deducting the quantity of a product from the inventory. If the inventory falls below zero due to concurrent transactions, the consistency property ensures that the order transaction is rolled back to maintain the integrity of inventory quantities.
- Isolation: Isolation guarantees that each transaction operates independently of other concurrent transactions. It ensures that the intermediate state of a transaction is invisible to other transactions until it is committed. Isolation levels define the degree of concurrency and control over data access among concurrent transactions. By preventing interference between transactions, isolation ensures data integrity and prevents undesirable effects such as dirty reads, non-repeatable reads, and phantom reads.
Example: In a banking application, isolation ensures that two concurrent transactions attempting to withdraw from the same account cannot read each other’s intermediate states. Each transaction is isolated from the other, preventing conflicts and maintaining accurate balance calculations.
- Durability: Durability guarantees that once a transaction commits, its changes are permanently saved and will survive any subsequent system failures. Committed data is typically stored in transaction logs or other mechanisms that provide recoverability. Even in the event of power outages, crashes, or system failures, the database system ensures that the committed data remains intact and can be recovered to maintain data integrity and reliability.
Transactional Control Commands: Roll-Back, COMMIT and SAVEPOINT commands are called TCL-commands.
These commands are only used with DML statements (INSERT, UPDATE AND DELETE). While we creates or delete a table we can’t use these commands because these are automatically committed in database.
Now we will understand these TCL commands one by one –
COMMIT – it indicates that transaction was succeed and it is used to save the changes (modifications) in the database.
ROLLBACK – It shows that the transaction was failed and it is used to cancel all the changes in the database or we can say it roll back the data into its previous state. It is used to undo the transactions.
SAVEPOINT – It is used breaking transactions into multiple units. So user can easily rollback a transaction up to a point or a location.
For getting more clarity we will use some examples to understand the transactions.
First of all we have to create a table, you can create a table using the data which we provided you below:
Example of commit transaction in SQL Server:
COMMIT TRANSACTION:
Syntax for Commit transactions –
Now we will perform some DML-operations Such that INSERT, UPDATE and DELETE inside a transaction:
Note: @@TRANCOUNT function returns the number of BEGIN TRANSACTION statements.
After executing this we get that all the statements have commit. Once we commit the transection we can’t rollback the statements.
ROLLBACK TRANSACTION:
Without using ROLLBACK transaction we will execute this syntax and the data which we define would be saved or operation is performed.
Now we will use ROLLBACK at the end of this syntax and again execute it, then see what happened: Our data get ROLLBACK as before (see the table in image-1).
SAVEPOINT TRANSACTION –
Below is syntax for using SAVEPOINT TRANSACTIONS:
