Steff, a note on terminology: every query inside InnoDB always happens inside a transaction. In the AUTOCOMMIT=1 case there just is an automatic commit done at the end of each SQL statement.
In your log below I cannot see how connection 7 could ever receive the error "Table 'productsprovided' was not locked with LOCK TABLES". If that happens, then it is a bug in either your application code or MySQL. Can you say which SQL statement in the log below received that error? Note also: http://www.innodb.com/ibman.html#InnoDB_transaction_model " 8.5 When does MySQL implicitly commit or rollback a transaction? MySQL has the autocommit mode switched on in a session if you do not do SET AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (if MySQL binlogging is used), ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. If you you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. " Regards, Heikki sql query ... Our SQL in this application follows the following pattern. (the following is a section from the MysQL log with just one instance of the application running): 030125 20:24:29 7 Query SET autocommit=0 7 Query BEGIN 7 Query Select * from accountsprovided Where ExternalID='I06'AND FinServProvID = 'C33' 7 Query UPDATE accountsprovided SET Enabled='Y' WHERE AccountID = 'CKCBSBF2994309' 7 Query Select * from account Where AccountID = 'CKCBSBF2994309' 7 Query UPDATE account SET PreLimit=10000.00 WHERE AccountID = 'CKCBSBF2994309' 12 Query Lock Table Control Write 12 Query Select * From Control 12 Query Update Control set NextID = 6999244 12 Query Unlock Tables 7 Query INSERT INTO productsowned ( VestedValue, Quantity ) VALUES ( 7293.90, 7293.9) ... About another 40 lines of SQL following this same general pattern but using different tables. 7 Query Update cachestatus Set UpdatedOn = null, UpdatedBy = 'XMLWarehouseLoader' Where PrimaryID = 'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311' 7 Query COMMIT 7 Query SET autocommit=1 ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 8:54 PM Subject: Re: Transaction problems using InnoDB, "not locked with LOCKTABLES" > Heikki, > I wish I could reproduce this outside of production. To this point > the only place we have seen this is in production. I did turn the > logging on for MySql and the connection number assigned for all > the SQL which we would have expected to be within a transaction > did not change. Within the same time the connection number used > in the lock table code was always a different connection from the > connection in use by the "transaction" code. > > Is there an easy way we could check to see if we are within a > transaction prior to issuing a SQL call? My current guess is that > something is causing us to "terminate" the transaction but we are > not aware of it, so we continue doing database updates thinking we > are within a transaction and thus the "table" does not need to be > locked. If we could detect from within our code the existence of an > open transaction on the connection we are using, then we might be > able to narrow down the situation which causes the transaction to > "break". The checking would have to be a real check back to the > database, not just some boolean we keep in our program. > > Any ideas you have on how to narrow the focus of our research > would be greatly appreciated. > > Thanks > Steff > > On 13 Feb 2003 at 20:03, Heikki Tuuri wrote: > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Copies to: <[EMAIL PROTECTED]> > Subject: Re: Transaction problems using InnoDB, "not > locked with LOCKTABLES" > Date sent: Thu, 13 Feb 2003 20:03:37 +0200 > > > 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 > > > > > -- > 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
