atomic insert or increment in ActiveRecord/Rails

Question

What's the best way to implement an atomic insert/update for a model with a counter in Rails? A good analogy for the problem I'm trying to solve is a "like" counter with two fields:

url   : string
count : integer

Upon insert, if there is not currently a record with a matching url, a new record should be created with count 1; else the existing record's count field should be incremented.

Initially I tried code like the following:

Like.find_or_create_by_url("http://example.com").increment!(:count)

But unsurprisingly, the resulting SQL shows that the SELECT happens outside the UPDATE transaction:

Like Load (0.4ms)  SELECT `likes`.* FROM `likes` WHERE `likes`.`url` = 'http://example.com' LIMIT 1
(0.1ms)  BEGIN
(0.2ms)  UPDATE `likes` SET `count` = 4, `updated_at` = '2013-01-17 19:41:22' WHERE `likes`.`id` = 2
(1.6ms)  COMMIT

Is there a Rails idiom for dealing with this, or do I need to implement this at the SQL level (and thus lose some portability)?

Answer 1

You can use pessimistic locking,

like = Like.find_or_create_by_url("http://example.com")
like.with_lock do
    like.increment!(:count)
end

Answer 2

I am not aware of any ActiveRecord method that implemts atomic increments in a single query. Your own answer is a far as you can get.

So your problem may not be solvable using ActiveRecord. Remember: ActiveRecord is just a mapper to simplify some things, while complicating others. Some problems just solvable by plain SQL queries to the database. You will loose some portability. The example below will work in MySQL, but as far as I know, not on SQLlite and others.

 quoted_url = ActiveRecord::Base.connection.quote(@your_url_here)
::ActiveRecord::Base.connection.execute("INSERT INTO likes SET `count` = 1, url = \"#{quoted_url}\" ON DUPLICATE KEY UPDATE count = count+1;");

Answer 3

Here is what I have come up with so far. This assumes a unique index on the url column.

begin
  Like.create(url: url, count: 1)
  puts "inserted"
rescue ActiveRecord::RecordNotUnique
  id = Like.where("url = ?", url).first.id
  Like.increment_counter(:count, id)
  puts "incremented"
end

Rails' increment_counter method is atomic, and translates to SQL like the following:

UPDATE 'likes' SET 'count' = COALESCE('count', 0) + 1 WHERE 'likes'.'id' = 1

Catching an exception to implement normal business logic seems rather ugly, so I would appreciate suggestions for improvement.