MySQL transactions

Introduction

The creation of one logical unit from a set of related tasks is known as a transaction. These related statement sets will either be committed or rolled back in case of any error. In other words, we can define transactions in MySQL as a group of SQL statements (mostly DML or Data Manipulation Language statements) and these must be executed as one unit.

Two things will happen if multiple modifications are done by the MySQL transaction in the database.

• If the transaction is committed, then all the modifications will be successful.
• If the transaction is rolled back, then all the modifications will be undone.

We can say that a transaction operation will not be considered as successful if any available operation is not completed in the statement set. No result will be obtained by the transaction operation, if any statement fails.

A transaction operation in MySQL begins with the first executable SQL statement and is considered complete when it finds a commit or a rollback, either implicitly or explicitly.

Properties of Transaction

MySQL transactions show the ACID property. A stand for Atomicity, C stands for Consistency, I stands for Isolation, and D stands for Durability.

Atomicity

It makes sure that all the operations available in the work unit must be run completely. If not, then at the time of failure, the transaction will be aborted and all previously performed operations will be rolled back to their original state.

Consistency

It makes sure that the database is in a consistent state before and after the execution of the transaction.

For instance, if there is a transaction for transferring funds from one account to another account, then this property makes sure that the sum of funds of both accounts is equal before and after the transaction.

Isolation

It makes sure that the transaction will operate independently and it is also transparent to the other transactions until it is committed.

Durability

It makes sure that even if there is any failure in the system, the result or effect of a committed transaction will persist.

MySQL Transaction Statement

MySQL Transactions can be controlled as given below:

• To start the transaction, a START TRANSACTION statement is provided by MySQL. BEGIN and BEGIN WORK are provided by SQL as an alias for the START TRANSACTION statement.
• To commit the current transaction, we use the COMMIT statement. We use this statement for permanent changes in the database.
• If you want to roll back the current transaction, then use a ROLLBACK statement. Through this statement, you can discard all the changes made in the database as part of the transaction and return to its former state.
• If you want to enable or disable the autocommit mode for the current transaction, then use the SET autocommit statement. The COMMIT statement gets executed by default. Implement the following statements, if there is no requirement to commit changes automatically.

For setting autocommit to OFF execute the statement given below:

OR

For enabling autocommit, execute the statement given below:

OR

MySQL Transactions Example

Here we take an example of a students table in which we have student_id, student_name, and dob.

You have to break the SQL statements into several logical portions if you want to implement a transaction so that we can define what operations are required to be committed or rolled back.

COMMIT Example

Implement the following steps to create a transaction.

• A START TRANSACTION statement is used to start the transaction.
• After this, select the dob of all the students.
• Insert a new record into the students table.
• For the successful completion of the transaction, use the COMMIT statement.

ROLLBACK Example

The following illustration will help us in understanding the rollback transaction.

It is not possible to roll back all the statements using a MySQL transaction. Some of these statements are DDL (Data Definition Language) commands which can ALTER, CREATE, DROP databases or CREATE, DROP, UPDATE tables as well as some stored routines. Keep in mind that while creating the transactions, these statements should not be present.

As we have rolled back the operations, after executing the above statements, no record will be deleted from the table.

SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

Inside the transaction, a special mark along with the name of the identifier is created by the SAVEPOINT statement. It allows you to roll back all the statements whose execution is done after that savepoint.

Syntax of SAVEPOINT statement:

In the current transaction, if we set different savepoints with the same name, then only the newly saved point is considered for the implementation of rollback.

The ROLLBACK TO SAVEPOINT statement enables the rollback of all the operations till the provided savepoint without aborting the transaction.

Syntax of ROLLBACK TO SAVEPOINT statement:

The RELEASE SAVEPOINT statement allows the destruction of the named savepoint from the current transaction, without rolling back the query execution effect after the savepoint.

Syntax of RELEASE SAVEPOINT statement:

Example

Let us assume a table named students, having the attributes student_id, student_name, student_dob and we have also inserted data in the table:

students

Let us get a clearer understanding of using these statements with the help of an example. In the example given below, SAVEPOINT and ROLLBACK TO SAVEPOINT statements are used to get an understanding of how it can be determined by the savepoint which operations of the current transaction are required to be rolled back.

• First, we need to start the transaction.
• Make a savepoint mark, after the insertion of data with the student_id equal to 6.
• Implement the ROLLBACK TO SAVEPOINT statement after the insertion of new data with the student_id equal to 7, so that the changes made after savepoint1 are discarded.
• Again, add data to the table with the student_id equal to 8.
• At last, implement a COMMIT statement to make the changes permanent and then display the data present in the students table.

Output:

By executing the above lines of code, only students with the ID of 6 and 8 are inserted in the students table. The student data inserted with ID of 7 is not available in the table as that statement is rolled back by using the ROLLBACK TO SAVEPOINT statement.

Now we are taking another example to get a better understanding of the use of RELEASE SAVEPOINT.

Output:

After execution of the above statements, all the operations performed by INSERT and UPDATE statements will modify the database successfully at the COMMIT statement as RELEASE SAVEPOINT will only destroy the savepoint without rolling back the statements.