Eloquent: Difference between Model::orberBy('created_at', 'desc')->first() and Model::all()->last()?

Question

Scenario:

I have a table in which there is a field called "Membership_ID", this is not primary key of the table and is not auto increment. Basically its a six digit UNSIGNED_ZEROFILL INT type, that needs to increment for each new record.

So what I was doing, I was getting last record in the table by created_at, and incrementing its membership_id:

$last_member = Model::orderBy('created_at', 'desc')->first();
$new_id = $last_member->membership_id++;

Strangely, a few records got duplicated membership_id. Upon inspecting I noticed the created_at time of these records with duplicated membership_id was almost identical.

Now I've changed it to:

$last_member = Model::all()->last();
$new_id = $last_member->membership_id++;

Tested it thoroughly and no duplication of Membership_ID so far.

The question is why the membership_id got duplicated in first case. In the created_at timestamp the value was not exactly similar, there was difference in seconds or in some cases the difference was of couple of minutes?

Answer 1

Answer to the question from the title

Model::orderBy('created_at', 'desc')->first() does something like this:

select * from Models order by 'created_at' desc limit 1

while Model::all()->last() does this

select * from Models

i.e. it fetches all models from the database (without explicit ordering) and the last one is returned by Laravel (in php). This is very inefficient and brittle as the actual Model returned relies on the default database ordering.

Answer to the actual question

membership_id got duplicated because of a race condition between two inserts. When two Models are created at the same time, two requests are processed at the same time by two php processes. Here's what can happen (or not, that's why you can only sometimes reproduce it):

1. [request 1] queries for last used membership_id -> gets N
2. [request 2] queries for last used membership_id -> gets N
3. [request 1] inserts new Model with membership_id == N
4. [request 2] inserts new Model with membership_id == N

Unless you use database transactions and the proper transaction isolation level, steps 2 and 3 can be like this (duplicate id) or interchanged (no duplicate id).

I see 2 solutions for you:

1

Use database transactions with Repeatable reads isolation level or higher for the 2 queries you make (get last membership_id and insert).

2

Use either a database SEQUENCE or a table to emulate it.

not a solution!

What you are doing now is not a solution! Unless you take the proper steps, it is just as vulnerable to race conditions as your first attempt. A race condition by definition can happen but is not guaranteed to happen. It is difficult to conclusively test for their absence.