MySQL Bugs: #75243: Locking rows from the source table for INSERT.. SELECT seems unnecessary in RBR

Bug #75243	Locking rows from the source table for INSERT.. SELECT seems unnecessary in RBR
Submitted:	17 Dec 2014 9:12	Modified:	2 May 2018 14:17
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlog, innodb, insert select, locking, RBR

Description:
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html
"INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED or innodb_locks_unsafe_for_binlog is enabled, and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally."

So when using the default REPEATABLE READ it will lock the rows in S. This is to make sure the roll-forward when recovery from backup will function.

I assume the 'roll-forward when recovery from backup' (shouldn't it be 'recovering' instead of 'recovery'?) means running mysqlbinlog. Or is this the InnoDB recovery from the redo logs?

This makes sense when binlog_format is STATEMENT. I don't think this makes sense when binlog_format=ROW or does it? And for MIXED.. that depends.

Does this mean that setting READ COMMITTED is not safe when using binlog recovery?

How to repeat:
See descripion

Suggested fix:
1. Don't set locks on S when binlog_format=ROW

Additionally:
2. Explain if READ COMMITTED is safe in case you need to recover from binlogs
3. Explain what recovery mechanism is applicable here
4. Explain why there are locks needed even if binlog_format=ROW

Thank you for the report.

Verified as described.

Posted by developer:
 
At InnoDB level, row locks and gap locks are taken based on the transaction isolation level.  While innodb_locks_unsafe_for_binlog does affect the gap locks in a certain way as described in the documentation, I don't see any further role for binlog format in this. And since innodb_locks_unsafe_for_binlog is a deprecated sysvar, it is best to think that transaction isolation level alone determines whether row locks and gap locks are taken.

To clarify this bugreport:
I was hoping that it might be possible to use less locks for 'INSERT INTO t SELECT .. FROM s' when the binlog format is row based.

If I understand it correctly the locks on the source table are needed to make sure these rows don't change before the transaction commits. This is understandable for statement based as otherwise the result in table t would be different when restoring from the binlog. But as RBR is used only the result matters (row images for table t) and the source (table s) doesn't.

This might improve performance because there can be more concurrency on table S.

* Only row-based binary logging is permitted with READ COMMITTED. 
https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

* The documentation has been updated to clarify that "recovery" in this context refers to roll-forward recovery using a statement-based binary log. The change should appear online soon.
https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

* As mentioned above, "If RBR is used, then the slave will not take any locks on S"

* As mentioned in "Disadvantages of Statement Based Replication", INSERT ... SELECT requires a greater number of row-level locks than with row-based replication.
https://dev.mysql.com/doc/refman/5.6/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-d...

If there are any issues raised in this bug report that have not been addressed, please open a new bug report or feature request.