We tried to do an LHM migration agains a large table with frequent writes the other day, and a flurry of deadlocks started appearing for writers to the table. According to the MySQL docs:
INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (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.
Which indicates we should be able to avoid deadlocks by lowering the transaction isolation level for those INSERT INTO SELECT statements coming from the migration.
We tried to do an LHM migration agains a large table with frequent writes the other day, and a flurry of deadlocks started appearing for writers to the table. According to the MySQL docs:
Which indicates we should be able to avoid deadlocks by lowering the transaction isolation level for those
INSERT INTO SELECTstatements coming from the migration.