Pessimistic VS Optimistic Lock

Use case as follows

1)      User A and User B try to update the same rows at same time  then one will get update and second will get overwrite .. if more than one user access the same row its called concurrency

Following Scenario for locking

1)      Pessimistic locking : We could prevent the User B to edit

2)      Optimistic locking : Throw the exception when user B tries to edit the same row at same time

 Solution for Optimistic locking in Hibernate:

Approach 1:

 You can create the version column in the DB level ,so entity has the field either its integer/date/timestamp. Advisable approach is should be integer.

How it works:

User A read the row from DB then the value of version is 1 and when user tries to update the row , while doing update it will retrieve the version no from DB and check with version no from current row , its same then it allows to update otherwise it will throw exception assume that some one already update the same row.

User  B read the same row at same time and tries to update the same row while doing check the update it will check the version no value here it will be different then User B will get the exception .

Issues for version column :

Legacy System , it’s difficult to add the column and impact the performance ..

Second Approach
Add the DIRTY or ALL and enable dynamic updates in each table level…

Limitations of this approach:

Dynamic updates are slower, because Hibernate instead of reusing the same update query all the time, will recreate the update statement for every request.

Pessimistic lock:

Pessimistic locking creates a lock in the database and enforces that no other user can edit the same data row