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