Spring Boot JDBCTemplate Optimistic Locking Example with MySQL and Java Multithreading

I show you how to perform optimistic locking with Java, SpringBoot and MySQL.

Pessimistic Locking VS Optimistic Locking
JDBCTemplate Optimistic Locking
NamedParameterJdbcTemplate Optimistic Locking
DataSourceTransactionManager
@Transactional annotation
I demonstrate the persistence lost update problem by creating a demo spring boot application containing a REST API and a JDBC template persistence layer.

Optimistic database locking can be achieved with many techniques, some popular methods are with a version column, or a modified integer column.

As a relational database will guarantee the ACID properties, we will ensure that each database write is performed within a transaction (Read committed transaction propagation if fine).
Each transaction will add a where clause to the sql, the where condition will ensure that the row's current value has not changed. And therefore guaranteed that the row has not been updated between the read/write. (As long as all writes follow the same transaction / code path).

Enjoy the demo!

Lost update problem and solve
Java Transaction Propagation Levels
Database connection Transaction Isolation Levels
Concurrency Control
Java
SpringBoot
SpringFramework
RESTful Webservice
Tomcat HTTP Request Thread breakpoint analysis
HTTP concurrency example
properties file MySQL Datasource JDBCTemplate autoconfiguration

Subscribe for more great tech content!

Enjoy!
Philip

Video Location: Slieve League Cliffs - Ireland, Co Donegal.

[keithscull]

Old, but still great approach:)
Just wanted to share my XP with implementing a similar approach in a microservice using CQRS on the command side..
Requirement in that scenario is that we create what's called an event store on the command side. In an event store, a record in the DB table or collection is never changed, only new records are added which can alternatively offset the pre-existing records (I.E deducting funds recently deposited)

The solution for optimistic concurrency control in this scenario was the below:
1. Instead of version column as shown in video, either create a version column with a UNIQUE index, or make primary key of table composite, consisting of both an ID # && a version #
2. When trying to save the new record you just need to catch whatever exception will be thrown by DB dependency due to unique constraint..

I was having a tough time fixing concurrency in MongoDB eventStore since it doesn't support transaction locks or serialization, but the above works !

[vazzdoin]

This was a good example. Thanks. can you please update an example how to handle StaleStateException in such cases. How to get the current version of the object/row and update the data in the exception catch itself ?

[madhurajanaradhanan]

Do we need a Version column in DB created for this purpose ? What if my table doesnt have version column

[Ajayhanand]

Where did you lock the row, Making a methof transactional does not lock the row

[hendisantika]

Nice video. Is there any github repo for all your tutorial?
Thanks

[peterwein]

Great Tutorial, reminds me of the concept of semaphores!

[shivugururaj]

great information. Is this source code available for reference? Thanks

[alexk]

thanks, ver nice, please share source :)