[Rails] Re: Unique Serial Number Generation and Locking

Ruby on Rails Monday, January 31, 2011



On Monday, January 31, 2011 2:16:02 PM UTC-7, Ruby-Forum.com User wrote:

I hope this "credit card" scenario is a contrived example. It seems to
me that it would be critical for the credit card number to be unique
across ALL users.

Yes, it's made up illustrative purposes.
 

You are correct. Row level locking is useless to prevent the duplication 

of values between rows due to the race condition during a SQL INSERT.

A unique index (possibly across multiple fields) would prevent such
duplication.

Hmm, so you are saying I could use a multi-field index (:user_id, :credit_card_number). Then, during inserts if a duplicate :credit_card_number is attempted a ActiveRecord::StatementInvalid would be generated, thus giving me an indication to try another credit_card_number.

Sounds workable, but is it realistic?
 

> Is my only option to lock the table, find the highest credit card
> number,
> add 1, update the new credit card number, save, and release the table
> lock?

It might be possible for you to use a sequence. But, in your case you
would need a separate sequence for each user if I understand you
correctly. Although this might work it may not be appropriate in your
case.

http://www.postgresql.org/docs/8.1/static/sql-createsequence.html

I don't like that, but nice to know.
 

My concern about locking the entire table would be that if something
went wrong then you might end up in a state where your entire table is
stuck in a locked state. I don't know for sure if that's an issue with
PostgreSQL, but something to consider.

Which concerns me as well.
 

There may also be other considerations if you ever have a need to use
more that one database backend for the purposes of scaling.

I would also try to avoid having to use a "max value" query every time
you needed to find the next number in sequence. You could instead create
a table that contains a foreign key to your users table with the next
sequence value stored there. You would then increment that value for
each insert similar to how database sequences work. This way you should
only have to be concerned about concurrent access to the table used for
sequencing.

I like that idea. That way I'm only locking that sequence table. This way I could use row level (pessimistic) locking.

So, have you are anyone else tried this?

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Posted by Ruby on Rails at 1:30 PM

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)