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

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 2

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