I found a solution here http://mysql.bigresource.com/Track/mysql-8TvKWIvE/
assuming select takes a long time to execute, will this lock the table for a long time?
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';
select * from MyTable t where _critera_.
//get the resultset and then
delete from MyTable t where t.id in(...result...)
Do I need to use a transaction? Is there a single query solution?
Yes, you need to use a transaction. You cannot delete and select rows in a single query (i.e., there is no way to "return" or "select" the rows you have deleted).
You don't necessarily need to do the
REPEATABLE READ option - I believe you could also select the rows
FOR UPDATE, although this is a higher level of locking.
REPEATABLE READ does seem to be the lowest level of locking you could use to execute this transaction safely. It happens to be the default for InnoDB.
How much this affects your table depends on whether you have an index on the
wee column or not. Without it, I believe MySQL would have to lock writes the entire table.