MySQL Bugs: #49009: Subselect seems to implicitly do LOCK IN SHARE MODE?

Bug #49009	Subselect seems to implicitly do LOCK IN SHARE MODE?
Submitted:	23 Nov 2009 23:43	Modified:	24 Dec 2009 19:47
Reporter:	time e.less	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.77	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Isolation level, lock, LOCK IN SHARE MODE, subselect, transaction

Description:
When running a subselect on a table that has earlier had an INSERT to it, the statement locks as though the earlier statement was done with lock in share mode.

How to repeat:
drop table if exists version;
drop table if exists names;
create table IF NOT EXISTS version (
       id int AUTO_INCREMENT primary key
) engine innodb;
create table IF NOT EXISTS names (
       id int auto_increment primary key,
       name varchar(64),
       version int
) engine innodb;

MySQL client session one:

set session transaction isolation level read uncommitted;
start transaction;
insert into version values ();
insert into names (name, version) values ('foo1', (select max(id) from
version));
select sleep(15);
commit;

MySQL client session two:

set session transaction isolation level read uncommitted;
start transaction;
insert into version values ();
insert into names (name, version) values ('foo1', (select max(id) from
version));
select sleep(15);
commit;

The second "insert into version values ()" will hang for 15 seconds while the 1st transaction sleeps. But the first transaction shouldn't be locking the version table at all!

Suggested fix:
I can find no transaction isolation level that allows both transactions to complete concurrently. Make one, or make read-committed or read-uncommitted work like documented?

Thank you for the problem report. Please, send the results of:

show global variables like 'innodb_locks_unsafe_for_binlog';

I am familiar with that variable. Another person having similar problems mentions turning this variable on to make things work, at the cost of more danger to transactions.

But when I look at documenation on transaction isolation levels, I don't see mention of it.

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

That page has no word "binlog" on the entire page. If you require this to be ON for the transaction isolation levels to work as documented, you should probably mention it in the docs?

That said, here's the info you've requested:

mysql> show variables like '%unsafe%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   | 
+--------------------------------+-------+

It is OFF, which as I understand it, is the safe/proper way to have this variable set.

I just wanted to point out that other page, http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html, says:

"INSERT INTO T SELECT ... FROM S WHERE ...  sets an exclusive index record without a gap lock on each row inserted into T. If 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."

I think this quote describes your situation. Maximum value selected is locked with S lock, and it prevents insertion of the next value from concurrent transaction. 

That's why, please, check if setting innodb_locks_unsafe_for_binlog makes any difference.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".