I have an application to copy data off onto external disks. The copy requests are stored in a MySQL database and will be read from multiple copy machines, which run a bash script to pick up requests. Once a request is picked up, it is set as "inprogress" in the database. However, I'm trying to avoid a situation where multiple machines read the request close together and start copying the same data.

I was going to use table locking to do this, but I'm struggling because a table lock expires when a session expires so if I do:

```
mysql="mysql -h dbhost -u user -pPassword diskcopydb"
echo "LOCK tables diskcopy WRITE;" | $mysql
echo "SELECT SQL_NO_CACHE * FROM diskcopy WHERE status=\"request\"" | $mysql

```

the lock has actually expired by the time it gets to getting the requests, so the race condition persists. There is a question here:

[MySQL from the command line - can I practically use LOCKs?](https://stackoverflow.com/questions/1324697/mysql-from-the-command-line-can-i-practically-use-locks)

where the SQL commands are packaged into a block and they are piped together into MySQL, but I need to get output from MySQL half way through to pick up the requests. Does somebody have a recipe for this? It seems like it ought to be a fairly common use-case...