[YSQL] Getting conflicts on INSERT

[jeffreims]

We are having a few issues with conflicting transactions. New unique INSERTs seem to conflict with select statements and in some cases inserts seem to be having conflicts with other (unique)
insert transactions

On our development boxes we're running a single node with RF set to 1.
Currently running version:
{"build_id": "", "build_hostname": "scheduler.c.yugabyte.internal", "build_number": "16", "version_number": "2.0.0.0", "build_clean_repo": "true", "build_username": "centos", "build_timestamp": "14 Sep 2019 18:43:22 UTC", "git_hash": "0b543e8ec9f16ae989ba29873e2d1d7977551b23", "build_type": "RELEASE"}

With:
default_transaction_isolation='serializable'

In the logs below we can see that transaction 58d240af-9e0c-4968-86d1-942ec06641e9 is conflicting on bb262c57-ce8e-40e3-bf6b-cef6709c92d0 and later some other insert statements are conflicting on 58d240af-9e0c-4968-86d1-942ec06641e9.

2019-11-14 22:34:03.319 UTC [11281] ERROR:  Operation failed. Try again.: 2019-11-14 22:34:03.312 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.319 UTC [11281] ERROR:  Operation failed. Try again.: 58d240af-9e0c-4968-86d1-942ec06641e9 Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.319 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.335 UTC [11281] ERROR:  Operation failed. Try again.: 4b46dd94-3a4f-44dc-bf9a-5afb0109e603 Conflicts with higher priority transaction: 58d240af-9e0c-4968-86d1-942ec06641e9
2019-11-14 22:34:03.335 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.341 UTC [11281] ERROR:  Operation failed. Try again.: 71f919d8-8bb3-4a52-adb0-b7dced11e567 Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.341 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.350 UTC [11281] ERROR:  Operation failed. Try again.: 05d7bdfd-3cfc-4e7f-8284-ef185d657a3d Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.350 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.355 UTC [11281] ERROR:  Operation failed. Try again.: 681cfb1f-d712-4c05-b909-b938f732bd5c Conflicts with higher priority transaction: 58d240af-9e0c-4968-86d1-942ec06641e9
2019-11-14 22:34:03.355 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.319 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.335 UTC [11281] ERROR:  Operation failed. Try again.: 4b46dd94-3a4f-44dc-bf9a-5afb0109e603 Conflicts with higher priority transaction: 58d240af-9e0c-4968-86d1-942ec06641e9
2019-11-14 22:34:03.335 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.341 UTC [11281] ERROR:  Operation failed. Try again.: 71f919d8-8bb3-4a52-adb0-b7dced11e567 Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.341 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.350 UTC [11281] ERROR:  Operation failed. Try again.: 05d7bdfd-3cfc-4e7f-8284-ef185d657a3d Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.350 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.355 UTC [11281] ERROR:  Operation failed. Try again.: 681cfb1f-d712-4c05-b909-b938f732bd5c Conflicts with higher priority transaction: 58d240af-9e0c-4968-86d1-942ec06641e9
2019-11-14 22:34:03.355 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING

Some more logs:

Operation failed. Try again.: 356031bb-37bb-40bd-8972-edff8a6b5c61 Conflicts with higher priority transaction: f67eb51c-e167-4dbb-8958-ed288b67a93f
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: 135a8f4f-55bc-4072-8a0e-cc930b960c5b Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: cbdc3ed3-81b4-42f5-a65d-f95e08425235 Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: 2aab209d-8c8f-4233-bb6c-178ae0421439 Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: 192103b6-f272-4e8b-a1ad-bf8096bb01df Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: f96f98be-74c1-4f33-8130-769335f8c2dd Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: 9a0c9c53-8daf-4567-97e4-0cd51ecf06ec Conflicts with higher priority transaction: 192103b6-f272-4e8b-a1ad-bf8096bb01df
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

