Row level locking with InnoDB
Hi, I have a quick question, which I did not find answerd by the manual: Given this sample query: SELECT COUNT(ID) FROM mytable WHERE property=value FOR UPDATE Which rows in 'mytable' are locked after this query? Every row with property=value, because these rows were used to calculate COUNT(ID). No rows, because no row is actually returned / selected. Thanks and greetings, Kai Ruhnau -- This signature is left as an exercise for the reader. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row-level locking question...
Andre, - Original Message - From: "Andre Charbonneau" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, November 17, 2003 5:11 PM Subject: row-level locking question... > Hi, > > Let say that I have the following transaction: > > 1. Read value v1 from table t1. > 2. Do some computation using v1. > 3. Update value v2 from table t2. > > If in the above I don't want any other concurrent transaction to read v2 > until I'm done updating it, how should I put an exclusive lock on it? > > Using InnoDB, would the following be the way to do it (in transaction > mode, seriliazable isolation level)? > > SELECT v2 from t2 FOR UPDATE; // (Do this to prevent others from reading v2) > > SELECT v1 from t1; > > (do the computation) > > UPDATE t2 set v2=; > > COMMIT; > > > In the above statements, I first read the value v2 to put an exclusive > lock on that row. But I don't really need the value of v2, I just need > to lock it down. note that UPDATE t2 set v2=; automatically sets an x-lock on the row to update. If the above is the whole story about your application logic, you really do not need to do SELECT v2 from t2 FOR UPDATE; first. But, to get serializable execution, you NEED to do a locking read SELECT v1 from t1 LOCK IN SHARE MODE; to freeze t1 so that v1 cannot change meanwhile! --- To sum up, the following program does serializable execution: BEGIN; SELECT v1 from t1 LOCK IN SHARE MODE; (do the computation of v2 based on v1) UPDATE t2 set v2=; COMMIT; > Is the above approach the way to go or is there a more > elegant/correct way of doing this? > > Thanks. > -- > Andre Charbonneau Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row-level locking question...
On Mon, Nov 17, 2003 at 10:10:52AM -0500, Andre Charbonneau wrote: > Hi, > > Let say that I have the following transaction: > > 1. Read value v1 from table t1. > 2. Do some computation using v1. > 3. Update value v2 from table t2. > > If in the above I don't want any other concurrent transaction to read v2 > until I'm done updating it, how should I put an exclusive lock on it? > > Using InnoDB, would the following be the way to do it (in transaction > mode, seriliazable isolation level)? Have you tried it yourself? That would probably answer your question faster. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 64 days, processed 2,503,781,143 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
row-level locking question...
Hi, Let say that I have the following transaction: 1. Read value v1 from table t1. 2. Do some computation using v1. 3. Update value v2 from table t2. If in the above I don't want any other concurrent transaction to read v2 until I'm done updating it, how should I put an exclusive lock on it? Using InnoDB, would the following be the way to do it (in transaction mode, seriliazable isolation level)? SELECT v2 from t2 FOR UPDATE; // (Do this to prevent others from reading v2) SELECT v1 from t1; (do the computation) UPDATE t2 set v2=; COMMIT; In the above statements, I first read the value v2 to put an exclusive lock on that row. But I don't really need the value of v2, I just need to lock it down. Is the above approach the way to go or is there a more elegant/correct way of doing this? Thanks. -- Andre Charbonneau -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about row level locking with InnoDB tables
Steve, - Original Message - From: ""Steve McWilliams"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, August 27, 2003 12:07 AM Subject: Question about row level locking with InnoDB tables > Hello, > > I am relatively new to MySql (4.0.14) but I have read through the relevent > documentation and am still confused about how row level locking behaves > with InnoDB tables. > > I created a database with a single innodb table which has 2 columns, one > of which is indexed. The locking behavior I see when I test against this > database is that it uses row level locks if the "SELECT ... FOR UPDATE" > involves the indexed column, but uses table level locks if instead it > involves the non-indexed column. > > For example, if I have 2 mysql clients that perform the following operations: > > client1> set autocommit=0; > client1> begin; > client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE; > client2> set autocommit=0; > client2> begin: > client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE; > > The above query by client2 will block if the column in question is not > indexed, implying that client1 has somehow locked the entire table, even > though client1 and client2 are selecting different rows. a locking SELECT will set row locks (usually next-key locks) on every index record it looks at. This is necessary to prevent 'phantom rows' from appearing in your result set. If you do not have an index on my_column, MySQL needs to scan the whole table and thus it locks every row. http://www.innodb.com/ibman.html#Locks_set_by_statements http://www.innodb.com/ibman.html#Next_key_locking http://www.innodb.com/ibman.html#Cope_with_deadlocks > Am I misconfiguring something, or does InnoDB simply only support row > level locking when you are selecting indexed rows? > > Thanks in advance, > > Steve McWilliams > Software Engineer > Emprisa Networks > 703-691-0433x21 > [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about row level locking with InnoDB tables
Hello, I am relatively new to MySql (4.0.14) but I have read through the relevent documentation and am still confused about how row level locking behaves with InnoDB tables. I created a database with a single innodb table which has 2 columns, one of which is indexed. The locking behavior I see when I test against this database is that it uses row level locks if the "SELECT ... FOR UPDATE" involves the indexed column, but uses table level locks if instead it involves the non-indexed column. For example, if I have 2 mysql clients that perform the following operations: client1> set autocommit=0; client1> begin; client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE; client2> set autocommit=0; client2> begin: client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE; The above query by client2 will block if the column in question is not indexed, implying that client1 has somehow locked the entire table, even though client1 and client2 are selecting different rows. Am I misconfiguring something, or does InnoDB simply only support row level locking when you are selecting indexed rows? Thanks in advance, Steve McWilliams Software Engineer Emprisa Networks 703-691-0433x21 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB row level locking?
>-Original Message- >From: Fernando Grijalba [mailto:fernando@;ggtours.on.ca] >Sent: Wednesday, November 06, 2002 11:41 AM >To: MySQL Help >Subject: InnoDB row level locking? > > >In the documentation it says that InnoDB supports row level >locking. How >can I ensure that rows are locked using ADO in a VB Applications >communicating with MySQL through MyODBC? > >I have tried setting my Recordsets to adLockPessimistic, but >that does not >seam to work. Make sure you are working inside transactions. then add this text to the end of your select statments "FOR UPDATE" This will ensure that no one else updates after you have selected the record. But it will still let others read. Commiting your transaction will release the lock. sean. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB row level locking?
In the documentation it says that InnoDB supports row level locking. How can I ensure that rows are locked using ADO in a VB Applications communicating with MySQL through MyODBC? I have tried setting my Recordsets to adLockPessimistic, but that does not seam to work. Any help will be really appreciated. Thank you, JFernando ** sql ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ROW LEVEL Locking not affected
Shilline, - Original Message - From: "Shilline Lee" <[EMAIL PROTECTED]> To: ""Heikki Tuuri"" <[EMAIL PROTECTED]> Sent: Monday, October 14, 2002 4:08 AM Subject: Re: ROW LEVEL Locking not affected > > >And then ? please tell me the detail :) > if I "SET AUTOCOMMIT = 0" in the session of user A > then when should I "COMMIT" in the session of user A > >coz I put the shared data in the table "test" and let many programs deal with these data, >so I just want to lock some rows selected by user A till I have them deleted or updated. > user B should not see these rows after the exclusive select of user A. how can I do ? > please address these general questions to the General MySQL Mailing List. You should commit A when his atomic work is ready. If B should not see the rows locked by A, then also B should use a locking read. A plain consistent reads an old snapshot of rows and is not blocked by any locks. Regards, Heikki > - Original Message - > From: ""Heikki Tuuri"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.mysql > Sent: Monday, October 14, 2002 2:23 AM > Subject: Re: ROW LEVEL Locking not affected > > > > Shilline, > > > > ----- Original Message - > > From: "Shilline Lee" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.mysql > > Sent: Sunday, October 13, 2002 7:16 AM > > Subject: ROW LEVEL Locking not affected > > > > > > > > > > Hello all: > > > > > > I did a row level lock with my database ( MySQL-4.0.3-beta with > > InnoDB ) > > > but it seems that it's not affected. > > > I have tried "FOR UPDATE" and "LOCK IN SHARE MODE", the lock seems not > > > affected, > > > why ? thanks in advanced ! > > > > did you remember to > > > > SET AUTOCOMMIT = 0 > > > > in the session of user A? > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > --- > > InnoDB - transactions, hot backup, and foreign key support for MySQL > > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > > > > > sql query > > > > > User A: > > > > > > SELECT * FROM test FOR UPDATE; > > > +---+---+ > > > | 1 | 2 | > > > +---+---+ > > > | 1 | 2 | > > > +---+---+ > > > 1 row in set (0.00 sec) > > > > > > User B: > > > mysql> SELECT * FROM test; > > > > > > +---+---+ > > > | 1 | 2 | > > > +---+---+ > > > | 1 | 2 | > > > +---+---+ > > > 1 row in set (0.00 sec) > > > > > > mysql> delete from submit; > > > Query OK, 1 row affected (0.01 sec) > > > > > > User A: > > > > > > mysql> SELECT * FROM test; > > > Empty set (0.00 sec) > > > > > > > > > > > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ROW LEVEL Locking not affected
Shilline, - Original Message - From: "Shilline Lee" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, October 13, 2002 7:16 AM Subject: ROW LEVEL Locking not affected > > Hello all: > > I did a row level lock with my database ( MySQL-4.0.3-beta with InnoDB ) > but it seems that it's not affected. > I have tried "FOR UPDATE" and "LOCK IN SHARE MODE", the lock seems not > affected, > why ? thanks in advanced ! did you remember to SET AUTOCOMMIT = 0 in the session of user A? Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query > User A: > > SELECT * FROM test FOR UPDATE; > +---+---+ > | 1 | 2 | > +---+---+ > | 1 | 2 | > +---+---+ > 1 row in set (0.00 sec) > > User B: > mysql> SELECT * FROM test; > > +---+---+ > | 1 | 2 | > +---+---+ > | 1 | 2 | > +---+---+ > 1 row in set (0.00 sec) > > mysql> delete from submit; > Query OK, 1 row affected (0.01 sec) > > User A: > > mysql> SELECT * FROM test; > Empty set (0.00 sec) > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Alex, - Original Message - From: "Varshavchick Alexander" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 06, 2002 11:49 AM Subject: Re: Performance Problems with InnoDB Row Level Locking... > Heikki, thank you for the answer. So on the systems other than Linux or > Solaris the best flush method should be fdatasync, is it correct? In this > case, if I don't specify innodb_flush_method option, fdatasync will not be > used - it'll be fsync be default instead? My system is FreeBSD, so which > value for innodb_flush_method can be optimal? yes, but it is mapped to fsync on all Unixes. You can edit the source code and test other options. Also O_SYNC and O_DSYNC. > Thanks > > > Alexander Varshavchick, Metrocom Joint Stock Company > Phone: (812)118-3322, 118-3115(fax) Regards, Heikki > On Fri, 6 Sep 2002, Heikki Tuuri wrote: > > > Date: Fri, 6 Sep 2002 10:27:03 +0300 > > From: Heikki Tuuri <[EMAIL PROTECTED]> > > To: Varshavchick Alexander <[EMAIL PROTECTED]> > > Cc: [EMAIL PROTECTED] > > Subject: Re: Performance Problems with InnoDB Row Level Locking... > > > > Alexander, > > > > - Original Message - > > From: "Varshavchick Alexander" <[EMAIL PROTECTED]> > > To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Friday, September 06, 2002 10:08 AM > > Subject: RE: Performance Problems with InnoDB Row Level Locking... > > > > > > > Hi Heikki, > > > > > > one more question please about innodb_flush_log_at_trx_commit: if there > > > was some way of increasing the delay between log flushes more than 1 sec, > > > can you estimate will it bring any real effect in performance? I know > > > it'll raise the risk of losing some last transactions if something > > > crashes, but we can go for it gaining the speed. How can it be done if > > > it's worth doing? > > > > it should not be worth doing. > > > > A disk can do some 70 random writes per second, and the log flush (calling > > fsync on the log file) typically uses 2 disk writes: > > > > (1) writing the end of the log to the log file on disk, and > > (2) updating the file access timestamps in the 'inode' of the file, if we > > are using a Unix file system. > > > > Thus the performance benefit of less than 1 log flush per second is small. > > On the other hand, we might add an option which allows flushing the log 1 - > > 50 times per second. > > > > Note that the file flush method fdatasync is supposed to eliminate the write > > (2) above. Unfortunately there was evidence fadatasync caused file > > corruption in Linux and Solaris, and it is currently mapped to the ordinary > > fsync. > > > > > Thanks > > > > > > sql, query > > > > > > Alexander Varshavchick, Metrocom Joint Stock Company > > > Phone: (812)118-3322, 118-3115(fax) > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > --- > > InnoDB - transactions, hot backup, and foreign key support for MySQL > > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Heikki, thank you for the answer. So on the systems other than Linux or Solaris the best flush method should be fdatasync, is it correct? In this case, if I don't specify innodb_flush_method option, fdatasync will not be used - it'll be fsync be default instead? My system is FreeBSD, so which value for innodb_flush_method can be optimal? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 6 Sep 2002, Heikki Tuuri wrote: > Date: Fri, 6 Sep 2002 10:27:03 +0300 > From: Heikki Tuuri <[EMAIL PROTECTED]> > To: Varshavchick Alexander <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: Performance Problems with InnoDB Row Level Locking... > > Alexander, > > - Original Message - > From: "Varshavchick Alexander" <[EMAIL PROTECTED]> > To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, September 06, 2002 10:08 AM > Subject: RE: Performance Problems with InnoDB Row Level Locking... > > > > Hi Heikki, > > > > one more question please about innodb_flush_log_at_trx_commit: if there > > was some way of increasing the delay between log flushes more than 1 sec, > > can you estimate will it bring any real effect in performance? I know > > it'll raise the risk of losing some last transactions if something > > crashes, but we can go for it gaining the speed. How can it be done if > > it's worth doing? > > it should not be worth doing. > > A disk can do some 70 random writes per second, and the log flush (calling > fsync on the log file) typically uses 2 disk writes: > > (1) writing the end of the log to the log file on disk, and > (2) updating the file access timestamps in the 'inode' of the file, if we > are using a Unix file system. > > Thus the performance benefit of less than 1 log flush per second is small. > On the other hand, we might add an option which allows flushing the log 1 - > 50 times per second. > > Note that the file flush method fdatasync is supposed to eliminate the write > (2) above. Unfortunately there was evidence fadatasync caused file > corruption in Linux and Solaris, and it is currently mapped to the ordinary > fsync. > > > Thanks > > > > sql, query > > > > Alexander Varshavchick, Metrocom Joint Stock Company > > Phone: (812)118-3322, 118-3115(fax) > > Best regards, > > Heikki Tuuri > Innobase Oy > --- > InnoDB - transactions, hot backup, and foreign key support for MySQL > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Joe, - Original Message - From: "Joe Shear" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Sent: Friday, September 06, 2002 2:13 AM Subject: Re: Performance Problems with InnoDB Row Level Locking... > Hi, > On a side note, are there any plans to backport the spurious insert > deadlock fix to the 3.23 series? sorry, but 3.23 is now frozen from new 'features'. People want it to stay as it is. We are also encouraging people to test the 4.0 series. > thanks > joe Regards, Heikki > On Thu, 2002-09-05 at 16:02, Heikki Tuuri wrote: > > Steve, > > > > - Original Message - > > From: "Orr, Steve" <[EMAIL PROTECTED]> > > To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Friday, September 06, 2002 1:23 AM > > Subject: RE: Performance Problems with InnoDB Row Level Locking... > > > > > > > Heikki, > > > > > > > Next-key locking in InnoDB allows you to lock the non-existence of rows > > > > and thus prevents phantom rows from appearing. > > > OK, now I understand what you're getting at with phantom rows. > > > > > > But given the tradeoff between the inadvertant next-key deadlocking > > > challenge and a the phantom rows challenge, I think I'd rather have the > > > phantom rows challenge because: 1) it's not as common of a problem; and 2) > > > on the few occasions when I am confronted with it I can easily prevent it > > > with a table lock. The need to work around next-key deadlocking issues is > > > constant (EVERY database insert, update or delete) and it seems the > > solution > > > > normally you have to be prepared to reissue your transactions. Deadlocks > > generated by UNIQUE KEY checks and FOREIGN KEY checks are not the result of > > next-key locking, but a general issue in row level locking. These checks > > involve implicit reading of tables in connection with insert/delete/update. > > Thus you can get surprising deadlocks. Proving some system deadlock-free is > > difficult. > > > > SHOW INNODB STATUS is the tool to analyze deadlock problems in 3.23.52 or > > 4.0.3. With it we found the spurious insert deadlocks which were removed in > > 4.0.3. There may still be spurious deadlocks which appear in real-world > > applications and which are easy to remove by fine-tuning the next-key > > locking algorithm. > > > > > is more difficult to achieve and may eventually come back to serialization > > > or table level locks which is what I'm trying to avoid to begin with. I've > > > already addressed some deadlock issues with frequent commits, smaller > > > transactions, and better indexes and I only want to lock tables when I > > > absolutely have to. > > > > > > I may be wrong but it just seems to me that the next-key locking approach > > > merely creates another concurrency issue and the subsequent next-key > > > deadlock problem is just too significant to ignore. > > > > We need serializability to make MySQL replication and recovery from the > > binlog to work. That is why InnoDB cannot allow phantom rows. It would be a > > relatively easy change in InnoDB itself to lower the isolation level so that > > phantom rows would be allowed. > > > > > Humbly, > > > Steve > > > > Regards, > > > > Heikki > > > > > -Original Message- > > > From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, September 05, 2002 2:54 PM > > > To: Orr, Steve > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: Performance Problems with InnoDB Row Level Locking... > > > > > > > > > Steve, > > > > > > - Original Message - > > > From: "Orr, Steve" <[EMAIL PROTECTED]> > > > To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> > > > Sent: Thursday, September 05, 2002 11:04 PM > > > Subject: RE: Performance Problems with InnoDB Row Level Locking... > > > > > > > > > > Heikki, > > > > > > > > You wrote... > > > > > You are getting so many deadlocks that some transactions do not pass > > at > > > > > all? > > > > No, the transactions eventually succeed but performance suffers. Like I > > > > said, this is a stress test to identify bottlenecks in database > > > performance. > > > > > > > > > &
Re: Performance Problems with InnoDB Row Level Locking...
Alexander, - Original Message - From: "Varshavchick Alexander" <[EMAIL PROTECTED]> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, September 06, 2002 10:08 AM Subject: RE: Performance Problems with InnoDB Row Level Locking... > Hi Heikki, > > one more question please about innodb_flush_log_at_trx_commit: if there > was some way of increasing the delay between log flushes more than 1 sec, > can you estimate will it bring any real effect in performance? I know > it'll raise the risk of losing some last transactions if something > crashes, but we can go for it gaining the speed. How can it be done if > it's worth doing? it should not be worth doing. A disk can do some 70 random writes per second, and the log flush (calling fsync on the log file) typically uses 2 disk writes: (1) writing the end of the log to the log file on disk, and (2) updating the file access timestamps in the 'inode' of the file, if we are using a Unix file system. Thus the performance benefit of less than 1 log flush per second is small. On the other hand, we might add an option which allows flushing the log 1 - 50 times per second. Note that the file flush method fdatasync is supposed to eliminate the write (2) above. Unfortunately there was evidence fadatasync caused file corruption in Linux and Solaris, and it is currently mapped to the ordinary fsync. > Thanks > > sql, query > > Alexander Varshavchick, Metrocom Joint Stock Company > Phone: (812)118-3322, 118-3115(fax) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Performance Problems with InnoDB Row Level Locking...
Hi Heikki, one more question please about innodb_flush_log_at_trx_commit: if there was some way of increasing the delay between log flushes more than 1 sec, can you estimate will it bring any real effect in performance? I know it'll raise the risk of losing some last transactions if something crashes, but we can go for it gaining the speed. How can it be done if it's worth doing? Thanks sql, query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Steve,
- Original Message -
From: "Orr, Steve" <[EMAIL PROTECTED]>
To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 06, 2002 1:23 AM
Subject: RE: Performance Problems with InnoDB Row Level Locking...
> Heikki,
>
> > Next-key locking in InnoDB allows you to lock the non-existence of rows
> > and thus prevents phantom rows from appearing.
> OK, now I understand what you're getting at with phantom rows.
>
> But given the tradeoff between the inadvertant next-key deadlocking
> challenge and a the phantom rows challenge, I think I'd rather have the
> phantom rows challenge because: 1) it's not as common of a problem; and 2)
> on the few occasions when I am confronted with it I can easily prevent it
> with a table lock. The need to work around next-key deadlocking issues is
> constant (EVERY database insert, update or delete) and it seems the
solution
normally you have to be prepared to reissue your transactions. Deadlocks
generated by UNIQUE KEY checks and FOREIGN KEY checks are not the result of
next-key locking, but a general issue in row level locking. These checks
involve implicit reading of tables in connection with insert/delete/update.
Thus you can get surprising deadlocks. Proving some system deadlock-free is
difficult.
SHOW INNODB STATUS is the tool to analyze deadlock problems in 3.23.52 or
4.0.3. With it we found the spurious insert deadlocks which were removed in
4.0.3. There may still be spurious deadlocks which appear in real-world
applications and which are easy to remove by fine-tuning the next-key
locking algorithm.
> is more difficult to achieve and may eventually come back to serialization
> or table level locks which is what I'm trying to avoid to begin with. I've
> already addressed some deadlock issues with frequent commits, smaller
> transactions, and better indexes and I only want to lock tables when I
> absolutely have to.
>
> I may be wrong but it just seems to me that the next-key locking approach
> merely creates another concurrency issue and the subsequent next-key
> deadlock problem is just too significant to ignore.
We need serializability to make MySQL replication and recovery from the
binlog to work. That is why InnoDB cannot allow phantom rows. It would be a
relatively easy change in InnoDB itself to lower the isolation level so that
phantom rows would be allowed.
> Humbly,
> Steve
Regards,
Heikki
> -Original Message-
> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 05, 2002 2:54 PM
> To: Orr, Steve
> Cc: [EMAIL PROTECTED]
> Subject: Re: Performance Problems with InnoDB Row Level Locking...
>
>
> Steve,
>
> - Original Message -----
> From: "Orr, Steve" <[EMAIL PROTECTED]>
> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
> Sent: Thursday, September 05, 2002 11:04 PM
> Subject: RE: Performance Problems with InnoDB Row Level Locking...
>
>
> > Heikki,
> >
> > You wrote...
> > > You are getting so many deadlocks that some transactions do not pass
at
> > > all?
> > No, the transactions eventually succeed but performance suffers. Like I
> > said, this is a stress test to identify bottlenecks in database
> performance.
>
>
>
> some optimization of next-key locking is possible. I recently removed some
> spurious deadlocks in 4.0 because a big customer complained of the
problem.
>
> >
> > > Consider a consistency rule: "sum of all balances in table ACCOUNT
must
> > > be 1,000,000". How do you keep that true if your database cannot block
> > > phantom rows?
> > Can't that be accomplished by the "SELECT ... FOR UPDATE" syntax?
>
> That does not block new inserts to the table in Oracle. If you do:
>
> INSERT INTO account2 SELECT * FROM account;
>
> and someone else at the same time inserts within a single transaction 2
rows
> ('Jones', 1000), ('Smith', -1000) to table account, you may end up with
> table account2 where the sum of balances is not 1,000,000, though table
> account always had 1,000,000 as the sum.
>
> The way to work around these serializability issues in Oracle is to use
> table level locks. In the 1980's, when Oracle did not yet have foreign
keys
> constraints, people used table level locks to implement referential
> integrity. You have to lock the NON-existence of child rows when you
delete
> a parent row.
>
> Next-key locking in InnoDB allows you to lock the non-existence of rows
and
> thus prevents phantom rows from appearing.
>
> > Thanks again,
> > Ste
RE: Performance Problems with InnoDB Row Level Locking...
Heikki,
> Next-key locking in InnoDB allows you to lock the non-existence of rows
> and thus prevents phantom rows from appearing.
OK, now I understand what you're getting at with phantom rows.
But given the tradeoff between the inadvertant next-key deadlocking
challenge and a the phantom rows challenge, I think I'd rather have the
phantom rows challenge because: 1) it's not as common of a problem; and 2)
on the few occasions when I am confronted with it I can easily prevent it
with a table lock. The need to work around next-key deadlocking issues is
constant (EVERY database insert, update or delete) and it seems the solution
is more difficult to achieve and may eventually come back to serialization
or table level locks which is what I'm trying to avoid to begin with. I've
already addressed some deadlock issues with frequent commits, smaller
transactions, and better indexes and I only want to lock tables when I
absolutely have to.
I may be wrong but it just seems to me that the next-key locking approach
merely creates another concurrency issue and the subsequent next-key
deadlock problem is just too significant to ignore.
Humbly,
Steve
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 05, 2002 2:54 PM
To: Orr, Steve
Cc: [EMAIL PROTECTED]
Subject: Re: Performance Problems with InnoDB Row Level Locking...
Steve,
- Original Message -
From: "Orr, Steve" <[EMAIL PROTECTED]>
To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
Sent: Thursday, September 05, 2002 11:04 PM
Subject: RE: Performance Problems with InnoDB Row Level Locking...
> Heikki,
>
> You wrote...
> > You are getting so many deadlocks that some transactions do not pass at
> > all?
> No, the transactions eventually succeed but performance suffers. Like I
> said, this is a stress test to identify bottlenecks in database
performance.
some optimization of next-key locking is possible. I recently removed some
spurious deadlocks in 4.0 because a big customer complained of the problem.
>
> > Consider a consistency rule: "sum of all balances in table ACCOUNT must
> > be 1,000,000". How do you keep that true if your database cannot block
> > phantom rows?
> Can't that be accomplished by the "SELECT ... FOR UPDATE" syntax?
That does not block new inserts to the table in Oracle. If you do:
INSERT INTO account2 SELECT * FROM account;
and someone else at the same time inserts within a single transaction 2 rows
('Jones', 1000), ('Smith', -1000) to table account, you may end up with
table account2 where the sum of balances is not 1,000,000, though table
account always had 1,000,000 as the sum.
The way to work around these serializability issues in Oracle is to use
table level locks. In the 1980's, when Oracle did not yet have foreign keys
constraints, people used table level locks to implement referential
integrity. You have to lock the NON-existence of child rows when you delete
a parent row.
Next-key locking in InnoDB allows you to lock the non-existence of rows and
thus prevents phantom rows from appearing.
> Thanks again,
> Steve
Best regards,
Heikki
> -Original Message-----
> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 05, 2002 1:30 PM
> To: Orr, Steve; [EMAIL PROTECTED]
> Subject: Re: Performance Problems with InnoDB Row Level Locking...
>
>
> Steve,
>
> - Original Message -----
> From: "Orr, Steve" <[EMAIL PROTECTED]>
> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Thursday, September 05, 2002 9:49 PM
> Subject: RE: Performance Problems with InnoDB Row Level Locking...
>
>
> > Hello again Heikki and thanks for your informative reply.
> >
> > Regarding...
> > > innodb_flush_log_at_trx_commit=2
> > This is not an option as we must guarantee no lost transactions. But I
> will
> > test it out of curiosity just to see what the performance difference is.
>
> if you want to get high performance for disk flushes, you should buy a
disk
> with a battery-backed cache, and check that the disk driver is aware of
it.
>
> Otherwise we are constrained by the disk rotation speed, some 150 rounds /
> second.
>
> > Regarding...
> > > InnoDB uses next-key locking to ensure serializability and that
'phantom
> > > rows' do not appear. You can get lock conflicts even if the queries
> > > seemingly would not overlap.
> > Does this mean that InnoDB is locking the next leaf in the B-Tree? That
>
> No, only the next index record in alphabetical order.
>
> > would explain the problem as
Re: Performance Problems with InnoDB Row Level Locking...
Steve,
- Original Message -
From: "Orr, Steve" <[EMAIL PROTECTED]>
To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
Sent: Thursday, September 05, 2002 11:04 PM
Subject: RE: Performance Problems with InnoDB Row Level Locking...
> Heikki,
>
> You wrote...
> > You are getting so many deadlocks that some transactions do not pass at
> > all?
> No, the transactions eventually succeed but performance suffers. Like I
> said, this is a stress test to identify bottlenecks in database
performance.
some optimization of next-key locking is possible. I recently removed some
spurious deadlocks in 4.0 because a big customer complained of the problem.
>
> > Consider a consistency rule: "sum of all balances in table ACCOUNT must
> > be 1,000,000". How do you keep that true if your database cannot block
> > phantom rows?
> Can't that be accomplished by the "SELECT ... FOR UPDATE" syntax?
That does not block new inserts to the table in Oracle. If you do:
INSERT INTO account2 SELECT * FROM account;
and someone else at the same time inserts within a single transaction 2 rows
('Jones', 1000), ('Smith', -1000) to table account, you may end up with
table account2 where the sum of balances is not 1,000,000, though table
account always had 1,000,000 as the sum.
The way to work around these serializability issues in Oracle is to use
table level locks. In the 1980's, when Oracle did not yet have foreign keys
constraints, people used table level locks to implement referential
integrity. You have to lock the NON-existence of child rows when you delete
a parent row.
Next-key locking in InnoDB allows you to lock the non-existence of rows and
thus prevents phantom rows from appearing.
> Thanks again,
> Steve
Best regards,
Heikki
> -Original Message-
> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 05, 2002 1:30 PM
> To: Orr, Steve; [EMAIL PROTECTED]
> Subject: Re: Performance Problems with InnoDB Row Level Locking...
>
>
> Steve,
>
> - Original Message -
> From: "Orr, Steve" <[EMAIL PROTECTED]>
> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Thursday, September 05, 2002 9:49 PM
> Subject: RE: Performance Problems with InnoDB Row Level Locking...
>
>
> > Hello again Heikki and thanks for your informative reply.
> >
> > Regarding...
> > > innodb_flush_log_at_trx_commit=2
> > This is not an option as we must guarantee no lost transactions. But I
> will
> > test it out of curiosity just to see what the performance difference is.
>
> if you want to get high performance for disk flushes, you should buy a
disk
> with a battery-backed cache, and check that the disk driver is aware of
it.
>
> Otherwise we are constrained by the disk rotation speed, some 150 rounds /
> second.
>
> > Regarding...
> > > InnoDB uses next-key locking to ensure serializability and that
'phantom
> > > rows' do not appear. You can get lock conflicts even if the queries
> > > seemingly would not overlap.
> > Does this mean that InnoDB is locking the next leaf in the B-Tree? That
>
> No, only the next index record in alphabetical order.
>
> > would explain the problem as those rows could be updated by other
> sessions.
> > If that's the case then I think the next-key locking architecture is the
> > problem because it introduces "artificial" deadlocks on heavily used
> tables
> > and indexes that would otherwise not occur.
>
> Yes, that is true.
>
> > (See http://portal.acm.org/citation.cfm?id=335461&dl=ACM&coll=portal#)
> >
> > Using the same code on the same machine I'm getting dramatically better
> > performance with PostgreSQL and Oracle both of which implement
> multi-version
> > concurrency control with an ANSI isolation level of "read committed." I
> > understand that this isolation level allows for unrepeatable reads but
> this
> > is easily overcome programatically (if needed). It seems like the
> repeatable
> > read isolation level isn't as practical and isn't really needed that
> often.
> > Based on the work arounds you listed in the coping with deadlocks link,
I
> > don't see any way around my performance problem. (I had already tried
> > transaction resubmission but it just perpetuates the problem.)
>
> You are getting so many deadlocks that some transactions do not pass at
all?
> Then best to serialize them with LOCK TABLES or the 'semaphore table'
method
> expla
Re: Performance Problems with InnoDB Row Level Locking...
Sorry, I forgot to add the reference to that 'ACID' characterization. It is http://openacs.org/philosophy/why-not-mysql.html :). " Why Not MySQL? by Ben Adida, part of the OpenACS Project. NOTE: This Document was written in May 2000. Thus, it is outdated and does not represent the latest data concerning MySQL. I will attempt to find time to rewrite this with more current information soon (August 10th, 2001) Consistency The database is transformed from one valid state to another valid state. A transaction is legal only if it obeys user-defined integrity constraints. Illegal transactions aren't allowed and, if an integrity constraint can't be satisfied the transaction is rolled back. For example, suppose that you define a rule that postings in a discussion forum table must be tied to a valid user ID. Then you hire Joe Novice to write some admin pages. Joe writes a delete-user page that doesn't bother to check whether or not the deletion will result in an orphaned discussion forum posting. Oracle will check, though, and abort any transaction that would result in you having a discussion forum posting by a deleted user. Isolation The results of a transaction are invisible to other transactions until the transaction is complete. For example, suppose you have a page to show new users and their photographs. This page is coded in reliance on the publisher's directive that there will be a mugshot for every user and will present a broken image if there is not. Jane Newuser is registering at your site at the same time that Bill Olduser is viewing the new user page. The script processing Jane's registration does inserts into several tables: users, mugshots, users_demographics. This may take some time if Jane's mugshot is large. If Bill's query starts before Jane's transaction commits, Bill won't see Jane at all on his new-users page, even if Jane's insertion into some of the tables is complete. " Regards, Heikki - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: "Orr, Steve" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 10:30 PM Subject: Re: Performance Problems with InnoDB Row Level Locking... > Steve, > > - Original Message - > From: "Orr, Steve" <[EMAIL PROTECTED]> > To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Thursday, September 05, 2002 9:49 PM > Subject: RE: Performance Problems with InnoDB Row Level Locking... > > > > Hello again Heikki and thanks for your informative reply. > > > > Regarding... > > > innodb_flush_log_at_trx_commit=2 > > This is not an option as we must guarantee no lost transactions. But I > will > > test it out of curiosity just to see what the performance difference is. > > if you want to get high performance for disk flushes, you should buy a disk > with a battery-backed cache, and check that the disk driver is aware of it. > > Otherwise we are constrained by the disk rotation speed, some 150 rounds / > second. > > > Regarding... > > > InnoDB uses next-key locking to ensure serializability and that 'phantom > > > rows' do not appear. You can get lock conflicts even if the queries > > > seemingly would not overlap. > > Does this mean that InnoDB is locking the next leaf in the B-Tree? That > > No, only the next index record in alphabetical order. > > > would explain the problem as those rows could be updated by other > sessions. > > If that's the case then I think the next-key locking architecture is the > > problem because it introduces "artificial" deadlocks on heavily used > tables > > and indexes that would otherwise not occur. > > Yes, that is true. > > > (See http://portal.acm.org/citation.cfm?id=335461&dl=ACM&coll=portal#) > > > > Using the same code on the same machine I'm getting dramatically better > > performance with PostgreSQL and Oracle both of which implement > multi-version > > concurrency control with an ANSI isolation level of "read committed." I > > understand that this isolation level allows for unrepeatable reads but > this > > is easily overcome programatically (if needed). It seems like the > repeatable > > read isolation level isn't as practical and isn't really needed that > often. > > Based on the work arounds you listed in the coping with deadlocks link, I > > don't see any way around my performance problem. (I had already tried > > transaction resubmission but it just perpetuates the problem.) > > You are ge
Re: Performance Problems with InnoDB Row Level Locking...
Steve, - Original Message - From: "Orr, Steve" <[EMAIL PROTECTED]> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 9:49 PM Subject: RE: Performance Problems with InnoDB Row Level Locking... > Hello again Heikki and thanks for your informative reply. > > Regarding... > > innodb_flush_log_at_trx_commit=2 > This is not an option as we must guarantee no lost transactions. But I will > test it out of curiosity just to see what the performance difference is. if you want to get high performance for disk flushes, you should buy a disk with a battery-backed cache, and check that the disk driver is aware of it. Otherwise we are constrained by the disk rotation speed, some 150 rounds / second. > Regarding... > > InnoDB uses next-key locking to ensure serializability and that 'phantom > > rows' do not appear. You can get lock conflicts even if the queries > > seemingly would not overlap. > Does this mean that InnoDB is locking the next leaf in the B-Tree? That No, only the next index record in alphabetical order. > would explain the problem as those rows could be updated by other sessions. > If that's the case then I think the next-key locking architecture is the > problem because it introduces "artificial" deadlocks on heavily used tables > and indexes that would otherwise not occur. Yes, that is true. > (See http://portal.acm.org/citation.cfm?id=335461&dl=ACM&coll=portal#) > > Using the same code on the same machine I'm getting dramatically better > performance with PostgreSQL and Oracle both of which implement multi-version > concurrency control with an ANSI isolation level of "read committed." I > understand that this isolation level allows for unrepeatable reads but this > is easily overcome programatically (if needed). It seems like the repeatable > read isolation level isn't as practical and isn't really needed that often. > Based on the work arounds you listed in the coping with deadlocks link, I > don't see any way around my performance problem. (I had already tried > transaction resubmission but it just perpetuates the problem.) You are getting so many deadlocks that some transactions do not pass at all? Then best to serialize them with LOCK TABLES or the 'semaphore table' method explained in the manual. Too much concurrency sometimes degrades performance, and it is better to fall back to coarser granularity locking. > If the repeatable read isolation level presents a performance problem that I > can't work around then I'd rather have the read committed isolation level. > Are there any plans to enable the read committed isolation level in InnoDB? > It seems like read committed is the most commonly implemented isolation > level amongst the other database vendors so what was behind the decision to > implement the repeatable read isolation level in InnoDB? Just curious. :-) No, unfortunately MySQL replication and recovery from the binlog requires that updating transactions have serializable execution. We log complete SQL statements. They have to be performed in the exact same way in the slave or in recovery. Consider INSERT INTO ... SELECT ... If phantom rows could appear in the result set of the SELECT, the number of rows inserted in the slave could differ from what happened in the master. > In the link you gave you state: > "You can get deadlocks even in the case of transactions which just insert or > delete a single row. That is because these operations are not really > 'atomic': they automatically set locks on the (possibly several) index > records of the row inserted/deleted." > > So... if these operations are not atomic then does that mean that MySQL > still does not pass the ACID test even with InnoDB? InnoDB of course complies to the 'ACID' rules. What I meant with the above is that even a single insert into a table is a complex sequence of operations. People often forget that several indexes may be updated, foreign key checks are made in other tables. Deadlocks can arise in unexpected ways. Next-key locking actually makes InnoDB more 'ACID' than Oracle or PostgreSQL. Phantom rows compromise the 'C' and 'I' in Oracle and PostgreSQL. Consider a consistency rule: "sum of all balances in table ACCOUNT must be 1,000,000". How do you keep that true if your database cannot block phantom rows? " Consistency The database is transformed from one valid state to another valid state. A transaction is legal only if it obeys user-defined integrity constraints. Illegal transactions aren't allowed and, if an integrity constraint can't be satisfied the transaction is rolled back. For example, suppose
RE: Performance Problems with InnoDB Row Level Locking...
Hello again Heikki and thanks for your informative reply. Regarding... > innodb_flush_log_at_trx_commit=2 This is not an option as we must guarantee no lost transactions. But I will test it out of curiosity just to see what the performance difference is. Regarding... > InnoDB uses next-key locking to ensure serializability and that 'phantom > rows' do not appear. You can get lock conflicts even if the queries > seemingly would not overlap. Does this mean that InnoDB is locking the next leaf in the B-Tree? That would explain the problem as those rows could be updated by other sessions. If that's the case then I think the next-key locking architecture is the problem because it introduces "artificial" deadlocks on heavily used tables and indexes that would otherwise not occur. (See http://portal.acm.org/citation.cfm?id=335461&dl=ACM&coll=portal#) Using the same code on the same machine I'm getting dramatically better performance with PostgreSQL and Oracle both of which implement multi-version concurrency control with an ANSI isolation level of "read committed." I understand that this isolation level allows for unrepeatable reads but this is easily overcome programatically (if needed). It seems like the repeatable read isolation level isn't as practical and isn't really needed that often. Based on the work arounds you listed in the coping with deadlocks link, I don't see any way around my performance problem. (I had already tried transaction resubmission but it just perpetuates the problem.) If the repeatable read isolation level presents a performance problem that I can't work around then I'd rather have the read committed isolation level. Are there any plans to enable the read committed isolation level in InnoDB? It seems like read committed is the most commonly implemented isolation level amongst the other database vendors so what was behind the decision to implement the repeatable read isolation level in InnoDB? Just curious. :-) In the link you gave you state: "You can get deadlocks even in the case of transactions which just insert or delete a single row. That is because these operations are not really 'atomic': they automatically set locks on the (possibly several) index records of the row inserted/deleted." So... if these operations are not atomic then does that mean that MySQL still does not pass the ACID test even with InnoDB? Thanks again and I'm eagerly awaiting your reply. Respectfully, Steve Orr -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 05, 2002 9:05 AM To: [EMAIL PROTECTED] Subject: Re: Performance Problems with InnoDB Row Level Locking... Steve, - Original Message - From: ""Orr, Steve"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, September 05, 2002 5:52 PM Subject: Performance Problems with InnoDB Row Level Locking... > Background: > I've developed a simplistic Perl program to test database performance with > concurrent session queries. The queries involve inserts, updates, and > deletes in order to test database performance in an OLTP mult-user ACID > compliant scenario. Obviously this is not a "real world test" but it does > stress the database engine's ability to manage transactions so it is > somewhat valid for comparison purposes. > > Problem: > When I do an insert/update/delete I sporadically get the following: > ":mysql::st execute failed: Deadlock found when trying to get lock; Try > restarting transaction at dafunc.pm line 340..." The word "deadlock" is > misleading because all the database changes are based on a session number > ensuring that no session is trying to change data that is also being changed InnoDB uses next-key locking to ensure serializability and that 'phantom rows' do not appear. You can get lock conflicts even if the queries seemingly would not overlap. For example, CREATE TABLE emptytable(a INT NOT NULL, PRIMARY KEY(a)) TYPE = InnoDB; user 1: SELECT * FROM emptytable WHERE a = 100 LOCK IN SHARE MODE; will make user 2: INSERT INTO emptytable VALUES (150); wait for a next-key lock on the 'supremum' of the primary index. These operations would not overlap when using so-called predicate locking, but that is too expensive to implement. In transactional databases deadlocks are a classic problem. Please refer to http://www.innodb.com/ibman.html#Cope_with_deadlocks. > by another session. It appears a time out is occurring before the shared row > level locks are acquired. Changing innodb_lock_wait_timeout doesn't seem to > do much. How do I tune this? > > Given table locking problems associated with MyISAM, it was thought that > InnoDB would perform better but in my tests performance is worse.
Re: Performance Problems with InnoDB Row Level Locking...
Alexander, - Original Message - From: "Varshavchick Alexander" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 6:51 PM Subject: Re: Performance Problems with InnoDB Row Level Locking... > Heikki, one little question - is it a mistype, or can a flush log interval > duration be controlled by this option? The value should only be 0 or 1 as > the documentation says... there is a new value 2 introduced in 3.23.52: " A new setting innodb_flush_log_at_trx_commit=2 makes InnoDB to write the log to the operating system file cache at each commit. This is almost as fast as the setting innodb_flush_log_at_trx_commit=0, and the setting 2 also has the nice feature that in a crash where the operating system does not crash, no committed transaction is lost. If the operating system crashes or there is a power outage, then the setting 2 is no safer than the setting 0. " 0 = write to log file once per second 1 = write to log file and flush the log to disk at every commit 2 = write to log file at every commit, but only flush to disk once per second I think 2 will be the best setting for most high-end users. > On Thu, 5 Sep 2002, Heikki Tuuri wrote: > > > You can try setting > > > > innodb_flush_log_at_trx_commit=2 > > > > if you can afford losing some last transactions in a power outage or an > > operating system crash. > > > > > Alexander Varshavchick, Metrocom Joint Stock Company > Phone: (812)118-3322, 118-3115(fax) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Heikki, one little question - is it a mistype, or can a flush log interval duration be controlled by this option? The value should only be 0 or 1 as the documentation says... On Thu, 5 Sep 2002, Heikki Tuuri wrote: > You can try setting > > innodb_flush_log_at_trx_commit=2 > > if you can afford losing some last transactions in a power outage or an > operating system crash. > sql,query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Problems with InnoDB Row Level Locking...
Steve,
- Original Message -
From: ""Orr, Steve"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, September 05, 2002 5:52 PM
Subject: Performance Problems with InnoDB Row Level Locking...
> Background:
> I've developed a simplistic Perl program to test database performance with
> concurrent session queries. The queries involve inserts, updates, and
> deletes in order to test database performance in an OLTP mult-user ACID
> compliant scenario. Obviously this is not a "real world test" but it does
> stress the database engine's ability to manage transactions so it is
> somewhat valid for comparison purposes.
>
> Problem:
> When I do an insert/update/delete I sporadically get the following:
> ":mysql::st execute failed: Deadlock found when trying to get lock; Try
> restarting transaction at dafunc.pm line 340..." The word "deadlock" is
> misleading because all the database changes are based on a session number
> ensuring that no session is trying to change data that is also being
changed
InnoDB uses next-key locking to ensure serializability and that 'phantom
rows' do not appear. You can get lock conflicts even if the queries
seemingly would not overlap.
For example,
CREATE TABLE emptytable(a INT NOT NULL, PRIMARY KEY(a)) TYPE = InnoDB;
user 1: SELECT * FROM emptytable WHERE a = 100 LOCK IN SHARE MODE;
will make
user 2: INSERT INTO emptytable VALUES (150);
wait for a next-key lock on the 'supremum' of the primary index.
These operations would not overlap when using so-called predicate locking,
but that is too expensive to implement.
In transactional databases deadlocks are a classic problem. Please refer to
http://www.innodb.com/ibman.html#Cope_with_deadlocks.
> by another session. It appears a time out is occurring before the shared
row
> level locks are acquired. Changing innodb_lock_wait_timeout doesn't seem
to
> do much. How do I tune this?
>
> Given table locking problems associated with MyISAM, it was thought that
> InnoDB would perform better but in my tests performance is worse. It
appears
> InnoDB is taking a long time to acquire shared row level locks and is
timing
> out. If that's the case then the benefit of row level locks over table
level
> locks is more than offset by the internal InnoDB overhead to manage the
> locks. Any other explanations? Any known performance issues with InnoDB?
Any
> Perl DBI driver performance issues?
>
> Here's a snippet of the Perl code for the curious:
> -
> ## Update by session, rand_val...
> sub updSessionRand {
> eval {
> my $rtnval= 0 ;
> $estart = time() ;
> my $dbh = @_[1] ;
> $sess_val = @_[2] ;
> $sqlStmt = "UPDATE bench_data SET text_val='updated text by rand_val',
> timestamp_val=$timestamp
> WHERE sess_val = ? AND rand_val between ? AND ? ";
>
> my $stmtHdl=$dbh->prepare($sqlStmt);
> $stmtHdl->execute($sess_val,$sess_val+900,$sess_val+1500) ;
> $dbh->commit();
> $edone = time();
> $totsec = $edone-$estart;
> print "Session:$sess_val, upd02, seconds:$totsec\n";
> };
> if ($@) {
> warn "Session $sess_val upd02 failed.\n $@";
> $rtnval = 1 ;
> }
> return $rtnval ;
> }
> -
>
> (Side Note: In a separate process I found out that the syntax "SELECT ...
> FOR UPDATE" produces exclusive locks so I changed it to "SELECT ... LOCK
IN
> SHARE MODE" and that helped matters. I also tried setting the transaction
> isolation level to serializable but that was worse.)
>
> I am running MySQL version 3.23.52-max-log with Linux 2.4.18-3
> (RedHat 7.3.2.96-110) on 2 PentiumIII processors with 2GB RAM.
>
> Here are some current innodb related my.cnf settings:
> set-variable = innodb_lock_wait_timeout=300
> innodb_flush_log_at_trx_commit=1
You can try setting
innodb_flush_log_at_trx_commit=2
if you can afford losing some last transactions in a power outage or an
operating system crash.
> set-variable = innodb_buffer_pool_size=384M
> set-variable = innodb_additional_mem_pool_size=20M
> set-variable = innodb_thread_concurrency=4
>
>
> TIA!
> Steve Orr
> sql,query
Best regards,
Heikki
Innobase Oy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Performance Problems with InnoDB Row Level Locking...
Background:
I've developed a simplistic Perl program to test database performance with
concurrent session queries. The queries involve inserts, updates, and
deletes in order to test database performance in an OLTP mult-user ACID
compliant scenario. Obviously this is not a "real world test" but it does
stress the database engine's ability to manage transactions so it is
somewhat valid for comparison purposes.
Problem:
When I do an insert/update/delete I sporadically get the following:
":mysql::st execute failed: Deadlock found when trying to get lock; Try
restarting transaction at dafunc.pm line 340..." The word "deadlock" is
misleading because all the database changes are based on a session number
ensuring that no session is trying to change data that is also being changed
by another session. It appears a time out is occurring before the shared row
level locks are acquired. Changing innodb_lock_wait_timeout doesn't seem to
do much. How do I tune this?
Given table locking problems associated with MyISAM, it was thought that
InnoDB would perform better but in my tests performance is worse. It appears
InnoDB is taking a long time to acquire shared row level locks and is timing
out. If that's the case then the benefit of row level locks over table level
locks is more than offset by the internal InnoDB overhead to manage the
locks. Any other explanations? Any known performance issues with InnoDB? Any
Perl DBI driver performance issues?
Here's a snippet of the Perl code for the curious:
-
## Update by session, rand_val...
sub updSessionRand {
eval {
my $rtnval= 0 ;
$estart = time() ;
my $dbh = @_[1] ;
$sess_val = @_[2] ;
$sqlStmt = "UPDATE bench_data SET text_val='updated text by rand_val',
timestamp_val=$timestamp
WHERE sess_val = ? AND rand_val between ? AND ? ";
my $stmtHdl=$dbh->prepare($sqlStmt);
$stmtHdl->execute($sess_val,$sess_val+900,$sess_val+1500) ;
$dbh->commit();
$edone = time();
$totsec = $edone-$estart;
print "Session:$sess_val, upd02, seconds:$totsec\n";
};
if ($@) {
warn "Session $sess_val upd02 failed.\n $@";
$rtnval = 1 ;
}
return $rtnval ;
}
-
(Side Note: In a separate process I found out that the syntax "SELECT ...
FOR UPDATE" produces exclusive locks so I changed it to "SELECT ... LOCK IN
SHARE MODE" and that helped matters. I also tried setting the transaction
isolation level to serializable but that was worse.)
I am running MySQL version 3.23.52-max-log with Linux 2.4.18-3
(RedHat 7.3.2.96-110) on 2 PentiumIII processors with 2GB RAM.
Here are some current innodb related my.cnf settings:
set-variable = innodb_lock_wait_timeout=300
innodb_flush_log_at_trx_commit=1
set-variable = innodb_buffer_pool_size=384M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_thread_concurrency=4
TIA!
Steve Orr
sql,query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: row level locking .. urgent
On Wed, Sep 04, 2002 at 10:28:36AM +0530, Sekhar.Thota wrote: > Hi, > > I have did setup for MySQL - Innodb tables. But I am not getting support for > row level locks. Whenever I am issuing a update the innodb tables are > locking my tables. Please help me whether I need to set any variables in > sqld file to get the support for row level locks ??? Have you read the InnoDB manual at www.innodb.com yet? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 28 days, processed 577,372,495 queries (231/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
row level locking .. urgent
Hi, I have did setup for MySQL - Innodb tables. But I am not getting support for row level locks. Whenever I am issuing a update the innodb tables are locking my tables. Please help me whether I need to set any variables in sqld file to get the support for row level locks ??? Regards, Sekhar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Row level Locking
Hi, I have did setup for MySQL - Innodb tables. But I am not getting support for row level locks. Whenever I am issuing a update the innodb tables are locking my tables. Please help me whether I need to set any variables in sqld file to get the support for row level locks ??? Regards, Sekhar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Row level locking
What tests have you done to satisfy to yourself that it is turned off? =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Tom Robinson [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 04, 2002 6:10 AM To: [EMAIL PROTECTED] Subject: Row level locking Hi, Ive just downloaded MySQL and am trying to switch on row level locking. Ive changed the tables to InnoDB but it still seems to be switched off - are there other things which need to be done? Thanks, Tom Robinson Workforce Systems - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Row level locking
Hi, Ive just downloaded MySQL and am trying to switch on row level locking. Ive changed the tables to InnoDB but it still seems to be switched off - are there other things which need to be done? Thanks, Tom Robinson Workforce Systems - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Row level locking in InnoDB
Kishor, >Hi, > >We are trying to implement row level locking in our appln. >we have a table Orderwe are locking the table using >SELECT * FROM Order FOR UPDATE; >but still another user is able to update this table. why? check with mysql>SHOW TABLE STATUS FROM yourdatabasename; that your table really is InnoDB type. >we have autocommit set to zero.Our requirement is >1. user selects a order for editing. >2. then another user should not be able to edit this order.any ideas? >thanx in advance > >kishor Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Row level locking in InnoDB
Hi, We are trying to implement row level locking in our appln. we have a table Order we are locking the table using SELECT * FROM Order FOR UPDATE; but still another user is able to update this table. why? we have autocommit set to zero. Our requirement is 1. user selects a order for editing. 2. then another user should not be able to edit this order. any ideas? thanx in advance kishor _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase released in MySQL-3.23.34a source distribution: transactions and row level locking now in MySQL
On Monday 12 March 2001 17:17, Heikki Tuuri wrote: > Hi, readers of the mailing list! > > the Innobase table handler has finally been released as a part of > the MySQL-3.23.34a source distribution Tarball. Great news! Congratulations for a great addition to the (already) great MySQL software. -- Jean-Luc Fontaine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innobase released in MySQL-3.23.34a source distribution: transactions and row level locking now in MySQL
Hi, readers of the mailing list! the Innobase table handler has finally been released as a part of the MySQL-3.23.34a source distribution Tarball. Note that the Windows source distribution or the binary distributions do not contain yet Innobase. I have copied below the release note. I will post more information to my website www.innobase.fi as I get feedback and bug reports from users. --- Innobase engine released in the MySQL-3.23.34a source distribution for Unix. (Helsinki, March 12th, 2001) The Innobase engine source code has been released under the GNU GPL License 2 as a part of MySQL-3.23.34a source distribution for Unix. You can find the source distribution file from the MySQL website: http://www.mysql.com/downloads/mysql-3.23.html, look at source downloads for 3.23.34a Tarball. Innobase adds transactions, rollback, commit, row level locking, and an Oracle-style consistent non-locking read to MySQL, the popular open-source database. The combination MySQL/Innobase is probably the world's fastest disk-based relational transactional database. How to compile MySQL-3.23.34a with Innobase? After downloading, unzipping, and untarring the 'Tarball', go to the main directory of MySQL (usually named mysql-3.23.34a), and type: ./configure --with-innobase Then you have to compile and install MySQL. For information on this look at the MySQL documentation. To create tables in the Innobase format you have to first specify Innobase startup options in the my.cnf file. For instructions how to specify them, see section 8.7 of the MySQL online manual at the MySQL website. You can create tables in the Innobase format by specifying TYPE=INNOBASE after the table creation statement: CREATE TABLE t10 (a int not null, b int, primary key (a)) TYPE=INNOBASE; Questions, comments, bug reports on MySQL/Innobase: Please send your feedback on Innobase to [EMAIL PROTECTED] You can also post your feedback on the MySQL mailing lists: see the Documentation section of the MySQL website on instructions how to subscribe and post to the mailing lists. The main mailing list, simply named mysql, is the liveliest one. If you can report a repeatable bug, you can also post to the bugs mailing list. I would also like to receive reports from those who have tested Innobase, but who have no problems. It is helpful to know where the system is working well. Helsinki, March 12th, 2001 Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A new engine under MySQL with trx & row-level locking
Hi! I hope that this message goes to the right 'thread' in the mailing list; not replied to a thread before. Monty recommended that we should wait still for a few weeks before releasing MySQL 4.0/Innobase-alpha, to keep source code versions in control. But I can now report here the current status of the work, and I will post an update on this list every week. The current status of MySQL/Innobase is that it runs on Intel Windows NT and Intel Linux. It runs a shortened version of the 'bench' test suite of MySQL without errors, and also can run my own multithreaded stress tests. The current TODO list I have is the following: 1. Optimize the MySQL/Innobase interface regarding to CPU time, and add support for small, less than 7.5 kB BLOBS. 2. Test correct handling of all MySQL data types and the SQL NULL value. 3. Give appropriate information to the MySQL query optimizer about table sizes, and also to users about the file space occupied by each table, index, and other data structures. 4. Tell the MySQL query optimizer if a secondary index contains all the required columns: no need then to search the clustered index (in Innobase every table has a clustered index where the data is stored, this is similar to Sybase clustered indexes). 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks within its own lock table, but if a user uses also MySQL LOCK TABLES... or BDB locks, a deadlock can occur where Innobase does not know of all the locks: this is resolved by adding a timeout for a lock wait, say 100 s., after which the transaction is rolled back. 6. Make the (implicit) select in UPDATE TABLE ... SET ... an x-locking read, not a consistent read. 7. Interface SELECT ... FOR UPDATE and SELECT ... IN SHARE MODE to Innobase, (MySQL parser in 4.0 already knows these syntactic constructs). 8. Move the SQL NULL value as the first in the alphabetical order. 9. Prevent MySQL from using 'generate_table' in DELETE FROM ... . That is not good if we want a rollback. 10. Port to Solaris, HP-UX and all different Unix flavors. --- 11. Add support for > 7.5 kB BLOBS. 12. Writing a direct CREATE INDEX (currently MySQL internally uses ALTER TABLE if an index is created to a table already holding rows). 13. Writing a lock monitor. 14. Writing a disk i/o monitor. Items 1 - 10 mean mainly small changes in source code, and they are necessary to make the database useful. Items 11 - 14 are long-term projects. Lots of work :), but Innobase now contains 100 000 lines of C, so the above changes 1-10 might be small in proportion to the work already done. If you readers of the list have opinions, you have a chance to influence the TODO list above: I can try to mould the list according to what people feel is the most relevent thing to do. Best regards, Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A new engine under MySQL with trx & row-level locking
I am currently developing an export control system that uses mysql and would be very interested in testing your new tansaction table handler. Roger Grayson [EMAIL PROTECTED] Motorola::SPS::ASP::SoCDT::MACS::me Andy Jennings wrote: > > If there is a possibility for outside testing of this then I would also like > the opportunity, alpha/beta code or not. > > Sounds extremely interesting. > > Andy Jennings > Programmer/Systems Engineer > eAccountable > 214-273-5492 > > -Original Message- > From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 18, 2001 12:47 PM > To: Heikki Tuuri > Cc: [EMAIL PROTECTED] > Subject: Re: A new engine under MySQL with trx & row-level locking > > On Thu, Jan 18, 2001 at 04:47:47PM +0200, Heikki Tuuri wrote: > > > Monty recommended that I should write to this list. I am currently > > putting my database engine called Innobase under MySQL as a new > > transactional table handler. It is projected to come out with MySQL > > version 4 as free open source software. > > > > The Windows and Linux versions of the combined MySQL/Innobase > > database already work. > > This is excellent news! > > > Innobase supports transactions, rollback, recovery, row-level > > locking, and also consistent non-locking reads in the Oracle > > style. I noticed from the messages posted yesterday to this list > > that the lack of transactions has been a major complaint of some > > free database users about MySQL. Transactions will come to MySQL > > with Innobase, and also with the Berkeley DB handler, which is an > > alternative transaction-safe table handler currently being installed > > under MySQL. > > Sounds very good. The obvious question, then, is where can we find out > more about it? I assume that this is alpha-quality right now? Is it > available for those of us who'd like to test it out and see what it > can do? > > Thanks, > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 328-7878Fax: (408) 530-5454 > Cell: (408) 439-9951 > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A new engine under MySQL with trx & row-level locking
If there is a possibility for outside testing of this then I would also like the opportunity, alpha/beta code or not. Sounds extremely interesting. Andy Jennings Programmer/Systems Engineer eAccountable 214-273-5492 -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 18, 2001 12:47 PM To: Heikki Tuuri Cc: [EMAIL PROTECTED] Subject: Re: A new engine under MySQL with trx & row-level locking On Thu, Jan 18, 2001 at 04:47:47PM +0200, Heikki Tuuri wrote: > Monty recommended that I should write to this list. I am currently > putting my database engine called Innobase under MySQL as a new > transactional table handler. It is projected to come out with MySQL > version 4 as free open source software. > > The Windows and Linux versions of the combined MySQL/Innobase > database already work. This is excellent news! > Innobase supports transactions, rollback, recovery, row-level > locking, and also consistent non-locking reads in the Oracle > style. I noticed from the messages posted yesterday to this list > that the lack of transactions has been a major complaint of some > free database users about MySQL. Transactions will come to MySQL > with Innobase, and also with the Berkeley DB handler, which is an > alternative transaction-safe table handler currently being installed > under MySQL. Sounds very good. The obvious question, then, is where can we find out more about it? I assume that this is alpha-quality right now? Is it available for those of us who'd like to test it out and see what it can do? Thanks, Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A new engine under MySQL with trx & row-level locking
On Thu, Jan 18, 2001 at 04:47:47PM +0200, Heikki Tuuri wrote: > Monty recommended that I should write to this list. I am currently > putting my database engine called Innobase under MySQL as a new > transactional table handler. It is projected to come out with MySQL > version 4 as free open source software. > > The Windows and Linux versions of the combined MySQL/Innobase > database already work. This is excellent news! > Innobase supports transactions, rollback, recovery, row-level > locking, and also consistent non-locking reads in the Oracle > style. I noticed from the messages posted yesterday to this list > that the lack of transactions has been a major complaint of some > free database users about MySQL. Transactions will come to MySQL > with Innobase, and also with the Berkeley DB handler, which is an > alternative transaction-safe table handler currently being installed > under MySQL. Sounds very good. The obvious question, then, is where can we find out more about it? I assume that this is alpha-quality right now? Is it available for those of us who'd like to test it out and see what it can do? Thanks, Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A new engine under MySQL with trx & row-level locking
Hi! This is the first time I am posting to the MySQL mailing list. So let us see if this message gets through. Monty recommended that I should write to this list. I am currently putting my database engine called Innobase under MySQL as a new transactional table handler. It is projected to come out with MySQL version 4 as free open source software. The Windows and Linux versions of the combined MySQL/Innobase database already work. Innobase supports transactions, rollback, recovery, row-level locking, and also consistent non-locking reads in the Oracle style. I noticed from the messages posted yesterday to this list that the lack of transactions has been a major complaint of some free database users about MySQL. Transactions will come to MySQL with Innobase, and also with the Berkeley DB handler, which is an alternative transaction-safe table handler currently being installed under MySQL. You will be able to use Innobase through MySQL by creating your tables with CREATE TABLE FOO () TYPE = INNOBASE option. Otherwise they will appear like any MySQL tables. Transaction commit and rollback are done in MySQL through COMMIT and ROLLBACK commands. Row-level locking is automatically performed in SQL commands like INSERT and UPDATE. SELECTs will by default use the consistent read mechanism, which means that no locks are placed, rather the SELECT will read a consistent snapshot of the database at a point of time. This means that Innobase is a multi-versioned database: it stores old versions of rows as long as they might be needed in SELECTs. The row-level locks in Innobase are next-key locks: this means that transactions are serializable and so-called phantom rows will not appear. SELECTs will have also options ...FOR UPDATE and ...IN SHARE MODE, which mean that the SELECT places exclusive or shared locks on rows it reads. These options are useful in some applications. Innobase tables are placed in files you specify in the my.cnf file, along with the file sizes. The files form an Oracle-style tablespace where all Innobase tables and other data structures are stored. The CPU performance of MySQL/Innobase should be the best of all disk-based relational transactional databases. At least the simple join and insert tests I have run suggest this. A restriction currently is that a row in Innobase can be at maximum 7.5 kB long. BLOBS should appear some time in the future when I have time to implement them. If you readers of the MySQL mailing list have comments or questions about this, I would be pleased to receive feedback on this mailing list. Best regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
