Steff,
> We have our connection set to Autocommitt=1, and No table locks
> are ever explicitly being done on this table anyplace in any of our
> code modules.
in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK
TABLES. You cannot lock just some table and use others unlocked.
I repeat that the error could be caused if your application has a bug and
uses the same connection to do the SELECT as it has used to LOCK some other
table.
Are you absolutely sure you do not mix connections in your application?
Did you have the general query log running at the time of the error? If yes,
can you check from it what was the number of the connection that issued the
query resulting in an error, and did that same connection earlier issue a
LOCK TABLES?
Regards,
Heikki
Innobase Oy
sql query
..............
Subject: LOCK TABLES error , on a select without any update ?
From: Steff.envisage1.com
Date: Thu, 20 Feb 2003 17:25:43 -0500
Hi All,
Once again we have run into a situation where our production
database is throwing an error regarding the use of table locks for
"no apparent reason".
We are running MySql on windows with InnoDb. We have a section
of code which is working fine most of the time but occasionally will
through an error complaining that a table is not locked.
Based on the Java stack dump, the SQL statement which caused
this error to appear was
Select TransactionTypeID From transactionheader Where
TransactionHeaderID ="1234"
The error reported was :
java.sql.SQLException: General error: Table 'transactionheader'
was not locked with LOCK TABLES
We have our connection set to Autocommitt=1, and No table locks
are ever explicitly being done on this table anyplace in any of our
code modules.
Will someone please help me understand why a select statement
without an update would ever cause a tables not Locked error ?
Thanks
Steff
--
Steff McGonagle
Envisage Information Systems, Inc.
Phone (315) 497-9202 x16
www.EnvisageSystems.com
www.Enveritus.com
...............
Our SQL in this application follows the following pattern. (the following is
an 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
---------------------------------------------------------------------
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
