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
» 
Comment and Contribute