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