locking - MySQL select for update returns empty set even though a row exists -

locking - MySQL select for update returns empty set even though a row exists -

i'm seeing unusual issue mysql's "select update". using version 5.1.45. have 2 tables:

mysql> show create table tag; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tag | create table `tag` ( `id` int(10) unsigned not null auto_increment, `name` varchar(255) not null, `message` varchar(255) not null, `created_at` bigint(20) unsigned not null, primary key (`id`) ) engine=innodb auto_increment=16 default charset=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table live_tag; +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | live_tag | create table `live_tag` ( `tag_id` int(10) unsigned not null, key `live_tag_tag_fk` (`tag_id`), constraint `live_tag_tag_fk` foreign key (`tag_id`) references `tag` (`id`) ) engine=innodb default charset=utf8 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

the first stores versions ("tags") user has saved, along commit message. sec table contains id of version "live". there foreign key on live_tag.tag_id referencing tag(id). live_tag ever contains single row. row updated when new version committed. before updating live_tag row execute statement:

mysql> select tag_id live_tag update;

however, when run statement in 2 terminals, , update tag_id in 1 of them, mysql returns "empty set" in sec terminal instead of new value:

-- terminal 1 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 2 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 1 mysql> select tag_id live_tag update; +--------+ | tag_id | +--------+ | 2 | +--------+ 1 row in set (0.00 sec) -- terminal 2 mysql> select tag_id live_tag update; -- hangs (waiting lock) -- terminal 1 mysql> update live_tag set tag_id = 1; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> commit; query ok, 0 rows affected (0.01 sec) -- terminal 2 returns next previous "select tag_id live_tag update" empty set (8.54 sec) -- why empty set?

i did not delete rows, updated 1 row in live_tag, why isn't mysql seeing update?

what's more weird, i've noticed if set live_tag higher value previously, sec terminal correctly returns new value:

-- terminal 1 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 2 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 1 mysql> select tag_id live_tag update; +--------+ | tag_id | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) -- terminal 2 mysql> select tag_id live_tag update; -- hangs (waiting lock) -- terminal 1 mysql> update live_tag set tag_id = 2; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> commit; query ok, 0 rows affected (0.01 sec) -- terminal 2 returns next previous "select tag_id live_tag update" +--------+ | tag_id | +--------+ | 2 | +--------+ -- right

the problem occurs when set tag_id lower value previously.

is due foreign key constraint on tag_id? or because i'm selecting rows in table (no 'where' clause)?

what i've tried:

after dropping keys on live_tag.tag_id, works correctly.

i added id column live_tag, , limited 'select update' 'where id = 1'. works correctly.

i tried 3 terminals. after committing 1, 2 returns empty set. few seconds later, 3 returns empty set (even though haven't committed 2).

i'm fine adding id column table, still curious odd behavior? i've tried googling , searching here, haven't found answer.

update

barmar's theory seems correct, since tried suggested test, , got 1 row in response:

-- terminal 1 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 2 mysql> start transaction; query ok, 0 rows affected (0.00 sec) -- terminal 1 mysql> select tag_id live_tag update; +--------+ | tag_id | +--------+ | 2 | | 3 | +--------+ 2 rows in set (0.00 sec) -- terminal 2 mysql> select tag_id live_tag update; -- hangs -- terminal 1 mysql> update live_tag set tag_id=1 tag_id=2; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> update live_tag set tag_id=4 tag_id=3; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> select * live_tag; +--------+ | tag_id | +--------+ | 1 | | 4 | +--------+ 2 rows in set (0.00 sec) mysql> commit; query ok, 0 rows affected (0.00 sec) -- terminal 2 returns +--------+ | tag_id | +--------+ | 4 | +--------+ 1 row in set (34.02 sec)

anyone have newer version of mysql wants seek this?

from dependency on setting value of indexed column higher or lower, looks lock beingness placed on index entry. database engine scans index, , stops @ first locked entry, waiting released.

when first transaction committed, index unlocked, , waiting transaction continues scanning index. because value lowered, before in index. resumed scan doesn't see because has passed point.

to confirm this, seek next test:

create 2 rows, values 2 , 3. in both transactions, select ... update in transaction 1, alter 2 1, 3 4. commit transaction 1.

if guess correct, transaction 2 should homecoming row 4.

this seems bug me, don't think should ever partial results this. unfortunately, it's hard search @ bugs.mysql.com, because word "for" ignored when searching because it's short or common. quoting "for update" doesn't seem find bugs contain phrase.

mysql locking