My goal is to have a simple, distributed synchronization primitive across multiple processes & machines. The solution must use a database (Oracle or PostgreSQL) as this is currently the only central place to store data (e.g. no Redis).
The programming language is Java. Most libraries I found, like Shedlock or Spring's org.springframework.integration.jdbc.lock.DefaultLockRepository, store the lock-rows permanently and use separate transaction for locking. This results in more complex cleanup and some risk that conflicting business transactions are running in parallel.
I would like to use a dedicated lock table, which holds only a single unique key.
Then executing insert and delete in the same transaction would block other tx with same key in case of concurrent lock and cleanup automatically afterwards:
INSERT INTO LOCKTAB (key) VALUES (:key);
DELETE FROM LOCKTAB WHERE KEY=:key;
COMMIT;
My question is: What kind of issues could arise if those locks are executed with low collision probability, but in high frequency and in long running business transactions (speaking of minutes)?