MySQL Bugs: #37232: Innodb might get too many read locks for DML with repeatable-read

Bug #37232	Innodb might get too many read locks for DML with repeatable-read
Submitted:	5 Jun 2008 17:38	Modified:	20 Jun 2010 0:57
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.24	OS:	Any
Assigned to:	Satya B	CPU Architecture:	Any
Tags:	DML, innodb, REPEATABLE-READ, v6

Description:
This code in ha_innobase::store_lock determines when InnoDB won't get read locks on rows read during the execution of a DML statement. I think it misses several cases.
1) If the binlog is not open, this code path should be entered as this code exists to make binlog events produce the same output on a slave. If the binlog isn't open, then there is no problem.
    if ((srv_locks_unsafe_for_binlog || !binlog_open() || ...
2) What about SQLCOM_DELETE and SQLCOM_REPLACE_SELECT?

                if ((srv_locks_unsafe_for_binlog
                     || isolation_level == TRX_ISO_READ_COMMITTED)
                    && isolation_level != TRX_ISO_SERIALIZABLE
                    && (lock_type == TL_READ || lock_type == TL_READ_NO_INSERT)
                    && (sql_command == SQLCOM_INSERT_SELECT
                        || sql_command == SQLCOM_UPDATE
                        || sql_command == SQLCOM_CREATE_TABLE)) {

                        /* If we either have innobase_locks_unsafe_for_binlog
                        option set or this session is using READ COMMITTED
                        isolation level and isolation level of the transaction
                        is not set to serializable and MySQL is doing
                        INSERT INTO...SELECT or UPDATE ... = (SELECT ...) or
                        CREATE  ... SELECT... without FOR UPDATE or
                        IN SHARE MODE in select, then we use consistent
                        read for select. */

                        prebuilt->select_lock_type = LOCK_NONE;
                        prebuilt->stored_select_lock_type = LOCK_NONE;

How to repeat:
Look at the code above

Mark,

in SQLCOM_DELETE and SQLCOM_REPLACE_SELECT InnoDB always locks the result set. The reason is that the statement semantics would be vague in the case we would just use a consistent read to read the result set.

I deemed INSERT ... SELECT ... simple enough that using a consistent read in the SELECT makes sense on lower locking levels. The semantics is clear there.

It is intentional that binlogging does not affect the locking behavior. It would be a surprise to users if locking would change when they toggle the binlog on or off.

I am assigning the rest of this bug report to Inaam.

Regards,

Heikki

> in SQLCOM_DELETE and SQLCOM_REPLACE_SELECT InnoDB always locks the
> result set. The reason is that the statement semantics would be vague
> in the case we would just use a consistent read to read the result
> set.

How is 'replace into foo select ...' any different from 'insert into foo select ...'?

> It is intentional that binlogging does not affect the locking behavior.
> It would be a surprise to users if locking would change when they toggle
> the binlog on or off.

This behavior was a huge denial of service on slaves, until I changed it. And many people will not learn that they need to set innodb_locks_unsafe_for_binlog until too late.

The problem is that I have many long running queries on slaves doing things like:
insert into ScratchInnodbTable select * from ReplicatedInnodbTable

and the replication workload has changes (insert/update/delete) for ReplicatedInnodbTable. When this occurs, the slave gets many timeouts and replication gets far behind because of read locks held on ReplicatedInnodbTable.

Even with innodb_locks_unsafe_for_binlog, the problem still occurs for multi-table delete statements, multi-table update statements and replace-select.

Mark,

hmm... REPLACE INTO t SELECT ... acts as an insert or an update depending on whether the rows in t exist.

You are right that the semantics are sufficiently clear in this case. We could relax locking in a future 6.x version.

I am not so sure about the multi-table delete and update. Generally, it may confuse people if we use the LATEST version of one table (the updated table) and a SNAPSHOT version of another table.

Regards,

Heikki

Thank you for the bug report.

Bug: http://bugs.mysql.com/bug.php?id=37357 has been marked as duplicate of this one.

Queued to 6.0-bugteam

Noted in 6.0.10 changelog.

Previously, InnoDB performed REPLACE INTO T SELECT ... FROM S WHERE
... by setting shared next-key locks on rows from S. Now InnoDB
selects rows from from S with shared locks or as a consistent read,
as for INSERT ... SELECT. This reduces lock contention between
sessions.

Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091009133747-iq5nu042j74xc32c) (merge vers: 5.1.40) (pib:13)

Noted in 5.1.41 changelog.

Setting report to NDI pending push into 5.5.x.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.

For 6.0.x, already fixed in 6.0.10.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)