Bug #39222 no difference between LOCK IN SHARE MODE and LOCK FOR UPDATE

• Submitted: 3 Sep 2008 19:00
• Modified: 3 Sep 2008 19:32
• Reporter: Julien B
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.51-log
• OS: Any
• CPU Architecture: Any
• Tags: LOCK IN SHARE MODE / LOCK FOR UPDATE

[3 Sep 2008 19:00] Julien B

Description:
I am working with REPEATABLE-READ isolation level.
I have an innodb table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I have some records inside:

insert into users values (1,'test',20);
insert into users values (2,'test2',21);
...

i am lauching two mysql clients command line.

on each command line i start a transaction.
Then i am doing this query on the first client:
select * from users where id=1 for update.

On the second command line, i do:
select * from users where id=1

There should be a wait (or lock) at this point because the "for update" should put an exclusive mode...
Or i get the row #1...

How to repeat:
There is no difference bettween lock in "share mode" and "for update" with select statement

[3 Sep 2008 19:32] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Use SELECT FOR UPDATE in second client if you want to get SELECT locked.