Bug #2039 | SELECT FOR UPDATE does not lock the selected rows | ||
---|---|---|---|
Submitted: | 7 Dec 2003 17:16 | Modified: | 18 Dec 2003 15:45 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.1.0-alpha-max-nt | OS: | Windows (Win2k) |
Assigned to: | CPU Architecture: | Any |
[7 Dec 2003 17:16]
[ name withheld ]
[7 Dec 2003 23:10]
Alexander Keremidarski
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Additional info: Session A: mysql> create table x (id int primary key, a int) type=innodb; Query OK, 0 rows affected (0.06 sec) mysql> insert into x values(1, 1); Query OK, 1 row affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from x where id=1 for update; +----+------+ | id | a | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec) Session B: mysql> select * from x where id=1 for update; ERROR 1205: Lock wait timeout exceeded; Try restarting transaction
[8 Dec 2003 1:15]
[ name withheld ]
Thanks for the fast response! Your example gave me the idea: In my tests, i did not include "type=innodb" in the create table statement, since the doc says that this was the default. Omitting it, however, the select for update's do not work. (Regardless of in which DB i put the table.) Now I have retested the whole, and if I specify "type=innodb", the locking works. The error can be reproduced for example with the following: (Tested with Squirrel, of course autocommit=off) create table testx (id int unique not null, a int); 0 Rows Updated Query 1 elapsed time (seconds) - Total: 0,07, SQL query: 0,07, Building output: 0 insert into testx values (1, 1); 1 Rows Updated Query 1 elapsed time (seconds) - Total: 0,01, SQL query: 0,01, Building output: 0 select * from testx where id=1 for update; which returns immediately in both sessions. Regards, Balazs
[18 Dec 2003 15:45]
Alexander Keremidarski
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: Default table type is runtime parameter and if not specifed default is MyISAM not InnoDB. This is explained in MySQL Manual and it is also explained that SELECT FOR UPDATE applies to InnoDB storage only. Current default table setting can be verified with: mysql> show variables like "table_type"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | table_type | MYISAM | +---------------+--------+