Steff, ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 7:21 PM Subject: Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"
> Heikki, > Thanks for the reply. > > My confusion is that we are only using 1 table within our lock > area of the code. The error is coming back on the connection > which is not doing any table locks. The error comes when we are in > the middle of a transaction block. > > Is there some reason a table lock on one connection would > affect a transaction tacking place on another connection, provided > none of the SQL statements within the transaction touch the table > being locked ? no. It is a bug either in your application or MySQL. Are you sure you are using client connections in the right way, not mixing them? Can you create a repeatable test case? > Thanks in advance for your guidance. > > Steff Regards, Heikki sql query > On 13 Feb 2003 at 16:48, Heikki Tuuri wrote: > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: "Mark Matthews" <[EMAIL PROTECTED]>, > <[EMAIL PROTECTED]> > Copies to: <[EMAIL PROTECTED]> > Subject: Re: Transaction problems using InnoDB, "not > locked with LOCKTABLES" > Date sent: Thu, 13 Feb 2003 16:48:42 +0200 > > > Mark, Steff, > > > > ----- Original Message ----- > > From: "Mark Matthews" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Wednesday, February 12, 2003 3:09 AM > > Subject: Re: Transaction problems using InnoDB, "not locked with > > LOCKTABLES" > > > > > > > -----BEGIN PGP SIGNED MESSAGE----- > > > Hash: SHA1 > > > > > > [EMAIL PROTECTED] wrote: > > > > Hello, > > > > We are experiencing severe problems when running MySql with > > > > INNODB in a production environment. Applications which work fine > > > > under light load fail when under production load. > > > > > > > > Our MySql environment is as follows: > > > > OS Platform : Windows 2000 Service Pack 2 > > > > Machine description: > > > > Compiler : VC++ 6.0 > > > > Architecture : i686 > > > > Total Memory : 2097151 KB RAM > > > > Server Info 3.23.54-max-nt-log > > > > have_innodb YES > > > > innodb_additional_mem_pool_size 104857600 > > > > innodb_buffer_pool_size 1048576000 > > > > innodb_data_file_path ibdata1 > > > > innodb_data_home_dir > > > > innodb_file_io_threads 4 > > > > innodb_force_recovery 0 > > > > innodb_thread_concurrency 8 > > > > innodb_flush_log_at_trx_commit 1 > > > > innodb_fast_shutdown ON > > > > innodb_flush_method > > > > innodb_lock_wait_timeout 50 > > > > innodb_log_arch_dir > > > > innodb_log_archive OFF > > > > innodb_log_buffer_size 1048576 > > > > innodb_log_file_size 5242880 > > > > innodb_log_files_in_group 2 > > > > innodb_log_group_home_dir .\ > > > > innodb_mirrored_log_groups 1 > > > > > > > > > > > > We have a large block of SQL statements (about 50) which we > > > > would like to treat as one transaction. The software works fine > > > > when running in a limited environment, but issues intermittent > > > > errors when running in production. > > > > > > > > All the errors follow the pattern of "java.sql.SQLException: > > > > General error: Table 'productsprovided' was not locked with LOCK > > > > TABLES". > > > > > > this is a MySQL error. In MySQL there is no deadlock detection of > > table level locks. That is why you have to acquire in one shot locks > > to all tables you are going to use inside your LOCK TABLES. > > > > > > ... > > > I suggest taking a look at Paul Dubois' excellent book, 'MySQL > > > Cookbook' from O'reiley, as he has a whole section on generating > > > sequences, a few of which require _NO_ locking on your part :) > > > > > > -Mark > > > - -- > > > MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/ > > > > > > For technical support contracts, visit > > > https://order.mysql.com/?ref=mmma > > > > > > __ ___ ___ ____ __ > > > / |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]> > > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - > > > JDBC/Java > > > /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA > > > <___/ www.mysql.com > > > > Best regards, > > > > Heikki > > Innobase Oy > > sql query > > > > > > > -- > Steff McGonagle > Envisage Information Systems, Inc. > > Phone (315) 497-9202 x16 > www.EnvisageSystems.com > www.Enveritus.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
