MySQL Transactions

A transaction is a simultaneous collection of functions for manipulating datasets and is carried out as though it was a single entity of work. In other terms, a transaction can never be accomplished until each particular process is successful inside the unit. The whole process will collapse if any transaction inside the process crashes. Several SQL queries are clubbed into a unit, and all of them will be executed together as a portion of its transaction. Whenever a transaction incorporates several updates to a database, and the transaction is committed, all the modifications work, or all the updates are reversed whenever the operation is rolled back.

Transaction Properties

Transactions, frequently known by the term ACID, have four main general properties.

• Atomicity: This guarantees that all tasks inside the work unit become completed successfully; else, just at the failure point, the process is terminated, and prior processes are restored to their old state.
• Consistency: This means that upon a sufficiently committed process, the database updates state appropriately.
• Isolation: It helps transactions to work with one another and individually and transparently.
• Durability: This makes sure that in the event of a system breakdown, the outcome or consequence of a committed transaction retains.

MySQL Transactions Working:

Within MySQL, the two terms, Commit and Rollback are primarily used only for MySQL transactions. The transactions start only with BEGIN WORK declaration and finish either by a COMMIT declaration or a ROLLBACK declaration. The SQL instructions comprise the majority of the transaction amongst both the starting and stopping statements. Such event series is irrespective of the specific programming language utilized. You will make a suitable path in whatever language you are using to build the application. The below SQL statements can be implemented using the mysql query() feature.

• BEGIN: Start your process or transaction by providing the BEGIN WORK SQL instruction.
• Add SQL COMMAND: one or even more SQL statements such as SELECT, INSERT, UPDATE, and DELETE, respectively. Confirm even if there's no mistake and everything is in compliance with your expectations.
• COMMIT: The COMMIT instruction must be imposed after a successful transaction is done, such that the modifications to all of the associated tables can take full effect.
• ROLLBACK: If a malfunction occurs, it's indeed appropriate to send a ROLLBACK instruction to revert each table specified in the transaction towards its former condition.
• AUTOCOMMIT: By default, MySQL applies the modifications permanently to a database. If AUTOCOMMIT is set to 1 (standard), then each SQL query (whether or not inside a transaction) is assumed to be a completed transaction and committed until it is completed by default. To avoid automatic commit, set AUTOCOMMIT to 0.

Example 01: AutoCommit Mode On:

MySQL operates with the Autocommit phase allowed through default. It ensures that MySQL saves the changes on the disk to create it perpetually as long as we run a query that adjusts (alters) a table. It is not necessary to turn back the move. Let's try with AUTOCOMMIT on mode. Open your MySQL command-line shell and type your password to begin.

Take an example of a table 'book' that has been created in a database 'data'. Right now, we haven't performed any query on it yet.

>> SELECT * FROM data.book;

Step 2: This process is to update the table 'book'. Let's update the value of column 'Author' where the 'Name' of a book is 'Home'. You can see changes have been made successfully.

>> UPDATE data.book SET Author = 'Cristian Steward' WHERE Name = 'Home';

By having a glimpse of the updated table, we have a modified value of the author where 'name' is 'Home'.

>> SELECT * FROM data.book;

Let's use the ROLLBACK command to revert the changes by simply adding the below query. You can see that the ROLLBACK query doesn't work here as it shows that '0 rows affected'.

>> ROLLBACK;

You can even see the table as well. The table has got no changes after the ROLLBACK statement execution so far. This means that the ROLLBACK doesn't work when we have AUTOCOMMIT on by default.

>> SELECT * FROM data.book;

Example 02: AutoCommit Mode Off:

To revert the changes made, let's try with AUTOCOMMIT off mode. Using the same example of table 'book', we will perform some changes on it. We will be using the START TRANSACTION declaration to deactivate its auto-commit phase or simply type the below command to set AUTOCOMMIT off.

>> SET AUTOCOMMIT = 0;

Let's say we have the same table 'book' in our database, and we have to make changes to it. Then revert those changes to the old ones again.

>> SELECT * FROM data.book;

If you haven't turned off the AUTOCOMMIT mode, then make a start with a START TRANSACTION query in the command line shell as below.

>> START TRANSACTION;

We will be updating the same table using the UPDATE command by setting the 'Author' as 'Aliana' where the 'Name' of a book is 'Dream'. Do it by using the below command. You will see that the changes will be made successfully and effectively.

>> UPDATE data.book SET Autho = 'Aliana' WHERE Name = 'Dream';

Let's check whether the above query has worked perfectly and made changes to the table or not. You can check the updated table by using the below SELECT command as always.

>> SELECT * FROM data.book;

You can see that the query has worked great, as shown below.

Now, it's a turn of the ROLLBACK command to perform its function. Try the ROLLBACK command in your command line to roll back the recent update to the table.

>> ROLLBACK;

Let's check whether the ROLLBACK query has been worked as it should work or not. For this, you have to check the table 'book' again by using the 'SELECT' command as always.

>> SELECT * FROM data.book;

You can see from the below output that ROLLBACK has finally worked. It has reverted the changes made by the UPDATE query on this table.

Conclusion:

That's all for MySQL transactions. I hope this guide will help you to perform MySQL transactions conveniently.