List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:September 1 2011 2:46pm
Subject:Re: locked non-existent row
View as plain text  
> >-----Original Message-----
> >From: Peter Brawley [mailto:peter.brawley@stripped]
> >Sent: Wednesday, August 31, 2011 10:40 AM
> >To: rik@stripped; mysql@stripped
> >Subject: Re: locked non-existent row
> >
> >On 8/31/2011 4:50 AM, Rik Wasmus wrote:
> >>> While a transaction in one thread tries to update a non-existent InnoDB
> >>> row with a given key value, an attempt to insert that value in another
> >>> thread is locked out. Does anyone know where this behaviour is
> >>> documented?
> 
> [JS] Forgive my ignorance, but I thought that was standard behavior for a
> row- or row-range lock (not just MySQL) in any DBMS that supported row
> locking. (Back when these things were first being invented, one term was
> "predicate locking.") The general idea was that you are locking rows that
> meet certain criteria, whether any or all of them exist or not. You're
> locking not only the existence, but the potential existence, of those
> rows.
> 
> I would expect it to apply not only to keys, but to any set. For example,
> 
> SELECT * FROM `t` WHERE `t`.`x` < 3 FOR UPDATE;
> 
> should lock all rows where `t`.`x` < 3 for update, insertion, or deletion
> -- regardless of whether or not `x` is a key. Otherwise you have no way of
> knowing who wins.
> 
> The ability to lock non-existent records is critical.

I concur, although this is just a transaction consisting of 1 statement :).
-- 
Rik Wasmus
Thread
locked non-existent rowPeter Brawley31 Aug
  • Re: locked non-existent rowRik Wasmus31 Aug
    • Re: locked non-existent rowPeter Brawley31 Aug
      • Re: locked non-existent rowRik Wasmus | GRIB1 Sep
      • RE: locked non-existent rowJerry Schwartz1 Sep
        • Re: locked non-existent rowRik Wasmus1 Sep
          • Re: locked non-existent rowPeter Brawley1 Sep
          • RE: locked non-existent rowJerry Schwartz1 Sep
        • Re: locked non-existent rowPeter Brawley2 Sep
  • Re: locked non-existent rowJochem van Dieten2 Sep
    • Re: locked non-existent rowPeter Brawley2 Sep