Auto-incremented values can appear out of order if inserted in transactions

Last week I was surprised to learn that auto-incrementing field values in MySQL don't always necessarily appear in sorted order to clients. That is, it's possible for a count of rows in a table to increase while the max of an auto-inc col remains unchanged. Auto-incremented values are created when the record is inserted, not necessarily when the transaction during which the record is inserted is committed.

One of our systems at work can replicate updated rows from a table given that a column in it contains strictly incrementing values denoting the order of updates. I.e., if our system observes a row where the replication column has value X, subsequent replication jobs will only query for rows with a replication column value >= X. This is useful for efficiently replicating tables with a modified_at column or an append-only table with an incrementing ID. Most of the time...

We have a user who was inserting rows very rapidly into one such source database. They were using an AUTO_INCREMENTing column as a replication key for our system. In general there's no issue with doing this. We found, however, that in this case, rows were being inserted in batches as part of transactions happening parallel.

I discovered that auto-incrementing values are created at insertion time, not at commit time. In the case of relatively long-running transactions that happen simultaneously, this can result in auto-inc field values being visible in an unpredictable order.

It's fairly easy to illustrate this problem. Here I use two MySQL connections to show what can happen when there are multiple transactions inserting into a table simultaneously.

First, create a table with an auto-inc field and insert a row.

