DbTimestamp cause stale update exception

Description

DbTypeStamp is unreliable in some cases, like Odbc + SqlServer 2008.
It does not round the obtained value according to declared dialect accuracy, causing stale update exception 2/3 of times when the underlying type is a DateTime with 3.333ms resolution. (Dialect instruct to round to 10ms for such case, but DbTypeStamp do not honor that.)

Environment

None

Activity

Show:
Alex Zaytsev
September 28, 2017, 1:23 AM

It seems IsDatabaseGeneratedTimestamp is incorrectly checks for TimestampType instead of DbTimestampType.

Frédéric Delaporte
September 28, 2017, 8:31 AM

Maybe it is just the comment which is misleading. Checking TimestampType instead of DbTimestampType is likely done for allowing bulk inserts (DML HQL/Linq) with the former in addition to the later (which inherits from the former).

This IsDatabaseGeneratedTimestamp current only purpose is for allowing bulk inserts on versioned entities if the dialect do not accept parameters in insert but the version can be generated DB side. It is not used for the regular pattern of persisting entities. It is not used when the dialect accepts parameters in insert (in which case it is seeded on .Net side, meaning an additional query too with DbTimestampType).

Alex Zaytsev
September 28, 2017, 9:18 AM

On Hibernate's side IsDatabaseGeneratedTimestamp is checking for DbTimestampType

Frédéric Delaporte
September 28, 2017, 9:47 AM
Edited

This is just meaning Hibernate side is likely not supporting Timestamp in DML insert when the dialect does not support parameters, while Nhibernate would support Timestamp. But it is not tested: this code executes only if SupportsParametersInInsertSelect is false, and there is currently no dialect setting that to false. Maybe this is a bit conceptually debatable to have Timestamp generated db-side in some corner cases instead of app-side, but it actually does the job with a very unlikely chance of failure. (Failure could occur with a db server ahead of time, and the app that does later an update yielding the exact same timestamp as new version, causing the optimistic locking mechanism to be unable to protect that one update from a concurrent other update.) Since all dates types are version currently, this code could even be changed for accepting any date types, allowing them to be bulk insert as version too even when the dialect does not supports parameters in insert. But all that is just about an extreme corner case not even possible without defining a custom Dialect. This IsDatabaseGeneratedTimestamp has no purpose for the general versioning implementation.

By the way, does NHibernate have any version type actually handling a db side generation without additional queries? Maybe I have missed them, but I do not believe it has some. The generated="always" on version case is handled by re-loading the entity after the insert/update, so it is not any better than what does DbTimestamp. Rather than removing this type, we should probably just document the averse effect any db-side generated property, including version, has on performances. Well, it is already somewhat implicit within the doc, since it explains how NHibernate handles it.

Those additional queries could be avoided only if some way to use features like OUTPUT clause were implemented. But since this seems a SQL-Server specific feature, we will probably not see something to be ported from Hibernate on that subject.

Alex Zaytsev
September 28, 2017, 10:22 AM
Edited

Those additional queries could be avoided only if some way to use features like OUTPUT clause were implemented. But since this seems a SQL-Server specific feature, we will probably not see something to be ported from Hibernate on that subject.

PL/SQL (Oracle, Postgres, etc?) do support RETURNING statement with the similar semantic to the OUTPUT, so, it is possible to implement in DB-agnostic way.

Assignee

Frédéric Delaporte

Reporter

Frédéric Delaporte

Labels

None

Components

Fix versions

Affects versions

Priority

Minor
Configure