Hi all

I have not used neither Hibernate and optimistic lock before and after reading the programmer's guide of the Hibernate I am very interested in using it.

However, I am not sure it and want to hear the advice from the experienced developers.

I am using MS SQL Server and I have tried using the timestamp to implement the optimistic lock and I think it works fine. However, I don't know how the other databases implement it. Because in MS SQL Server the data of the timestamp is actually binary and I have to get the InputStream to get the timestamp instead of getTimestamp and I want to know if I use Hibernate, will it ensure that the timestamp works on different database without changing the code?

Any other advices/suggestions regarding the optimistic lock is very welcome!


Thanks!


Kind regard

Exactly why did you not just use a DATETIME column?

Hello,

I think the whole issue requires some clarification.

In some recent projects (pure SQL no ORM) we were using optimistic concurrency control based on automatic timestamp handling by MSSQL and Sybase - the database type TIMESTAMP. Please do not confuse this type with java.sql.Timestamp or any other "time" type. It is in fact some binary data type that makes sure that it is changed each time the row is updated - it works automatically, thus the application must take care only of checking whether meantime the recard changed or not:
update ........ where id=1231414 and stamp=stamp_when_loaded.
On Oracle 8i the very same behavior can be achieved using triggers.

Now we decided to move to Hibernate.
As I understand, Hibernate has NO SUPPORT for such automatic (database implemented) versioning. It however provides its own realization - it can automatically increment version number of a record (or update time) and check it against value in the database during updates. It's OK, it works for all databases - platform independent.

The hibernate approach has one disadvantage though. We have heterogenous environment, there are some legacy applications that access the same tables and potentially rows as our Hibernate powered application. Obviously, these legacy applications rely on the automatic db versioning of records, and reimplementation is out of question.

If your application (hibernate powered) is the only application that manipulates the data, then do not use TIMESTAMP db type, use long type for instance and let hibernate take care of versioning and optimistic concurrency control. If your case is the same as mine (legacy applications) then if you want to use hibernate then you must to make some workarounds.

For our purpose I made some changes in Hibernate classes:
Loader
AbstractEntityPersister
TypeFactory
Now I believe it works with automatic database versioning (MSSQL TIMESTAMP type).

I just wonder if such feature should not be implemented in some future version of Hibernate, since there might be more cases like mine.

BTW, timestamp database type can be read using getObject method - it should be database vendor independent.


BR,

Marcel

Hi

Thanks a lot! It is exactly what I want to know and you perhaps described the situation much better than I did.




My case is the same as yours. Could you please tell me what changes did you make to those classes? Also do you know what database support the MSSQL TIMESTAMP type?





Thanks it is very useful, much better than reading 8 bytes from the inputStream.


Thanks again it helps lots!

Edmond

The changes require some digging into the Hibernate code.
We are at the beginning of development, thus it is still under question whether it will work well. First of all, we defined new type of versioning.
In mapping file for the object:
<version
name="version"
type="my-timestamp"
column="stamp"
/>

Now in the class net.sf.hibernate.type.TypeFactory
the new type is registered in the static initializer:
.
.
.
basics.put( "my-timestamp", com.hack.hibernate.Timestamp.TIMESTAMP);
BASIC_TYPES = Collections.unmodifiableMap(basics);
.
.
.

The key is then the com.hack.hibernate.Timestamp implementation:

public class Timestamp extends TimestampType implements VersionType,Serializable
{
public static final Timestamp TIMESTAMP = new Timestamp();

public Object next(Object current)
{
return current;
}

public Object seed()
{
return null;
}

public Object get(ResultSet rs, String name) throws SQLException {
return rs.getObject(name);
}

public Class getReturnedClass() {
return Object.class;
}

public void set(PreparedStatement st, Object value, int index) throws SQLException
{
st.setObject(index,value);
}

public int sqlType() {
return Types.OTHER;
}

public String getName() { return "trema-timestamp"; }

public String toString(Object val)
{
return val.toString();
}
}


In addition, the following line must be commented in net.sf.hibernate.persister.AbstractEntityPersister
if ( isVersioned() ) propsToUpdate[ getVersionProperty() ] = true;



Now, this solution handles optimistic concurrency automatically using database type TIMESTAMP. It has, however, disadvantages. Clearly, the object cannot be cached after update is performed (unless the new value of timestamp is fetched). Clearly, no two successive updates of the same object in the same session can happen - the second one must fail due to optimistic concurrency control.

I believe that this is the way to make it work, however it requires a lot of testing and further digging into the code. Perhaps I forgot something or made some mistake. I just tried some simple test case that satisfied me for now.

