Transactions in MySQL - Page 2

July 20, 2004

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers