MySQL Bugs: #5884: innodb_locks_unsafe_for

Bug #5884	innodb_locks_unsafe_for_binlog
Submitted:	4 Oct 2004 16:51	Modified:	5 Oct 2004 13:09
Reporter:	John David Duncan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1	OS:
Assigned to:		CPU Architecture:	Any

Description:
I am perplexed by the description of innodb_locks_unsafe_for_binlog (sec. 16.5, innodb startup 
options)...

What is the default value for this variable?  
Does setting it to either 0 or 1 result in a broken implementation of some transaction isolation 
levels, but not others? 
What is the effect on the binlog (which is not mentioned in the description at all) of setting it to 0 
or 1?
Does setting it one way or another effect performance at all? 

How to repeat:
n/a

Default value for a innodb option locks_unsafe_for_binlog is false i.e. 0. If you set the
value of this option to 1 it means that Innodb does not take gap type locks.

Normally InnoDB uses an algorithm called next-key locking. InnoDB does the row-level locking in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters. Thus the row-level locks are actually index record locks. The locks InnoDB sets on index records also affect the ``gap'' before that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the index order. This option causes InnoDB not to use next-key locking in searches or index scans. Next-key locking is still used to ensure foreign key constraints and duplicate key checking. Note that using this option may cause phantom problems: Suppose that you want to read and lock all children from the child table with an identifier value larger than 100, with the intent of updating some column in the selected rows later:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

Suppose that there is an index on the id column. The query will scan that index starting from the first record where id is bigger than 100. Now, if the locks set on the index records do not lock out inserts made in the gaps, a new row will meanwhile be inserted to the table. If you now execute the same SELECT within the same transaction, you will see a new row in the result set returned by the query. 

When using this option Innodb cannot quarantee repeatable reads i.e. following
queries might not return exactly the same result set:

set autocommit = 0;
SELECT * FROM child WHERE ID > 100 FOR UPDATE;
SELECT * FROM child WHERE ID > 100 FOR UPDATE;

As the name of the option says, using this option with binlog is not safe. This is because if we have following query:

set autocommit = 0;
INSERT INTO B ( SELECT * FROM A WHERE ID > 100 FOR UPDATE);

Now using innodb_locks_unsafe_for_binlog option set to 1 will allow insert to table A with id > 100, but they are not copied to table B. In some situations this might broke replication. Using this option allows more concurrency and that way effect on performance.

I had a small error on option values. The correct values for Innodb startup option
innodb_locks_unsafe_for_binlog are true or false.