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
