Question 21452 in Mysql Question posted in
The official MySQL documentation can be found here.

Parallel save giving a duplicate key error in Spring R2DBC – Mysql

I am getting a duplicate key error from the database (MySQL) for the scenario I have explained below.

Background

I need to save an entity to my database, but the entity id is not managed/generated by my system, so it comes from 3rd party with an id and I need to save it with its id. When our application does not manage its id, Spring Data(R2DBC) cannot know if it is a new entity or not because there will be an id on it all the time. According to their documentation, there are several ways to tell Spring if it is a new entity or not, so I have chosen to implement a Persistable entity so that I can tell Spring if it is a new entity. However, I need to query DB to understand if it exists or not. Please note that I am using Spring reactive so putting a synchronized keyword is not a solution for me…

Here is the problem

Imagine that 2 requests come almost at a time. For the second request, it will query the DB and get nothing since the first request has not been saved yet. It will decide to create the second request, but at that point, the first request is committed to DB, and as a result, the second request will get a duplicate key error since I told Spring that it is a new one.

I have been thinking of solutions to solve this problem but I couldn’t find any yet… I would really appreciate it if you could help me on this matter.

Thank you in advance!

Tags:

2

Answers


  1. 0

    You’re gonna have the same problem with pretty much any entity system. Let me explain: Let’s say we have a register endpoint. Two people register at the exact same time using the exact same email address. Then both are gonna be saved to the database since R2DBC does the same flow taking the exact same time for both requests.

    The simplest solution for this problem is not checking the email at all and just accepting that you’re not gonna deal with it with your own code. Instead you can change the database schema for your table. I don’t know how you create your table but I just use a sql file with the code for it inside. In my case by making the email column UNIQUE we can prevent this problem in the database instead of preventing it using our own logic. My schema file for the accounts table looks like this:

    create table if not exists accounts (id SERIAL NOT NULL, username VARCHAR(32), rank VARCHAR(32), email VARCHAR(32) UNIQUE, password VARCHAR(512), invitor INT, data TEXT, PRIMARY KEY (id));

    You can also check out the w3schools article about the UNIQUE constraint.

    Login or Signup to reply.
  2. 0

    I use a pre-query and hold the result with a hot publisher. You could see there has another way with "brandRepo.existsById" without a pre-query, it is also worked but slower, so I comment it. flatmap is more faster and async but in my business I need concatMap.

        Mono<List<Brand>> existedBrands = brandRepo.findAll().collectList().cache(); // hot source
    SenderRecord<String, String, String> senderRecord = SenderRecord.create(
                new ProducerRecord<>(topic, "refresh-brand-finished", dateStr(YYYY_MM_DD_UD)), dateStr(YYYY_MM_DD_UD));
    return Mono.fromCallable(commonService::parseProduct)
                .subscribeOn(Schedulers.boundedElastic()) // sync operation
                .publishOn(Schedulers.parallel())
                .flatMapMany(Flux::fromIterable)
                .concatMap(this::fetchBrandsWithWebClient) // we need one by one concat
                .filter(Brand::isNotEmptyBrand)
                .concatMap(brand -> this.setBrandNew(brand, existedBrands))
//                .flatMap(brand -> brandRepo.existsById(brand.getId())
//                        .map(b -> b ? brand.setBrandNew(false) : brand.setBrandNew(true)))
                .collectList()
                .flatMapMany(brandRepo::saveAll)
                .thenMany(kafkaSender.send(Mono.just(senderRecord)))
                .then(Mono.just("success"));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search