MySQL Bugs: #9355: Locked aliases not recognized during queries

Bug #9355	Locked aliases not recognized during queries
Submitted:	23 Mar 2005 12:12	Modified:	28 Mar 2005 17:50
Reporter:	Huns	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.20-log	OS:	Linux (Debian/kernel 2.4.29)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Hello,

I am trying to implement something like safe transactions using MyISAM until our provider gets around to installing InnoDB. I find it necessary to insert/update the same tables more than once per lock, and according to the MySQL manual, this means I have to lock the tables as aliases. I can insert/update on tables by name, but if I do it by alias (as I must in this case), it tells me that I didn't lock the table.

MySQL server version is 4.0.20-log.

Here is the relevant output:

-- Querying a locked table's alias does not work
LOCK TABLE checkTable AS theAlias WRITE;
Query OK, 0 rows affected (0.01 sec)

INSERT INTO theAlias VALUES('test value');
ERROR 1100 (00000): Table 'theAlias' was not locked with LOCK TABLES

UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
-- Create table
DROP TABLE IF EXISTS checkTable;
CREATE TABLE `checkTable` (
	`asdf` VARCHAR( 5 ) NOT NULL
);

-- Querying a locked table works
LOCK TABLE checkTable WRITE;
INSERT INTO checkTable VALUES('test value');
UNLOCK TABLES;

-- Querying a locked table's alias does not work
LOCK TABLE checkTable AS theAlias WRITE;
INSERT INTO theAlias VALUES('test value');
UNLOCK TABLES;

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See the manual for LOCK TABLES.
http://dev.mysql.com/doc/mysql/en/lock-tables.html
In particular, note the following:

"
if you lock a table using an alias, you must refer to it in your
queries using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;
"

See, "SELECT * FROM t AS myalias" and *not* "SELECT * FROM myalias". As you cannot alias a table in INSERT (no syntax like INSERT INTO TABLE t1 AS myalias) there's no way you can insert into a table that was locked by alias.