In SQL Server, the COMMIT, ROLLBACK, and SAVEPOINT commands are used to manage transactions.
A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions ensure that if one part of the sequence fails, the entire sequence is rolled back, and no changes are made to the database. The COMMIT, ROLLBACK, and SAVEPOINT commands let you control the boundaries of transactions.
Here are the details of each command:
1. COMMIT:
The COMMIT command is used to permanently save changes that have been made to the database within a transaction. If the transaction is successful, the data will be permanently saved. If the transaction is unsuccessful, the changes are rolled back, and the data is restored to its previous state.
Syntax:
COMMIT;Example:
BEGIN TRANSACTION;
UPDATE employee SET salary=salary+5000 WHERE department_id=1;
COMMIT;This transaction increases the salary of all employees in department 1 by 5000, and if the transaction is successful, it is permanently committed to the database.
2. ROLLBACK:
The ROLLBACK command is used to undo any changes made within a transaction. If the transaction fails or there is an error, you can use the ROLLBACK command to return the database to its previous state, undoing any changes that were made within the transaction.
Syntax:
ROLLBACK;Example:
BEGIN TRANSACTION;
UPDATE employee SET salary=salary+5000 WHERE department_id=1;
IF @@ERROR <> 0
ROLLBACK;This transaction increases the salary of all employees in department 1 by 5000, but if there is any error, the entire transaction is rolled back, and the data is restored to its previous state.
3. SAVEPOINT:
The SAVEPOINT command is used to mark a point within a transaction to which you can later roll back. This allows you to undo a portion of a transaction that has already been committed.
Syntax:
SAVE TRANSACTION savepoint_name;Example:
BEGIN TRANSACTION;
UPDATE employee SET salary=salary+5000 WHERE department_id=1;
SAVE TRANSACTION my_savepoint;
UPDATE employee SET salary=salary+5000 WHERE department_id=2;
IF @@ERROR <> 0
ROLLBACK my_savepoint;
COMMIT;This transaction increases the salary of all employees in department 1 by 5000 and saves a savepoint. Then, it increases the salary of all employees in department 2 by 5000, but if there is any error, only the changes made after the savepoint are rolled back, and the first update statement is still committed.
In summary,
- COMMIT command permanently saves changes made within a transaction in the database
- ROLLBACK command undo any changes made within a transaction and restores the data to a previous state
- SAVEPOINT command marks a point within a transaction that allows to roll back to later.