Atomic select/update with mysql.

[pinkbike]

Consider a table called T1 with the following columns

ID (unique)
VALID
VALUE

Table has the following data
1,1,a
2,0,b
3,1,c
4,0,d

I would like to grab the id of the FIRST NON valid (valid=0) row and at the same time update that row's valid field to 1.
Basically do the following

Select id from T1 where valid='0' limit 1
update T1 set valid='1' where id='$the_id_from_previous_select'

I need to do these two calls atomically AND without locking the table.
Can you use subselects to do this? If mysql even supports that, are they atomic at the database level?
Maybe transactions will do the job? Is that even possible with the current mysql?

Thank for your idea/opinions.

[tomhath]

I don't think mysql has transactions yet. You could synchronize updates with semaphores if you are on a Unix platform; I don't think they work on Windows.