Re: using column as 'mutex'

On Mon, Nov 30, 2009 at 9:02 PM, Dennis Gearon <gearond(at)sbcglobal(dot)net> wrote:
> I want to use a dbase record in Postgresql as a mutex in a garbage collection/file upload system.
>
> If I tranfer an uploaded file to a directory, write all its meta information and file name in a record, the user should have 5 minutes to finish inputting the required info for the record. (I want to store in file system instead of database table).
>
> So there will be two processes racing against each other in the database, the web page changing the status of the record to 'complete', and the cron job erasing files that have status 'in-process' and are older than ten minutes.
>
> So there will probably be 6 possible scenarios:
>
> 1/ Web site changes status before the cron job gets to the record and deltes the file, no problem.
> 2/ Cron job changes the record and deletes the file before the website gets to the record, no problem.
>
> 3/ Both access the the record relatively at the same time, the web site having the earlier, lower transaction number and also finishes first.
> 4/ Both access the the record relatively at the same time, the cron job having the earlier, lower transaction number and also finishes first.
>
> 5/ Both access the the record relatively at the same time, the web site having the later, HIGHER transaction number BUT finishes first.
> 6/ Both access the the record relatively at the same time, the cron job having the later, HIGHER transaction number BUT finishes first.

Just make sure that when you inspect the record, do so: a. in a
transaction (preferably a brief one) and b. WITH UPDATE until your
operation completes. That way if two operations collide one will wait
for the other to complete. If you need to lock an external structure
(like a file) for a period shorter or longer than a single
transaction, use an advisory lock.

merlin