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
Tags: DML, innodb, REPEATABLE-READ, v6
Triage: Triaged: D3 (Medium)

[5 Jun 2008 17:38] Mark Callaghan
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
[9 Jun 2008 13:35] Heikki Tuuri
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
[9 Jun 2008 15:06] Mark Callaghan
> 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.
[9 Jun 2008 17:13] Heikki Tuuri
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
[17 Jun 2008 22:24] Miguel Solorzano
Thank you for the bug report.
[12 Aug 2008 17:30] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=37357 has been marked as duplicate of this one.
[11 Mar 2009 22:36] Timothy Smith
Queued to 6.0-bugteam
[27 May 2009 20:30] Paul Dubois
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.
[14 Oct 2009 14:40] Bugs System
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)
[14 Oct 2009 17:01] Paul Dubois
Noted in 5.1.41 changelog.

Setting report to NDI pending push into 5.5.x.
[22 Oct 2009 6:36] Bugs System
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)
[22 Oct 2009 7:08] Bugs System
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)
[22 Oct 2009 20:07] Paul Dubois
Noted in 5.5.0 changelog.

For 6.0.x, already fixed in 6.0.10.
[18 Dec 2009 10:32] Bugs System
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)
[18 Dec 2009 10:48] Bugs System
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)
[18 Dec 2009 11:03] Bugs System
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)
[18 Dec 2009 11:18] Bugs System
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)
[5 May 2010 15:13] Bugs System
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)
[6 May 2010 15:53] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:02] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:30] Bugs System
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)
[28 May 2010 6:58] Bugs System
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)
[29 May 2010 23:03] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:14] Bugs System
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)
[15 Jun 2010 8:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:05] Bugs System
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)
[17 Jun 2010 12:50] Bugs System
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)
[17 Jun 2010 13:32] Bugs System
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)