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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.51-log OS:Any
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.