Postgresql conflict. Retrying.
Operation failed. Try again.: 4d52929e-c9d4-4b62-b27f-c625c229f801 Conflicts with higher priority transaction: 356031bb-37bb-40bd-8972-edff8a6b5c61
INSERT INTO versions (as_scope,as_type,as_key,as_string,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous,as_timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING

[kmuthukk]

hi @jeffreims --

Thx for filing the issue. Can you also share the table DDL (including any indexes, foreign key constraints, etc.)?

[jeffreims]

Hi @kmuthukk,

@ddorian should have them for you if I am right.

[jaki]

Given just the above information, this seems related to issue #2823. If that is the case, conflicts are expected behavior for the current serializable isolation level in Yugabyte.

[ddorian]

Just sent the schema internally.

[ndeodhar]

Wanted to share a quick update: I am able to reproduce this and see the same behavior - transaction conflicts and conflicts on the same transaction ID.
Will investigate this further tomorrow and share an update once we find the root cause.

[ndeodhar]

Hi @jeffreims, I looked into this some more and this is expected behavior. Here's why:

(1) Why are transaction conflicts expected?
From the errors, it looks like you have multiple threads that are writing the same PK value. Now, in serializable and repeatable read (snapshot) isolation mode, if multiple writers update the same row, only one writer will succeed and the others will be aborted with transaction conflict error. This is true even when your insert statement has ON CONFLICT DO NOTHING clause. This is because this clause only serves to work as an upsert and avoid UNIQUE constraint violation errors for single writer. It does not, however, protect from concurrent transaction write conflicts. I see the same behavior in vanilla postgres too if I use serializable or repeatable read isolation mode.

(2) Why does the same transaction number appear in multiple error messages?
If there are multiple, conflicting writers (transactions), then we detect conflicts for different pairs of those transactions. So, it's possible to see error messages of the type:
txn A conflicts with txn B and
txn C conflicts with txn A.
Given that such error messages have roughly the same timestamp, this is what seems to be happening in your case.

I'm closing this issue since it's expected behavior. Feel free to re-open if you have further questions.

[florishermsen]

Hi @ndeodhar. Thanks so much for your help so far. I'm the author of the script that produced the conflicts.

From the errors, it looks like you have multiple threads that are writing the same PK value.

This is not the case, which is why we are confused as to what the reason for these conflicts could be. The errors are produced by a single python script that executes all database mutations sequentially. This also means that a new attempt (by the same process) is only made after YB has communicated that the previous attempt has definitely failed.

Additionally, the PK is a UUID randomly generated before each insert so even IF multiple scripts would be running concurrently, they would not write to the same PK value.

We hope that you can shine some light on this!

[ndeodhar]

Hi @florishermsen
Thanks for the clarification. Per the error messages posted in the issue:

2019-11-14 22:34:03.319 UTC [11281] ERROR:  Operation failed. Try again.: 58d240af-9e0c-4968-86d1-942ec06641e9 Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.319 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.335 UTC [11281] ERROR:  Operation failed. Try again.: 4b46dd94-3a4f-44dc-bf9a-5afb0109e603 Conflicts with higher priority transaction: 58d240af-9e0c-4968-86d1-942ec06641e9
2019-11-14 22:34:03.335 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING
2019-11-14 22:34:03.341 UTC [11281] ERROR:  Operation failed. Try again.: 71f919d8-8bb3-4a52-adb0-b7dced11e567 Conflicts with higher priority transaction: bb262c57-ce8e-40e3-bf6b-cef6709c92d0
2019-11-14 22:34:03.341 UTC [11281] STATEMENT:  INSERT INTO versions (as_scope,as_type,as_key,as_timestamp,as_string,archived_at,flags,data,as_id,version_parent,version_current,modified_at,modified_by,modified_in,version_previous) VALUES ('company','user_account','f03be24fca3379c96ba225fb5196c3ffbdaa321d4a5be8bbbd41931c8d5eac19',1573770843138,'Pizza',1573770843258,ARRAY['audit','backup','search'],'{}','400d48dee92c3fcd64ed3e7bc91957bed6d72e2931e22b6ad33f8c98e11b09a6',NULL,'0f7a3643-b509-4c26-83a9-4dcffec3c369',1573770843291,'0f7a3643-b509-4c26-83a9-4dcffec3c369','/platform/python-company/tests/document.py','c1cee2f7-96f9-464b-aacc-70557d5bb94a') ON CONFLICT DO NOTHING

It seems like the same IDs are being inserted repeatedly, which is what led me to believe that the script is inserting duplicates.

Can you please share the script that you used? I tried a multi-threaded sample program that generates UUIDs randomly and I don't run into this issue. So, it'll be helpful to have your script so we can better debug this.

[florishermsen]

@ndeodhar You are correct that the same IDs are being inserted, but that's just because they are a retry of the same query by the same script. Note that after N retries, usually between N=1 and 10, the insert does succeed (still the exact same query).

I'll try to make a small script that replicates what we do.

[florishermsen]

@ndeodhar after upgrading to 2.0.6 we don't see these conflicts anymore (so far so good). The added support for foreign keys may have solved the issues. I think you can close this ticket for now! Thanks

[ndeodhar]

Good to hear that! Thanks for the update @florishermsen !