CODEWITHSUNDEEP
mysql
moggizx
moggizx

Reputation: 476

Lock "local" MySQL table

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...

Upvotes: 0

Views: 610

Answers (1)

Cillier
Cillier

Reputation: 1021

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;

Upvotes: 1

Related Questions