0

I have the following situation in sql server. I have two tables (companies and orders). Within orders there is the companyid. I need a complex identity or sequence, to identify the next order number relating to the company.

Ex: Companies

ID - Name
1 - Microsoft
2 - Google

Orders

ID - Description - Number - CompanyID
1  - Name        - 1      - 1
2  - Name        - 2      - 1
3  - Name        - 1      - 2
4  - Name        - 2      - 2

The problem is the number. It needs to be an incremental according to the company. What is the best solution?

I ask this, because I'm running the insertion of several requests in parallel and I'm having a deadlock in sql server. I request the last Number using Max of Orders, but how is parallel, iam having a deadlock. What would be the best solution?

Thanks.

SqlZim
  • 37,248
  • 6
  • 41
  • 59
Artur Bianchi
  • 1
  • use Updlock and Tablock together that will give you exclusive lock. that will prevent the dead lock . refer this to https://msdn.microsoft.com/en-us/library/ms187373.aspx – Sankara Mar 23 '17 at 19:16
  • 1
    what happens when an order is deleted? – SqlZim Mar 23 '17 at 19:24
  • I found a similar problem here http://stackoverflow.com/questions/8956044/avoiding-concurrency-problems-with-max1-integer-in-sql-server-2008-making-ow – Artur Bianchi Mar 24 '17 at 13:45

1 Answers1

0

I believe that several SEQUENCE objects could help you.

"Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function."

But there is a caveat: "The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table." This means that you cannot guarantee the consecutiveness of the values - and to be honest I don't think that you can ever guarantee such a feature, not without using complex logic.

Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14
  • I think I cant have a complex sequence. Using sequence I would have to create ONE sequence by company. In my scenario I can have company daughters. – Artur Bianchi Mar 23 '17 at 18:58
  • Yes, you would need one sequence per company. And like I said, still you cannot guarantee that there will be no "holes" (at least without RESTARTing the sequence). I don't think there is an easy solution, though. – Giorgos Altanis Mar 23 '17 at 19:16
  • Thanks man. I found a similar problem here http://stackoverflow.com/questions/8956044/avoiding-concurrency-problems-with-max1-integer-in-sql-server-2008-making-ow – Artur Bianchi Mar 24 '17 at 13:46