List:General Discussion« Previous MessageNext Message »
From:Duncan Hill Date:May 13 2005 10:39am
Subject:Re: Read past Equivalent in MySQL
View as plain text  
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.
Thread
Read past Equivalent in MySQLRamesh G13 May
  • Re: Read past Equivalent in MySQLmfatene13 May
    • Re: Read past Equivalent in MySQLmfatene13 May
    • Re: Read past Equivalent in MySQLRamesh G13 May
  • Re: Read past Equivalent in MySQLMartijn Tonies13 May
  • Re: Read past Equivalent in MySQLMartijn Tonies13 May
    • Re: Read past Equivalent in MySQLmfatene13 May
  • Re: Read past Equivalent in MySQLMartijn Tonies13 May
    • Re: Read past Equivalent in MySQLRamesh G13 May
  • Re: Read past Equivalent in MySQLMartijn Tonies13 May
    • Re: Read past Equivalent in MySQLRamesh G13 May
      • Re: Read past Equivalent in MySQLDuncan Hill13 May
  • Re: Read past Equivalent in MySQLMartijn Tonies13 May
    • Re: Read past Equivalent in MySQLEric Bergen13 May
      • Re: Read past Equivalent in MySQLDuncan Hill13 May
        • RE: Read past Equivalent in MySQLGordon13 May
          • Re: Read past Equivalent in MySQLDuncan Hill14 May
            • Re: Read past Equivalent in MySQLmfatene14 May