My system has one use case where it needs to insert into a table in a legacy SQL Server database. This table existed when the schema ran on a version of Sybase that did support identity columns. Some customers still use that version so 'just make it identity' is not a valid solution to my problem. As a result, I cannot use 'native' or 'identity' id generation. At least three other systems write to this table prohibiting use of standard 'increment'. The table does not offer any candidate keys that would allow me to use a 'select' id generator.

All current applications and the old version of my application used two-selects to calculate (Max(id) +1) and assign ids and then retried the transaction when an occasional blowup happened.

I could do this with a custom version of increment that performed a select every time. Would it be better to use 'assigned' and my own query to find MAX+1 rather than writing a custom generator?

In either case I am a little unclear on how risky it would be to implement retry logic when duplicate keys turned up (experience says this is less than 5% of the time). I'd prefer if inserts to this table could participate in a larger JTA transaction but that is looking iffy at the moment. Has anyone ever done successful retries of clear();begin();save();flush();commit();?

Has anyone dealt with similar issues, or any issues at all, with legacy tables and Max+1 id strategies?

I am running Hibernate 3.0.5 under JBoss 4.0.3 using the Session per JTA transaction pattern.