You are here

Sequences... Next in line, please

A few weeks back, a question came up on the NuoDB Community Forum regarding a table ID column which had been created using the “GENERATED ALWAYS AS IDENTITY” statement. The values displayed in this column had suddenly jumped from 61 to 72 and the poster was confused as to why.

To recreate this scenario, let’s say the table was created using the following syntax:

SQL>  CREATE TABLE deli_Department(

deli_ticket INTEGER GENERATED ALWAYS AS IDENTITY (deliSequence),

customer STRING,

deli_Order STRING,

purchase_Date TIMESTAMP);

The table in our example is being used by the local grocery store deli counter to track purchases made by their clients. Around noon, the deli manager decided to run a query to return that mornings transactions. Here is what a segment of the returned results might look like:

DELI_TICKET CUSTOMER DELI_ORDER PURCHASE_DATE
59 Sudhir Green Green Eggs and Ham 2013-04-01 14:22:03
60 Loretta Lime Pickled Herring 2013-04-01 14:22:59
61 Mathieu Gray Potato Salad 2013-04-01 14:23:40
72 Betty White Swiss Cheese 2013-04-01 14:30:34

Just like our poster, the Deli Manager also notices a gap in the Deli_Ticket column, where the numerical values jump from 61 to 72. However, looking at correlating timestamps in the Purchase_Date column, the manager notices that ticket 72 was rung in about 7 minutes after ticket 61. After talking with the clerk behind the counter, she finds out that one of their regular customers (Mathieu Gray) was a bit indecisive about what he wanted to order and held up the line. During that time several other customers who had come up to the deli counter and taken a ticket, grew impatient and decided that they would do some other shopping first and come back to the deli later. The next customer in line after Mathieu, was Betty, who had received ticket number 72.

In this analogy, the deli’s ticket represents a database object known as a Sequence, which was created when we created our “deli_Department” table and invoked the “GENERATED ALWAYS AS IDENTITY” statement on the “deli_ticket” column. With each new transaction, the sequence would dispense the next available number in the queue. Even if the transaction did not commit (the customer decides to leave the deli and go do some other shopping first), the sequence continues to move forward to the next number in the list, never dispensing or reusing the same number twice.

Sequences in general are nothing more that what one of our engineers, Trek Palmer, describes as “Atomic Counters” that produce a list of ordered, unique numerical values.

Similar to Schemas, Tables, Indexes and Views, Sequences are just another type of database object. In NuoDB we are able to “poke a sequence” (another Trek-ism) by SELECTING the “NEXT VALUE FOR” a given sequence. As each poke advances the sequence to the next ordered value, each new transaction sees the result of the transaction that came before it, which may or may not have yet been committed. Even if the previous transaction fails and its changes are rolled back, the sequence will not re-use the value, instead it will simply move forward and issue the next value in the list. Just as the ticket dispenser at the deli will always issue the next ticket in the roll, never dispensing the same number from a single roll, twice.  The implementation enables NuoDB to efficiently and safely handout sequences across multiple transaction engines without introducing a central bottleneck  in the system.

Here’s a quick look at how NuoDB’s implementation of Sequences, differs from another database system such as PostgreSQL:

Function

PostgresSQL

NuoDB

Create/Drop Sequence

True

True

Set initial value

True

True

Set the incremental value True

increment by 1

Read current value w/o incrementing

True (violates Atomicity and Consistency)

False

Re-use previous values

True (violates Consistency)

False

Generation of unique field value

False

True

To experiment with our implementation of Sequences, please login into the main website to download the NuoDB Cloud Database Management System and fire up our command line based NuoSQL tool.

If you have questions or need a little help getting started, you can reach us directly by either sending an email to support@nuodb.com or by posting a question to theNuoDB Community Forum.

Add new comment