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屋!