1. Introduction
An SQL transaction is a logical unit of one or more SQL statements. A transaction has ACID properties:
- Atomicity: all or none of changes are performed. For example, when transferring money from one account to another. The two update SQL statements must be performed as a unit. Use both commit and rollback commands.
- Consistency: data is in a consistent state when a transaction starts and ends. For example, when transferring money from one account to another, the total value of both accounts is the same at the start and end of each transaction.
- Isolation: changes occurring in a transaction will not be visible to any other transaction until that particular change in that transaction is committed.
- Durability: after a transaction successfully completes, then changes persist even in the event of a system failure.
2. Technologies Used
The example code in this article was built and run using:
- MySQL
- SQL
3. Setup Table
In this step, I will create an account table with three records.
setup
create table account(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
balance DECIMAL(10,2) NOT NULL,
PRIMARY KEY ( id )
);
insert into account(name, balance) values('mary',100);
insert into account(name, balance ) values('shan',200);
insert into account(name, balance ) values('zheng',300);Verifying with a SELECT statement. It should display three records.
select statement
mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 100.00 | | 2 | shan | 200.00 | | 3 | zheng | 300.00 | +----+-------+---------+ 3 rows in set (0.00 sec)
4. Transaction Control
There are three commands to control the transactions within a relational database management system:
- commit – to commit the changes.
- rollback – to rollback the changes. Can rollback to an optional savepoint.
- savepoint – to mark the transaction’s savepoint.
4.1 The SET TRANSACTION Command
The SET TRANSACTION command sets the transactions’ isolation levels or access mode, etc. Here are two examples.
set transaction
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec)
4.2 The SAVEPOINT Command
A savepoint is a logical rollback point within a transaction. When you set a savepoint, you can undo the events up to the savepoint using the rollback command. Here is the syntax to create a savepoint:
SAVEPOINT {identifier}define a savepoint
mysql> savepoint initialSave; Query OK, 0 rows affected (0.00 sec)
The savepoint name can be used in a ROLLBACK command.
4.3 The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command releases the savepoint. The released savepoint can no longer be used in the ROLLBACK command.
release a savepoint
mysql> release savepoint initialSave; Query OK, 0 rows affected (0.00 sec) mysql> rollback to initialSave; ERROR 1305 (42000): SAVEPOINT initialSave does not exist mysql>
4.4 The ROLLBACK Command
The ROLLBACK command rollbacks the changes to the start of the transaction or a specified savepoint. First, I will show an example which rollbacks to the start of the transaction.
rollback
mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 100.00 | | 2 | shan | 200.00 | | 3 | zheng | 300.00 | +----+-------+---------+ 3 rows in set (0.00 sec) mysql> update account set balance = 0 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 100.00 | | 2 | shan | 200.00 | | 3 | zheng | 300.00 | +----+-------+---------+ 3 rows in set (0.00 sec) mysql>
As you can see here, the rollback command eased the update statement.
Next, I will also show another example that rollbacks to a savePoint.
rollback to {savepoint}
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = 10 where id = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> savePoint zheng10; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance=200 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 90.00 | | 2 | shan | 200.00 | | 3 | zheng | 10.00 | +----+-------+---------+ 3 rows in set (0.00 sec) mysql> rollback to zheng10; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 90.00 | | 2 | shan | 210.00 | | 3 | zheng | 10.00 | +----+-------+---------+ 3 rows in set (0.00 sec) mysql>
As you can see here, the update statement after the savepoint zheng10 gets erased with the rollback to zheng10 command.
4.5 The COMMIT Command
The commit command saves the changes permanently within the transaction.
commit
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance =balance-10 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance = balance +10 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | mary | 90.00 | | 2 | shan | 210.00 | | 3 | zheng | 300.00 | +----+-------+---------+ 3 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql>
5. Summary
In this example, I explained the SQL transaction and how to use commit and rollback command along with a savepoint to control the transaction.
6. Download the Source Code
You can download the full source code of this example here: SQL Transaction Example
