[RESOLVED] Locking a row from being READ

[eyeRmonkey]

I need to temporarily lock a row from being read by any other queries. I need to do this because I need to get the next row marked as "TODO", then change it to "IN PROCESSING". I don't want one of the other instances of my script to also grab it before I have a chance to mark it as "IN PROCESSING".

Current scenario (not desired): (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on, but now two scripts have a handle on the same record


Desired scenario: (in order of execution)
SCRIPT 1: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 123
SCRIPT 2: SELECT * FROM artists WHERE status='todo' LIMIT 1 result: primary_key of 456 because 123 gets ignored somehow
SCRIPT 1: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (123)
SCRIPT 2: UPDATE artists SET status='in processing' WHERE primary_key = 123 updates the record it just got a handle on (456)

I would LIKE to be able to do this all in one query, but I will settle for multiple queries if that's what must be done.

I know MySQL has the SELECT ... FOR UPDATE command, but I can't figure out how to use and the idea of messing with auto commit stuff scares me. I don't want to turn off auto commit and have to change all my other queries just to make the manually commit.

I'm using PHP and MySQL 5.

Any help at ALL would be greatly appreciated.