multiple applications using the same sequence

Hi,

I've stumbled upon the following situation:

because multiple applications insert into the same table, and use the same sequence (which is per table as this is an oracle db), i get a unique constraint exception because someone else has already inserted with that sequence number.

ORA-00001: unique constraint (SWBAPPS.MBRCHANGEITEM$PK) violated
ORA-06512: at line 3

I assume this happens because lightspeed retrieves the sequence number instead of using it to insert directly. I want lightspeed to do this:

insert into table_name (Id_column, ...) values (id_col_sequence.nextval, ....)

Doesen't lightspeed do this type of insert ?

If not, how can I make it do this, or what other options do I have (i could create a stored procedure and do the insert like that from there, but this is not an ellegant solution).

Any help appreciated.

Thanks

LightSpeed retrieves blocks of IDs from the sequence, and does so transactionally, so there should be no duplication.  Duplicates usually mean that the LightSpeedContext.IdentityBlockSize and the sequence INCREMENT BY are out of sync.  These two values MUST be the same.  The default for IdentityBlockSize is 10, so if you are not overriding IdentityBlockSize then the INCREMENT BY must also be 10.

LightSpeed does not pass id_col_sequence.nextval in the INSERT because it needs to know the ID in order to wire up foreign keys -- hence the strategy of reserving a block of IDs from the sequence and dispensing them as entities are added to the unit of work.

 My sequence in oracle has the following parameters :

Min value:1 Max value:999999999999

Increment by: 1   Cache size:20

My code looks like this:

            INamingStrategy old = swbappsContext.NamingStrategy;
            MyNamingStrategy strategiaMea = new MyNamingStrategy();

            swbappsContext.NamingStrategy = strategiaMea;
            swbappsContext.IdentityMethod = IdentityMethod.MultiSequence;
            swbappsContext.IdentityBlockSize = 1;
           

Should I set IdentityBlockSize to 20 ?

I am running this pl/sql script in a test environment to replicate the issue and simulate "some other application" inserting into the same tables.

declare
lv_changeid  number;
lvSubrRec    swbapps.mbr_subscribers%rowtype;
begin 

  select * into lvSubrRec from mbr_subscribers where swmsisdn='0740263544';
 
  for i in 1..100000 loop

  if MOD(i,20)=0 then --inserting 20 rows every second
    dbms_lock.sleep(1);
    commit;
  end if;


   
    SELECT swbapps.mbr_change_sq.NEXTVAL INTO lv_changeid FROM dual;
    INSERT INTO swbapps.mbr_change
      (swchangeid,
       swcontactid,
       swstatus,
       swpriority,
       swsubject,
       swcreatedby,
       swdatecreated,
       swdaterequested,
       swinuse,
       swexecutionpriority,
       swsubrid,
       swsubmittedby,
       swdatesubmitted)
    VALUES
      (lv_changeid,
       lvSubrRec.Swcontactid,
       'In Process',
       'Medium',
       'Subscribe',
       'ABS_Selfcare',
       SYSDATE,
       SYSDATE,
       0,
       1,
       lvSubrRec.Swsubrid,
       565453,
       SYSDATE);

    INSERT INTO swbapps.mbr_change_item
      (swchangeitemid,
       swchangeid,
       swcontactid,
       swtcjupsubrid,
       swchangefor,
       swchangeitemdefid,
       swchangeitemtype,
       swchangeitemsubtype,
       swstatus,
       swcreatedby,
       swdatecreated,
       swvalid,
       swsequenceno,
       SWPARAMETER06)

    VALUES
      (swbapps.MBR_CHANGE_ITEM_SQ.NEXTVAL,
       lv_changeid,
       lvSubrRec.Swcontactid,
       lvSubrRec.Swsubrid,
       '0740263544',
       1790,
       '....',
       '.......',
       'Submitted',
       'ABS_Selfcare',
       SYSDATE,
       1,
       1,
       'something');
   -- COMMIT;
    end loop;

exception when others then
 
dbms_output.put_line(sqlerrm);

end;

