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