Simulating Auto Increment in VoltDB

Auto incrementing fields are quite useful, particularly for allocating values to primary keys. MySQL has AUTO_INCREMENT and PostgreSQL has a SERIAL data type. VoltDB has neither, nor anything remotely close to them. This brief article will show you how to simulate auto-incrementing fields in VoltDB. It assumes some knowledge of VoltDB.

VoltDB implements a subset of ANSI-standard SQL. It supports the basic CRUD operations (INSERT, SELECT, UPDATE, DELETE) but it does not have support for automatically generating unique identifiers. It is possible, however, to simulate these in VoltDB, as per this entry in the FAQ. What we can do is create a table that stores the name of the table and the current value that can be used as the unique value for, say, a given column. The schema for the table is shown below:

CREATE TABLE IDENTIFIER (
   TABLE_NAME VARCHAR(100) NOT NULL,
   CURRENT_VALUE INTEGER DEFAULT 1 NOT NULL,
   PRIMARY KEY (TABLE_NAME)
);
The next step is to create a stored procedure that, when called, will return the current value for a given table. The stored procedure will read the current value, increment it, and then return the value to the client.

The stored procedure looks like this:

import org.voltdb.*;

 

@ProcInfo (
  partitionInfo = "IDENTIFIER.TABLE_NAME: 0",
  singlePartition = true
)
public class GenerateUniqueIdentifier extends VoltProcedure {

  public final SQLStmt select = new SQLStmt(
    "SELECT CURRENT_VALUE FROM IDENTIFIER WHERE TABLE_NAME = ?"
  );

  public final SQLStmt update = new SQLStmt(
    "UPDATE IDENTIFIER SET CURRENT_VALUE = CURRENT_VALUE + 1 " +
    "WHERE TABLE_NAME = ?"
  );

  public VoltTable[] run(String tableName)
    throws VoltAbortException {

      voltQueueSQL(select, tableName);
      VoltTable[] idResult = voltExecuteSQL();

      voltQueueSQL(update, tableName);
      voltExecuteSQL(true);

      return idResult; // Return the current value for the table
  }
}

As this procedure demonstrates, it is possible to execute multiple SQL statements from inside a stored procedure. It also highlights the advantage of having single-threaded partitions. The table is partitioned on the table name therefore the current value for a given table (each row) is stored in a single partition. As each partition is single-threaded, and stored procedures are run sequentially, there is no risk of the current value for a given table being altered between the time the procedure reads the current value and updates it; this would not be the case in a multi-threaded environment where some kind of locking would have to be used.

With the stored procedure in place, it can be called at will. The returned values can then be used in subsequent calls to other stored procedures. And that is how you can simulate auto-increment in VoltDB. At some point I will upload a complete working solution. Checkout the autoincrement branch of my example VoltDB project to play around with some working code that implements the above. The End.

  • Tim Callaghan

    Another option, if you can get by with just two unique values per stored procedure invocation, is to use the positive and negative values of getTransactionId() from within the stored procedure.  I wrote up this in the following post, http://community.voltdb.com/node/17 

    -Tim

  • Tom Morris

    Hi Simon,
    Have you tried using VoltDB as the underlying RDBMS for Alfresco?
    Tom

  • http://www.simonbuckle.com/ Simon Buckle

    Hi Tom,

    No, I haven’t tried using VoltDB with Alfresco. VoltDB only supports a subset of SQL so would be interesting to see if it would be possible to port Alfresco’s database schema to it. Is this something you are considering doing?

    Simon

  • Tom Morris

     Yes. I’m very keen to try it out, I just need to allocate the time.

    I suppose my first task will be (as you say) to check the SQL statements managed by the myBatis ORM tier, and ensure compatibility with this: http://community.voltdb.com/docs/UsingVoltDB/AppxSQL

  • http://www.simonbuckle.com/ Simon Buckle

    Hi Tom,

    Just thought I would mention that I recently wrote an introduction to VoltDB that has just been published on IBM’s developerWorks site:

    http://www.ibm.com/developerworks/java/library/os-voltdb/index.html 

    Simon

  • Ryan Betts

    If you need unique but not necessarily auto-increment, you might also consider the builtin as mentioned by Tim.  Note that the unique id generated by this example will not be globally unique — it will only be partition-wise unique — which is very different. 

  • Priidu

    How come will the ID generated by the example above be unique only partition-wise? I can’t see it for some reason…