-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 [EMAIL PROTECTED] wrote:
Hello,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)
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?
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
