MySQL的命令行 - 我可以实际使用锁？ [英] MySQL from the command line - can I practically use LOCKs?

问题描述

我在做与使用的mysql 命令行程序，一个MySQL进行交互于数据库的一个bash脚本。我想在SQL中使用表锁。我可以这样做吗？

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"

我问，是因为表锁只为会话，所以不会锁尽快，MySQL的程序完成释放？

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?

推荐答案

号有基本理念 - 只有运行mysql的一次，并提供了应工作的液体制剂，但它留下的FIFO磁盘

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

号也是正确的，我搞砸了：一个简单的回声X'GT; FIFO 将关闭FIFO;我记错。和我（删除）评论w.r.t.定时不适用了，不好意思。

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.

这是说，你不需要一个FIFO，你可以使用进程间的管道。并通过我的旧版本的MySQL脚本来看，一些工作类似于这一点，但您不能可让任何命令写入stdout（没有一些EXEC招数）。

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}

另一种选择可能是一个文件描述符分配到FIFO，然后把它在后台运行。这是的非常的类似于号做了，但EXEC选项，将不需要一个子shell来运行的bash命令;因此，将允许您设置RC中的其他东西：

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:

• 这code有没有错误检查对未锁定（或任何SQL命令
  其他东西之内）。这就是的绝对的不平凡。


• 由于在第一个例子中，其他的东西是一个子shell中，你不能轻易
  从上下文中设置的脚本的返回code。

这篇关于MySQL的命令行 - 我可以实际使用锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！