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

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

Recommended Posts

How to give MAX + 1 ID to registered data when adding a new record
How to add a new hash / array
[Rails] How to write when making a subquery
How to fix a crash when deleting Realm data in Swift UI List
How to clear all data in a particular table
How to create a data URI (base64) in Java
[Java] How to start a new line with StringBuilder
How to get the latest live stream ID for a channel without using the YouTube Data API
How to insert a video
How to create a method
Things to think about when deciding on a new system architecture
How to create a new Gradle + Java + Jar project in Intellij 2016.03