What are transactions in SQL?

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:

Lifecycle of a transaction

Lifecycle of a Transaction

Properties of a transaction

A database transaction has four main properties that should be catered to while writing a transaction. These are called the ACID properties. They are:

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability

All of these properties are explained here.

Transaction control commands

To control a transaction, use the following commands:

1. BEGIN

We 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:

12student_id12student_nameabcdef
students_table

2. COMMIT

The 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 TRANSACTION
UPDATE students_table
SET student_name = 'xyz' 
WHERE student_id = 1
COMMIT
12student_id12student_namexyzdef
Table updated in database

3. ROLLBACK

In 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_table

WHERE student_id = 1

ROLLBACK
12student_id12student_namexyzdef
Rollback to the previous state of database

4. SAVE

To 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 point1
DELETE FROM students_table WHERE student_id = 1
SAVE TRANSACTION point2
DELETE FROM students_table WHERE student_id = 2
ROLLBACK TRANSACTION point1
        --OR
ROLLBACK TRANSACTION point2
12student_id12student_namexyzdef
Rollback to savepoint "point1"
1student_id2student_namedef
Rollback to savepoint "point2"

Note

  1. All changes are made in the temporary database. To make them permanent, we need to COMMIT them.

  2. The BEGIN TRANSACTION can also be written as BEGIN TRANS.

  3. Assigning a name to a transaction is optional.

  4. By default, all DML commands (INSERT, UPDATE, DELETE) are auto-committed unless we define a transaction.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)