Lock "local" MySQL table

Question

I'm trying to do the same thing as described in MySQL: Insert record if not exists in table, but I'm running it on command line so I need to lock tables manually. This is what I have:

LOCK TABLES `mytable` WRITE;
INSERT INTO `mytable` (`mycolumn`)
SELECT * FROM (SELECT 'test') AS temp
WHERE NOT EXISTS ( 
  SELECT `mycolumn`
  FROM `mytable`
  WHERE `mycolumn` = 'test'
) LIMIT 1;
UNLOCK TABLES;

However, when I run this I get the error:

ERROR 1100 (HY000) at line 239: Table 'mytable' was not locked with LOCK TABLES

The command executes fine without lock/unlock in MySQL Workbench. My guess is that I need to lock the "temp" table, but how do I do that??

LOCK TABLES `mytable` WRITE, <something> AS temp WRITE;

Since "temp" is not an actual table in the database, it's just (SELECT 'test').

I can add that the reason why I don't (just) want to create a unique index is that this results in an error that I don't know how to handle since the exit code for the command line process is always just 1 in case of an error and I really don't want to parse stderr for it...

Is `mytable` by any chance a temporary table ? Post the output of: show create table `mytable`; — Cillier, Oct 22 '13 at 14:45
mytable, CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(10) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci — moggizx, Oct 23 '13 at 08:38

score 1 · Accepted Answer · answered Oct 23 '13 at 10:55

This behavior is well documented in the MySQL manual page for LOCK TABLES;

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

So, this should work for you:

LOCK TABLES `mytable` WRITE, `mytable` as m2 READ
INSERT INTO `mytable` (`value`) SELECT * FROM (SELECT 'test') AS temp
WHERE NOT EXISTS ( 
  SELECT `value`
  FROM `mytable` as `m2`
  WHERE `value` = 'test'
) LIMIT 1;
UNLOCK TABLES;

Lock "local" MySQL table

1 Answers1