sql

SQL Transaction Example

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

Download
You can download the full source code of this example here: SQL Transaction Example

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button