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'
> 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
> >
> >
>
>
--
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
