0

We have a SPA app with big traffic and sometimes occasionally double rows are inserted in several parts of application.

For example user registration. Normally the validation mechanism will do the trick checking if email address already exists, however I was able to reproduce the problem by dispatching the same request twice using axios resulting doubled user in the database.

I initially thought that the second request should throw validation error but apparently it's too quick and checks user before the first request was able to store it.

So I put 500ms delay between those requests and yes it worked, the second request thrown a validation error.

My question is, what are the techniques to prevent double inserts IF TWO REQUESTS ARE ALREADY DISPATCHED IN THE SAME FRACTION OF A SECOND?

Of course we have blocked submit form button (since the beginning) after making first request, yet people somehow manages to dispatch requests twice.

undefinedman
  • 620
  • 1
  • 11
  • 25
  • 1
    Did you look into Doctrine's Concurrency ? https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/transactions-and-concurrency.html And did adding `UNIQUE` constraint in the table still let double inserts ? – Diabetic Nephropathy Oct 15 '20 at 18:09

1 Answers1

2

One option I've utilized in the past is database locking. I'm a bit rusty on this, but in your case:

  1. Request WRITE LOCK for the table
  2. Run SELECT on table to find user.
  3. Run INSERT on table.
  4. Release WRITE LOCK.

This post on DB locking should give you a better idea of what locks apply what affect. Note: some database systems may implement locks differently.

Edit: I should also note that there will be additional performance issues using database locks.

T Paone
  • 473
  • 2
  • 8
  • Yes, your answer and a comment of @Diabetic Nephropathy explains database related problems. However, what if the controller does not handle a database connection but some business logic? – undefinedman Oct 15 '20 at 19:28
  • 1
    @undefinedman To be fair, that's your question, how to prevent double insertions in a database. Symfony in particular has other [locking mechanisms](https://symfony.com/doc/current/components/lock.html) that don't deal with databases. You could try rearchitecting your app so your actions are idempotent. But there's no easy answer, it's going to depend on the process. – msg Oct 15 '20 at 19:55
  • The documentation @Diabetic Nephropathy linked does outline some code that could be placed in a controller. However, depending on what vendor is used for the database, and how other business logic operates, it may be necessary to have solutions performed in at multiple application layers (e.g., enforcing a UNIQUE on an email, as mentioned above). – T Paone Oct 15 '20 at 20:03