Database concurrency issues

[stevecnz]

Are there any guidelines for how database concurrency issues should be handled when developing a web application using Spring and iBatis?

The only data persistence framework I've used to date is a proprietary framework that requires each table to have a number of standard columns, including a column named "id" which contains a unique object id, and a numeric column named "update_id" which is set to 1 on insert, then incremented by one for each update. The following example illustrates the optimistic locking strategy:

1. Session A instantiates a Product object, which gets populated with values for product id 100, fetched as follows:

Product product = new Product(100);
product.fetch();

The fetch() method generates and executes the following SQL, and sets the corresponding attributes in the product object.

select id, update_id, product_name, price
from product
where id = 100;

Lets assume the value of update_id for the fetched row is 25.

2. Session A updates the price of the product and saves the changes.

product.setPrice(product.getPrice() * 1.1);
product.save();

The save() method executes the following SQL:

update product
set update_id = update_id + 1,
product_name = [value of product.getProductName()]
price = [value of product.getPrice()]
where id = 100
and update_id = 25;

Notice the condition on update_id in the above where clause. This ensures that the row will only be updated if it hasn't been changed by another session since it was fetched. If zero rows are updated, the save() method throws an exception to indicate stale data.

Is anything resemling this kind of functionality provided with Spring or iBatis? If not, how is concurrency normally handled?

Thanks in advance

Steve

[irbouho]

Steve,

iBatis allows using users' own sql queries. You can absolutely reproduce this exact optimistic locking strategy:

  <result-map name="result" class="com.compagny.Product">
    <property name="id" column="ID" columnIndex="1"/>
    <property name="updateId" column="UPDATE_ID" columnIndex="2"/>
    ...
  </result>

  <mapped-statement name="getProduct" result-map="result">
    select id, update_id, product_name, price 
    from product 
    where id = #value#
  </mapped-statement>

  <mapped-statement name="updateProduct">
    update product 
    set update_id = update_id + 1, 
         product_name = #productName# 
         price = #price# 
    where id = #id#
       and update_id = #updateId#
  </mapped-statement>

  <mapped-statement name="insertProduct">
    insert int product &#40;id, update_id, product_name, price&#41;
    values &#40;#id#, 1, #productName#, #price#&#41;
  </mapped-statement>

jpetstore (a sample that comes with Spring distribution) shows how you can use Spring/iBatis to build data driven web applications.

[Colin Sampaleanu]

If you need this strategy, you could also just use Hibernate, which supports versioning out of the box. There is in fact nothing stopping you from using HIbernate for most O/R mapping, and then still using iBatis for the somewhat easier SQL based queries it provides, for example.