Get the next Object Id before create the object for a Model Rails

Question

I have a Model I need to get the next record id which is going to be create before create that object in my model like:

MyModel.last.id #=> 10
MyModel.last.destroy
MyModel.last.id #=> 9, so (Model.last.id + 1) would be 10... but...
MyModel.create  #=> 11, my next id was actually 11

Can you please suggest a better way to solve my problem?

Thanks

I think you need a [HiLo id generation](https://stackoverflow.com/questions/282099/whats-the-hi-lo-algorithm) here. Bad news is, it's not very common in rails. In fact, I haven't seen a single usage of it in my practice. But the idea is solid. — Sergio Tulentsev, Mar 16 '18 at 08:38
I cannot think of any good use case for this. As @SergioTulentsev points out, most ways of actually **using** this information will entail race conditions. — Alexander Presber, Mar 16 '18 at 08:51
@MoMolog: there are atomic/safe ways of getting the next id (sequences in PG, for example). As for how it can be used: preparing a graph of interconnected objects and inserting them all at once. — Sergio Tulentsev, Mar 16 '18 at 08:56

Stefan · Accepted Answer · 2018-03-16T08:50:09.817

3

You are looking for the table's AUTO_INCREMENT value. MySQL stores such metadata in the INFORMATION_SCHEMA database. The AUTO_INCREMENT values can be found in the TABLES table. The table contains one entry for each database and table.

I don't think Rails or the MySQL gem provide any built-in method for fetching it.

I have used something like this in one of my previous projects:

# config/initializers/auto_increment.rb

module AutoIncrement
  def auto_increment_value
    connection.execute(<<-SQL.squish).first[0]
      SELECT `AUTO_INCREMENT`
        FROM `INFORMATION_SCHEMA`.`TABLES`
       WHERE `TABLE_SCHEMA` = '#{connection.current_database}'
         AND `TABLE_NAME` = '#{table_name}'
    SQL
  end
end

ActiveRecord::Base.extend(AutoIncrement)

You can then execute:

MyModel.auto_increment_value

and it will return the current value for the table's AUTO_INCREMENT value, e.g. 11.

Note that it is not safe to use that value as an explicit ID for your record. You should let MySQL handle the assignment of new IDs – that's what AUTO_INCREMENT is for.

edited Mar 16 '18 at 08:50

answered Mar 16 '18 at 08:32

Stefan

109,145
14
143
218

3

Isn't this _extremely_ prone to race conditions? – Sergio Tulentsev Mar 16 '18 at 08:35
@SergioTulentsev it just fetches a value from a database table. What do you mean? – Stefan Mar 16 '18 at 08:37
two threads fetch the same value and try to use it as id. Or simply doing this select also magically updates the counter? – Sergio Tulentsev Mar 16 '18 at 08:39
@SergioTulentsev two threads fetching and using the same value creates a race condition, not the above code. I don't know why the OP needs the ID beforehand or how it is going to be used. – Stefan Mar 16 '18 at 08:42
2

Fair enough. Anyway, I thought a warning would not hurt :) – Sergio Tulentsev Mar 16 '18 at 08:45
1

@SergioTulentsev your right, I've added a warning. I used that code to check if the highest id matches the AUTO_INCREMENT value to determine whether the latest record was deleted – the user was given the option to re-use that id then. – Stefan Mar 16 '18 at 08:49

score 1 · Answer 2 · answered Mar 27 '18 at 08:56

1

Why worry about the id? If you have some other reasons for a contiguous number, use another field and do some select max on a before_create.

FWIW, MySQL, and Mariadb auto increment only resets on a database restart, which avoids race situations.

answered Mar 27 '18 at 08:56

Sam Sabey

169
1
3

Get the next Object Id before create the object for a Model Rails

2 Answers2