Andy Jackman said:
> 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.
You almost have it. Look up 'Lock Tables' in the manual.
Unlike most database engines, mysql allows various types of tables. The
defaults is myisam which does not support transactions; however, InnoDB
does. Please see docs for details.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
