How to implement persistence lock without JPA?

Question

I am working on a Java Spring-Boot API project for which the database layer is not going to use JPA for most of the entities (due to the chosen database design, which I haven't been able to implement in JPA: [database model] and for which I have raised another question a few days ago: [question]).

I am concerned about concurrent edit of the entities, and wonder how should I go about implementing some sort of lock, to avoid concurrent changes, but without counting on JPA for that.

What are some common approaches people used to implement prior to JPA and Hibernate?

Does your database itself not handle locking? Most do. If not there's a bunch of ways to [lock](https://winterbe.com/posts/2015/04/30/java8-concurrency-tutorial-synchronized-locks-examples/) in java. — BeUndead, Feb 10 '20 at 18:52
I am using MySQL, not sure if it handles locking by default. Will check about that, thanks for pointing out. — Sir Beethoven, Feb 10 '20 at 19:04

Ken Chan · Accepted Answer · 2020-02-10T19:26:08.143

For pessimistic locking, JPA finally boils down to using the JDBC to issue select * from foo where id = 1 for update (in MySQL case) which locks the row of ID 1 . No one can select this row before the transaction that locks this row is finished ( Also see this which select for update may lock the whole table in some case). So you can make sure only one guy can change this row.

For optimistic locking , JPA finally boils down to first introducing a version number for each record. Whenever a record is selected , it will also select this version number (assume the selected version is 10) . And whenever a update is made , it will always increase this version by one and check that the version is still not changed by other by using the following AND condition :

update foo set bar = xxxx , version = version + 1 where id = 1 and version = 10;

So , if no records can be updated , that means another guy already changed this record before you update it (i.e that guy already updated the version to 11 which make your AND condition fails) . You can simply show some error messages to user and tell them to reload/ retry again etc. depending on your business requirement.

I mean you can simply simulate the above methodology using JDBC as they are just some simple SQL statements at the end....

Thanks man, it was really helpful on giving me a better understanding of the matter. Now I know what I have to do. — Sir Beethoven, Mar 05 '20 at 13:49

Alan Sereb · Answer 2 · 2020-02-12T15:40:43.863

To keep it simple, use serializable transaction locking. It is easier than version-like locking and solves most of the problems.

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- do stuff
COMMIT;

Spring Framework makes it easy for you by providing @Transactional annotation, where you can specify isolation level as well as propagation strategy. I find it extremely useful and easy to use.

EDIT: MySQL Aurora (AWS RDS) does not support serializable transaction isolation level, so use with caution.

In case if it is not sufficient, refer to @Ken Chan's answer. According to your question, your concern is concurrent edit of the entities, so this should do the trick.

How to implement persistence lock without JPA?

2 Answers2