How to do atomic select without limit in subquery?

Post #320,948 by drook 2/3/10 12:42:14 PM 2/3/10 1:14:40 PM Reply	How to do atomic select without limit in subquery? I'm trying to grab a batch of records out of a queue using a subquery. UPDATE queue SET status = 'process-$PID' WHERE ID in ( SELECT ID FROM queue WHERE status = 'pending' AND target_time > NOW() ORDER BY target_time ) LIMIT $batch_size I'm trying to do it in one statement so it's thread-safe. Problem is MySQL says "You can't specify target table 'queue' for update in FROM clause." Is there a way to to this in MySQL? (And stored procedures aren't an option, my host doesn't allow them.) [edit] I just tried it using transactions. Start transaction -> select rows -> update rows -> commit transaction. While testing, I ran it without the commit, but the update happened anyway. So what does MySQL do with transactions anyway? -- Drew Edited by drook Feb. 3, 2010, 01:14:40 PM EST Expand All History
Post #320,967 by static 2/3/10 6:48:11 PM Reply	Several things happening there. Let's get the simple things out of the way first. Transactions don't work for MyISAM tables; they have to be InnoDB. You knew that already, didn't you? :-) And then there's MySQL's AutoCommit flag, although START TRANSACTION is supposed to disable that until you do COMMIT (or ROLLBACK). But it's probably the table-type. The problem with the query is that you're referencing the same table in the sub-query that you're trying to update. MySQL doesn't allow that. Why can't you shift the sub-query's condition to the outer UPDATE? `UPDATE queue SET status = 'process-$PID' WHERE status = 'pending' AND target_time > NOW() ORDER BY target_time LIMIT $batch_size` That's atomic, even on MyISAM tables. Wade. Q:Is it proper to eat cheeseburgers with your fingers? A:No, the fingers should be eaten separately.
Post #320,969 by drook 2/3/10 8:27:14 PM Reply	Doh! I was over-thinking it. And this is an InnoDB table, so I'm not sure why it autocommitted. Just put in your simpler version and yup, it works. -- Drew

Welcome to IWETHEY!