locked non-existent row

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?

-- connection 1
drop table if exists t;
create table t( lockid char(3), lockinfo char(8), primary
key(lockid,lockinfo) );
insert into t values('abc','def');
begin work;
update t set lockinfo='bar' where lockid='foo';

-- connection 2:
insert into t values('aaa','bbb');
Query OK, 1 row affected (0.00 sec)
insert into t values('foo','bar'); -- waits for connection 1 transaction

PB



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

> 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?
>
> -- connection 1
> drop table if exists t;
> create table t( lockid char(3), lockinfo char(8), primary
> key(lockid,lockinfo) );
> insert into t values('abc','def');
> begin work;
> update t set lockinfo='bar' where lockid='foo';
>
> -- connection 2:
> insert into t values('aaa','bbb');
> Query OK, 1 row affected (0.00 sec)
> insert into t values('foo','bar'); -- waits for connection 1 transaction


It has to do with transaction isolation levels. I assume REPEATABLE READ by
default for InnoDB:

http://dev.mysql.com/doc/refman/5.0/en/set-
transaction.html#isolevel_repeatable-read

" For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE,
and DELETE statements, locking depends on whether the statement uses a unique
index with a unique search condition, or a range-type search condition. For a
unique index with a unique search condition, InnoDB locks only the index
record found, not the gap before it. For other search conditions, InnoDB locks
the index range scanned, using gap locks or next-key (gap plus index-record)
locks to block insertions by other sessions into the gaps covered by the
range."
--
Rik Wasmus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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?
>>
>> -- connection 1
>> drop table if exists t;
>> create table t( lockid char(3), lockinfo char(8), primary
>> key(lockid,lockinfo) );
>> insert into t values('abc','def');
>> begin work;
>> update t set lockinfo='bar' where lockid='foo';
>>
>> -- connection 2:
>> insert into t values('aaa','bbb');
>> Query OK, 1 row affected (0.00 sec)
>> insert into t values('foo','bar'); -- waits for connection 1 transaction
>
> It has to do with transaction isolation levels. I assume REPEATABLE READ by
> default for InnoDB:
>
> http://dev.mysql.com/doc/refman/5.0/en/set-
> transaction.html#isolevel_repeatable-read
>
> " For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE,
> and DELETE statements, locking depends on whether the statement uses a unique
> index with a unique search condition, or a range-type search condition. For a
> unique index with a unique search condition, InnoDB locks only the index
> record found, not the gap before it. For other search conditions, InnoDB locks
> the index range scanned, using gap locks or next-key (gap plus index-record)
> locks to block insertions by other sessions into the gaps covered by the
> range."
Yes, that's what the question is about, it says for a unique key value,
as in this case, it "locks only the index record found". There is no
index record. InnoDb behaves, though, as if there is one. Where is the
documentation for that?

PB

------


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

On Wednesday 31 August 2011 16:39:52 Peter Brawley 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?
> >>
> >> -- connection 1
> >> drop table if exists t;
> >> create table t( lockid char(3), lockinfo char(8), primary
> >> key(lockid,lockinfo) );
> >> insert into t values('abc','def');
> >> begin work;
> >> update t set lockinfo='bar' where lockid='foo';
> >>
> >> -- connection 2:
> >> insert into t values('aaa','bbb');
> >> Query OK, 1 row affected (0.00 sec)
> >> insert into t values('foo','bar'); -- waits for connection 1 transaction
> >
> > It has to do with transaction isolation levels. I assume REPEATABLE READ
> > by default for InnoDB:
> >
> > http://dev.mysql.com/doc/refman/5.0/en/set-
> > transaction.html#isolevel_repeatable-read
> >
> > " For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
> > UPDATE, and DELETE statements, locking depends on whether the statement
> > uses a unique index with a unique search condition, or a range-type
> > search condition. For a unique index with a unique search condition,
> > InnoDB locks only the index record found, not the gap before it. For
> > other search conditions, InnoDB locks the index range scanned, using gap
> > locks or next-key (gap plus index-record) locks to block insertions by
> > other sessions into the gaps covered by the range."
>
> Yes, that's what the question is about, it says for a unique key value,
> as in this case, it "locks only the index record found". There is no
> index record. InnoDb behaves, though, as if there is one. Where is the
> documentation for that?

Is says it employs different strategies, I see it as:

(unique index with a unique search condition => InnoDB locks only the index
record found)
OR
(other search conditions => InnoDB locks the index range scanned, using gap
locks or next-key )

And 'lockinfo' is most certainly in your index, a primary key is a special
kind of UNIQUE, but still unique. The INSERT cannot complete as the
(lockid,lockinfo) location (foo,bar) is locked, until commit or rollback. See
the index as a seperate storage from your normale table-rows which your INSERT
needs to put something in to complete the insert. The UPDATE has locked that
location, so the INSERT waits for that. That's the 'index record' they're
talking about I gather.

Also: please respond to the list only, I have enough mail as it is, responding
to the list keeps things nicely organized :)
--
Rik

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: locked non-existent row

>-----Original Message-----
>From: Peter Brawley [mailto:peter.brawley@earthlink.net]
>Sent: Wednesday, August 31, 2011 10:40 AM
>To: rik@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.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@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=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

> >-----Original Message-----
> >From: Peter Brawley [mailto:peter.brawley@earthlink.net]
> >Sent: Wednesday, August 31, 2011 10:40 AM
> >To: rik@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.

I concur, although this is just a transaction consisting of 1 statement :).
--
Rik Wasmus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

On 9/1/2011 9:46 AM, Rik Wasmus wrote:
>>> -----Original Message-----
>>> From: Peter Brawley [mailto:peter.brawley@earthlink.net]
>>> Sent: Wednesday, August 31, 2011 10:40 AM
>>> To: rik@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.
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 :).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: locked non-existent row

>> The ability to lock non-existent records is critical.
>
>I concur, although this is just a transaction consisting of 1 statement :).
>--
[JS] As the Three Musketeers used to say, "One for all and all for one!"

Peter, I couldn't even //understand// that paragraph about key gaps. The
document writers might have assumed that this aspect of locking was universal
and well known, and didn't think it needed explanation.

As for MS SQL Server, I would be dumbfounded if it worked differently; but
I've been dumbfounded before by software design.

My standard answer for "Why does it work that way?" is "I am not a mental
health professional, nor do I play one on TV."

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@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=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley 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?

In the manual it is called "gap locking":
http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-l ocks.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locki ng.html

Jochem

--
Jochem van Dieten
http://jochem.vandieten.net/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

On 9/1/2011 9:06 AM, Jerry Schwartz wrote:
>> -----Original Message-----
>> From: Peter Brawley [mailto:peter.brawley@earthlink.net]
>> Sent: Wednesday, August 31, 2011 10:40 AM
>> To: rik@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: jerry@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=gcdmg-mysql-2@m.gmane.org

Re: locked non-existent row

On 9/2/2011 6:15 AM, Jochem van Dieten wrote:
> On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley 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?
> In the manual it is called "gap locking":
> http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-l ocks.html
> http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locki ng.html
That makes sense. A reference in
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html# isolevel_repeatable-read
to those bits would be helpful to users.

PB

>
> Jochem
>
> --
> Jochem van Dieten
> http://jochem.vandieten.net/
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org