0

Will the following case work correctly? I open a transaction on the backend, do an update, check how many rows have been changed, and if no rows have been changed, I do an insert, then commit the transaction. I can't use unique index and "insert ... on conflict do update" due to business logic. There can be several requests with the same parameters at the same time. Wouldn't there be multiple inserts in this case?

transaction isolation level - read committed

I expect only one insert will always be done and all updates will be done correctly.

Alena
  • 1
  • What happend when you tried? – Frank Heikens Jul 16 '23 at 14:41
  • when I send 1000 simultaneous requests, several rows appear in the database instead of 1 – Alena Jul 16 '23 at 17:11
  • That's how it should work, you don't have a unique index and you don't lock the table either. Your datamodel and your application allow multiple records with the same data. – Frank Heikens Jul 17 '23 at 06:05
  • that is, if two such transactions are started at the same time, one of them will not wait for the other to complete? In this case, there are no locks at all? – Alena Jul 17 '23 at 07:27
  • and if done through the "insert ... on conflict update", then the lock will be? what is the difference between these cases? – Alena Jul 17 '23 at 07:29
  • For the INSERT ... ON CONFLICT you need a constraint. And a constraint does have access to uncommitted data, it will wait until the first statement commits. No matter what you do, you need a constraint or you have to lock the table to avoid concurrency. – Frank Heikens Jul 17 '23 at 07:52
  • I see, thank you!! – Alena Jul 17 '23 at 08:58

1 Answers1

0

It should function properly. Each statement within the transaction will only see committed data up to the beginning of the statement if the isolation level is set to read committed.

To avoid multiple updates or duplicate insertion, concurrent requests with the identical parameters will each individually execute the update and check for affected rows. As each request runs within an isolated view of the data, it is ensured that just one insert is carried out even if no rows were altered during that particular request. To accomplish the desired behaviour, it is crucial to maintain the read committed isolation level and the transaction boundary throughout the operation.

Muhammad Sarmad
  • 118
  • 4