T-SQL Transactions
The Transaction is the unit of work performed opposite of the database. Any transaction that reads from or writes to a database.
Transaction is the spread of one or several changes to a database.
For example, if we are recording, updating, or deleting the history of the table, then we create the transaction of the table. To control the operations of the data integrity is essential, and it is used to handle the error of the database. We add some SQL queries to the group and execute the transaction part.
Properties of Transactions:
The transaction has four properties, which are referred as ACID property –
- Atomicity – It ensures that all operations with the work unit are successfully completed; Otherwise, the transaction is cancelled at the point of failure, and the earliest activities are moved back to the former position. This means either all are successful or none.
- Consistency – This ensures that the database changes state correctly on a successfully committed transaction.
- Compatibility– Bringing the database from one consistent state to another steady-state ensures delivering the database from one steady-state to another constant state.
- Isolation – Isolation ensures that one transaction is different from other transactions. This enables operations to work transparently with each other.
- Stability – This ensures the outcome or impact of committed transactions in case of system failure. Stability means that once the transaction is done, it will remain in the event of errors, power loss, etc.
Transaction Control
The commands are used to control the transactions, these commands are given below-
- COMMIT– It is used to save the changes.
- ROLLBACK– ROLLBACK is used to roll back the changes.
- SAVEPOINT – SAVEPOINT creates the group of transactions with the help of rollback command.
- Set Transport – It returns a name on the transaction.
Transactional commands are used with the help of DML commands such as INSERT, UPDATE and DELETE. It cannot be applied when we are creating or dropping the tables.
In MS SQL Server, to use transactional control commands, we initiate a ‘trans start’ or transaction command; otherwise, the command may not work.
COMMIT Command
It is also known as Transactional Command. The command is used by the database to save changes.
Syntax:
The syntax of COMMIT command is given below.
Example
See the EMPLOYEES table, which has the records.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Hamilton | 23 | Australia | 34000 |
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 4 | Twinkle | 30 | Turkey | 50000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 6 | Michal | 31 | Bhutan | 20000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
Following command example will delete records from the table having age = 30 and then COMMIT the changes in the database.
In the result part, 4 and 6 rows from the table have been deleted. SELECT statement is generating the output given below:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Hamilton | 23 | Australia | 34000 |
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
ROLLBACK Command:
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. Rollback command is used to undo the set of transactions.
Syntax:
Example
See the EMPLOYEES table, which has the following records.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Hamilton | 23 | Australia | 34000 |
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 4 | Twinkle | 30 | Turkey | 50000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 6 | Michal | 31 | Bhutan | 20000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
Following command example will delete records from the table having age = 20 and then ROLLBACK the changes in the database.
The delete operations have no effect on the result of the table.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Hamilton | 23 | Australia | 34000 |
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 4 | Twinkle | 30 | Turkey | 50000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 6 | Michal | 31 | Bhutan | 20000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
SAVEPOINT Command:
SAVEPOINT is the point in a transaction when we roll the transaction back to a certain point without rolling the entire operation.
Syntax:
The syntax of SAVEPOINT command:
The order serves the creation of SAVEPOINT between transaction statements.
SAVEPOINT Syntax:
In the below example, we delete three different records from the EMPLOYEES table. Then create a SAVEPOINT before each delete so that we can load SAVEPOINT at the time to return the data into its original state.
Example:
Consider the EMPLOYEES table which has the following records –
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Hamilton | 23 | Australia | 34000 |
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 4 | Twinkle | 30 | Turkey | 50000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 6 | Michal | 31 | Bhutan | 20000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
Following are the series of operations –
Begin Tran
Save point created.
1 row deleted.
Save point created.
1 row deleted.
These commands took their place.
We have decided to ROLLBACK the SAVEPOINT, which is recognized as SP2. Because SP2 was created after 1 deletion, and 2 last deletions have not been done.
Rollback has been completed.
Note: First deletion has taken place when we rolled back to SP2.
6 rows have selected.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 2 | Warner | 34 | England | 22000 |
| 3 | Martin | 28 | China | 25000 |
| 4 | Twinkle | 30 | Turkey | 50000 |
| 5 | Tinu | 32 | Nepal | 45000 |
| 6 | Michal | 31 | Bhutan | 20000 |
| 7 | Harper | 20 | Bangladesh | 15000 |
SET TRANSACTION Command
The set transmission command will be used to initiate database transactions. The command is used to generate the characteristics of transaction which is given as following:
Syntax of SET TRANSACTION:
Below is the syntax of the SET TRANSACTION.