How to give MAX + 1 ID to registered data when adding a new record

When adding a new record in Rails, there may be cases where you want to automatically assign a unique ID. For example, give the maximum value +1 of the my_id field.

I wrote a sample that gives a unique id = my_id when registering the name of the room.

Success story

  def assign
    my_id = -1
    room = Room.find_by(room: params[:room])
    if !room.nil?
      my_id = room.my_id
    else
      Room.connection.execute(
        "INSERT INTO rooms (my_id, room, created_at, updated_at) SELECT COALESCE(max(my_id), 0)+1, '#{params[:room]}', '#{Time.now}', '#{Time.now}' from rooms"
      )
      my_id = Room.find_by(roomr: params[:room]).my_id
    end
    redirect_to("/rooms/#{my_id}")
  end

The point is

• Perform the maximum value search of my_id in the subquery of the INSERT statement so that the query is issued once (exclusive)
• Set the current time in created_at and update_at
• Take measures against nulls with COALESCE

Failure example

  def assign
    my_id = -1
    room = Room.find_by(room: params[:room])
    if !room.nil?
      my_id = room.my_id
    else
      new_room = Room.create(
        my_id: Room.max(my_id) + 1,
        room: params[:room]
      )
      my_id = new_room.my_id
    end
    redirect_to("/rooms/#{my_id}")
  end

In the case of failure, between begin transaction and commit transaction

1. SELECT statement-> To search for Room.max (my_id)
2. For INSERT statement-> Room.create

Is issued, Insufficient exclusion when multiple clients call assign at the same time my_id will be duplicated

Actually

I really want to write it in ActiveRecord without issuing raw SQL, Isn't there a good way?

Reference URL

Bring the maximum column value +1 at the time of INSERT I want to do MAX + 1 at the same time as the INSERT statement. I want to register data and issue max at the time of INSERT To ensure that ActiveRecord find_or_create_by is executed