Using the framework for testing we created in earlier articles, let’s try to modify some data. We are writing a small program that increments a counter. Our table looks like this, and contains 10 counters:
We are using some very simple programming to increment a counter:
Incrementing a single counter, 1000 times
In this code, we run the SQL
update demo set counter=counter+1 where id=3
or similar in order to increment a counter, and commit immediately. The loop
will run 1000 iterations, and we can time it. The speed of the loop is
completely dependent on how fast our hardware can commit data to disk.
14.4 seconds for 1000 commits are 14ms per commit, to an old SATA SSD - not
very fast at all, but this is 10 years old hardware. This is slow, because
db.commit() issues a single fsync to the Redo Log, waiting for it to
return - we have shown this in an earlier article.
We can globally reconfigure MySQL to write to disk unsafely:
In this configuration, MySQL will on commit still write the data into the file system buffer cache, but will no longer force the buffer cache to disk, foregoing the disk write and the wait for its completion. The server will flush data to disk in the background in 1s intervals.
Should the database server process crash, but the server hardware does not, nothing is lost: The file system buffer cache will eventually be written out do disk.
Should the server hardware crash, up to one second of data is being lost, but the database will still be consistent - just not at the transaction everybody expects.
Relaxing the disk write constraints speeds up writing considerably, because we no longer have to wait for the slow disk to acknowledge the writes.
Further relaxing the disk write constraints does not make things faster, just more dangerous:
In this mode, the database server will not even push data into the file system buffer cache on commit, but only once per second initiate a batch write and fsync. On any crash, up to one second of data will be lost, but at least it will be at a transaction boundary.
To make things more interesting, we will now run multiple copies of this programs in various ways.
Incrementing two counters concurrently, 1000 times each
We can see the program counted correctly, both counters have the expected target value. We can also see how the program took a lot longer to execute: While we have multiple cores, and are running two copies of Python and two thread in the database concurrently, there is still interference between the two threads, and things are slower (20.4 seconds instead of 14.4 seconds).
Incrementing the same counter with two processes, 1000 times each
What happens here is that we are running two instances of the script
concurrently, each of which is incrementing the same row
id=3. As updating
a row will also exclusively lock it, the second writer has to wait a bit
until the first one commits (which will also drop the lock). Then the second
writer can get access, and do its thing while the first writer has to wait.
Execution time goes up further, from 20.4s to 25.3s.
Read-Modify-Write, gone wrong
We can query the database differently, in a two stage process, allowing for more complex updates. Here we read a value from the database into the application (read phase), change it applicationn side (modify phase) and then write the changed value back.
Doing this is called a read-modify-write cycle.
In this piece of code we again count to 1000, but we are doing it in a three step process, called a read-modify-write cycle. We also implemented it wrongly for demonstration purposes:
The read phase is done with a
SELECTstatement that retrieves the current counter value from the database.
The modify phase increments the counter, application side, in memory. This can in real applications be an arbitrarily complicated process that takes a non-trivial amount of time and maybe even connects to different backend systems and services.
The write phase then uses
UPDATEto write back the previously read value to the database.
There is no error handling, and - for the purposes of demonstration - no locking.
We are running this, standalone, and then in multiple copies:
This is not slower than the single update statement, despite the fact that we have to talk to the database in two round trips instead of one.
But, when we are running this twice, we can see that due to the missing locking the results are wrong:
The way we write this code, we extract the value from the database into the application, getting a value - say 10, and increment that.
Meanwhile the other instance also reads the database, and gets the same value, also 10.
We increment, and write back the new value, 11. Then the other instance does the same, and also writes back 11. Two increment events in two processes have been executed, but the counter has been incremented effectively only one step, because the rmw-cycles overlapped.
We need to lock the row
id=3 when we take out the value from the
database into the application like we would take out a book from the
library. We can give up the lock when we write back and commit. The second
process, attempting to take out the same value we just took out, would stall
and hang, waiting for the (new) value to become available, and only then can
Read-Modify-Write, done right
We modify the program to use
SELECT ... FOR UPDATE instead of a simple
SELECT when taking out the value during the read phase of the rmw
SELECT ... FOR UPDATEis a read statement, but locks the rows it fetches as if it were a write statement. In doing that, it anticipates the
UPDATEoperation that is to follow.
What happens is that the
SELECT ... FOR UPDATE creates X-locks on at
least the rows it returns. An X-lock or exclusive lock is a lock that
guarantees that only one thread (us) can access the row while the lock is
being held. All other attempts to read that row are stalled and have to wait
until the lock is released, at
Our program now counts correctly. Locks mean waiting, so of course it is slower.
And this is what the code looks like: