MySQL Bugs: #39994: innodb_locks_unsafe_for

Bug #39994	innodb_locks_unsafe_for_binlog
Submitted:	13 Oct 2008 6:56	Modified:	27 Jan 2009 19:24
Reporter:	Vinod Sugur	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	6.0.6-alpha-community	OS:	Microsoft Windows
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	vs_innodb

Description:
Hi,

Please clarify on the below documentation for innodb_locks_unsafe_for_binlog variable.

<quote>

In this case, the second UPDATE must wait for a commit or rollback of the first UPDATE. The first UPDATE  has an exclusive lock on row (2,3), and the second UPDATE while scanning rows also tries to acquire an exclusive lock for the same row, which it cannot have. This is because UPDATE two first acquires an exclusive lock on a row and then determines whether the row belongs to the result set. If not, it releases the unnecessary lock, when the innodb_locks_unsafe_for_binlog variable is enabled. 

</unquote>

The above statements says that the mentioned scenario will occur when the variable innodb_locks_unsafe_for_binlog is ENABLED but its actually reverse.

Please clarify the same.

Thanks,
Vinod

How to repeat:
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+

Executed the below example as given in documentation:

CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

Suppose that one client executes these statements:

SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;

Then suppose that another client executes these statements following those of the first client:

SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;

The second updates waits as the rows are locked by the first update.

The above scenario occurred when the variable value is set to innodb_locks_unsafe_for_binlog flag is DISABLED.

Suggested fix:
Documentation should corrected

The example in the manual does indeed work as described for innodb_locks_unsafe_for_binlog *disabled*, not *enabled*. It seems the manual does describe what you would expect to happen, but that the actual behavior does not follow the description.

It would be good to have an InnoDB developer look at this and comment whether the manual is wrong (and what it should say instead, if so), or whether there's really a bug here.

I've done some further testing, and it appears the results are version specific:

In 5.0.x, the second client blocks until the first client terminates its transaction, regardless of whether innodb_locks_unsafe_for_binlog is enabled.

In 5.1.x and 6.0.x, the second client blocks until the first client terminates its transaction if innodb_locks_unsafe_for_binlog is disabled, but does not block if innodb_locks_unsafe_for_binlog is enabled. This is true regardless of the binary logging format (row, statement, or mixed).

What are the expected results, and should they be version specific?

Heikki, would you like to comment?

Paul,

the paragraph in the manual is in error.

Your testing revealed the expected results. 

Let us assume that innodb_locks_unsafe_for_binlog is ENABLED (or in 5.1 or later we do READ COMMITTED):

- In 5.1 and later, InnoDB does a 'semi-consistent' read and returns the latest COMMITTED version of the row to MySQL, so that MySQL can decide if the latest COMMITTED version of the row satisfies the WHERE condition of the UPDATE.

- In 5.0, InnoDB has to wait because 5.0 did not have the semi-consistent read trick.

Let us then assume that innodb_locks_unsafe_for_binlog is DISABLED:

- Then InnoDB has to wait in all MySQL versions.

Regards,

Heikki

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I've updated the example for 5.1 and 6.0 to read like this:

If innodb_locks_unsafe_for_binlog is enabled, the first UPDATE
acquires x-locks and releases those for rows that it does not modify:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

For the second UPDATE, InnoDB does a "semi-consistent" read,
returning the latest committed version of each row to MySQL so that
MySQL can determine whether the row matches the WHERE condition of
the UPDATE: 

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

Semi-consistent read is available as of MySQL 5.1.5. Before 5.1.5,
the second UPDATE proceeds part way before it blocks. It begins 
acquiring x-locks, and blocks when it tries to acquire one for a row
still locked by first UPDATE. The second UPDATE does not proceed
until the first UPDATE commits or rolls back:

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); block and wait for first UPDATE to commit or roll back

In this case, the second UPDATE must wait for a commit or rollback of
the first UPDATE, even though it affects different rows. The first
UPDATE has an exclusive lock on row (2,3) that it has not released.
As the second UPDATE scans rows, it tries to acquire an exclusive
lock for that same row, which it cannot have. Thus, enabling
innodb_locks_unsafe_for_binlog still does not allow operations such
as UPDATE to overtake other similar operations (such as another
UPDATE) even when they affect different rows.