Auto increment a column based on another column avoiding race condition

Question

Lets say I have a model Document.

Document
document_id -> int
client_id -> int

I want the document id to be auto incremented based on the client id avoiding a race condition. I have an unique constraint on document_id, client_id.

 client_id   document_id 
 1           1
 1           2
 2           1
 1           3

Approach 1: Right now, Im setting the document id on before_create callback but it leads to race condition and sets the same value when multiple documents are received for the same client at the same time.

before_create :set_identifier def set_identifier self.document_id = client.documents.maximum(:document_id).next end

This raises the below error when 2 documents are created at the same time.

ActiveRecord::RecordNotUnique PG::UniqueViolation: ERROR: duplicate key value violates unique constraint

Approach 2: Tried using DB triggers on postgresql. It works fine. But the only problem is that, the updated value doesn't reflect initially on the assigned variable. I need to do a force reload on the variable.

DB Function:

CREATE OR REPLACE FUNCTION set_document_identifier()
RETURNS TRIGGER AS $$
DECLARE
max_identifier INTEGER;
BEGIN
  SELECT MAX(document_id) INTO max_identifier
  FROM documents
  WHERE client_id = NEW.client_id;
IF max_identifier IS NULL THEN
    max_identifier := 1;
END IF;
NEW.document_id := max_identifier;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DB Trigger:

CREATE TRIGGER before_insert_trigger_documents
BEFORE INSERT ON documents
FOR EACH ROW
EXECUTE FUNCTION set_document_identifier();

Rails:

doc = Document.create!(client_id: 1)
doc.document_id -> nil
doc.reload.document_id -> 1

Is there any other possible OTB solution which rails provide or a workaround that can be done on the triggers so that the value is set before the transaction commit?

Answer 1

You obviously can't have unique incrementing values per client_id and also have multiple users generating their own next value concurrently. That's just how the universe works.

The simplest method to deal with this is (as you discovered) to have a single point where this is done - in the database.

You should also either (a) lock appropriately to prevent concurrent inserts or (b) expect duplicate key errors and handle them.

Answer 2

1. If what you showed is really how your trigger works, you forgot to increment. This would set first document_id to 1. Next insert for the same client_id finds only that 1, which makes it max() and that insert will also use that same value of 1. You'd keep getting 1's all the time: demo. If you add the +1, it'll kind of work: demo2.

   SELECT MAX(document_id)+1 INTO max_identifier
   

2. Even if you fix that, when you insert multiple documents for a single client_id concurrently, they will all find and use the same max(), resulting in two different documents with the same id.

3. a workaround that can be done on the triggers so that the value is set before the transaction commit

   The value is set before the transaction commit but if your app the returning clause on the insert issued by Rails doesn't include the value you were hoping to generate, you need to tell it to go find it afterwards based on the record's unique id that it does always have.

If you instead use generated, seed-based sequences like in the similar thread I mentioned, it'll work with concurrent inserts too: demo3

create function seeded_sequence_nextval(seed text, 
                                        owner_table regclass default null,
                                        owner_column text default 'ctid') 
returns int as $f$
declare sequence_name text:=concat_ws('__','seeded_sequence',
                                           owner_table,
                                           owner_column,
                                           seed);
begin execute format('create sequence if not exists %I owned by %s;',
                     sequence_name,
                     case when owner_table is null then 'none'
                          else format('%s.%I',owner_table,owner_column)
                     end);
      return nextval(format('%I',sequence_name));
end $f$ language plpgsql;

CREATE OR REPLACE FUNCTION set_document_identifier()
RETURNS TRIGGER AS $f$
BEGIN
SELECT seeded_sequence_nextval(NEW.client_id::text,'documents','document_id')
  INTO NEW.document_id;
RETURN NEW;
END;
$f$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_trigger_documents BEFORE INSERT ON documents
FOR EACH ROW EXECUTE FUNCTION set_document_identifier();

There's a potential caveat to spawning many sequences but it might not necessarily be a thing.

Answer 3

You cannot encapsulate the document creation logic in a service object that receives an array of documents regardless of whether it is a single document?

With that approach, you can obtain the id of the first document from the model and then increment it one by one in your service to save the ID without having problems in the records.

class Creator
  def initialize(documents)
     @user
     @documents
  end
  
  def call
     document_id = Document.id_per_user(user)
     documents.each do |doc|
       doc.id = document_id
       doc.create!
       
       document_id += 1
     end
  end
end

Another approach is use the callback as in your first solution and then rescue the ActiveRecord::RecordNotUnique, then:

document_id += 1
retry