Hi everyone,

I'm encountering a race condition in my Mojolicious application when using Minion workers to perform database inserts. It appears that last_insert_id is not reliably returning the ID for the insert performed by the current job, especially under load with multiple workers. I suspect this is due to how database connections are managed across the forked worker processes.

My Setup:

• Framework: Mojolicious::Lite
• Background Jobs: Minion
• Database: PostgreSQL
• Connector: DBIx::Connector, initialized once in a helper using a state variable.

Relevant Code:

1. Database Connector Helper:

   helper connector_dcm => sub {
       state $db = DBIx::Connector->new(
           'dbi:Pg:dbname=...', 'user','pass',
           { pg_enable_utf8 => 1, AutoCommit => 1 }
       );
   };

2. Generic Insert Helper:

   helper insertDictIntoTable => sub {
       my ($self, $table, $dict) = @_;
       my $sql = SQL::Abstract->new(quote_char=>'"');
       my $dbh = $self->db; # Gets connection from the helper above
   
       my($stmt, @bind) = $sql->insert($table, $dict);
       my $sth = $dbh->prepare($stmt);
       $sth->execute(@bind);
   
       # This call is not safe across concurrent workers
       return $dbh->last_insert_id(undef, undef, $table, 'id');
   };

3. Minion Task:

   app->minion->add_task(my_task => sub {
       my ($job, @args) = @_;
       my $self = $job->app;
   
       # ... logic to build $mydict ...
       my $mydict = { ... };
   
       # This call is subject to the race condition
       my $new_id = $self->insertDictIntoTable('my_table', $mydict);
   
       # The $new_id is sometimes incorrect, belonging to an
       # insert from another worker.
       $self->do_something_with($new_id);
   });

The Problem:

When multiple Minion workers execute this task concurrently, the $new_id returned by insertDictIntoTable is sometimes the ID generated by an INSERT from a different worker. This leads to incorrect data processing.

My question is: What is the recommended, race-condition-safe way to get the last inserted ID in a multi-worker Minion environment, specifically with PostgreSQL?

Is my DBIx::Connector setup with state appropriate for a pre-forking server like Minion, or is there a better pattern?

Any advice would be greatly appreciated.

Thanks