When managing a database, several operations can execute that may need to be tracked in case of errors. For example, when you want to withdraw money at an atm, you enter the amount you wish to withdraw, and the system checks the database to determine whether you are allowed to withdraw the requested amount. If so, the system deducts the money from the database and sends a success message to you. However, if there was a power outage, the withdrawal process may fail while in progress. Transactions help us keep track of any modifications made to the database and handle them more accurately. For cases like this, the changes can be rolled back or overwritten.
A transaction is a logical work unit in a database that conducts a single or several actions. Transactions can be made up of a single read, write, delete, or update operation or a mix of all actions.
Now let's briefly highlight and discuss some properties/qualities of a transaction.
Atomicity indicates that the transaction guarantees all actions inside the work unit are completed. Otherwise, the transaction is aborted at the point of failure, and all preceding actions are reverted to their previous state.
Consistency means that the transaction guarantees that the database changes states appropriately after a successfully committed transaction.
Isolation means that all transactions must be independent of one another and transparent to one another.
Durability means that the transaction assures that the outcome or impact of a committed transaction is consistent in the event of a system failure.
Let's now discuss some commands used to control transactions in your database.
COMMIT is a transactional command used to save all the changes made previously by a transaction to the database. This command saves all the changes made since the last commit or rollback command.
DELETE FROM users WHERE name = 'Test'; COMMIT;
BEGIN; DELETE FROM users WHERE id = 4 AND id = 5; ROLLBACK;
SET TRANSACTION command starts a database transaction. This command can be used to set parameters for the following transaction. You can, for example, make a transaction read-only or read-write.
SET TRANSACTION [READ ONLY |READ WRITE]
SAVEPOINT command creates a point in the transaction where all the changes before it are saved. You can create a SAVEPOINT for only the changes after the last save point. This command is beneficial when you want to roll back the changes to a particular point in the transaction history.
BEGIN; DELETE FROM users WHERE id =6; SAVEPOINT SP1; DELETE FROM users WHERE id =2; SAVEPOINT SP2; ROLLBACK TO SP1;
In the code above, we initialized the transaction with the
BEGIN command, then deleted the rows with id 6 and 2. For each deletion, we created two
SP2. We then rolled back the transaction to the first
RELEASE SAVEPOINT command is used to delete a previously generated
SAVEPOINT. Once a
SAVEPOINT has been released, you can no longer use the
ROLLBACK command to reverse transactions that have occurred since the last
RELEASE SAVEPOINT SAVEPOINT_NAME;
Let's look at the modes we can set and how they affect a transaction.
MySQL enables auto-commit mode by default. When auto-commit mode is enabled, each SQL statement is assessed as a transaction, and the results determine whether the statement is committed or rolled back. The successful statements are committed, while the unsuccessful ones are promptly rolled back. To manually disable auto-commit mode, simply run the command:
SET @@autocommit := 0;
COMMIT explicitly commits a transaction, however, it is also possible to use transaction controls to implicitly commit transactions. For example:
BEGIN; SET AUTOCOMMIT = 1;
In this article, you learned what transactions are, why they are important, and how to use them in the MySQL database. In addition, you learned some transactional control commands and the three modes of transactions in MySQL.
If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.