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?
No.
Thanks, Juan
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]