Hi Mark,
Good call. The way we understand the SQL log it looks like all
of the SQL commands we had expected to be part of the
transaction are on connection 7, while the counter incrementation
which is done outside of the transaction was done on connection
12.
At one point we thought that maybe we where "sharing" a
connection with something else in the application, but the log
seems to show that only the transaction is using the connection in
question.
Thanks for asking the questions. The more questions
everybody asks the better the chance that the light bulb will go on
and show me some stupid thing I am doing without realizing it. :)
By the way, thanks for the code snippet on the counter. We
have already modified ourbase class to incorporate this. The code
is undergoing test today and tomorrow.
Thanks
Steff
On 13 Feb 2003 at 15:10, Mark Matthews wrote:
Date sent: Thu, 13 Feb 2003 15:10:25 -0600
From: Mark Matthews <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Copies to: Heikki Tuuri <[EMAIL PROTECTED]>,
[EMAIL PROTECTED],
[EMAIL PROTECTED],
[EMAIL PROTECTED]
Subject: Re: Transaction problems using InnoDB, "not
locked with LOCKTABLES"
> -----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
>
--
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
