List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 1 2011 3:25pm
Subject:Re: locked non-existent row
View as plain text  
On 9/1/2011 9:46 AM, Rik Wasmus wrote:
>>> -----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.
Yes, though I'm told that SQL Server, for example, does not do this 
(I've not confirmed that myself). The question here is whether the cited 
docs para adequately describes what InnoDB is doing, ie treating a 
single non-existent key value as a range.

PB

-----

> I concur, although this is just a transaction consisting of 1 statement :).
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