0

I have application that receives multiple requests from external sources (invoices from point-of-sale units). It gets tens of requests per second, and some of those requests are the same (have same request body).

request data is transformed and saved to two associated tables (foreign key). if record already exists (queried by unique composite key), record is updated, otherwise record is added.

the problem is that sometimes if two requests with same body are received at the same time app throws exception that unique key already exists and it can't insert it:

 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

It's probably some kind of race condition in MySQL but can't figure it out

Whole process is wrapped in Laravel transaction. I tried setting different isolation levels. SERIALIZABLE resolves that issue but then I get lot of deadlock exceptions, and as I can see sometimes record is not saved at all.

Mario Tadić
  • 13
  • 1
  • 4

1 Answers1

0

This is simple, what happen here, you have some value declared as "UNIQUE" or "Primary Key" or something like that, and you are trying to insert again, some key restriction it's blocking the insert, this should be good, you avoid duplicate entries in your database, but what you need to do its check if the data what you are trying to insert exist before in the database, not all the columns, you should ask for your keys or key combination, I can not help you more if I don't know the data or the table...

aasanchez
  • 179
  • 1
  • 16
  • Good answer except for this part "but what you need to do its check if the data what you are trying to insert exist before in the database, " this is wrong. The right approach is to let the database handle it and then catch the exception that is thrown. – e4c5 May 04 '17 at 09:44
  • You are right, if its a lot of data, this could take a lot of time, going and back, checking just the and probably could be the just a few time when he have this issue, some simple solutions could be https://laracasts.com/discuss/channels/eloquent/eloquent-trycatch-on-duplicate-key-exception-and-delete – aasanchez May 04 '17 at 10:00
  • @aasanchez as I wrote in my question, if record exists it's updated, I already check if record exists. but I think some kind of race condition happens and both of same records try to be inserted at the same time, check is skipped somehow... – Mario Tadić May 04 '17 at 10:02
  • 1
    And as i mentioned my comment, checking before hand is completley wrong. The right way is to catch the exception. – e4c5 May 04 '17 at 10:06
  • but i have to check because I need to update the record if already exists – Mario Tadić May 04 '17 at 11:24