Self join queries on LOCKED TABLES

>Description:
an alias name for a locked table in a query is not recognized
producing an

ERROR 1100: Table 'alias_name' was not locked with LOCK TABLES

error message

tested against 3.23.37 and 4.0.1
>How-To-Repeat:
CREATE TABLE selfjoin(id int);
LOCK TABLES selfjoin READ;
SELECT t1.id, t2.id FROM selfjoin t1, selfjoin t2 WHERE t1.id=t2.id;
>Fix:

>Submitter-Id:
>Originator:
>Organization: Six Offene Systeme GmbH
--
Hartmut Holzgraefe hartmut@six.de http://www.six.de/ +49-711-99091-77
>
>MySQL support: none
>Synopsis: table alias names and locked tables
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.1-alpha-max (Official MySQL-max binary)
>Server: /usr/local/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bin/mysql admin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.1-alpha-max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql4.sock
Uptime: 7 days 6 hours 20 min 39 sec

Threads: 68 Questions: 88678 Slow queries: 22 Opens: 20210 Flush tables: 1 Open tables: 67 Queries per second avg: 0.141
>Environment:

System: Linux hartmut 2.4.16 #8 Die Jan 15 14:57:44 CET 2002 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static'
LIBC:
-rwxr-xr-x 1 root root 1384168 Sep 20 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 25215580 Sep 20 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 20 2001 /usr/lib/libc.so
-rw-r--r-- 1 root root 856542 Sep 24 2001 /usr/lib/libc-client.a
lrwxrwxrwx 1 root root 20 Nov 12 2001 /usr/lib/libc-client.so -> libc-client.so.2000c
-rwxr-xr-x 1 root root 718160 Sep 24 2001 /usr/lib/libc-client.so.2000c
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared --with-berkeley-db --with-innodb


------------------------------------------------------------ ---------
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 bugs-thread12169@lists.mysql.com
To unsubscribe, e-mail

Re: Self join queries on LOCKED TABLES

Hartmut Holzgraefe wrote:
>>Description:
>
> an alias name for a locked table in a query is not recognized
> producing an
>
> ERROR 1100: Table 'alias_name' was not locked with LOCK TABLES
>
> error message

ok, after reading the docs i now know that this is expected
behaviour, but i still can't see the reason for it

my problem here is that i know which tables are going to be used
but some functions i call may create self joins on the fly so
i do not know in advance which aliases will be used for which table



--
Hartmut Holzgraefe hartmut@six.de http://www.six.de/ +49-711-99091-77


------------------------------------------------------------ ---------
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 bugs-thread12171@lists.mysql.com
To unsubscribe, e-mail

Re: Self join queries on LOCKED TABLES

Hi!

Sorry for the late reply but I have been on vacation.

>>>>> "Hartmut" == Hartmut Holzgraefe writes:

Hartmut> Hartmut Holzgraefe wrote:
>>> Description:
>>
>> an alias name for a locked table in a query is not recognized
>> producing an
>>
>> ERROR 1100: Table 'alias_name' was not locked with LOCK TABLES
>>
>> error message

Hartmut> ok, after reading the docs i now know that this is expected
Hartmut> behaviour, but i still can't see the reason for it

Each table in a FROM clause is a separate table instance in MySQL,
even if the table is used many times. (This is not trivial to fix).

The reason for requiring all locks in advance is that we want to avoid
all possible deadlocks when using LOCK TABLE.

If we didn't require this, we would instead have to do quite expensive
dead-lock detection for all internal locks and this could cause a
notable slowdown for all queries.

Hartmut> my problem here is that i know which tables are going to be used
Hartmut> but some functions i call may create self joins on the fly so
Hartmut> i do not know in advance which aliases will be used for which table

It's not a big overhead to generate some extra alias in LOCK TABLE.

If LOCK TABLES doesn't work for you, then another solution is to use
INNODB, in which case you would not have to use LOCK TABLES at all.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.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 bugs-thread12235@lists.mysql.com
To unsubscribe, e-mail

Re: Self join queries on LOCKED TABLES

Michael Widenius wrote:
> Hartmut> ok, after reading the docs i now know that this is expected
> Hartmut> behaviour, but i still can't see the reason for it
>
> Each table in a FROM clause is a separate table instance in MySQL,
> even if the table is used many times. (This is not trivial to fix).

yes, from looking at the source i was afraid so ...

> The reason for requiring all locks in advance is that we want to avoid
> all possible deadlocks when using LOCK TABLE.
>
> If we didn't require this, we would instead have to do quite expensive
> dead-lock detection for all internal locks and this could cause a
> notable slowdown for all queries.

well, this sounds reasonable, maybe this explanation should be added
to the documentation?

> Hartmut> my problem here is that i know which tables are going to be used
> Hartmut> but some functions i call may create self joins on the fly so
> Hartmut> i do not know in advance which aliases will be used for which table
>
> It's not a big overhead to generate some extra alias in LOCK TABLE.

it's not a performance problem, it's about visibility of data
the code that does the locking does not know about the aliases that
some subroutines may introduce and the subroutine doesn't know about
the locking

sure, i could add some extra aliases for each table, just in case,
but then i'd have to do usage tracking for each table in the subroutine
where today it just does something like "FROM ..., table AS private_alias_$n"
where $n is incremented after each use ...

maybe an "LOCK TABLES ADD ..." would help me here?


> If LOCK TABLES doesn't work for you, then another solution is to use
> INNODB, in which case you would not have to use LOCK TABLES at all.

to many installations out there, customers won't update (and even if
they did -> our support would maybe try to kill me for that ...)

and at least one of the tables uses fulltext indices so we are stuck
with MyISAM at least for that one for now :(

so right now we are back to no locking at all. the risk of dumping
inconsistent data is rather low in this special application and
even if it happens the chances of visible effects on the other sides
are rather low, so we can live without althought it would be a
nice-to-have here

--
Hartmut Holzgraefe hartmut@six.de http://www.six.de/ +49-711-99091-77


------------------------------------------------------------ ---------
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 bugs-thread12242@lists.mysql.com
To unsubscribe, e-mail