InnoDB Repeating AUTO_INCREMENT Values

This is a continuation of my earlier post with the same title. Recently while solving a problem where values of an auto_increment fields were getting repeated, a well documented and often neglected feature of InnoDB came forward. Lets first have a glimpse of the system and the problem.

We are maintaining a job queue in an InnoDB table for a distributed resource environment. This job queue is populated by distributed schedulers from the system, the job is assigned to a resource and once the job is done, it is removed from the job queue. The system runs on an assumption that the job id, which is an auto_increment field, always remain unique throughout the life of the system. I acknowledge that the usage is debatable, but lets keep it separate from this.

The problem: The operations team restarted the MySQL server in between. After that restart, some job id's got repeated. This broke the basic assumption of unique job id's and affected the whole business at large.

What caused this: After some hours of debugging and hypothesizing we figured out that the way InnoDB handles auto_increment values at server start is completely different from how MyISAM does and somewhat against the basic assumption of auto_increment values. InnoDB doesn't persists the auto_increment value between two MySQL instances. Rather when the server starts, for the first insert into the table, InnoDB executes the equivalent of this statement.

SELECT MAX(ai_col) FROM T FOR UPDATE;

As I said, this is a well documented feature, but often neglected. In our case, it happened that before the server shutdown, few of the values from the upper bracket were deleted, as normally they would have been.

Workarounds:

If you have such a system, where you rely too much on the auto_increment values from InnoDB, delete values frequently and assume them to be unique throughout the system, beware!

1. Try and give up the auto_increment column, if you can afford to, and maintain the logic of incrementing values in your code. There might be other concerns, but then its all your own choice.
2. Have another table which has only one auto_increment column, reference this from your original table and make sure you don't delete values from the upper bracket. You can safely expire the older entries in order to keep this table small enough.

Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: MySQL, InnoDB, AUTO_INCREMENT