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-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.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=arch...@jab.org
Re: locked non-existent row
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
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-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.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=arch...@jab.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=arch...@jab.org
RE: locked non-existent row
-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. 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
Re: locked non-existent row
-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. 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=arch...@jab.org
Re: locked non-existent row
On 9/1/2011 9:46 AM, Rik Wasmus 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. 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=arch...@jab.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: 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
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=arch...@jab.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=arch...@jab.org
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=arch...@jab.org
