Running into RETRY_COMMIT_DEADLINE_EXCEEDED from DELETE then INSERT

Hi all,

I am attempting to do the following operation on CockroachDB tables: delete a certain number of rows, and then insert rows in that same table from a select from another table. This seems like a simple enough usecase that shouldn’t give me trouble, however I am running into this error during the INSERT statement, after the DELETE statement gets completed:

psycopg2.errors.SerializationFailure: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_COMMIT_DEADLINE_EXCEEDED - txn timestamp pushed too much; deadline exceeded by 1m43.941914338s (1627486085.126021989,2 > 1627485981.184107651,0)): meta={id=426e0094 key=/Table/3367/3/8/-1/-1/-1/1/0 pri=0.00500749 epo=0 ts=1627486085.126021989,2 min=1627485714.941731641,0 seq=28590913} lock=true stat=PENDING rts=1627486085.126021989,2 wto=false max=1627485715.441731641,0
HINT:  See: https://www.cockroachlabs.com/docs/v20.2/transaction-retry-error-reference.html#retry_commit_deadline_exceeded

I am using psycopg2 to run those commands, and they are affecting a rather large number of rows (14 million removed and added).

I have tried looking around the documentation that’s available for advice on how to solve this. The piece of advice that looked the likeliest to help me was to proceed in batches, however I still run into that same error if I do that.

This seems like a pretty basic use-case and one that would be easy to solve, but I am rather new to CockroachDB so I am seeking some advice on what would be the best practice here.