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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 6.0.6-alpha-community | OS: | Windows |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | vs_innodb |
[13 Oct 2008 6:56]
Vinod Sugur
[18 Nov 2008 18:52]
Paul DuBois
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.
[19 Nov 2008 20:33]
Paul DuBois
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?
[19 Nov 2008 22:28]
Mikhail Izioumtchenko
Heikki, would you like to comment?
[20 Nov 2008 17:09]
Heikki Tuuri
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
[27 Jan 2009 19:24]
Paul DuBois
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.