BR

I forgot to add the following hack in AbstractEntityPersister
propertyUpdateability[i] = prop.isUpdateable()&&prop!=model.getVersion();
propertyInsertability[i] = prop.isInsertable()&&prop!=model.getVersion();

instead of

propertyUpdateability[i] = prop.isUpdateable();
propertyInsertability[i] = prop.isInsertable();

I've exactly the same requirement as Marcel. And cannot move away from Sybase Timestamps because of legacy applications.

I wanted to ask, whether Marcel's solution proofed to be successful in the meantime. Are there any efforts to build this into Hibernate or provide an extension point?



Is there a way to force Hibernate to fetch the DB-generated Timestamp after an update/insert?

What about iterating over all Objects in Interceptor.postFlush() and evict them from the Session and SessionFactory. I think for the Session this requires some tricks because it's not allowed to access the current Session in the Interceptor. And what happens, if the application code keeps a reference to an evicted instance?

Any ideas are welcome.
Ernst

Hello Ernst,

bad news,

I discourage usage of the solution described by me earlier. It has been just a try, this approach has many other implications, some other things do not work. You really need to understand the Hibernate's internals to try to patch it to use SYBASE timestamps. Anyway, it is generally not a good idea to patch anything, because you're risking difficulties when upgrading to newer version of Hibernate.

Our solution was to alter the "legacy application" to use number based (and Hibernate friendly) versioning, something perhaps not applicable for you.


You may also consider using intelligent triggers to increment the version number for your legacy application, however such trigger must apply only for legacy applications, not hibernate (detection based on user name? connection properties? don't know).

Good luck anyway,

Marcel

Hi,

Has there been any further progress with respect to using Sybase timestamps for optimistic locking?

I am in the situation where it seems I will need this functionality - the DBAs where I work are refusing to allow me to use int for the version column on a new database schema because "The Sybase standard for optmistic locking is timestamp".

It would seem that in order for Hibernate to support this, it would have to be enhanced to:

1. Handle the binary datatype that Sybase uses for timestamp columns.

2. Support insert="false" and update="false" for the version property in the same way that is presently possible on normal properties (since Sybase is responsible for populating timestamp columns).

Is there any chance of seeing this in Hibernate in the near future?

I fear that if not, I will have to ditch Hibernate and handle all the database access manually.

Alternatively, if anyone can give me good arguments to use to convince our DBAs not to force us to use timestamps, I'd like to hear them :)

There are three ways how you can get new functionality into Hibernate:

- write it yourself, its open source
- post a feature request to JIRA and see if it is rejected or voted
- buy our commercial support services

Ditching Hibernate seems like the least efficient option.

An alternative to this whole timestamp/version issue would be for the alternative, record compare approach:

Hibernate reads the record, and stores it in a cache somewhere. Just before the update hibernate re-reads the record, compares it with it's cached copy, and if it is different, it knows the record has changed on the database since it was read, so it throws some sort of optimsitic locking exception.

This approach isn't the best in terms of performance, but avoids having to change tables to have timestamps or version numbers on them.

Thanks for the suggestions.

I'm going to continue trying to convince our DBAs to change the datatype - failing that, I will have a look at the code - if I think I can implement it, I'll see if I can persuade my employer to allow me to work on it.

Thinking through the cache-and-compare approach, it seems it would suffice to cache and compare only the sybase timestamp values. I worry that it might not perform well enough, though, given all the extra selects. It would also seem a shame to have to implement it in the application code when Hibernate could be managing it.

I think the support option is unlikely to happen, since our architecture group has not officially approved Hibernate for use. I am considering myself lucky that I'm being allowed to use it at all (big-company bureaucracy). I fear that this timestamp issue may actually be used by some as an excuse to try to have its use explicity forbidden :-(

Once again, thanks for your replies.

We are about 90% of the way through a major project, replacing entity beans and manual SQL queries/object population with Hibernate.

While the process hasn't gone as smoothly as I expected, performance is way faster, and our project code base is probably 55% of what it was before we started hibernating stuff.

You need to sell Hibernate not to your DBA's (they are just as customer of you, the Application Developer, in your situtation), but to your managers.

You need to tell them that Hibernate will:

Reduce your codebase significantly - newer apps will be quicker and easier to write, existing apps will require less maintenacne

Increase your performance in a major way

Applications will be more portable (due to dialects).

To add a timestamp field to each file on the database is nothing more than a single alter script, which shouldn't have a big impact (unless you are an RPG programmer on an AS/400, as this would necessitate a recompile of all programs that used the files!).

Stand proud, and fight the good cause !