How to code optimistic and pessimistic locking from java code

Question

I know what optimistic and pessimistic locking is, but when you write a java code how do you do it? Suppose I am using Oracle with Java, do I have any methods in JDBC that will help me do that? How will I configure this thing? Any pointers will be appreciated.

Solution 1

You can implement optimistic locks in your DB table in this way (this is how optimistic locking is done in Hibernate):

1. Add integer "version" column to your table.
2. Increase the value of this column with each update of corresponding row.
3. To obtain lock, just read "version" value of the row.
4. Add "version = obtained_version" condition to where clause of your update statement. Verify number of affected rows after update. If no rows were affected - someone has already modified your entry.

Your update should look like

UPDATE mytable SET name = 'Andy', version = 3 WHERE id = 1 and version = 2

Of course, this mechanism works only if all parties follow it, contrary to DBMS-provided locks that require no special handling.

Hope this helps.

Solution 2

Suppose I am using Oracle with Java, do I have any methods in JDBC that will help me do that?

This Oracle paper should provide you with some tips on how to do this.

There are no specific JDBC methods. Rather, you achieve optimistic locking by the way that you design your SQL queries / updates and where you put the transaction boundaries.