Block java threads around checking uniqness with SELECT followed by INSERT

So there is some table that can only have unique values. I want to read or create new value. I though this would be fairly simple, but for some reason synchronized is not working for me... Maybe it's just the hot summer day and I'm missing something simple ;)

The error thrown when two threads try to create the same signature:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique

I'm working with JDK17, Hibernate5, Spring5 (not migrating to Jakarta yet).

// update that needs to be isolated or it throws constraint violation upon creating duplicate signature
private void updateSignature(HoldingEntity holding, String signature) {
    var signatureInDb = signatureDAO.getByText(signature);

    SignatureEntity newSignature = null;
    if (signatureInDb != null) {
        newSignature = signatureInDb;
    } else {
        newSignature = new signatureEntity();
        newSignature.setText(signature);
        signatureDAO.create(newSignature);
    }

    holding.setSignature(newSignature);
}

As you can imagine updateSignature is a part of a larger service that creates holdings.

    @Transactional(rollbackFor = {Throwable.class})
    public HoldingEntity create(HoldingParams params)
            throws HoldingServiceException {
        // ...
        updateSignature(holding, signature);
        // ...
    }

Simplified import thread creation:

public void executeImport(Long jobId, String importType, ImportHelperThread importHelperThread) {
    // setup sub-threads
    ExecutorService executor = Executors.newFixedThreadPool(splitThreadCount);
    CompletionService<Void> completionService = new ExecutorCompletionService<>(executor);

    // run sub-threads
    for (int i = 0; i < splitThreadCount; i++) {
        final int threadIndex = i;
        completionService.submit(() -> {
            try {
                initImportThread(jobId);
                importHelperThread.run(threadIndex);
            } finally {
                ThreadContextHolder.clear();
            }
            return null;
        });
    }

    // stop accepting new subtasks
    executor.shutdown();
    executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
}

So I actually went with table lock there. It shouldn't be too bad for that specfic situation. I really don't know why @Transactional(isolation = Isolation.SERIALIZABLE) didn't work. I guess it might be Hibernate+Postgres problem (Postgres doesn't really have nested transactions)...

Anyway this kind of works:

// HoldingServiceImpl.java
// update that needs to be isolated or it throws constraint violation upon creating duplicate signature
private void updateSignature(HoldingEntity holding, String signature) {
    // lock just before...
    GenericDAO.lockTable(sessionFactory, SignatureEntity.TABLE);
    // ...reading data to check uniqueness
    var signatureInDb = signatureDAO.getByText(signature);

    // update or insert&update
    SignatureEntity newSignature = null;
    if (signatureInDb != null) {
        newSignature = signatureInDb;
    } else {
        newSignature = new SignatureEntity();
        newSignature.setText(signature);
        signatureDAO.create(newSignature);
    }
    holding.setSignature(newSignature);
}

// GenericDAO.java
public static void lockTable(final SessionFactory sessionFactory, final String table) {
    // Lock the entire table using a native SQL query
    String schemaPrefx = GenericDAO.getSchemaPrefx(sessionFactory);
    String sql = "LOCK TABLE %s%s IN EXCLUSIVE MODE".formatted(schemaPrefx, table);
    sessionFactory.getCurrentSession().createNativeQuery(sql).executeUpdate();
}

SELECT from other threads is still possible, so no problem in reading the data without locking out. No deadlocks even under high pressure. Hibernate/PG just waits until the end of transaction from the moment it runs the lock query. Note that it would be best to make the updateSignature transactional or at least keep it at the end of transaction (so make the lock as late as possible).