# DB strategy - Lock Table?

**URL:** <https://www.ruby-forum.com/t/db-strategy-lock-table/151183>\
**Category:** Rails\
**Created:** [October 17, 2008, 8:18pm UTC](https://www.ruby-forum.com/t/db-strategy-lock-table/151183 "2008-10-17T20:18:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![shagymoe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ebca7d/32.png) [@shagymoe](https://www.ruby-forum.com/u/shagymoe)\
**Post date:** [October 17, 2008, 8:18pm UTC](https://www.ruby-forum.com/t/db-strategy-lock-table/151183/1 "2008-10-17T20:18:03Z")

</div>

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.

Would locking the entire table be a good solution to this problem? It  
seems like it will cause timeout problems. If this is the way to go,  
what is the best “rails” way to accomplish this? If not, what  
suggestions do you have on how to solve this problem?

Thanks much,

Shagy

---

<div class="post-metadata">

**Author:** ![shagymoe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ebca7d/32.png) [@shagymoe](https://www.ruby-forum.com/u/shagymoe)\
**Post date:** [October 17, 2008, 9:40pm UTC](https://www.ruby-forum.com/t/db-strategy-lock-table/151183/2 "2008-10-17T21:40:36Z")

</div>

On 17 Oct 2008, at 19:18, Shagy M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> 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.

Fred

---

<div class="post-metadata">

**Author:** ![shagymoe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ebca7d/32.png) [@shagymoe](https://www.ruby-forum.com/u/shagymoe)\
**Post date:** [October 17, 2008, 10:30pm UTC](https://www.ruby-forum.com/t/db-strategy-lock-table/151183/3 "2008-10-17T22:30:04Z")

</div>

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.