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 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".
> >
> > 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=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
> >
> > 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?
>
> It appears that you are trying to use two different locking models,
> and not noticing the problems with doing that until you get enough
> concurrency going in your system (i.e. production load levels)
>
> Heikki can add to this, but it's not usually the best idea in the
> world to mix 'LOCK TABLES' and InnoDB (see
> http://www.innodb.com/ibman.html#InnoDB_restrictions) unless you
> really know how it's going to interact.
>
> From your SQL, it appears that you are attempting to use 'LOCK
> TABLES'
> to implement a 'sequence' generator. There are two shortcomings with
> your approach. First it won't scale well, because all transactions are
> serializing on a single row...which is probably why you're getting
> these problems. Second, you're locking for _every_ transaction, when
> if you want to use this sequence-generator concept, you'd be better
> off grabbing a range of ids during the lock phase, caching that range,
> and dispensing them from the Java end. This will limit the serializing
> effect, but the size of the 'range' will need to be tuned to match
> your load to avoid the same problem, but not waste ids.
>
> 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
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.1.90 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQE+SZ7EtvXNTca6JD8RAj53AKCF2dA8nsHVJAw7OLx/KvmFdHsivACgmAec
> VLAje7jcy7AmEloRUFiGD7Q=
> =IMEe
> -----END PGP SIGNATURE-----
>
>
> ---------------------------------------------------------------------
> 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
