This tutorial explains the basics of the MySQL Transaction such as how to Start, Commit, and Rollback a transaction with simple examples:
With databases, it is imminent that anyone learning afresh or a senior database person should know at least the basics of MySQL TRANSACTION.
MySQL broadly means executing a set of instructions/statements all or none.
In this tutorial, we will understand what MySQL Transaction is, how you can create transactions, and what other important points should be considered while using transactions.
=> Click here for the complete MySQL tutorial series
Table of Contents:
MySQL TRANSACTION
As we know, a Transaction is an entity – generally a group of SQL statements, or a combination of statements and stored procedure calls, which should be executed all or none.
For example, if a Transaction has 4 statements – S1 to S4 and for instance – S4 throws some error may be due to incorrect syntax or access or any functional issue, then the statements S1 to S3 would be rolled back and the database would reach its original state as if no statement got executed.
The best example to understand a MySQL TRANSACTION is a money transfer between 2 accounts of the same bank.
Imagine, how would this work without a TRANSACTION?
Suppose debit was a success, but credit did not happen, maybe because of some DB issues. In this case, the database would be in an inconsistent state.
So, in this case, there would be a debit from the customer’s account but no credit in the other customer’s account. Ideally, you would want this transaction (both credit and debit) to happen else none of them to happen. i.e. in this case, had it been a transaction, then a failed credit would have resulted in a rollback of debit operation and there would have been no change to the database state.
Let’s now understand what properties does MySQL TRANSACTION have and how we can actually use them.
Properties Of MySQL TRANSACTION
MySQL supports the ACID properties for a transaction-safe Relational Database Management System. Let’s see each of these properties in brief.
- A (Atomicity): Transactions support atomicity by running ALL or NONE – i.e. either all the statements of a transaction would be executed or NONE of them.
- C (Consistency): The Consistency property ensures that the database should be in a consistent state before and after the transaction is complete. This is supported by the atomic nature of the transaction.
- I (Isolation): MySQL provides the concept of locks along with the transaction. This ensures that during transaction execution, no other operation can happen on that row of data.
- D (Durability): Durability refers to the ability of the database to recover from failures. i.e. even if there are system failures, any transaction once successful should be able to apply the changes.
MySQL Transaction Syntax
Let’s have a look at the syntax in the simplest form:
START TRANSACTION;
{sql statement 1}
{sql statement 2}
...
...
COMMIT;
MySQL transaction contains commands to indicate the beginning and end of a transaction along with other options that allow the MySQL engine to perform necessary commit or rollback operations.
- START TRANSACTION: It refers to the beginning/initiation of the transaction. At this point internally auto-commit is turned off i.e. unless explicitly committed the statements would not be committed.
- MySQL statements: There can be multiple related statements within the transaction that would be executed all or none.
- COMMIT: This refers to the final statement of the transaction, which directs MySQL engine to write the changes done.
Let’s see an example.
We will also validate the MySQL Transaction at an intermediate stage by creating a new session of MySQL and query the table which is impacted by the transaction that is yet to be committed.
Scenario: Consider an example where there’s a bank accounts table where we are doing an intra bank transfer i.e. person holding account A is trying to transfer $100 to account B.
CREATE TABLE statements for the scenario:
CREATE TABLE bankaccounts(accountno varchar(20) PRIMARY KEY NOT NULL, funds decimal(8,2));
Add information of 2 accounts named ACC1 and ACC2 with an initial fund balance of $1000 each.
INSERT INTO bankaccounts VALUES("ACC1", 1000);
INSERT INTO bankaccounts VALUES("ACC2", 1000);
Now, let’s see the transaction statements below:
START TRANSACTION or BEGIN; --statement1 UPDATE bankaccounts SET funds=funds-100 WHERE accountno='ACC1'; --statement2 UPDATE bankaccounts SET funds=funds+100 WHERE accountno='ACC2'; --statement3 COMMIT; --statement4
Please note that we have named the statements in the above transaction from statement1 to statement4.
The flow of transaction statements is as below:
- START TRANSACTION: This line indicates MySQL to begin or initiate a transaction.
- Statements that need to be executed.
- COMMIT which would represent the termination of the transaction.
In order to see whether the intermediate state of the transaction is committed, we will execute the debit statement in the transaction and create a new MySQL transaction which queries the accounts table.
Let’s execute statement1 and statement2 in the above transaction and open a new session of MySQL (either using the UI client like a workbench or through the command-line client) and run SELECT statement for the details of funds in our table.
SELECT * FROM bankaccounts;
The output is as below:
As you can see above, the fund’s amount is still unchanged, even though we have executed the statement2 which debits the first account by $100.
Now let’s execute the credit statement in the transaction and finally the COMMIT statement.
Query the accounts table from the second MySQL session.
SELECT * FROM bankaccounts;
You would notice that since the transaction results are committed, the values are now updated and funds are debited and credited from ACC1 and to ACC2, respectively.
ROLLBACK – Undo A Transaction
To revert all the changes that were done as part of a given transaction, the ROLLBACK command is executed. It would result in reverting back the transaction-related changes.
ROLLBACKS are generally applied when there are errors observed/occurred during transaction execution.
Let’s see an example of using the ROLLBACK command.
We would use the same transaction to debit ACC1 and credit ACC2 with a fund of $100
START TRANSACTION; --statement1 UPDATE bankaccounts SET funds=funds-100 WHERE accountno='ACC1'; --statement2 UPDATE bankaccounts SET funds=funds+100 WHERE accountno='ACC2'; --statement3 ROLLBACK; --statement4
You can see that we have just replaced the COMMIT statement with ROLLBACK.
Once all these statements are executed, and if you run a SELECT query, you will find that there has been no change in the fund’s balance as we have rolled back the change that the transaction has done.
MySQL AUTOCOMMIT
While working with transactions, it’s important to understand the use of MySQL session variable AUTOCOMMIT
MySQL by default works with AUTOCOMMIT set to true, which means that each individual statement is executed as a transaction of its own and is committed/applied automatically.
Suppose, you are running a normal SELECT query as below:
SELECT * FROM bankaccounts;
With auto-commit turned ON, this statement gets executed as:
START TRANSACTION; SELECT * FROM bankaccounts; COMMIT;
When we explicitly work with transactions and specify statements inside a ‘START TRANSACTION’ block then the auto-commit is turned OFF. The changes should be committed or rolled back for the transaction to be applied or reverted respectively.
To update this property, you can simply use the ‘SET’ keyword. For example, to turn AUTOCOMMIT to OFF you can use either of the 2 statements
SET AUTOCOMMIT = 0 SET AUTOCOMMIT = OFF
In order to get the value of the AUTOCOMMIT variable, you can use the command below:
SHOW VARIABLES LIKE 'AUTOCOMMIT';
Frequently Asked Questions
Q #1) Are MySQL transactions atomic?
Answer: Yes, the transaction is by concept implementing atomicity in MySQL.
What atomic simply means is – ALL or NONE and MySQL transactions support exactly this. Here, if there are no errors, then all the statements would be executed. But in event of any discrepancy or errors, NONE of the statement in the transaction would be executed.
Q #2) How do I rollback a MySQL transaction?
Answer: A transaction can either be committed or rolled back/reverted. To revert the changes of a transaction, you can simply run the ROLLBACK command before executing the COMMIT.
For example, refer to the below transaction, where we are using ROLLBACK to revert the changes done during the transaction.
START TRANSACTION; --statement1 UPDATE bankaccounts SET funds=funds-100 WHERE accountno='ACC1'; --statement2 UPDATE bankaccounts SET funds=funds+100 WHERE accountno='ACC2'; --statement3 ROLLBACK; --statement4
Q #3) How to fix the lock wait timeout exceeded for a transaction in MySQL?
Answer: There are 2 reasons for this error to happen. These are:
- The lock timeout has expired/elapsed while waiting for a transaction to complete.
- The transaction is waiting/dependent on another transaction to obtain access to the table.
The timeout is configured using an environment variable named ‘innodb_lock_wait_timeout’
The default value for this variable is 50 seconds. If you are expecting the transactions to take longer than this, then this value could be increased or decreased depending on the requirement. This could prevent the users from getting the ‘lock wait timeout exceeded error’.
To get the current value of this variable, execute the below command:
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
And to SET/UPDATE value of this variable, you can use:
SET innodb_lock_wait_timeout=100;
Q #4) Does MySQL support nested transactions?
Answer: No, MySQL transactions cannot be nested. This is because whenever you start a Transaction, it’s implicitly committed to the local disk (and then finally written to disk when the final commit happens).
Imagine using the same table in nested transactions, then it would be nondeterministic for the inner transaction to acquire a lock for a table that’s already part of the outer transaction.
Q #5) What are XA transactions?
Answer: XA transactions are distributed transactions i.e. where the database systems are distributed (and are essentially represented by different host machines)
XA TRANSACTIONS follow a 2 phase commit protocol –
During the 1st phase, the transaction manager obtains a lock on all the requested resources in the transaction.
During the 2nd phase, the transaction manager commits or rollbacks depending on the result of transaction statements.
Q #6) Can DDL statements be rolled back?
Answer: Some statements cannot be rolled back, like, DDL (Data Definition Language) statements. For example, creating a table, altering a table, dropping a field or table, etc. If these statements are used within the transactions and there’s a need to rollback, then these statements would be excluded from the list.
Conclusion
In this tutorial, we learned about the basics of transactions in MySQL. We also learned about the COMMIT and ROLLBACK commands and validated them with their usage.
Transaction is a very heavily used feature of databases across applications where TRANSACTION properties, namely ACID – Atomicity, Consistency, Isolation, and Durability are to be preserved.