Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Sequence Values in NuoDB

DZone's Guide to

Sequence Values in NuoDB

· Java Zone
Free Resource

The single app analytics solutions to take your web and mobile apps to the next level.  Try today!  Brought to you in partnership with CA Technologies

Ever wonder why your Sequence numbers in NuoDB are not necessarily assigned in sequential order?

Sequences can be used to auto-generate unique values, such as the primary key column of a table. Sequences also do not respect transactional isolation so they will not cause conflicts if multiple transactions are using the same sequence at the same time. This is why using a Sequence is better than manually maintaining an integer column of a table to assign values.

For a distributed database like NuoDB, the challenge is to ensure that every request for the NEXT VALUE of each Sequence is globally unique, without requiring a network communication every time the NEXT VALUE of a Sequence is requested.

To do this, each NuoDB Transaction Engine (TE) that makes an initial request for the NEXT VALUE of a specific Sequence will, at that time, be assigned a block of numbers to use for that specific Sequence to assign the NEXT VALUE for the ID. If multiple TEs are running in the domain, each TE will send this request to the Sequence Chairman TE for that specific sequence. This Sequence Chairman TE keeps track of what sequence numbers have been distributed across all TEs for that specific sequence by maintaining a few values for each Sequence:

  • The Sequence Quantum Value defines how many Sequence numbers to distribute to a TE when a TE requests a block of Sequence numbers. The current NuoDB default value for each sequence created is set to 100 (see note below about overriding).
  • The Sequence Floor Value maintains the starting value to use for distributing Sequence numbers. The initial NuoDB default value is set to 1, but can be changed by the user when creating the sequence or by altering the sequence. You cannot alter this value to be less than the current Sequence Ceiling Quantum Value.
  • The Sequence Ceiling Quantum Value defines the minimum value that would be used as a Sequence Floor Value if the Sequence Chairman TE is shut down or the entire database is shut down. The current NuoDB value is 1000. And the value increments by 1000.

NOTE: In a future release version of NuoDB, when each sequence is created, using the CREATE SEQUENCE command, the user will be allowed to define the default value (currently 100) of that Sequence's Quantum Value. The user will also be able to alter this value by using the ALTER SEQUENCE command.

  • A lower default value may affect performance by increasing messaging by a TE to the Sequence chairman TE for its next block of Sequence numbers.
  • A higher number may increase the amount of unused Sequence numbers. This is because once a Sequence chairman TE assigns a block of sequence numbers to a TE, the Sequence Floor Value and the Sequence Ceiling Quantum Value (if reached) for that sequence increases (it never decreases), regardless of whether those sequence numbers are used by the requesting TE. 

To illustrate this, we start by creating a Sequence and, optionally, defining the initial Sequence Floor Value. This particular Sequence will be used to auto-generate the value of the ID column defined as the primary key of a table. Each TE that executes an INSERT SQL statement will provide a NEXT VALUE for this sequence. This sequence NEXT VALUE is used to populate the auto generated ID column.

Each Sequence has its own Sequence Chairman TE. For this example, we will discuss how the Sequence Chairman TE ensures that Sequence numbers assigned for this specific Sequence, seq_sequence1, are guaranteed to be unique, but are not necessarily sequential.

CREATE SEQUENCE seq_sequence1 START WITH 1;

The TE to which you are connected to at the time you issue the CREATE SEQUENCE command will be the Sequence Chairman TE for that specific Sequence.

When any TE performs an INSERT SQL statement, it sends a message to the Sequence Chairman TE (or if that TE is the Chairman, it returns the NEXT VALUE) for a block of Sequence numbers to use for auto-generating the ID column values. Since the Sequence Quantum Value is 100, the Sequence Chairman TE will send the requesting TE the next block of 100 numbers available, starting with the Sequence Floor Value. In this case, numbers 1-100. As each INSERT SQL statement is executed on that TE, the NEXT VALUE is sequentially incremented by 1.

TE #1:  

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

1

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

2

The first TE requesting a block of sequence numbers causes the Sequence Floor Value to be reset. In this case, the new Sequence Floor Value for this Sequence is reset to 101. Any TE, including the Sequence Chairman TE, will be assigned new Sequence numbers as needed, starting with this new Sequence Floor Value, 101.

TE #2:

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

101

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

102

In this example, if TE #2 is performing inserts into a table faster than TE #1, the result will be that ID values on rows being inserted will not increment sequentially, but are all IDs are guaranteed to be unique.

Each time a TE requests Sequence numbers, a block of 100 Sequence numbers are assigned to that TE and the Sequence Floor Value is bumped up, in increments of 100, which is the default Sequence Quantum Value defined for this sequence.

This continues until the Sequence Ceiling Quantum Value (1000) is reached or the Sequence Chairman TE (or the entire database) is shut down.

TE #3:

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

901

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

902

...

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

1000

What happens when the Sequence Ceiling Quantum Value is reached?

When a TE requests a block of Sequence numbers and that block of Sequence numbers equals or exceeds that Sequence's Ceiling Quantum Value, the Sequence Chairman TE bumps up the Sequence Ceiling Quantum Value of that sequence to the next Sequence Ceiling Quantum Value. The NuoDB Sequence Ceiling Quantum Value default is 1000 and it increments in intervals of 1000. In this example, the new Sequence Ceiling Quantum Value will be incremented to 2000.

When a Sequences Ceiling Quantum Value is incremented, the Sequence Chairman TE sends a message to all TEs of this new Sequence Ceiling Quantum Value.

Sequence numbers continue to be assigned by the Sequence Chairman TE in blocks of 100. The only difference is that the Sequence Ceiling Quantum Value has been incremented from 1000 to 2000.

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

1001  

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

1002

Maintaining this Sequence Ceiling Quantum Value keeps track of Sequence numbers that have definitely not been distributed. This Sequence Ceiling Quantum Value for each Sequence is known by all TEs. In the case where this Sequence Chairman TE is stopped, any other TE can become that Sequence's Chairman TE and start distributing Sequence numbers. This new Sequence Chairman TE will assign new Sequence numbers starting at that Sequence's Ceiling Quantum Value, and know that new Sequence numbers assigned will be unique. The new Sequence Chairman TE would reset the Sequence's Ceiling Quantum Value to the next value and would message all other TEs of this new Sequence's Ceiling Quantum Value. 

What happens to sequences when a DB is restarted?

After a database is restarted, the Sequence Chairman TE does not know what Sequence numbers have been previously assigned, but does know what the Sequence's Ceiling Quantum Value is. This Sequence Ceiling Quantum Value is the only means by which the Sequence Chairman TE can begin to assign new Sequence numbers and ensure that they will be unique.

In the above example, the initial Sequence Ceiling Quantum Value was 1000. TEs requested blocks of Sequence numbers until the Sequence Chairman TE issued 1000 Sequence numbers for that Sequence. Once the Sequence Ceiling Quantum Value was hit, the Sequence Chairman TE bumped the Sequence Ceiling Quantum Value to 2000 and continued to issue Sequence numbers in increments of 100. In this example, blocks of Sequence numbers continued to be assigned by the Sequence Chairman TE (1001-1100, then 1101-1200, etc.).

Following this example, at the time of the database restart, this Sequence Ceiling Quantum Value is 2000. After restart, the Sequence Chairman TE will assign Sequence numbers, starting with this Sequence Ceiling Quantum Value, 2000, in blocks of 100, to all requesting TEs. The Sequence Chairman TE will also bump the Sequence Ceiling Quantum Value to 3000 and message all other TEs of that new Sequence Ceiling Quantum Value. If before the restart, the last block of Sequence numbers assigned by the Sequence Chairman TE ended with 1200, the next block of sequence numbers assigned will begin with 2000, creating a gap in Sequence numbers assigned, but ensuring that sequence numbers are unique.

What happens when a sequence is altered to reset the Sequence Floor Value?

No Sequence can be altered to have its Sequence Floor Value (starting value) set to anything less than or equal to the current Sequence Ceiling Quantum Value. If this is attempted, the TE will issue an error message:

SQL> alter sequence seq_sequence1 start with 10;

sequence "TEST.SEQ_SEQUENCE1" new floor 10, is less than current ceiling value 3000

SQL> alter sequence seq_sequence1 start with 3001;

SQL> select next value for seq_sequence1 from dual;

SEQ_SEQUENCE1

--------------

3001

In this example, after the Alter Sequence statement is issued, the Sequence Chairman TE increments the current Sequence Ceiling Quantum Value to 4000. It then begins to assign new Sequence numbers, beginning with this new Sequence Floor Value of 3001, in blocks of 100, until that next Sequence Ceiling Quantum Value is reached, or the database is restarted.

What happens to sequences that are migrated from another database?

NuoDB provides a tool for migrating existing databases to NuoDB. The migrator tool will create all existing database Sequences as new Sequences in NuoDB. The starting value of each Sequence in NuoDB will be greater than the maximum value of each Sequence defined in the existing database. How this is calculated and how that sequence is created in NuoDB depends on the source database being migrated from. A separate migrator tool technical blog post will discuss this in detail.

CA App Experience Analytics, a whole new level of visibility. Learn more. Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Madolyn Sullivan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}