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?
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....