DB strategy - Lock Table?

Hi all,

I have a process which creates a new object from a backgroundrb job. This object is versioned and the version is based on the existing objects in this table. For example, I've got a table called spreadsheets and it has a column called version. Each version is incremental and when a new one is created, the last version is found
by a query.

The problem I'm having is that when two background jobs are creating
the same type of object, the read the table at the same time and thus
assign the same version number to the object they are saving. I end up with two objects with the same version number. I cannot make it a unique column because it the table holds different objects with different version paths.

Just create a unique index on the pair (or triple etc. ) of columns
that you are interested in.


I think I've solved it by using :lock in the find like this:

spreadsheets = Spreadsheet.find(:all,                                 :conditions => ["parent_id = ?", self.id],                                 :lock => "FOR UPDATE")

This tables uses acts_as_tree and originally I was just calling self.children.