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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
