Here's my table definition:

CREATE TABLE `files` (
`id` int(11) NOT NULL auto_increment,
`checksum` char(32) NOT NULL default '',
`size` bigint(20) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `checksum` (`checksum`(8))

This table is used to store information on files. When I delete a file, instead of 
deleting the row, I set checksum to the empty string and size to 0. When I want to add 
a new file, I try the following query, and if it returns 0 rows affected, I will then 
try a regular insert:

UPDATE files SET checksum='[md5 of file]', size=[size of file] WHERE checksum='' AND 
size=0 ORDER BY id LIMIT 1

The idea is to try and reuse the id's of deleted files, and if there aren't any, then 
I do an INSERT to create a new id. 

My concern is that if I have many clients doing this, that updates from different 
clients may catch the same row and I would lose some data. Am I correct?

Thanks,
Juan

Reply via email to