CODEWITHSUNDEEP
javapostgresqljooq
Mahdi
Mahdi

Reputation: 2279

jOOQ transaction does not work as expected

I have a back-end API that does a simple 'getOrCreate' operation on DB.

For some reason, this API can get called twice at the same time, hence I have made a change to not create the record twice (by adding a transaction). However, every single time that the code runs, two records are created.

Two API calls share the same DSLContext instance, but each call runs on a separate thread.

Sample code:

public static Record getOrCreateData(int id, DSLContext dslContext) {

        var result = dslContext.transactionResult(config -> {
            var dsl = DSL.using(config);
            var existingData = dsl
                .selectFrom(TABLE1)
                .where(TABLE1.LINK_ID.eq(id))
                .fetchOptionalInto(RecordDto.class);

            if ( existingData.isPresent() ) {
                return existingData.get();
            }

            var record = dsl.newRecord(TABLE1);
...
            record.store();

            return record.into(RecordDto.class);
        });

        return result;
    }

I'm using Hikari CP with Postgresql database with default config.

How can I fix this?

Upvotes: 1

Views: 47

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220752

There's no reason for the transaction to fail or to prevent any duplicates if there's no formal definition of "duplicate" in your database. Two queries can read the same table and see that there isn't any record by the predicate TABLE1.LINK_ID.eq(id), and they both succeed in inserting a new record concurrently.

You cannot prevent this before trying to insert (unless you lock the entire table, or use some other mutex mechanism inside the database, or in the client), but you could add a UNIQUE constraint on LINK_ID to prevent the duplicates after trying to insert. That way, one of the two transactions will succeed with its insertion, and the other will fail with a constraint violation exception.

Upvotes: 1

Related Questions