mysql> CREATE TABLE animals (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into animals (name) values ('aardvark');
Query OK, 1 row affected (0.02 sec)

Observe that a simple SELECT query returns the row, as expected. The id field starts at 1.

mysql> select * from animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | aardvark |
+----+----------+
1 row in set (0.00 sec)

Now, start a new transaction and insert some more records:

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

mysql> insert into animals (name) values ('bear'), ('cow'), ('dinosaur');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

In another terminal, connect to the database and select from the table. You'll see only the original row is visible. The three records inserted during the transaction have not yet been committed.

mysql> select * from animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | aardvark |
+----+----------+
1 row in set (0.00 sec)

Now, still in the second terminal, start a separate transaction and insert three more records. This time, though, commit the transaction changes.

If you query the database after that commit, you'll see that the three most recently committed rows are visible and are returned by the query. Also note, importantly, that auto-incrementing id field values have incremented past the values that were inserted but not yet committed.

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

mysql> insert into animals (name) values ('elephant'), ('frog'), ('groundhog');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aardvark  |
|  5 | elephant  |
|  6 | frog      |
|  7 | groundhog |
+----+-----------+
4 rows in set (0.00 sec)

Back in the first terminal, you can commit the first transaction. Now all the records will be returned by a SELECT.

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

mysql> select * from animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | aardvark  |
|  2 | bear      |
|  3 | cow       |
|  4 | dinosaur  |
|  5 | elephant  |
|  6 | frog      |
|  7 | groundhog |
+----+-----------+
7 rows in set (0.00 sec)

I think the moral of the story is: don't rely on the order in which values in an AUTO_INCREMENT column appear. There could be rows in transactional flight with smaller values than the max of the auto-inc column.

Online courses

I recently completed the Machine Learning Foundations course offered through Coursera by the University of Washington. I found it to be more accessible and more of a survey class than Andrew Ng's Machine Learning course. It was eye-opening to learn how powerful machine learning algorithms are relatively easy to begin working with. Thinking about what kinds of breakthroughs the present AI summer will lead to is exciting. I'll probably continue to take a hobbyist's interest in ML and deep learning--luckily there's an endless torrent of free or inexpensive material on the subject.

After that I decided to take another course that was more applicable to my regular SE job--Functional Programming Principles in Scala. So far the class is challenging and fun. And it's nice to have an excuse to work with IntelliJ IDEA. I have a decent grasp of practical functional programming from doing Clojure development at work, but I wanted to get a more formal exposure to it.

Started using Spacemacs

For a long time I'd been using Sublime Text as my primary code/text editor, and I loved it very much. A few months ago, though, I decided it was time for a change. Connecting to an nrepl via Sublime wasn't supported, I had some concerns about the long-term support of a closed-source editor, and I started hearing about an editor called Spacemacs.

As someone who doesn't find the endless text editor war particularly interesting, and who just wanted a modern, powerful all-purpose editor, Spacemacs appealed to me. Longtime users of either Vim or Emacs will likely scoff at Spacemacs' unholy marriage of the two. Spacemacs is essentially a bunch of configuration on top of Emacs with Vim keybindings as the default ("The best editor is neither Emacs nor Vim, it's Emacs and Vim!"). Whenever I tried to pick up Emacs in the past, I found the keybindings unintuitive. I liked Vim keybindings more (and had been using ST's Vintage Mode to approximate that), but both Emacs and Vim lacked for me the seamlessness of ST's Package Control and its aesthetic.

Spacemacs seems to be the sweet spot for now. Its "layers" system is almost as simple as ST's package manager and the keybindings are logical and consistent across modes. And it looks good with very little customization.

It's not without warts--some mode buffers don't support evil keybindings and will fall back to Emacs bindings. The community-configuration is a double-edged sword: you get a lot of nice stuff out of the box, but when something goes wrong it can be hard to know how to fix it, although the community I've found to be helpful.

Some of my coworkers seem to have expressed worry that I'm investing time into what they see as a dead-end editor--that ten years from now I'll have accumulated a bunch of useless muscle memory. I'm not too concerned.

2015 Review

Professionally, I grew a lot in 2015. I started working at RJMetrics in April 2014. It was my first experience at a start-up, and my first real foray into writing software for the web. The shift in workplace culture and technology I experienced that first year made me excited and anxious. I was learning new stuff at a fast clip, and I'm happy to say that in 2015 that rate of learning didn't slow down.

In 2015 I made the jump from mostly front-end development to a mixture of front- and back-end. I finally started learning Clojure and functional programming.

I still do front-end work, and I've become someone that other members of the team come to with AngularJS questions. I feel more comfortable with Sass and styling than I did at first, and I'm more familiar with Gulp and our other front-end tooling. I'm looking forward to playing with Angular2, and maybe even ClojureScript and/or React down the line.

Over a few weeks in my downtime I built a single page app using Angular. Tumblr Top gives users a quick visual overview of a Tumblr blog's top original content.

This was fun because it was my first Angular app from scratch, my first experience with Material Design, and the first project I put on my public GitHub account.

It took me a while to pick up Clojure. I'd only ever done imperative programming, and I initially found Lisp syntax intimidating and confusing. And there was so much vocabulary. Arity, records, multimethods, monads, macros, etc.

There was a chicken/egg situation where I wanted to work on a Clojure project to learn the language, and I needed to learn the language to work on a Clojure project. Slowly, I began edging into it. Outside of work I did 4clojure problems, occasionally visited Clojure for the Brave and True, and began reading The Joy of Clojure (which I gave up and then later picked up again).

I did get a chance to use Clojure heavily at work--it came near the end of the year when my team was tasked to design and implement a notification system. In short, this new service allows for end-users to receive notifications about actionable errors throughout the system. The various microservices publish some event messages about their state, and the notification service consumes those messages, sending the proper email and/or in-app notifications. Making the new service robust, scalable, extensible, and idempotent was challenging, but it was probably the most important professional experience I had in 2015. The project is nearly done, but I finally feel comfortable with Clojure. I'm excited to continue using this beautiful and expressive language.

Over the summer I gave a presentation at work about the publish-subscribe messaging pattern. I enjoyed researching this topic and thinking about ways that our microservices could make use of an event bus and the pub/sub pattern.

Our production infrastructure at work is largely on AWS. This year, mostly due to my role on the notification project, I got a lot of exposure to this DevOpsy side of things. The notification pub/sub system employs SNS and SQS in a fanout topology, where message producers publish to an SNS topic. That topic fans out to one or more SQS queues, which consumers then read from. I touched other parts of AWS, too--OpsWorks and CloudWatch come to mind.

In general, I feel more comfortable at work in terms of the environment and my place within the company. I also feel more confident in my abilities.

The coming year looks promising. I want to increase my focus on my hobbies. I want to learn more about Clojure and use it in a personal project (maybe something with machine learning?)

PS: A few weeks ago I started using Taskwarrior to keep a todo list at the command line, and it's helped my focus at work tremendously.

Outside of work, in 2015 I:

• Finished reading Infinite Jest. It took me an embarrassingly long time, but it was likely the best book I ever read. In 2016 I want to read more. I renewed my library card and everything.
• Moved the literary web magazine I help run from loose HTML/CSS to WordPress. It's now a lot better for mobile devices, and a lot easier to maintain and expand.
• Tried out jogging...it's pretty fun. By the end of that experiment I was able to run three miles straight. Hopefully I'll have the drive to pick it up again in the spring.
• Visited Savannah, Nashville, and North Carolina's Outer Banks. I should travel more, too.

deWordify Updated

I just updated deWordify, a tool to convert Microsoft Word files to HTML. Now, you can optionally remove any unnecessary non-breaking spaces or line breaks formatted as paragraphs that are sometimes in Word files.

I made deWordify to help me format the Microsoft Word writing submissions that end up on the online literary magazine I maintain. I like that process improvement doesn’t really feel like work, but it’s probably some of the most important work.