How to prevent race conditions for following code?

[wuarmin]

Hey,

I experience race conditions (threads, processes) with following code. Hint: The insert and the find have to be separate calls. I cannot use returning.

def upsert(data)
  id = DB[:users].insert_conflict(target: :id, update: {
    name: Sequel[:excluded][:name],
    email: Sequel[:excluded][:email],
    updated_at: Sequel[:excluded][:updated_at],
   }).insert(data)
   
 DB[:users][id: id]
end

If multiple threads/processes call this code, it sometimes fails with following invalid sql-statement:

INSERT INTO "users" ("id", "name", "email", "created_at", "updated_at")
VALUES (2, 'Test', '[email protected]', '2023-04-17 16:38:11.512975+0200', '2023-04-17 16:38:11.512975+0200')
ON CONFLICT ("id")
DO UPDATE SET "name" = "excluded"."name", "email" = "excluded"."email", "updated_at" = "excluded"."updated_at"
RETURNING "2" -- here's the failure (it should be RETURNING "id", column "2" does not exist)

A db-transaction obviously doesn't help. Do I need to sync the overlying code (i.e. Redlock (redis process-lock))?

Thanks!
best regards

[jeremyevans]

What database are you using? Does switching DB[:users].returning(:id).insert_conflict fix the issue? Is it possible for you to put together a self contained example showing the issue?

[jeremyevans]

Only guess would be you have simultaneous access to the same connection object, such as non disconnecting before fork when using multiprocess code, or using the single threaded connection pool in a multithreaded application. Both of those would be a bug in the application and not in Sequel. I couldn't rule out a bug in Sequel at this point, but nothing comes to mind as a possible cause.

[wuarmin]

Thanks for your input. If I change the code to

def upsert(data)
  User.new(DB[:users].returning(:id, :name, :email, :updated_at, :created_at).insert_conflict(target: :id, update: {
    name: Sequel[:excluded][:name],
    email: Sequel[:excluded][:email],
    updated_at: Sequel[:excluded][:updated_at],
   }).insert(data).first)
end

the issue seems to be fixed.

Following code-change does not fix the issue:

def upsert(data)
  id = DB[:users].returning(:id).insert_conflict(target: :id, update: {
    name: Sequel[:excluded][:name],
    email: Sequel[:excluded][:email],
    updated_at: Sequel[:excluded][:updated_at],
   }).insert(data).first[:id])

  DB[:users][id: id]
end

[jeremyevans]

You probably want User.call or User.load instead of User.new, since these are for users that already exist in the database.

[wuarmin]

Thank you @jeremyevans.
With...

such as non disconnecting before fork when using multiprocess code

...you put me on the right track.
I think I found the issue(decafsucks/decafsucks#19). What do you think?

[jeremyevans]

That could certainly be the cause of the issue. When using fork for multiprocess code with Sequel, you should always make sure database connections are disconnected before fork. Sequel will reestablish connections as needed in the child processes.