@@identity vs. trigger in MS SQL Server

jkristian · **Posted:** Tue Sep 09, 2003 1:49 pm

A Hibernate native id generator doesn't work with a Microsoft SQL Server table that has a trigger that inserts a row into another table with an IDENTITY column. After Hibernate inserts a row, it executes 'select @@identity' to obtain the new row's identifier. This usually works. But if the insertion causes a trigger to insert a row into some other table that has an IDENTITY column, then 'select @@identity' returns the identifier of the latter row (that is the most recently generated identity), not what Hibernate expects. Havoc ensues.

This is a problem, for me. (I'm trying to apply Hibernate to a legacy database that matches this pattern.) Can you recommend a solution?

For example, here's a Hibernate mapping:

Code:

    <class name="com.docent.lms.entities.reference.ReferenceUser" table="DRUser">
        <jcs-cache usage="read-write"/>
        <id name="id" type="long" column="userId" unsaved-value="null">
            <generator class="native"/>
        </id>
        ...

And here's some Transact-SQL to construct a problematic database:

Code:

CREATE TABLE DRUser(
    userID                  int             IDENTITY(1,1),
    ...

CREATE TABLE DRUserAuditTrail(
    ID            int              IDENTITY(1,1),
    userID        int              NOT NULL,
    ...

CREATE TRIGGER TI_DRUser_Audit ON DRUser
FOR INSERT
AS
    SET NOCOUNT ON
    INSERT INTO DRUserAuditTrail (userID, fieldID, operation, lastChange, perpID, eventDate)
    SELECT INS.userID, ATF.ID, 'C', 'Y', ATU.perpID, ATU.eventDate
      FROM ...

I'm using Hibernate 2.0.2 and SQL Server 2000 8.00.760.

jkristian · **Posted:** Tue Sep 09, 2003 2:16 pm

One possible solution is to use the Transact-SQL function SCOPE_IDENTITY; that is, to make Hibernate execute 'select SCOPE_IDENTITY()' instead of 'select @@identity'. It's easy to make this change, by configuring Hibernate to use a new dialect:

Code:

public class MicrosoftSQLServerDialect extends net.sf.hibernate.dialect.SybaseDialect {

    public String getIdentitySelectString() {
        return "select SCOPE_IDENTITY()";
    }

}

(kudos to the Hibernate team, for making this so easy.)

But this doesn't have the desired effect: SCOPE_IDENTITY() returns 0, because Hibernate executed the preceding 'insert' in a different scope. To rectify this, I guess Hibernate would have to combine the 'insert' and 'select' into a single JDBC batch, using java.sql.Statement.addBatch and executeBatch. Is this practical? Would it be reasonable technique for any dialect with supportsIdentityColumns() == true?

gavin · **Posted:** Tue Sep 09, 2003 10:31 pm

AFAICT there is no way to implement this in JDBC. If you find a way, let me know.

Quote:

. To rectify this, I guess Hibernate would have to combine the 'insert' and 'select' into a single JDBC batch, using java.sql.Statement.addBatch and executeBatch. Is this practical?

No, this is not how the JDBC batch API works.

koma · **Posted:** Wed Oct 29, 2003 8:40 pm

There's a third function which does give the desired result :

Returns the last identity value generated for a specified table in any session and any scope.

Syntax
IDENT_CURRENT('table_name')

table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

IDENT_CURRENT is similar to the Microsoft

koma · **Posted:** Wed Oct 29, 2003 8:45 pm

no way to get access to the table name when the call for the identity value in the Dialect class is made, i guess ?

jkristian · **Posted:** Wed Oct 29, 2003 8:53 pm

The 'any session' part sounds bad. IDENT_CURRENT might return the same value via two sessions to two threads that insert rows into the same table concurrently (e.g. while serving two concurrent HTTP requests in a multi-threaded web server). It would be timing-dependent. The problem would occur if both threads complete their INSERT before either thread calls IDENT_CURRENT.

jkristian · **Posted:** Wed Oct 29, 2003 9:04 pm

A colleague suggested executing a single SQL statement to insert the row and get its identifier; something like:

Code:

insert into table(...) values(...) select scope_identity()

This is acceptable to MS SQL server (I was surprised to learn). But it's not trivial to make Hibernate do this (as far as I know).

micho2001 · **Posted:** Wed Oct 29, 2003 10:26 pm

I wonder since you've chosen hibernate, wouldn't it be easier to replace your triger logic in java code?
Or do you happen to have other clients inserting into this table?

jkristian · **Posted:** Thu Oct 30, 2003 3:26 pm

http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-437 would solve this problem. Please vote for it. :-)

koma · **Posted:** Sun Nov 02, 2003 2:10 pm

Correct me if I'm wrong but :

The patch only calls 'select SCOPE_IDENTITY()' after a INSERT statement, right ?
I guess this solves the problem for other processes inserting into the table and for insert triggers.
Our database has triggers on UPDATE and DELETE as well. I'm seeing the error :

net.sf.hibernate.HibernateException: Another object was associated with this id (the object with the given id was already loaded):

at net.sf.hibernate.impl.SessionImpl.doUpdate(SessionImpl.java:1348)
at net.sf.hibernate.impl.SessionImpl.update(SessionImpl.java:1247)

I've learnt that this problem is also related to the @@IDENTITY problem on SQL Server. Would it be hard to extend the fix for updates too ?

Thx in advance for any direction. guidance in this.

koen

jkristian · **Posted:** Mon Nov 03, 2003 2:36 pm

koma wrote:

The patch only calls 'select SCOPE_IDENTITY()' after a INSERT statement, right ?

Right.

koma wrote:

I'm seeing the error :

net.sf.hibernate.HibernateException: Another object was associated with this id (the object with the given id was already loaded):
at net.sf.hibernate.impl.SessionImpl.doUpdate(SessionImpl.java:1348)
at net.sf.hibernate.impl.SessionImpl.update(SessionImpl.java:1247)

I've learnt that this problem is also related to the @@IDENTITY problem on SQL Server.

Really? I don't understand why. How does Hibernate's use of @@IDENTITY lead to this exception? You can see what Hibernate is doing by setting hibernate.show_sql=true in hibernate.properties.

dmmorris · **Posted:** Mon Nov 03, 2003 7:08 pm

Are you using a JDBC 3.0 driver? I see that at least one SQL Server JDBC driver supports JDBC 3.0 and they claim their support is complete. With JDBC 3.0 you can use getGeneratedKeys() after an insert to retrieve the generated ID. Right now Hibernate does not directly support getGeneratedKeys() but there is a patch available and it seems likely that it will be applied some time soon. If you want a fresher version of the patch, I can build one that you could try.

jkristian · **Posted:** Mon Nov 03, 2003 7:19 pm

Using getGeneratedKeys() sounds like a good idea. Where can I find a patch to try?

I'm using NetDirect's JSQLConnect driver <http://www.j-netdirect.com/JSQLConnect/JSQLFeatures.html>, which allegedly supports JDBC 3.0.

dmmorris · **Posted:** Mon Nov 03, 2003 7:39 pm

Look here: http://opensource.atlassian.com/project ... key=HB-380

The patch that is out there right now is a little out of date (a week or so) but I will post attach a newer patch this evening.

dmmorris · **Posted:** Tue Nov 04, 2003 2:21 am

I updated the patch. Be careful with the Settings.java and SettingsFactory.java changes. Those two always come up as all-changed even though there were only a couple dozen lines that were actually changed. I don't have time to look into it now, but they compare against versions in the attic and replacing with current copy and reworking changes doesn't seem to help. The patch builder shouldn't care if a file is in the attic, but that is the only difference I can see.