I have a situation where I need to pick one entry from a table [table containing only 1 column id which is also a primary key], and in the same transaction I want to increment the key value and save it in the database. During this transaction, I don't want any other transaction to read the table, since unique id is required every time.
For e.g. table contains only one entry with id = 1 initially,
In transaction t1 -> select entry from table where id = 1 -> some processing -> save a new entry in the table [not incrementing id explicity since its AUTO_INCREMENT type].
Now during t1, no read should be made on this table [and I don't want to discard these read requests], read can be made only after 2nd entry with id=2 is stored in the table.
Findings: After some research I came to know about optimistic and pessimistic lock types and found pessimistic one more suitable, but still cannot relate to my situation. Please guide.
Adding code level changes that I have done to lock transaction:
public interface CodeKeyRepository extends JpaRepository<CodeKey, Integer> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value = "3000")})
CodeKey findFirstByOrderByIdDesc();
}
CodeKey is the entity for above mentioned table, containing only 1 column id.
