Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

[EMAIL PROTECTED] wrote:

Heikki,
The application which is having this problem is used to read an XML document and update a database. The application is part of a website, so there are always other interactions with the database while the "loader" is running. The log below was created at a time when no one was using the website, so the only thing we had running was 1 instance of the loader. This allowed us to get a view of the actual SQL being submitted by the loader.

The error did NOT show up when creating this log, and has never shown up in a non production environment. This is what is leading us to think that there are some other interactions going on which we are not aware of.
In production the loader will cause the "lock" error while loading a particular XML file. Then if you immediately try loading the same file again, no error will occur. The table name which shows up in the error can be any one of about 5 tables which are used in this block of SQL statements. Running multiple loaders at the same time in a test environment never produces the error.

[snip]

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.

Your query log actually shows 2 _different_ connections doing work...Thread id 7 and 12. Maybe your application is not using just one connection, but you think it is?

-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+TAnJtvXNTca6JD8RArAgAJ0RwFm6CpqzQoF6I+X5ySHIXWuA5ACgl94k
LQsIJ7K4C/Krjo8SfKiCXYs=
=XJJp
-----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