2

I'm creating a multitenant app where some of the tables need to have sequentially assigned integer values. The ordering is done independently for each tenant. As a concrete example, consider a Student table with a RegNumber column. RegNumber has to be assigned sequentially, but the sequence is local to each tenant.

The solution I'm thinking of involves using another table to hold the "next available" RegNumber value for each tenant, which leads me to a couple of questions:

  1. Is there a better way?
  2. What is the best way to do a "SELECT FROM tenant_studentid_sequence" and "INSERT INTO students" in a single transaction without excessive locking, and without the possibility of skipping or duplicating values?

In MySQL, I could use SELECT FOR UPDATE, but what about SQL Server 2008? There's quite a bit of discussion on this SO question, but it seems to be based on SQL Server 2005. Any changes in 2008? What's the recommended strategy?

Edit 1: I think I should clarify what I meant by "independently for each tenant". What I'm looking for is a way for each tenant to have a sequentially ordered set of student IDs. That is, tenant A will have students with IDs 1, 2, 3, ..., and so will tenant B. Think of them as business keys. I have GUIDs for global identity which is hidden from the customer.

Community
  • 1
  • 1
Ragesh
  • 2,800
  • 2
  • 25
  • 36
  • Is there a reason that you can't use an identity column for this? – JNK Sep 21 '10 at 13:12
  • I've edited the question to makes things a little clearer. An identity column would not allow me to have independent sequences for each tenant. I need tenants A and B to have instances of students with IDs 1, 2, 3, ... – Ragesh Sep 21 '10 at 13:53
  • 3
    This is the kind of requirement that needs to be pushed back. It is expensive to create a good solution for this and hard to properly maintain and utterly useless. Why do they need to have the same numbers per client? Utterly pointless 99.9% of the time. It is a waste of the clients' money to do this. – HLGEM Sep 21 '10 at 13:56
  • @HLGEM: It's not the same client; the app is multitenant. Each tenant needs to have a clean sequence of student IDs. – Ragesh Sep 21 '10 at 14:03
  • @Ragesh - so you have one table with multiple tenants, and you need a sequential ID for each? Why don't you just do a table for each tenant, and then access them in a view? That way you can use identity and not have to jump through all these hoops for no reason. – JNK Sep 21 '10 at 21:03
  • @JNK: Interesting idea. What you're suggesting is that I partition the data by tenant. That's definitely clean way to do it, but I'll have to see how feasible it would be in my scenario as I'm using NHibernate for all data access. – Ragesh Sep 22 '10 at 05:39
  • @Ragesh - you would have more expandability that way as well, since there would be minimal work involved in adding a new tenant or client. – JNK Sep 22 '10 at 10:10
  • 1
    WE have multi tenant database and use identities, the number shoudl be meaningless, it shouldn't matter if one client has id 1100 and another has 1101. Truly this is a bad requirement that is alot of work for no gain whatsoever. – HLGEM Sep 22 '10 at 13:45

2 Answers2

1

Are you trying to guarantee that RegNumber is unique across all tenants? If so, here's what I've done in similar situations. Use the auto-incrementing IDENTITY property, use the seed to identify each tenant in the ones digit and then increment by 10.

CREATE TABLE Student (
   RegNumber INT IDENTITY(1,10),
   ...
)

Tenant 1

IDENTITY(1,10) - generates IDs: 1, 11, 21, 31, ...

Tenant 2

IDENTITY(2,10) - generates IDs: 2, 12, 22, 32, ...

Tenant 3

IDENTITY(3,10) - generates IDs: 3, 13, 23, 33, ...

etc.

This will work up to 10 tenants. If you need to expand beyond that, simply extend this concept by incrementing by 100 (or 1000 or 10000...) instead as needed.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • No. In fact, I need the exact opposite. RegNumber should be repeatable across tenants. I've just updated the question to clarify this. – Ragesh Sep 21 '10 at 13:54
  • In that case, why not just use IDENTITY(1,1) and be done with it. – Joe Stefanelli Sep 21 '10 at 13:55
  • I can't use IDENTITY because the app is multitenant. The `student` table will be holding student records for different tenants. Won't IDENTITY just keep incrementing regardless of which tenant is inserting the record? – Ragesh Sep 21 '10 at 14:05
  • I understand now (I think). I was originally thinking of an isolated [mutlitenant architecture](http://msdn.microsoft.com/en-us/library/aa479086.aspx) with separate DBs. I now understand you're using a shared model. – Joe Stefanelli Sep 21 '10 at 14:13
0

If the number of IDs you need to generate is small (a few hundred), you can just use SELECT MAX(student_id)+1 FROM Student WHERE tenant_id = :tenant. If you add an index with the student and tenant IDs, the optimizer should use that instead of the main table and this will help keep locking to a minimum.

If you're talking thousands of IDs, then you might want to consider making an "ID server" that hands out ID values. At startup it would initialize itself with the max values for each tenant, then just return the next value in the sequence when you asked it for an ID. You'd want to have some way to make it reset itself (by re-reading the database) in case you wind up "wasting" an ID due to a transaction abort or something.

TMN
  • 3,060
  • 21
  • 23