MySQL Bugs: #55382: Assignment with SELECT expressions takes unexpected S locks in READ COMMITTED

Bug #55382	Assignment with SELECT expressions takes unexpected S locks in READ COMMITTED
Submitted:	19 Jul 2010 20:52	Modified:	25 Oct 2010 20:00
Reporter:	Bassam Tabbara	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.47 (innodb 1.0.8-11.1 ), 5.0, 5.1, 5.6.99	OS:	Linux (x64)
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
It looks like using SELECT expressions such as 

SET @result = (SELECT COUNT(*) FROM Test);

results in S locks being taken on the Test table even when running READ COMMITTED isolation.

How to repeat:
set transaction isolation level read committed;

CREATE TABLE Test (x int NOT NULL PRIMARY KEY);
INSERT Test VALUES (0);
INSERT Test VALUES (1);
INSERT Test VALUES (2);

BEGIN;

SET @result = (SELECT COUNT(*) FROM Test);

SHOW INNODB STATUS \G

------------
TRANSACTIONS
------------
Trx id counter 12C1B
Purge done for trx's n:o < 12C16 undo n:o < 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 4269, OS thread id 1175198032
MySQL thread id 384, query id 580703 192.168.2.1 root
---TRANSACTION 12C1A, ACTIVE 7 sec, process no 4269, OS thread id 1173600592
2 lock struct(s), heap size 376, 4 row lock(s)
MySQL thread id 421, query id 580767 localhost root
SHOW INNODB STATUS
TABLE LOCK table `Block`.`Test` trx id 12C1A lock mode IS
RECORD LOCKS space id 1652 page no 3 n bits 72 index `PRIMARY` of table `Block`.`Test` trx id 12C1A lock mode S
----------------------------

Note that the following does not result in any locks:

SELECT COUNT(*) FROM Test INTO @result;

This problem also happens with UPDATE...SET and INSERT...SET

Suggested fix:
No shared locks taken.

Thank you for the report.

Verified as described. At least inconsistency between 2 similar statement should be took in account.

The cause is that in this case, MySQL passes SQLCOM_SET_OPTION as the command to InnoDB (instead of SQLCOM_SELECT) when the select is embedded in the set option. As a result, InnoDB would do "locking reads for all SQL statements that
are not simple SELECTs" (with a few commands in a exempt lists). The fix would be adding the set option to the exempt list.

(gdb) p sql_command
$21 = 31

(gdb) p (enum enum_sql_command)31
$10 = SQLCOM_SET_OPTION

in  ha_innobase::store_lock()
{

...
             5) we let InnoDB do locking reads for all SQL statements that
                are not simple SELECTs; .....
....
                if ((srv_locks_unsafe_for_binlog   <=== a exempt list
                     || 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_REPLACE_SELECT
                        || sql_command == SQLCOM_UPDATE
-                       || sql_command == SQLCOM_CREATE_TABLE)){
+                       || sql_command == SQLCOM_CREATE_TABLE
+                       || sql_command == SQLCOM_SET_OPTION)) {
                        /* 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 REPLACE 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;
....
                } else {
                        prebuilt->select_lock_type = LOCK_S;  <== This is where we go now
                        prebuilt->stored_select_lock_type = LOCK_S;
                }

...
}

After the fix, it will not use table lock:

------------
TRANSACTIONS
------------
Trx id counter B01
Purge done for trx's n:o < 702 undo n:o < 0
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION B00, ACTIVE 8 sec, process no 9063, OS thread id 2972711792
MySQL thread id 1, query id 10 localhost root
SHOW ENGINE INNODB STATUS

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114815

3543 Jimmy Yang	2010-08-01
      Fix Bug #55382 Assignment with SELECT expressions takes unexpected S locks
      in READ COMMITTED
      
      rb://410 Approved by Sunny Bains

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Noted in 5.1.51, 5.5.7, 5.6.1 changelogs.

Assignment of InnoDB scalar subquery results to a variable resulted
in unexpected S locks in READ COMMITTED transation isolation
level.