Hi, Let''s say we have users concurrently performing the following queries inside a transaction (example taken directly from PostgreSQL site): User A: =====BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; ... COMMIT; User B: =====BEGIN; ... UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; ... COMMIT; Am I right assuming that in case of PostgreSQL the UPDATE query of User B would be working with an updated version of the accounts row, whereas in case of MySQL it would update the original one? If I use rails'' optimistic locking feature, I would ensure database-independent behaviour, but would have to implement the handling of rolled-back transactions on my own, right? Are there any best practices for concurrency control with rails out there? I am quite new to this subject, so please bear with me in case I am being ignorant ;) Thanks in advance, Alexei --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Hi, Let''s say we have users concurrently performing the following queries inside a transaction (example taken directly from PostgreSQL site): User A: =====BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; ... COMMIT; User B: =====BEGIN; ... UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; ... COMMIT; Am I right assuming that in case of PostgreSQL the UPDATE query of User B would be working with an updated version of the accounts row, whereas in case of MySQL it would update the original one? If I use rails'' optimistic locking feature, I would ensure database-independent behaviour, but would have to implement the handling of rolled-back transactions on my own, right? Are there any best practices for concurrency control with rails out there? I am quite new to this subject, so please bear with me in case I am being ignorant ;) Thanks in advance, Alexei -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On 8/25/06, Alexei Matveev <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Let''s say we have users concurrently performing the following queries > inside a transaction (example taken directly from PostgreSQL site): > > User A: > =====> BEGIN; > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > ... > COMMIT; > > > User B: > =====> BEGIN; > ... > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; > ... > COMMIT; > > Am I right assuming that in case of PostgreSQL the UPDATE query of User > B would be working with an updated version of the accounts row, whereas > in case of MySQL it would update the original one?Both databases support multiple levels of transaction isolation. Read their docs to see how this affects concurrent transactions. If I use rails'' optimistic locking feature, I would ensure> database-independent behaviour, but would have to implement the handling > of rolled-back transactions on my own, right?You''d have to rescue ActiveRecord::StaleObjectError and do something sensible, like alert the user of a conflict or try to resolve it yourself. Are there any best practices for concurrency control with rails out> there?Use optimistic locking when the chance of conflict is reasonably low (users hate to refill data and resolve conflicts). Use pessimistic locking when concurrency is high and you want to read a bunch of records and update them in the same transaction. The best way is to try to avoid locking altogether. Best, jeremy --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Jeremy Kemper wrote:> Both databases support multiple levels of transaction isolation. Read their > docs to see how this affects concurrent transactions.Well, actually I did read the docs before asking, I asked the question to make sure, I understood them correctly.> The best way is to try to avoid locking altogether.What would be the best way to do this? I have to make a counter which would display the number of views of a certain page. It has to be absolutely accurate even in case of multiple users accessing the page (thus incrementing the counter) simultaneously. I could, of course, organise the counter as a separate table with users adding a new row with the page_id of the accessed page on each view, but wouldn''t be adding hundreds/thousands of new rows, just to measure the number of views, sort of overkill? At least from reading the MySQL/PostgreSQL docs it seemed to me that using postgres would get rid of the problem alltogether due to MVCC Alexei --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On 8/26/06, Alexei Matveev <amatveich-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > Jeremy Kemper wrote: > > > Both databases support multiple levels of transaction isolation. Read > their > > docs to see how this affects concurrent transactions. > > Well, actually I did read the docs before asking, I asked the question > to make sure, I understood them correctly. > > > > The best way is to try to avoid locking altogether. > > What would be the best way to do this? > > I have to make a counter which would display the number of views of a > certain page. It has to be absolutely accurate even in case of multiple > users accessing the page (thus incrementing the counter) > simultaneously. > > I could, of course, organise the counter as a separate table with users > adding a new row with the page_id of the accessed page on each view, > but wouldn''t be adding hundreds/thousands of new rows, just to measure > the number of views, sort of overkill? > > At least from reading the MySQL/PostgreSQL docs it seemed to me that > using postgres would get rid of the problem alltogether due to MVCCYou won''t have trouble in either database. Your best bet is simply ''update pages set hits=hits+1''. This will obtain an exclusive row lock to do the update. If you''re doing page = Page.find(...); page.hits += 1; page.save, you need to wrap it in a serialized transaction or lock the page record. Page.find(..., :lock => true) jeremy --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On Aug 26, 2006, at 4:24 PM, Jeremy Kemper wrote:> On 8/26/06, Alexei Matveev <amatveich-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > The best way is to try to avoid locking altogether. > > What would be the best way to do this? > > I have to make a counter which would display the number of views of a > certain page. It has to be absolutely accurate even in case of > multiple > users accessing the page (thus incrementing the counter) > simultaneously.If you update that row via SQL directly, rather than ActiveRecord, you can avoid a problem by issuing an atomic update statement such as: update table set counter_column = counter_column + 1 This will avoid the external read-modify-write cycle entirely. -- -- Tom Mornini --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On 8/24/06, Alexei Matveev <amatveich-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Am I right assuming that in case of PostgreSQL the UPDATE query of User > B would be working with an updated version of the accounts row, whereas > in case of MySQL it would update the original one? > > If I use rails'' optimistic locking feature, I would ensure > database-independent behaviour, but would have to implement the > handling of rolled-back transactions on my own, right? > > Are there any best practices for concurrency control with rails out > there? > > I am quite new to this subject, so please bear with me in case I am > being ignorant ;)Alexei, I believe this is the same message you posted two days ago. Did you see the replies? In short: both databases do what you expect. Use a single update statement to avoid issues with concurrency, locking, and dirty reads. Best, jeremy --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
> Alexei, I believe this is the same message you posted two days ago. Did you > see the replies?Yes, I''ve seen the replies - I have no idea, why the same message got posted once again, days later :/ Thank you very much for your help! Alexei --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---