One of the optimistic locking features that Hibernate provides is automatic versioning. Versioned objects need to contain either a version or timestamp property that Hibernate uses to detect concurrent modifications at flush time. Hibernate’s versioning mechanism works by checking the number of rows that were affected by an update statement. Consider an accounts table like the one below.
mysql> describe accounts;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| balance | int(11) | NO | | NULL | |
| version | int(11) | NO | | NULL | |
+---------+---------+------+-----+---------+-------+For the sake of our example, let’s assume that someone has just opened an account. Their balance is currently $0, so their row in the accounts table would look something like this.
mysql> select * from accounts;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 0 | 1 |
+----+---------+---------+If our hypothetical user were to make a deposit of $100, Hibernate would increment the account object’s version property, and eventually execute the following update. Pay close attention to the values used for the version column. We’re setting the new version value to 2, but only if the row’s current value for version is 1.
mysql> update accounts
-> set balance = 100, version = 2
-> where id = 1 and version = 1;
Query OK, 1 row affected (0.00 sec)After executing the update statement, Hibernate will check the number of rows that were affected. If nobody else modified the row, then the version will still be 1, the update will modify exactly 1 row, and the transaction commits. Our account now has a balance of $100 and a version of 2.
mysql> select * from accounts;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 100 | 2 |
+----+---------+---------+What would have happened if somebody else had modified the row? Perhaps our bank offers a $5,000 sign up bonus for new accounts, and the bonus just happens to post at the exact same moment that we’re making our initial deposit. If the bonus transaction had committed after we read the account balance, but before we committed the deposit, the table would look like this.
mysql> select * from accounts;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 5000 | 2 |
+----+---------+---------+Now when our update statement is executed, the account has a balance of $5,000 and a version of 2. Because we’re updating where the version is 1, the update will “miss”. Zero rows will have been modified, and an exception is thrown.
mysql> update accounts
-> set balance = 100, version = 2
-> where id = 1 and version = 1;
Query OK, 0 rows affected (0.00 sec)This is exactly what we want to happen because if our transaction had succeeded, we would have just lost out on $5,000. At this point, our application will need to load a fresh instance of our account object and apply the deposit again.
mysql> update accounts
-> set balance = 5100, version = 3
-> where id = 1 and version = 2;
Query OK, 1 row affected (0.00 sec)This time our update succeeds, and the account has the correct balance of $5,100, with a version of 3.
mysql> select * from accounts;
+----+---------+---------+
| id | balance | version |
+----+---------+---------+
| 1 | 5100 | 3 |
+----+---------+---------+While automatic versioning solves the problem of concurrent modifications within the boundaries of a database transaction, if your business transactions span multiple server requests, automatic versioning alone isn’t enough. Hibernate has additional features that help with this situation, which I’ll go into in a future post.