On Friday 13 May 2005 11:34, Ramesh G typed:
> I am using InnoDB only.
> But, it's not skipping locked rows.
Ditto that here.
CREATE TABLE `a` (
`b` int(11) NOT NULL auto_increment,
PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from a;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
1-mysql> begin;
Query OK, 0 rows affected (0.00 sec)
1-mysql> select * from a where b < 4 for update;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)
2-mysql> select * from a;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.01 sec)
2-mysql> select * from a for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Even though session 1 has indicated it wants update rights on everything less
than 4, session 2 is still able to see those records. If session 1 updates a
row, session 2 doesn't see the update yet, which is correct, but not the
behaviour I need (and I surmise not the behaviour the OP needs).
In the end, I had to add a flag to my rows that I toggle when right after
select for update in the transaction. This means that other sessions won't
see the rows because they check for the toggle being set.
