Atomicity of a SELECT/UPDATE

[Andy Jackman]

Hi,
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?

Given something like this table:

create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);

I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.

If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could:

lock the tables,
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now() where t_value = 'whatever';
unlock the tables;

Without locking I could do something like this: (assume my-pid is unique
between all users of this application at any one moment)
while (1)
{
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now(), l_pid = <my-pid> where t_value =
'<whatever>' and l_pid is null;
select l_pid from tbl_new where t_value = '<whatever>';
if (l_pid == <my-pid>)
break;
// Else someone grabbed that record before us, go round and do it again
}

This sounds long winded to me. Anyone got a better suggestion?
Thanks,
Andy.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw