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/[EMAIL PROTECTED]