From: Peter Brawley
Date: September 2 2011 4:14pm
Subject: Re: locked non-existent row
List-Archive: http://lists.mysql.com/mysql/225638
Message-Id: <4E6100ED.2000906@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 9/1/2011 9:06 AM, Jerry Schwartz 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.
>
> Try it, you'll see.
I agree entirely. I didn't question the practice. My question concerns 
documentation.

PB

-----

>
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@stripped
> Web site: www.giiresearch.com
>
>
>
>
>
>
>