MySQL select to update returns empty set even if row exists
I see a strange problem with MySQL "select for update". I am using version 5.1.45. I have two 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 saved versions ("tags") of the user are saved with the commit message. The second table contains the ID of the version that is currently "live". There is a foreign key on the live_tag.tag_id reference tag (id). live_tag only ever contains one line. This line is updated when a new version is committed. Before updating the live_tag line, I execute this statement:
mysql> select tag_id from live_tag for update;
However, when I run this statement in two terminals and update the tag_id in one of them, sometimes MySQL returns an "empty set" in the second terminal instead of the new value:
-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs (waiting for lock)
-- TERMINAL ONE
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 TWO returns the following for previous "select tag_id from live_tag for update"
Empty set (8.54 sec) -- Why empty set?
I didn't delete any rows, I was just updating one row in the live_tag, why doesn't MySQL see the update?
Oddly enough, I noticed that if I set live_tag to a higher value than it did before, the second terminal will correctly return the new value:
-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs (waiting for lock)
-- TERMINAL ONE
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 TWO returns the following for previous "select tag_id from live_tag for update"
+--------+
| tag_id |
+--------+
| 2 |
+--------+
-- this is correct
The problem only occurs when the tag_id is set to LOWER than before.
Is this something because of foreign key constraint on tag_id? Or because I am selecting all rows in the table (no where clause)?
What I've already tried:
-
After removing the keys in live_tag.tag_id, it works correctly.
-
I added an id column to live_tag and limited my "select to update" "where id = 1". This also works correctly.
-
I tried this with 3 terminals. After committing 1, 2 immediately returns an empty set. After a few seconds, 3 also returns an empty set (although I haven't done 2 yet).
I'm fine with adding an id column to my table, but still curious about this strange behavior? I tried searching and searching here but couldn't find an answer.
UPDATE
Barmar's theory seems to be correct as I tried his suggested test and only got 1 line in the answer:
-- TERMINAL ONE
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL TWO
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL ONE
mysql> select tag_id from live_tag for update;
+--------+
| tag_id |
+--------+
| 2 |
| 3 |
+--------+
2 rows in set (0.00 sec)
-- TERMINAL TWO
mysql> select tag_id from live_tag for update;
-- hangs
-- TERMINAL ONE
mysql> update live_tag set tag_id=1 where 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 where tag_id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from live_tag;
+--------+
| tag_id |
+--------+
| 1 |
| 4 |
+--------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- TERMINAL TWO returns
+--------+
| tag_id |
+--------+
| 4 |
+--------+
1 row in set (34.02 sec)
Does anyone have a newer version of MySQL that wants to try this out?
source to share
From depending on setting the value of the indexed column above or below, it looks like the lock is actually being placed on the index entry. The database engine scans the index and stops at the first locked record, waiting for it to be released.
When the first transaction is committed, the index is unlocked and the wait transaction continues to scan the index. Since the value has been reduced, it is now in the index. Thus, the resumed scan does not see this because it has already passed that point.
To confirm this, try the following testing:
- Create two lines with values 2 and 3.
- In both transactions, do
SELECT ... FOR UPDATE
- In transaction 1, change 2 to 1, 3 to 4.
- Commit transaction 1.
If my guess is correct, transaction 2 should only return row with 4.
This seems like a bug to me as I don't think you should ever get partial results like this. Unfortunately, this is difficult to find on bugs.mysql.com because the word "for" is ignored in searches because it is too short or too common. Even quoting "to update" doesn't seem to find errors containing just that phrase.
source to share