2 HTTP requests simultaneously checking if record exists, if not creates it = duplicate records

Question

I have this app that, on first launch, calls 2 different API endpoints on my server backend (both with very different purposes). Both 2 methods, however, have a before_filter that authenticates the call using information passed in the HTTP_AUTH header (device identifier + api key).

If it fails to find a row from my devices table with the provided identifier, it automatically creates a new row with that identifier. My problem is, it seems that sometimes the 2 calls are so simultaneous that both requests don't find any record and so they both create a new device row (with results in duplicate rows that have the same device identifier).

My auth method looks like this:

def current_user      
    authenticate_with_http_basic do |udid, secret|
        if secret == APP_SECRET
            @device = Device.find_by_udid(udid)

            if !@device
                @device = Device.new
                @device.udid = udid
                @device.created_on = DateTime.now.utc
            end

            // set a bunch of properties on @device
            @device.connected_at = DateTime.now.utc
            @device.save
        end
    end
end

UDIDs are deprecated by Apple, please dont store them, you should be using GUIDs instead — deleted_user, Sep 18 '12 at 04:55
actually I called it udid but really it's the MD5-encoded MAC address :) — samvermette, Sep 18 '12 at 04:55
possible duplicate of [mysql insert race condition](http://stackoverflow.com/questions/264807/mysql-insert-race-condition) — noodl, Sep 18 '12 at 05:41

Sergio Tulentsev · Accepted Answer · 2012-09-18T06:36:49.910

8

This is why unique indexes were invented. Put a unique index on udid (so that only one device per udid can exist) and then your code may look like this (pseudocode):

def process
  device = Device.find

  unless device
    device = Device.create
  end

  # do your work with the device

rescue ActiveRecord::RecordNotUnique
  retry # this is an actual ruby keyword. It means "run this begin..end block 
        # again". In this case, there's an implicit begin..end block, which is 
        # the method body.
end

If a second worker tries to insert a device with udid that already exists, it will get an error and should try searching again.

edited Sep 18 '12 at 06:36

answered Sep 18 '12 at 04:53

Sergio Tulentsev

226,338
43
373
367

that `rescue` is far too generic. Only `retry` if you are certain that you know the error will go away on a second attempt, otherwise you're just spinning your wheels. Check that the error was a unique constraint violation, and only then should you `retry`. – Will Palmer Sep 18 '12 at 05:28
1

You can rescue ActiveRecord::RecordNotUnique since around rails 3 – Frederick Cheung Sep 18 '12 at 06:34
@FrederickCheung: oh, thanks. I haven't used ActiveRecord in a while :) – Sergio Tulentsev Sep 18 '12 at 06:37

2 HTTP requests simultaneously checking if record exists, if not creates it = duplicate records

1 Answers1