A transaction is a sequence of steps or tasks performed on a database as a single unit. If any of these tasks are not performed, the whole unit is rolled back to its previous state. Otherwise, the changes are updated in the database. In simpler terms, it is “ALL or NONE”, as shown below:
A database transaction has four main properties that should be catered to while writing a transaction. These are called the ACID properties. They are:
All of these properties are explained here.
To control a transaction, use the following commands:
BEGINWe assign a starting point to a transaction using the BEGIN TRANSACTION statement. After beginning, the transaction will either be committed or rolled back.
Consider the sample table (students_table) that has the two entries shown below:
COMMITThe ending point of a transaction is marked using the COMMIT command. It is used to update changes made by the transaction in the database and saves all modifications made until the last COMMIT or ROLLBACK command. This can be seen below:
BEGIN TRANSACTIONUPDATE students_tableSET student_name = 'xyz'WHERE student_id = 1COMMIT
ROLLBACKIn case of any failure or error during the transaction, we use the ROLLBACK command to undo all modifications made till the last COMMIT or ROLLBACK command. It would look like this:
DELETE FROM students_tableWHERE student_id = 1ROLLBACK
SAVETo save the current state of the database in a transaction, use the SAVE TRANSACTION. In case of any failure in the transaction, it is used to roll back to the previous save point without rolling back the complete transaction. This process is shown below:
SAVE TRANSACTION point1DELETE FROM students_table WHERE student_id = 1SAVE TRANSACTION point2DELETE FROM students_table WHERE student_id = 2ROLLBACK TRANSACTION point1--ORROLLBACK TRANSACTION point2
All changes are made in the temporary database. To make them permanent, we need to COMMIT them.
The BEGIN TRANSACTION can also be written as BEGIN TRANS.
Assigning a name to a transaction is optional.
By default, all DML commands (INSERT, UPDATE, DELETE) are auto-committed unless we define a transaction.