user157195 user157195 - 1 year ago 142
MySQL Question

mysql select for delete

I found a solution here
assuming select takes a long time to execute, will this lock the table for a long time?

SELECT foo FROM bar WHERE wee = 'yahoo!';
DELETE FROM bar WHERE wee = 'yahoo!';

I wish to use a criteria to select the rows in mysql, return them to my app as resultset, and then delete these rows. How can this be done? I know I can do the following but it's too inefficient:

select * from MyTable t where _critera_.
//get the resultset and then
delete from MyTable t where in(...result...)

Do I need to use a transaction? Is there a single query solution?

Answer Source

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.

Further reading:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download