Handle concurrency transaction with optimistic locking
At this time, i will write about one technique for maintaining data integrity due to concurrency update on one record. It will use optimistic locking technique.
Based https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-read-modify-write-cycles/ article there are some technique to handle concurrency transaction :
- Avoiding the read-modify-write with a calculated update
- Row level locking with SELECT … FOR UPDATE
- Use of SERIALIZABLE transactions
- Optimistic concurrency control, otherwise known as optimistic locking
ORM like hibernate uses @version (version annotation) on one of entity property as marking to do optimistic locking.
@version documentation : “Specifies the version field or property of an entity class that serves as its optimistic lock value. The version is used to ensure integrity when performing the merge operation and for optimistic concurrency control.
Only a single Version property or field should be used per class; applications that use more than one Version property or field will not be portable.
The Version property should be mapped to the primary table for the entity class; applications that map the Version property to a table other than the primary table will not be portable.
The type of version property is integer, so on every changes on the record, it will increase value by one on version column. The implementation of version when updating is like this :
We use version on where condition, this style of update query will check whether the record for update is latest update from table record or not, and if record is not found (may another transaction has updated the record) then it will return error “row was updated or deleted by other transaction”.
Source:
https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-read-modify-write-cycles/
