I'm Posting this to hopefully prevent others from pulling out their hair like I have.

I had a working app on Postgres, Oracle etc. But needed to support SQL Server 2005.

Using SQL Server 2005 with snapshot isolation mode, (see http://www.hibernate.org/382.html) I was getting some unexplained 'failed update' rollbacks, where SQL Server thought I was updating a stale row in a table. I knew I wasn't because I was doing my own in-code locking to explicitly try to avoid the issue.

In snapshot isolation, SQL Server tracks row versions from the start of your transaction to make sure you don't commit over someone else's updates.

It turned out that SQL Server was checking row versioning of the rows in tables other than the one being committed! Particularly anything referenced by a foreign key (one-to-one, many-to-one). Instead of just ensuring that the foreign key constraint wasn't violated, SQL Server decided it should make sure data in the other table didn't change!!! ARRRGGG (feelings toward certain companies omitted)

Consider this scenario:

Tables A & B
Table A references B with a foreign key constraint.

If one thread loads a row from A, modifies it, and commits.
But during this transaction, another thread commits changes to the referenced row in B. The commit of A will FAIL. SQL Server complains because B has changed even though we're only updating A!!!


WORKAROUND

By default, Hibernate was creating an update statement that set all the column values of A, including the value of the foreign key column. This is what triggered SQL Server to check B. By using the dynamic-update property on the entity class of A, only the changed values will be in the update statement and SQL Server will stop acting so stupid.

Thanks for this. Any other issues. I am in a similar situation with Postgres and Oracle. We will soon need to port to MSSQL-2005 and the heads up is helpfull.

No problem.

A few other anomalies to keep an eye out for...

datetime column has 3ms accuracy. Timestamps will get rounded to nearest 3ms value.

A unique constraint on a null column will only allow a single null value in the table.
An older version of hibernate (using schemaUpdate) generated this constraint on optional relationships. They would fail on the second (null) insert. This seems to have been fixed somewhere before v3.2.1.


During some load testing (about 1 million transactions) I got about 5 exceptions inserting into a table with a generated primary key and a BLOB (bytes) column, I haven't tracked it down further, but it feels like a microsoft jdbc driver bug:




Otherwise, things are starting to feel pretty stable in my app.