I have tried to change my IdentityBlockSize to 10, 20 and back to 1. None works.

            MyNamingStrategy strategiaMea = new MyNamingStrategy();

            swbappsContext.NamingStrategy = strategiaMea;
            swbappsContext.IdentityMethod = IdentityMethod.MultiSequence;
            swbappsContext.IdentityBlockSize = 1;
           

And MyNamingStrategy:

   public string GetMultiSequenceName(string defaultName, Type entityType)
        {
           
            if (entityType.Name=="MbrChange")
            {
                var x = "MBR_CHANGE_SQ";
                return x;
            }
            else if (entityType.Name == "MbrChangeItem")
            {
                var x = "MBR_CHANGE_ITEM_SQ";
                return x;
            }
            else return defaultName;
           
        }

Ah... I think the issue is that LightSpeed is allocating 'behind the sequence' whereas your other application is always allocating the nextval of the sequence.  That is, if the sequence's current value is 2000, then LightSpeed will allocate 2000 (and advance the sequence to 2001), whereas your other application would allocate 2001 (and advance the sequence to 2001).  When this happens in the reverse order, that's when things go awry: with the sequence at 2000, your app advances the sequence to 2001 and assigns 2001 to an ID; then LightSpeed, with the sequence at 2001, allocates 2001 as the ID and advances the sequence to 2001.  Now 2001 has been assigned as an ID twice and you get the error you're running into.

Unfortunately, I don't think there's an easy way around this.  The LightSpeed behaviour is well established and we can't readily change it.  The two options I can think of are:

1. Override GeneratedId() to manually request values from the sequence in a similar way to what is described in this thread: http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=3193.

2. Create a new sequence for LightSpeed to use, and to define the min and max values so that it will never overlap with the existing sequence.  This is a bit ugly in terms of the IDs you'll see, but is much easier than overriding GeneratedId().

Ivan, i'm affraid using the current value and then incrementing is not the correct way of doing it.

From an architecture point of view, you should never rely on other applications to make the increment for you. It's my responsibility to first increment, then use, and this can never go wrong.

All of these sources (and oracle themselves) say the same:

Book:

Oracle 8i : the Complete Reference page 437, by Kevin Loney and George Koch as part of Oracle Press, Osborne/McGraw-Hill

Oracle: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm

(how to use sequence values chapter)

http://www.oradev.com/oracle_sequence.jsp

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/pseudocolumns002.htm

(how to use sequence values chapter)

Can this be corrected please ? My application works in an enterprise environment where tens if not hundreds of applications work on the same tables, and nobody uses current value and then increment, and I have never seen this anywhere else either.

Thanks,

Bogdan

Thanks for the feedback.  I can't comment on the original design rationale, but the approach you describe and cite does make sense to me, and we agree this is how we should do it going forward.

Unfortunately, just changing it outright could create problems for existing users (e.g. if machines in a Web farm are upgraded one at a time in order to avoid outages).  So what I've implemented instead is an option mechanism.  For the time being, the current behaviour will remain the default.  But you will be able to override it with the "include final value" behaviour which will be compatible with your applications.

To do this, you will need a nightly build dated 16 Sept or above.  This will be available from about 1500 GMT.  This adds a new LightSpeedContext property, IdentityMethodOptions, and a new class, SequenceIdentityMethodOptions.  You will need to set these up as follows:

context.IdentityMethodOptions = new SequenceIdentityMethodOptions
{
  BlockBound = SequenceIdentityBlockBound.IncludeNextValue
};

IdentityMethodOptions can currently be set only from code; we will look at adding configuration file support at some future time.

Please let us know if this resolves your problem.  We have not been able to test it in an environment where other applications are sharing the sequence, so please bear with us if we need to do any further tweaking.

At some future point, we will probably make the new behaviour the default (and may remove the option for the current behaviour), but we will need to lay the groundwork first so that existing customers don't run into issues when upgrading.

Thanks once again for the feedback and for helping us to improve the product!

Ivan, thank you so much for such a quick answer and update. I have good confidence in you guys because of the way you treat these kind of things.

I will try the nightly build and let you know if it's ok.

 Ivan, I have finally been able to test your addition, and I confirm it now works correctly taking sequence.nextval.

Thank you.