Timeline for Concurrent transactions result in race condition with unique constraint on insert
Current License: CC BY-SA 4.0
13 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Jun 3, 2022 at 14:46 | comment | added | Gili | @ErwinBrandstetter Sorry, you're right. I'll post a follow-up question there. | |
Jun 3, 2022 at 14:22 | comment | added | Erwin Brandstetter | @Gili: Isn't that what my answer here suggests? dba.stackexchange.com/a/213625/3684 | |
Jun 3, 2022 at 14:12 | comment | added | Gili |
@ErwinBrandstetter Reviewing your other post, what happens if we were to add a retry loop around INSERT ... ON CONFLICT ... DO NOTHING ... SELECT existingRow ? I assume this would protect one against inserts that are not visible yet. It would be less efficient than the single statement you proposed, but would it be safe?
|
|
Sep 21, 2018 at 17:08 | comment | added | Erwin Brandstetter | You have quoted a sentence from my related answer out of context, which applies applies to the CTE case. I have no time for a test case right now. If you won't believe me, feel free. I have provided plenty of information. | |
Sep 21, 2018 at 16:56 | comment | added | CL. | @ErwinBrandstetter This question does not have any CTE. And please provide the sequence of commands what would lead to a race condition. | |
Sep 21, 2018 at 14:27 | comment | added | Erwin Brandstetter |
Note the difference between a nested SELECT in a CTE and a separate SELECT statement. Your separate SELECT operates on a new snapshot. But there is a time frame between INSERT and SELECT that allows race conditions.
|
|
Sep 21, 2018 at 14:19 | comment | added | CL. | @ErwinBrandstetter In my test, the sentence "The SELECT sees the same snapshot from the start of the query and also cannot return the yet invisible row." appears to be wrong. [In two connections: C1: BEGIN; C2: BEGIN; C1: INSERT; C2: tries same INSERT, blocks; C1: COMMIT; C2: unblocks; C2: SELECT sees the new row.] Again, please provide an example that proves the existence of the problem. | |
Sep 21, 2018 at 13:46 | comment | added | Erwin Brandstetter | I discussed possible issues in detail here: stackoverflow.com/a/42217872/939860 | |
Sep 21, 2018 at 13:39 | comment | added | CL. | @ErwinBrandstetter I tested this. Please provide a sequence of commands that shows the problem. | |
Sep 21, 2018 at 12:59 | comment | added | Erwin Brandstetter |
While this is simple and should work in most cases, it can still find no row (though the row is there) or find a row in the SELECT , which a concurrent transaction already deleted (yet uncommitted). Under heavy concurrent load you might have to do more (loop), like the other answers illustrate.
|
|
Jul 20, 2018 at 7:29 | vote | accept | Elliot Blackburn | ||
Jul 20, 2018 at 7:27 | comment | added | Elliot Blackburn | Thanks for this, a simple solution but one we overlooked as it would potentially end up being done in many places. That said, it doesn't mean it's bad, just means we've got some work to do! | |
Jul 19, 2018 at 8:59 | history | answered | CL. | CC BY-SA 4.0 |