 | | | DROP TEMPORARY TABLE waiting for table??? | DROP TEMPORARY TABLE waiting for table??? 2006-06-30 - By Nick Arnett
Back
I'm seeing something that I don't think should be happening, but I'm
not sure if it's a MySQL bug.
To allow some of my stored procedures to operate concurrently, I make
a temporary memory copy of some shared tables that are accessed by the
procedures. The temporary heap table has the same name as the table
I'm copying. The scenario here is a data warehouse with a bunch of
data marts and some shared dimensions that are in a separate database.
I'm copying the necessary rows of the commonly used shared dimension
tables, to overcome the problem of stored procedures locking all the
tables they're going to use, which was preventing concurrency.
The problem is that despite this, I'm seeing processes that are stuck
with status "Waiting for table" when they are trying to drop the
temporary table if it exists ("DROP TEMPORARY TABLE IF EXISTS
shared_dimensions.page_dim"). I always drop and recreate it at the
start of a series of analyses, so that they have the most recent copy.
I create the temporary heap table in an independent procedure, so it
can't be locking the table. There are other procedures using their
own temporary heap table copies of the table I'm copying, but they're
not using the "real" table, only the copy.
So... my question is, why is there any problem dropping a table that
should only be visible to the connection that's trying to drop it?
What's even more bizarre is that I get this problem even when the
temporary table doesn't exist, on a brand-new connection.
I've had this code running for a couple of weeks and just noticed the
problem, so I'm not sure if it cropped up right away or not. Haven't
had a chance to bounce the server yet.
Any insight appreciated.
Nick
--
Nick Arnett
narnett@(protected)
Messages: 408-904-7198
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|