My Favorite New Feature of MySQL 5.1: Less InnoDB Locking

MySQL 5.1 has a lot of nice new features, partitioning, event scheduler, dynamic logging, etc... Most of these require changing some application code or design logic to make use of them. However, there is a few new features that almost every user of InnoDB can take advantage of right away and that is the ability to set MySQL so that less locking is required for many statements in InnoDB. There are a few changes which make this possible.

The first one is the new auto_increment locking for InnoDB. There is a good article which talks about this here and a large section the in the manual.

In MySQL 5.0 and previous, InnoDB used a special query-duration table level lock to acquire the AUTO-INC lock. This basically caused all inserts into the same table to serialize. With single row inserts it normally wasn't too bad but could prevent some scalability with multiple threads inserting. However, where it was quite bad was large data insertions, such as INSERT...SELECT and LOAD DATA INFILE. While these were running, all other insertions into the table would be prevented due to this table level lock.

MySQL 5.1 has changed this behavior. There is a new option innodb_autoinc_lock_mode which controls how InnoDB will handle this. The default is a value of 1 which works well if you are doing only inserts where MySQL knows the amount of rows being inserted. For example, a single row insert or a multi-row insert would be fine in this mode. In these cases, it will use a very short term mutex which will be released immediately after acquiring the needed values. It should remove all contention issues if you are using only these statements.

Statements where the number of rows being inserted are unknown, such as INSERT...SELECT and LOAD DATA INFILE both still use the AUTO-INC table level lock for this and will cause other single row statements to lock on it. To fix this you need to set innodb_autoinc_lock_mode=2. This will make each row in these long running statements get a new value, rather than holding it for the duration. The problem with this is that now the values generated won't be consecutive anymore. Other transactions could grab values to use in the middle of the sequence.

For some applications this might be a problem (but shouldn't if you are just using the auto_increment for uniqueness). The other issues comes from the binary log. In statement level binary logging, the auto_increment starting point is logged and then the statement. However, since it is no longer consecutive, the slave can't create the appropriate values and replication will break. The solution to this also comes in 5.1 in the form of row level binary logging. You can enable all row based binary logging and it will then handle this without issue.

So to summarize, to pretty much completely remove auto_increment locking in InnoDB, you will need to set both innodb_autoinc_lock_mode=2 and binlog_format=row.


There is a second locking improvement in InnoDB related to the row level binary logging. To enable this, you will need to use READ COMMITTED isolation level. When you do this in 5.1, you will then cause InnoDB to reduce locking for DML statements a great deal. It will not lock any rows searched upon, but not changed with the DML statements.

In older versions every row that was searched in a data changing statement would be locked. For example, take the following statement:

DELETE FROM tbl WHERE key < 1000 AND non_key = 500

In 5.0, assuming the key was used, every row less than 1000 would be exclusively locked, even if only a few were changed. In 5.1 and READ COMMITTED, only the rows that are actually changed will be locked now. As you can imagine, this potentially can reduce locking contention a great deal.


One final topic I want to mention innodb_locks_unsafe_for_binlog option. People have asked if they should enable this when binlog_format=row. It makes sense to do so, since with row level binary logging, the restrictions for the binary log are gone. The answer is that this variable does not matter anymore in 5.1 if you set READ COMMITTED. In all cases where innodb_locks_unsafe_for_binlog used to reduce locking, READ COMMITTED now does as well.

From the manual:

This also means that if new items are added to the database, InnoDB does not guarantee serializability. Therefore, if innodb_locks_unsafe_for_binlog is enabled, InnoDB guarantees at most an isolation level of READ COMMITTED. (Conflict serializability is still guaranteed.)

This means that you also can not run higher than READ COMMITTED while using innodb_locks_unsafe_for_binlog, so there is no point in setting innodb_locks_unsafe_for_binlog.


So to summarize and get the best locking out of InnoDB in 5.1, you want to set the following options:

[mysqld]
innodb_autoinc_lock_mode=2
binlog_format=row
tx_isolation=READ-COMMITTED

Keep in mind that row based binary logging does potentially have additional disk overhead compared to statement binary logging, so as always, please test these values before using them in production.