Isolation of data in a session is the trickiest part of session management. A session may constitute of multiple requests. Transactions with appropriate isolation levels are used for data isolation in a request.
Martin Fowler in his book Patterns of Enterprise Application Architecture (P of EAA) uses two terms – system transaction and business transaction. A session can use one or more business transactions. A request is usually executed as one or more system transactions. A business transaction may require execution of multiple requests or in other words may consists of multiple system transactions. A system transaction cannot and should not span multiple requests. As the number of requests made in a session or a business transaction increase, data isolation becomes increasingly difficult to handle.
Various offline concurrency patterns such as Optimistic Offline Lock, Pessimistic Offline Lock, Coarse-Grained Lock and Implicit Lock are discussed in P of EAA. In this blog, I’ve written about a particular example and various strategies used for it to isolate a business transaction in JPA, JDBC and SQL.
Let’s see the example. To submit an order, there are two independent requests made in a session. First one fetches the product price and other submits an order. If the price changes between these two requests then whether old price or new price be applicable to the order is a business decision. If the old price have to be applied, then which session management, client-side or server-side, is used matters. If the client-side session management is used, then the price should be validated before creating the order.
Usually for such cases where we need to maintain updates we use versioning. In this case, we would use ProductId and Version as the composite primary keys for the Product table. Any change in the product price will create a new record with incremented version number. This strategy helps to refer to the correct product version (and thus its price) in OrderDetails table.
We can modify the product price by executing a SQL stored procedure from backend or by running another application which uses JDBC or JPA. We will use the default isolation level of READ_COMMITTED.
We would have used UPDATE query if we simply had to update the price. A transaction to run the UPDATE query to modify the price would have been used. By default, databases such as SQL Server or Oracle obtain a row level Exclusive (X) lock while executing UPDATE query. In JDBC, we would have run this stored procedure or disabled AUTO_COMMIT and run the update query. In JPA, we would have obtained an exclusive lock by using PESSIMISTIC_WRITE locking mode.
However, we need to maintain updates as new records in this example. Also, while creating a new record for product we need to ensure that the old record with latest version is not read while a new product record with new price is created. We will use SERIALIZABLE isolation level. When you start the transaction, execute SELECT query to fetch the product with latest version and then execute INSERT query to create product with new price. In JPA, we will use PESSIMISTIC_WRITE locking mode to fetch product record with highest version and then insert the product with modified price and updated version in the same transaction.
If the product price is modified between requests and we need to use the latest price, then we can notify the user about the price change. Usually, such a scenario rarely occurs. The product price needs to be fetched again while creating an order to identify price change.
If we don’t want the product price to be modified when the product is being used in a session then we can use OPTIMISTIC_FORCE_INCREMENT locking in JPA while fetching the product. While using JDBC or stored procedure, you’ll have to use update lock on the SELECT query which fetches product. However, its not a good idea to obtain update lock between requests in a session. Alternative strategies such as maintaining session count in database or Implicit Lock pattern (Offline Concurrency Patterns – P of EAA) can be used.
