How to LOCK TABLE ... do stuff ... UNLOCK TABLE with Spring Boot?

user_2739271 0
Viewed 4796

The idea is basically to extend some Repositories with custom functionality. So I got this setup, which DOES work!

@MappedSuperclass
abstract class MyBaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Int = 0

    var eid: Int = 0

}

interface MyRepository<T : MyBaseEntity> {

    @Transactional
    fun saveInsert(entity: T): Optional<T>
}

open class MyRepositoryImpl<T : MyBaseEntity> : MyRepository<T> {

    @Autowired
    private lateinit var entityManager: EntityManager

    @Transactional
    override fun saveInsert(entity: T): Optional<T> {

        // lock table
        entityManager.createNativeQuery("LOCK TABLE myTable WRITE").executeUpdate()

        // get current max EID
        val result = entityManager.createNativeQuery("SELECT MAX(eid) FROM myTable LIMIT 1").singleResult as? Int ?: 0

        // set entities EID with incremented result
        entity.eid = result + 1

        // test if table is locked. sending manually 2-3 POST requests to REST
        Thread.sleep(5000)

        // save
        entityManager.persist(entity)

        // unlock
        entityManager.createNativeQuery("UNLOCK TABLES").executeUpdate()

        return Optional.of(entity)
    }
}

How would I do this more spring-Like?

At first, I thought the @Transactional would do the LOCK and UNLOCK stuff. I tried a couple of additional parameters and @Lock. I did go through docs and some tutorials but the abstract technical English is often not easy to understand. At the end, I did not get a working solution so I manually added the table-locking, which works fine. Still would prefer a more spring-like way to do it.

spring-data-jpa spring-boot jpa spring java
1 Answers

1) There might be a problem with your current design as well. The persist does not instantly INSERT a row in the database. That happens on transaction commit when the method returns.

So you unlock the table before the actual insert:

    // save
    entityManager.persist(entity) // -> There is no INSERT at this point.

    // unlock
    entityManager.createNativeQuery("UNLOCK TABLES").executeUpdate()

2) Going back to how to do it only with JPA without natives (it still requires a bit of a workaround as it is not supported by default):

    // lock table by loading one existing entity and setting the LockModeType
    Entity lockedEntity = entityManager.find(Entity.class, 1, LockModeType.PESSIMISTIC_WRITE);

    // get current max EID, TRY NOT TO USE NATIVE QUERY HERE

    // set entities EID with incremented result

    // save
    entityManager.persist(entity)
    entityManager.flush() // -> Force an actual INSERT

    // unlock by passing the previous entity
    entityManager.lock(lockedEntity, LockModeType.NONE)
user_22024150
Related