Auto-increment on Azure Table Storage

22
votes

I am currently developing an application for Azure Table Storage. In that application I have table which will have relatively few inserts (a couple of thousand/day) and the primary key of these entities will be used in another table, which will have billions of rows.

Therefore I am looking for a way to use an auto-incremented integer, instead of GUID, as primary key in the small table (since it will save lots of storage and scalability of the inserts is not really an issue).

There've been some discussions on the topic, e.g. on http://social.msdn.microsoft.com/Forums/en/windowsazure/thread/6b7d1ece-301b-44f1-85ab-eeb274349797.

However, since concurrency problems can be really hard to debug and spot, I am a bit uncomfortable with implementing this on own. My question is therefore if there is a well tested impelemntation of this?

5
concurrencyprimary-keyazure-storageauto-incrementidentity-column
If your really worried on it, have a table in SQL azure generating the identity values? – Andrew
That's a pretty good suggestion. However that would force me to setup my SQL-server, because Azure SQL Data Services doesn't support identity-columns: shanmcarthur.net/cloud-services/… . – Yrlec

5 Answers

28
votes

For everyone who will find it in search, there is a better solution. Minimal time for table lock is 15 seconds - that's awful. Do not use it if you want to create a truly scalable solution. Use Etag!

Create one entity in table for ID (you can even name it as ID or whatever).

1) Read it.

2) Increment.

3) InsertOrUpdate WITH ETag specified (from the read query).

if last operation (InsertOrUpdate) succeeds, then you have a new, unique, auto-incremented ID. If it fails (exception with HttpStatusCode == 412), it means that some other client changed it. So, repeat again 1,2 and 3. The usual time for Read+InsertOrUpdate is less than 200ms. My test utility with source on github.

4
votes

I haven't implemented this yet but am working on it ...

You could seed a queue with your next ids to use, then just pick them off the queue when you need them.

You need to keep a table to contain the value of the biggest number added to the queue. If you know you won't be using a ton of the integers, you could have a worker every so often wake up and make sure the queue still has integers in it. You could also have a used int queue the worker could check to keep an eye on usage.

You could also hook that worker up so if the queue was empty when your code needed an id (by chance) it could interupt the worker's nap to create more keys asap.

If that call failed you would need a way to (tell the worker you are going to do the work for them (lock), then do the workers work of getting the next id and unlock)

  1. lock
  2. get the last key created from the table
  3. increment and save
  4. unlock

then use the new value.

4
votes

The solution I found that prevents duplicate ids and lets you autoincrement it is to

  1. lock (lease) a blob and let that act as a logical gate.

  2. Then read the value.

  3. Write the incremented value

  4. Release the lease

  5. Use the value in your app/table

Then if your worker role were to crash during that process, then you would only have a missing ID in your store. IMHO that is better than duplicates.

Here is a code sample and more information on this approach from Steve Marx

3
votes

If you really need to avoid guids, have you considered using something based on date/time and then leveraging partition keys to minimize the concurrency risk.

Your partition key could be by user, year, month, day, hour, etc and the row key could be the rest of the datetime at a small enough timespan to control concurrency.

Of course you have to ask yourself, at the price of date in Azure, if avoiding a Guid is really worth all of this extra effort (assuming a Guid will just work).