insert on duplicate key causing locking errors about singlestore-spark-connector HOT 5 CLOSED

This can happen if you have two jobs running at the same time that both are trying to commit more than one row with the same primary key as each other. That is, if job 1 wants to write to PK 3, and job 2 wants to write to PK 3, that's fine. But if both want to write to PK 3 and PK 7, then they can potentially get in a deadlock if they do that out of order with respect to each other.

For example:

1. job 1 writes to PK 3
2. job 2 writes to PK 7
3. job 1 tries to write to PK 7 and blocks
4. job 2 tries to write to PK 3 and blocks
5. deadlock
6. one job experiences lock wait timeout

You can resolve this two ways:

1. don't do that
2. sort by primary key in your job before you call .saveToMemSQL

Doing option 2 results in a new sequence of events:

1. job 1 writes to PK 3
2. job 2 tries to write to PK 3 and blocks
3. job 1 writes to PK 7
4. job 1 finishes
5. job 2 unblocks and writes to PK 3 and PK 7

from singlestore-spark-connector.

@rick-memsql any update on this issue. Looks like a basic problem of handling upserts (Combination of Insert/Update) which Cassandra supports by default.

from singlestore-spark-connector.

@PeterFaiman

Thanks for your detailed explanation.

From the two ways that you suggested.

1. Dont do that -> Not possible, we want to update the data, and we are just running batch jobs under DStream. There is no real control for programmer here.
2. Sort by primary key -> Since there is no Direct stream APIs for the connectors. we have to go to RDD under DStream and then do this sorting. But in a streaming job this extra set of sorting will increase the time latency into processing. Sorting itself is a costly operation. If we look for sorting an RDD is a bit costly operation indeed.

from singlestore-spark-connector.

It only matters that the keys within one transaction are sorted. So if you can pull 10k keys at a time from the final stage of the job, save those, and repeat, that will work just as well. It will also dramatically reduce sorting time.

from singlestore-spark-connector.

Workaround above should work

from singlestore-spark-connector.