On 9/1/2011 9:06 AM, Jerry Schwartz wrote:
-----Original Message-----
From: Peter Brawley [mailto:peter.braw...@earthlink.net]
Sent: Wednesday, August 31, 2011 10:40 AM
To: r...@grib.nl; mysql@lists.mysql.com
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: je...@gii.co.jp
Web site: www.giiresearch.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
