MySQL from the command line - can I practically use LOCKs?

Question

I'm doing a bash script that interacts with a MySQL datatabase using the mysql command line programme. I want to use table locks in my SQL. Can I do this?

mysql -e "LOCK TABLES mytable"
# do some bash stuff
mysql -u "UNLOCK TABLES"

The reason I ask, is because table locks are only kept for the session, so wouldn't the lock be released as soon as that mysql programme finishes?

Answer 1

[EDIT]

nos had the basic idea -- only run "mysql" once, and the solution nos provided should work, but it left the FIFO on disk.

nos was also correct that I screwed up: a simple "echo X >FIFO" will close the FIFO; I remembered wrongly. And my (removed) comments w.r.t. timing don't apply, sorry.

That said, you don't need a FIFO, you could use an inter-process pipe. And looking through my old MySQL scripts, some worked akin to this, but you cannot let any commands write to stdout (without some "exec" tricks).

#!/bin/bash
(
  echo "LOCK TABLES mytable READ ;"
  echo "Doing something..." >&2
  echo "describe mytable;" 
  sleep 5
  echo "UNLOCK  tables;" 
) | mysql ${ARGUMENTS}

Another option might be to assign a file descriptor to the FIFO, then have it run in the background. This is very similar to what nos did, but the "exec" option wouldn't require a subshell to run the bash commands; hence would allow you to set "RC" in the "other stuff":

#!/bin/bash
# Use the PID ($$) in the FIFO and remove it on exit:
FIFO="/tmp/mysql-pipe.$$"
mkfifo ${FIFO} || exit $?
RC=0

# Tie FD3 to the FIFO (only for writing), then start MySQL in the u
# background with its input from the FIFO:
exec 3<>${FIFO}

mysql ${ARGUMENTS} <${FIFO} &
MYSQL=$!
trap "rm -f ${FIFO};kill -1 ${MYSQL} 2>&-" 0

# Now lock the table...
echo "LOCK TABLES mytable WRITE;" >&3

# ... do your other stuff here, set RC ...
echo "DESCRIBE mytable;" >&3
sleep 5
RC=3
# ...

echo "UNLOCK TABLES;" >&3
exec 3>&-

# You probably wish to sleep for a bit, or wait on ${MYSQL} before you exit
exit ${RC}

Note that there are a few control issues:

• This code has NO ERROR CHECKING for failure to lock (or any SQL commands within the "other stuff"). And that's definitely non-trivial.
• Since in the first example, the "other stuff" is within a subshell, you cannot easily set the return code of the script from that context.

Answer 2

Here's one way, I'm sure there's an easier way though..

mkfifo /tmp/mysql-pipe
mysql mydb </tmp/mysql-pipe &
(
  echo "LOCK TABLES mytable READ ;" 1>&6 
  echo "Doing something "
  echo "UNLOCK  tables;" 1>&6
) 6> /tmp/mysql-pipe

Answer 3

A very interesting approach I found out while looking into this issue for my own, is by using MySQL's SYSTEM command. I'm not still sure what exactly are the drawbacks, if any, but it will certainly work for a lot of cases:

Example:

mysql <<END_HEREDOC
LOCK TABLES mytable;
SYSTEM /path/to/script.sh
UNLOCK TABLES;
END_HEREDOC

It's worth noting that this only works on *nix, obviously, as does the SYSTEM command.

Credit goes to Daniel Kadosh: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html#c10447