1

I've tried all sorts of things but so far I've been unable to find a solution to this...

We have an Orders table with an OrderID field that is currently an IDENTITY value. This has been causing problems for my customer because although the IDs are mostly sequential, there are occasionally big jumps of 1000 IDs. I researched this and discovered that it's a "feature" of SQL, and there isn't much I can do about it on Azure.

I want to therefore assign the OrderId values myself, but this is easier said than done. Orders come in from a number of sources, including a multi-threaded importer, so I have concurrency issues to deal with.

I have tried this stored procedure that uses a transaction and a table lock, but when I call SaveChanges() (in Entity Framework), I still get foreign key violations.

CREATE PROCEDURE orders.InsertOrder(
    ...
)
AS
BEGIN
    DECLARE @OrderId INT

    BEGIN TRANSACTION

        SELECT @OrderId = MAX(OrderId) + 1
        FROM orders.Orders
        WITH (TABLOCK, HOLDLOCK)

        INSERT INTO [orders].[Orders]
           ([OrderId],
            ...
           )
     VALUES
       (@OrderId,
        ...
        );
    COMMIT TRANSACTION
END

I'm lost as to what to try next...

Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
Kayleigh Swift
  • 25
  • 4
  • 1
    And why a broken sequence is a problem? – Rubens Farias May 25 '15 at 11:41
  • It is amazing to me that the SQL team does not fix this problem. It is known since 2012 and customers are complaining. Small, "nice" ID values are often useful. – usr May 25 '15 at 11:47
  • 1
    Basically, because the client says it is. I did try to explain the reasons things are the way they are, but he still wants this "fixed". – Kayleigh Swift May 25 '15 at 11:47
  • These jumps are usually a "problem" because the ID is (wrongly IMO) surfaced in something like and Invoice ID or similar which should not have massive jumps in numbers. – Simon W May 25 '15 at 11:49
  • possible duplicate of [Windows Azure SQL Database - Identity Auto increment column skips values](http://stackoverflow.com/questions/17012339/windows-azure-sql-database-identity-auto-increment-column-skips-values) – Simon W May 25 '15 at 11:57
  • You can have two IDs in your order table, one is auto generated, and you manage the second using simple query like update oid=(select max(oid) +1..) ... – Tim3880 May 25 '15 at 13:29

1 Answers1

1

foreign key violations

I hope you mean "primary key" because foreign key violations have nothing to do with this problem...

TABLOCK is a shareable read lock. Use TABLOCKX for a quick fix.

I think you should be using UPDLOCK, ROWLOCK, HOLDLOCK which is a well-known standard lock hint sequence.

Note, that you don't need sprocs for this at all. You simply need to execute the statement

    SELECT MAX(OrderId)
    FROM orders.Orders
    WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

and discard the result. Then, the proper locks are taken and you can use normal EF functionality to continue.

usr
  • 168,620
  • 35
  • 240
  • 369