Re: LOCK TABLES error , on a select without any update ?
Heikki, Thanks for the response. Just to be sure that we did not have a piece of code which we had forgotten about, I did a search of the Java classes involved in this application for the word "LOCK". I did not find any instance where we are locking tables. If we want to update a set of rows, we are just doing a regular update, we are NOT using any of the "Select... FOR UPDATE" syntax. I will check to make sure we are running the latest OS service pack. We are not set-up to know if a connection continues to throw an error after the first instance of this "lock" error. However I doubt that it continues too, given that we use a pool of connections and this error happens infrequently. For example we have had 275 logins so far today, and we have had 17 instances of this error (6 on one web server, 11 on the other webserver (both use the same database server)). This mornings errors all happened within a transaction block (Autocommitt=0). The Java code requested a rollback after detecting the error. The failures on Friday where in a different section of code which is NOT in a transaction block (Autocommitt=1). In both cases the symptoms where the same, a report of a file being accessed without "LOCK TABLES". I do not see any pattern with regards to the timing of the errors, though there are cases where both web servers are showing "Table NOT LOCKED' errors at the same times. Of the 17 errors this morning, 4 different table names show up in the errors, though it is the same code (different data) being run every time. Given the simplicity of our SQL statements and the overall simplicity of our database schema, I find it really hard to believe that we are coming across a bug in MySql. The only thing we do within this program is look for records, delete records, and update records. No big multi table joins, no special creation / deletion of tables. We just are not that sophisticated. :) I am wondering of the error is really something else, such as a timeout, which is coming back with the wrong error message. The MySql instance is not reporting any errors or exceptions, and the computer has plenty of memory and has not shown any stability problems. CPU utilization on the Database server seems to run between 20 and 50%. The machines hosting the servlets seem to be running between 40% and 90% utilization. At the times the errors occurred today I would have to guess everything would have been running under 50% utilization given that the number of logins per hour was less then 10% of what we see on normal business days. Since the server loads are relatively light on the weekends, I might be able to turn on some logs without impacting performance to much. What logs do you think I should turn on? I am going to send the MySql report to you under a different cover. Perhaps their is something in it which will catch your eye. Thanks Steff On 22 Feb 2003 at 10:52, Heikki Tuuri wrote: From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Copies to: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject:Re: LOCK TABLES error , on a select without any update ? Date sent: Sat, 22 Feb 2003 10:52:08 +0200 > Steff, > > - Original Message - > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: "Heikki Tuuri" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> Sent: Saturday, February 22, 2003 1:26 AM > Subject: Re: LOCK TABLES error , on a select without any update ? > > > > Hi Heikki, > > Thanks for picking up on this again. After the help from > > you and Mark last week, we removed ALL instances of the > > lock tables from our application. We used the idea Mark > > provided for getting our next sequence number without using > > any locks. In the past this was the only thing we where using > > table locks for. > > ok, good. Actually, with InnoDB you normally do not need table locks > at all. SELECT ... FOR UPDATE is what should be used to lock rows. > > Then this definitely is a bug somewhere in your application/MySQL/JDBC > driver/Java/operating system/hardware. Some possibilities: > > 1. Your application calls MySQL client functions in a wrong way and > causes some confusion. 2. A bug in the JDBC driver of MySQL. 3. A bug > in Java. 4. A bug in MySQL. 5. A bug in Windows 2000 (Service Pack 2). > 6. A fault in the hardware. > > You are the only one who has reported this bug. The error looks lik
Re: LOCK TABLES error , on a select without any update ?
Hi Heikki, Thanks for picking up on this again. After the help from you and Mark last week, we removed ALL instances of the lock tables from our application. We used the idea Mark provided for getting our next sequence number without using any locks. In the past this was the only thing we where using table locks for. In this application we are not even doing anything with begin/end transaction, we are totally dependent on each SQL statement standing on its own. We do use a connection pool and have modified our code to always set the autocommitt level = 1 before handing any connections out of the pool. Do you think we are missing some basic setting in our MySql.ini file ? Again the thing which makes this so hard to debug is it is intermittent and only in production, and not always on the same table or SQL statement. :( Do you know of any log files we can run which will only log errors or exceptions? This might limit the volume of the logs to something manageable and still let us see what connection has the error. Alternatively do you know what state the MySql connection thinks it is in, in order for it to want to through this kind of error? Can we test for the state from within our code ? Thanks again for your patience and assistance. I really want to be able to make MySql work for this application. Thanks Steff Steff, > We have our connection set to Autocommitt=1, and No table locks > are ever explicitly being done on this table anyplace in any of our > code modules. in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK TABLES. You cannot lock just some table and use others unlocked. I repeat that the error could be caused if your application has a bug and uses the same connection to do the SELECT as it has used to LOCK some other table. Are you absolutely sure you do not mix connections in your application? Did you have the general query log running at the time of the error? If yes, can you check from it what was the number of the connection that issued the query resulting in an error, and did that same connection earlier issue a LOCK TABLES? 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
LOCK TABLES error , on a select without any update ?
Hi All, Once again we have run into a situation where our production database is throwing an error regarding the use of table locks for "no apparent reason". We are running MySql on windows with InnoDb. We have a section of code which is working fine most of the time but occasionally will through an error complaining that a table is not locked. Based on the Java stack dump, the SQL statement which caused this error to appear was Select TransactionTypeID From transactionheader Where TransactionHeaderID ="1234" The error reported was : java.sql.SQLException: General error: Table 'transactionheader' was not locked with LOCK TABLES We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. Will someone please help me understand why a select statement without an update would ever cause a tables not Locked error ? Thanks Steff -- 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
Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"
Hi Mark, Good call. The way we understand the SQL log it looks like all of the SQL commands we had expected to be part of the transaction are on connection 7, while the counter incrementation which is done outside of the transaction was done on connection 12. At one point we thought that maybe we where "sharing" a connection with something else in the application, but the log seems to show that only the transaction is using the connection in question. Thanks for asking the questions. The more questions everybody asks the better the chance that the light bulb will go on and show me some stupid thing I am doing without realizing it. :) By the way, thanks for the code snippet on the counter. We have already modified ourbase class to incorporate this. The code is undergoing test today and tomorrow. Thanks Steff On 13 Feb 2003 at 15:10, Mark Matthews wrote: Date sent: Thu, 13 Feb 2003 15:10:25 -0600 From: Mark Matthews <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Copies to: Heikki Tuuri <[EMAIL PROTECTED]>, [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: Transaction problems using InnoDB, "not locked with LOCKTABLES" > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > [EMAIL PROTECTED] wrote: > > Heikki, > > The application which is having this problem is used to read an > > > > XML document and update a database. The application is part of a > > website, so there are always other interactions with the database > > while the "loader" is running. The log below was created at a time > > when no one was using the website, so the only thing we had running > > was 1 instance of the loader. This allowed us to get a view of the > > actual SQL being submitted by the loader. > > > > The error did NOT show up when creating this log, and has > > never shown up in a non production environment. This is what is > > leading us to think that there are some other interactions going on > > which we are not aware of. > > > > In production the loader will cause the "lock" error while > > loading > > a particular XML file. Then if you immediately try loading the same > > file again, no error will occur. The table name which shows up in > > the error can be any one of about 5 tables which are used in this > > block of SQL statements. Running multiple loaders at the same time > > in a test environment never produces the error. > > > [snip] > >>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=1.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. > > Your query log actually shows 2 _different_ connections doing > work...Thread id 7 and 12. Maybe your application is not using just > one connection, but you think it is? > > -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
Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"
Heikki, The application which is having this problem is used to read an XML document and update a database. The application is part of a website, so there are always other interactions with the database while the "loader" is running. The log below was created at a time when no one was using the website, so the only thing we had running was 1 instance of the loader. This allowed us to get a view of the actual SQL being submitted by the loader. The error did NOT show up when creating this log, and has never shown up in a non production environment. This is what is leading us to think that there are some other interactions going on which we are not aware of. In production the loader will cause the "lock" error while loading a particular XML file. Then if you immediately try loading the same file again, no error will occur. The table name which shows up in the error can be any one of about 5 tables which are used in this block of SQL statements. Running multiple loaders at the same time in a test environment never produces the error. The loaders contain the only code in the system which uses explicit transactions. We set the Autocommitt=0 just prior to beginning a transaction. Within the transaction block we do not issue any of the SQL commands (on this same connection) listed in the reference section 8.5. The more I think about this problem the more it feels like we are getting a SQL error along the way and not realizing our transaction is no longer active. The code which is doing these updates is spread across 15 or so methods. I suspect an error which is thrown in one method is not setting any attribute in our code to tell the other methods not to bother doing there part because the transaction has been cancelled due to some SQL exception. Is there a way to ask a conception if there is an open transaction ? Thanks Steff On 13 Feb 2003 at 21:59, Heikki Tuuri wrote: From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Copies to: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject:Re: Transaction problems using InnoDB, "not locked with LOCKTABLES" Date sent: Thu, 13 Feb 2003 21:59:22 +0200 > 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' >
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 Info3.23.54-max-nt-log > > > > > have_innodbYES > > > > > innodb_additional_mem_pool_size104857600
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 ? Thanks in advance for your guidance. Steff 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 Info3.23.54-max-nt-log > > > have_innodbYES > > > innodb_additional_mem_pool_size104857600 > > > innodb_buffer_pool_size1048576000 > > > 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
Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"
Hi Mark, Thanks for taking the time to reply. I like the idea of changing our counter model to reduce the contention around the one file. I will talk to the other developers about this. I re-read the reference you mentioned "http://www.innodb.com/ibman.html#InnoDB_restrictions"; and do not see the problem regarding using table level locks on the counter table, while we use transaction control on all the rest of the tables. I can see the potential for problems if we mix table locks and transaction control on the same table, but in our case the counter table is never referenced except by one function and that function uses the table level locks, and always uses its own connection. By re-asking my question I am not trying to say you are wrong, I am simply trying to understand things to make sure we can remove any other potential problems we have lurking in our code. Thanks again for taking the time to reply. This problem has been haunting us for two months now and yours is the first contact we have had with someone who works with MySql in a production environment. Thanks Steff On 11 Feb 2003 at 19:09, Mark Matthews wrote: Date sent: Tue, 11 Feb 2003 19:09:16 -0600 From: Mark Matthews <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Copies to: [EMAIL PROTECTED], [EMAIL PROTECTED] 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 Info3.23.54-max-nt-log > > have_innodbYES > > innodb_additional_mem_pool_size104857600 > > innodb_buffer_pool_size1048576000 > > 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". > > > > The table name will be different at various times (even though the > > code being executed is always the same), but the error is always > > talking about a failure to lock the tables. > > > > We are using two different connections within this one processing > > loop. The one connection shows up as number 7 below, while > > the other is number 12. Our expectation is that these two > > connections will operate independently, even though the Java code is > > working with them both within our "transaction". Our expectation is > > that a rollback on connection 7 will NOT effect the activities which > > had taken place on connection 12 across this same timespan. Along > > the same lines we would expect the lock and unlock on connection 12 > > will not affect the transaction under way on connection 7. > > > > 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'
Transaction problems using InnoDB, "not locked with LOCKTABLES"
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 Info3.23.54-max-nt-log have_innodbYES innodb_additional_mem_pool_size104857600 innodb_buffer_pool_size1048576000 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". The table name will be different at various times (even though the code being executed is always the same), but the error is always talking about a failure to lock the tables. We are using two different connections within this one processing loop. The one connection shows up as number 7 below, while the other is number 12. Our expectation is that these two connections will operate independently, even though the Java code is working with them both within our "transaction". Our expectation is that a rollback on connection 7 will NOT effect the activities which had taken place on connection 12 across this same timespan. Along the same lines we would expect the lock and unlock on connection 12 will not affect the transaction under way on connection 7. 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=1.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 We are unable to determine what other activities taking place on the server/tables are causing the conflict. Other applications are running against this database which are doing allot of Selects and a few updates, all without any explicit transaction control (autocommit=1). Will someone please provide us with some insights into what we are doing wrong? Thanks -- 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
