Locking multiple objects in MySQL
I was looking for a way to lock several objects in a single transaction. It took me some time to figure it out, but it turned out to be very simple.
MySQL, using InnoDB tables, placing WRITE locks on (a distributed representation of) "objects"
An object in a programming language usually needs to be stored in a database, for later retrieval. Most developers are using a relational database to do this. This means that an object is stored as a record in a table. And if it stays that simple, there is no problem. However, once an object gets more complex, it will span many more records, in several tables. And when two or more processes start editing the same object at the same time, data corruption may occur. The objects need to be locked explicitly for writing, so that the other process waits for the first one to finish. But how do you lock "objects"?
I will give you an example of data corruption:
A relation is represented by this main record (table: `relation_main`):
[relation_id, lastname, firstname]
this relation has a single e-mail address in a separate table (`relation_address`, because he can have many addresses)
This is an object that spans several (2) tables. At some point an extra e-mail address needs to be added to this relation and two processes (either people and or automated processes) enter the new address at the same time. The processes both check if the e-mail adres is present already, and if not, add it. But since both processes are doing this at the same time, the new address will have been entered twice.
If your application has comparable situations, think of locking the objects: one of the processes acquires the lock first, the other process needs to wait until the other process is completely done with the object, before it can make its change.
Now MySQL has a function for just this purpose: GET_LOCK(str, timeout). It enables you to acquire a single lock on any given identifier. It could be the relation_id of our example. RELEASE_LOCK(str) will release the lock again.
If this was all there is to it I wouldn't be writing this article. The point is this: on the request of a second GET_LOCK(), MySQL will release the first lock. So the function does not allow you to acquire multiple locks. In a transaction it is often necessary to lock multiple objects at once. To be able to do that, we need another technique. I thought of some terribly hopeless alternatives that I will not expose here. In stead, let's look at the conclusion.
To lock an "object", change a single special record that is part of the object, or that represents it, by updating it.
How does that work? Well, remember the simple case where an object is represented by a single record? There is no need for an explicit object lock, because MySQL will place a write lock on the record automatically. You can use this principle to place a lock on an object with multiple records as well. All you need to do is to mark one of the object's records as special, and make sure that all processes that modify the object, modify this special record first. This way, by writing to a single record, a lock is acquired for the entire object.
You can use a special "locking" table for this purpose, or use a special record inside of the object. It doesn't matter what update is made to the main record: update its last-modified time if you like. But writing the existing value of a field right back to it will do just fine:
UPDATE `relation_main` SET `firstname` = `firstname` WHERE `relation_id` = 1234;
No change is made to the data, but MySQL will still place a write lock on the record.
When a process performs this update it may need to wait for another process to release this "lock". Only after the UPDATE has completed, the process should read the object's current data from the database, because the other process have have changed it.