Transactions in MySQL

Tuesday Jul 20th 2004 by Ian Gilfillan
Share:

There are times when it is vitally important in which order queries run, and that all queries in a group run, or none at all. Learn about 'transactions' and why they are so important.

What is a transaction?

If you are asking this question, you are probably used to website databases, where most often it does not matter in which order you run transactions, and if one query fails, it has no impact on others. If you are updating some sort of content, often you will not care when the update is performed, as long as the reads are being taken care of quickly. Similarly, if an update fails, the reads can still carry on reading the old data in the meantime. However, there are times when it is vitally important in which order queries run, and that all queries in a group run, or none at all. The classic example is from the banking environment. An amount of money is taken from one person's account, and put into another, for example as follows, a 500-unit transaction:

UPDATE account1 SET balance=balance-500;
UPDATE account1 SET balance=balance+500;

Both queries must run, or neither must run. You cannot have the money being transferred out of one person's account, and then 'disappearing' if for some reason the second query fails. Both these queries form one transaction. A transaction is simply a number of individual queries that are grouped together.

A small dose of ACID

For a long time, when MySQL did not support transaction, its critics complained that it was not ACID compliant. What they meant, is that MySQL did not comply with the four conditions to which transactions need to adhere in order to ensure data integrity. These four conditions are:

  • Atomicity: An atom is meant to be the smallest particle, or something that cannot be divided. Atomicity applies this principle to database transactions. The queries that make up the transaction must either all be carried out, or none at all (as with our banking example, above).
  • Consistency: This refers to the rules of the data. For example, an article body may have to have an associated article heading. During the transaction, this rule may be broken, but this state of affairs should never be visible from outside of the transaction.
  • Isolation: Simply put, data being used for one transaction cannot be used by another transaction until the first transaction is complete. Take this example below, where an account balance starts at 900. There is a single deposit of 100, and a withdrawal of 100, so the balance at the end should remain the same.
    Connection 1: SELECT balance FROM account1;
    Connection 2: SELECT balance FROM account1;
    Connection 1: UPDATE account1 SET balance = 900+100;
    Connection 2: UPDATE account1 SET balance = 900-100;
    
    

    The balance is now 800, so we have lost 100. These two transactions should have been isolated, and the result supplied to Connection 2 only when the transaction from Connection 1 was complete.

  • Durability: Once a transaction has completed, its effects should remain, and not be reversible.

Down to work: InnoDB Transactions

Transactions are wrapped in BEGIN and COMMIT statements. Let's create a sample InnoDB table, and see how transactions work:

mysql> CREATE TABLE t (f INT) TYPE=InnoDB;

Now let's begin a transaction, and insert a record:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t(f) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t;
Empty set (0.00 sec)

Without a COMMIT statement, the insert was not permanent, and was reversed with the ROLLBACK. Note that the added record was visible during the transaction from the same connection that added it.

Consistent reads

Let's try looking from a different connection. For this exercise, open two connections to the database.

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
Empty set (0.02 sec) 

The important point is that running the same query from different connections (one within the middle of a transaction, the other from without) produces different results. Now, commit the transaction from the first connection, and run the query again from connection 2.

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

This behavior is called consistent reading. Any select returns a result up until the most recently completed transaction, with the exception of the connection doing the updating, as we saw above. By default, MySQL InnoDB tables perform consistent reads.

Automatic Commits

MySQL also automatically commits statements that are not part of a transaction. The results of any UPDATE or INSERT statement not preceded with a BEGIN will immediately be visible to all connections. You can change this behavior, as follows:

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

Now, note what happens, even if we do not specifically start a transaction with BEGIN.

Connection 1:

mysql> INSERT INTO t (f) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Commit the transaction from the first connection, then reset AUTOCOMMIT to 1, and repeat the exercise:

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (3);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

This time the transaction is committed immediately, and is visible from another connection, even without a specific COMMIT statement.

Read locks for Updating

Sometimes, the default consistent read is not what you want. There are cases where we would want to read a record in order to update it, knowing that we are not conflicting with any other connection that is doing the same thing. For example, two connections read a record, in order to insert a new value incremented by one from the current maximum.

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES(4);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t; 
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    4 |
+------+
5 rows in set (0.00 sec)

The result is that we have two records with the value 4, where we wanted to have one with 4, one with 5. We can overcome this by creating an update lock. This specifies that no other connections can read that data until the transaction is complete. Here is an update lock in action. First, we delete the erroneous record:

mysql> DELETE FROM t WHERE f=4;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.01 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;

No results are returned - MySQL is waiting until the active transaction is complete, and only then will it return the results Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

The results are now returned to connection 2. Note that the read may have timed out if you have taken too long:

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      4 |
+--------+
1 row in set (4.23 sec)

mysql> INSERT INTO t(f) VALUES(5);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

Read locks for sharing

Another kind of lock ensures you always read the latest data, but is not part of the transaction wanting to change the data itself. This is the LOCK IN SHARE MODE. It will stop any updates or deletes of the row being read, and if the latest data is still uncommitted, will wait until that transaction is committed before returning any results. Here's an example:

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t LOCK IN SHARE MODE;
+--------+
| MAX(f) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

Meanwhile, a second connection attempts to perform an update:

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;

The update waits until the lock from the other connection is released.

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 0 rows affected (6.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 1 row affected (43.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
+------+
5 rows in set (0.00 sec)

BDB tables handle transactions slightly differently, and the default MyISAM tables only in a very limited way (table locks only, as opposed to read locks), but if you are using transactions, you are most likely to be using InnoDB tables. If there's enough demand, I will write a follow up article on transactions in these table types, but hopefully you have enough to keep you busy in the meantime. Good luck!

» See All Articles by Columnist Ian Gilfillan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved