This seems to stem from a typical race condition for the "SELECT or INSERT" case.
Ruby seems to choose performance over safety in its implementation. Quoting "Ruby on Rails Guides":
The first_or_create
method checks whether first returns nil
or not. If
it does return nil
, then create
is called.
...
The SQL generated by this method looks like this:
SELECT * FROM clients WHERE (clients.first_name = 'Andy') LIMIT 1
BEGIN
INSERT INTO clients (created_at, first_name, locked, orders_count, updated_at)
VALUES ('2011-08-30 05:22:57', 'Andy', 0, NULL, '2011-08-30 05:22:57')
COMMIT
If that's the actual implementation (?), it seems completely open for race conditions. Another transaction can easily SELECT
between the first transaction's SELECT
and INSERT
. And then try its own INSERT
, which would raise the error you reported, since the first transaction has inserted the row in the meantime.
The time frame for a race condition could be drastically reduced with a data-modifying CTE. Even a safe version would not cost that much more. But I guess they have their reasons.
Compare this safe implementation: