Concurrency question for iBatis and Hibernate (jPetStore)

[Ibexx]

Hi. Quick question.

Does the jPetStore data access code correctly handle concurrency correctly? For example when retrieving a new Sequence number used in the Orders, what happens if a one thread calls getNextID() and gets 1000 as the next ID. Meanwhile before that thread continues with the call to update the sequence another thread calls getNextId and also receives 1000 as it’s new ID. This is going to be a problem. How does Spring-iBatis handle this? I’ve always handled this with sprocs and database auto-increment features before. Can iBatis and hibernate handle this in a way that I’m not understanding?

Thanks,
IBexx

------------------ Code frags -------------------------------

public class SqlMapSequenceDao extends SqlMapClientDaoSupport {

  /**
   * This is a generic sequence ID generator that is based on a database
   * table called 'SEQUENCE', which contains two columns (NAME, NEXTID).
   * This approach should work with any database.
   * @param name the name of the sequence
   * @return the next ID
   */
  public int getNextId(String name) throws DataAccessException {
    Sequence sequence = new Sequence(name, -1);
    sequence = (Sequence) getSqlMapClientTemplate().queryForObject("getSequence", sequence);
    if (sequence == null) {
      throw new DataRetrievalFailureException("Error: A null sequence was returned from the database (could not get next " +
      			name + " sequence).");
    }
    Object parameterObject = new Sequence(name, sequence.getNextId() + 1);
    getSqlMapClientTemplate().update("updateSequence", parameterObject, 1);
    return sequence.getNextId();
  }
}


<sqlMap namespace="Sequence">

  <resultMap id="result" class="org.springframework.samples.jpetstore.dao.ibatis.Sequence">
    <result property="name" column="name" columnIndex="1"/>
    <result property="nextId" column="nextid" columnIndex="2"/>
  </resultMap>

  <select id="oracleSequence" resultMap="result">
    select '$name$' as name, $name$.nextval as nextid from dual
  </select>

  <select id="getSequence" resultMap="result">
    select name, nextid from sequence where name = #name#
  </select>

  <update id="updateSequence">
    update sequence set nextid = #nextId# where name = #name#
  </update>

</sqlMap>

[steven.warren]

It doesn't look safe to me either. Normally speaking, I update the value first and then in the same transaction read the updated value and subract. Updating the record will lock it to prevent concurrent retrieval of the same number more then once.

Cheers
Steve

[Ibexx]

Can anyone point me to other references/samples/discussions along the lines of this topic? I understand the benefits of using database-generic designs for portability but it seems that to break this simple logic into two function calls (getSequence and updateSequence) just can't be enterprise quality design. On a current project I'm working on we simply delegate the generation of a unique ID to the database.
Thanks, Ibexx

[croco]

Hi,
Actually the code is thread safe because it uses database sequeces which are thread safe (do not run in transactions).
Whenever you do this:

select '$name$' as name, $name$.nextval as nextid from dual

the SQL will return the next number in the sequence, no matter the transaction.

Regards, Mircea.

[croco]

Hi,
At a closer look you are right, I was thinking that the Oracle sequeces were used. I should have read the post more carefully
Regards, Mircea

[Ibexx]

Mircea,
Yes... in that case the database would handle the synchonization for you. But it is very clear that these two separate calls (from Java to the database) are not thread-safe... unless I'm missing something...?

Ibexx

[croco]

Yes you are right, the code is not thread safe.
My solution to this problem would be something like this:
Instead of:

<select id="getSequence" resultMap="result">
    select name, nextid from sequence where name = #name#
  </select> 
<update id="updateSequence">
    update sequence set nextid = #nextId# where name = #name#
  </update>

I would do:

<select id="getSequence" resultMap="result">
    select name, nextid from sequence where name = #name# for update
  </select>
  <update id="updateSequence">
    update sequence set nextid = &#40;nextid + #cacheSize#&#41; where name = #name#
  </update>

A sample code would look like:

public class SqlMapSequenceDao extends SqlMapClientDaoSupport {

	private int cacheSize = 100
	private int startId = 0;
	private int counter = -1;
	
	private void loadCache(){
    		Sequence sequence = new Sequence(name, -1);
    		sequence = (Sequence) getSqlMapClientTemplate().queryForObject("getSequence", sequence);
    		if (sequence == null) {
      			throw new DataRetrievalFailureException("Error: A null sequence was returned from the database (could not get next " +
               			name + " sequence).");
    		}
		startId = sequence.getNextId();
    		Object parameterObject = new Sequence(name, startId + cacheSize);
    		getSqlMapClientTemplate().update("updateSequence", parameterObject, 1);
		counter = -1;
		
	}

 	public int getNextId(String name) throws DataAccessException {
		counter++;
		if(counter > cacheSize){
			loadCache();	
			counter++;
		}
		return startId + counter;
	}

}

The main idea is to provide ids from the in memory cache. The database should be accessed only once in 100 requests.

Hope this code is ok, haven't got time to check it. But overall it should give an idea of what I mean.

Regards, Mircea

[pdezenzio]

Looking at this example, I have questions about concurrency. I got the jpetstore example up and running under eclipse. I had no issues. I added a new feature that allows you to update the data for all of the inventory. This is where concurrency becomes an issue - more than one column can be updated, so what do I need to do to prevent one user from updating the quantity of "Golden Retriever" while another is updating the same quantity? Am I going to have to do a select on the values for user X to determine if changes were made by user Y and vice-versa?

Thanks.

[pdezenzio]

My question in the last post pertains to all values of a given row, so think of all the concurrency issues that could occur. The example given in this thread only talked about the sequence id, but I've allowed updates to any of the data. If I extended the example given in a prior post to all columns, it doesn't look elegant at all and seems to violate the principle of IoC. The developer shouldn't have to worry about concurrency issues anymore than closing connections.