What? VoltDB Stored Procedures?

written by Ryan Betts on July 1, 2010 with no comments

VoltDB is a relational store that uses SQL for its query language and stored procedures for its transactional unit of work. Clients invoke stored procedures to access the database. Stored procedures access data using parametrized SQL statements and can manipulate intermediate results in Java. Each stored procedure invocation is a transaction. A procedure’s data manipulations are atomic and isolated from other concurrently executing procedures.

Here’s a simple example, excerpted from the Voter sample application shipped with the VoltDB distribution kit. The application is meant to support a voting use case in which viewers of a TV show like “American Idol” phone in votes for their favorite contestents. This procedure verifies two business logic constraints. If both constraints are valid, the procedure increments a contestent’s vote count. The procedure returns the number of votes cast. This logic is fully transactional. The constraint checks and the optional insert all happen atomically and in full isolation from other votes being processed.

   public class Vote extends VoltProcedure {
   // check if the vote is for a valid contestant
   public final SQLStmt checkContestant =
   new SQLStmt("select contestant_number from contestants where contestant_number = ?;");

   // check if the voter has exceeded their allowed number of votes
   public final SQLStmt checkVoter =
   new SQLStmt("select num_votes from v_votes_by_phone_number where phone_number = ?;");

   // record the vote
   public final SQLStmt insertVote =
   new SQLStmt("insert into votes (phone_number, contestant_number) values (?, ?);");

   public long run(long phoneNumber, byte contestantNumber, long maxVotesPerPhoneNumber)
   {
   voltQueueSQL(checkContestant, contestantNumber);
   voltQueueSQL(checkVoter, phoneNumber);
   VoltTable resultsCheck[] = voltExecuteSQL();

   // if the contestant is valid and the voter has remaining votes, vote.
   if (resultsCheck[0].getRowCount() > 0) {
   if ((resultsCheck[1].getRowCount() == 0) ||
   (resultsCheck[1].fetchRow(0).getLong(0) < maxVotesPerPhoneNumber)) {
   voltQueueSQL(insertVote, phoneNumber, contestantNumber);
   voltExecuteSQL();
   return 1L;
   }
   }
   return 0L;
   }
   }

Why did we design VoltDB to work this way?

VoltDB’s choice to use stored procedures as transactions is motivated by three observations.

  • Round tripping unnecessary data (a large JSON document or unnecessary table columns) across the network to a client quickly becomes a bottleneck and an expensive one if you’re paying for bandwidth. VoltDB stored procedures allow full, transactional manipulation of stored data, eliminating unnecessary data transfer between application and database.
  • Application complexity is reduced when the data store provides stronger atomicity and isolation guarantees.
  • Multiple round-trips to the data store impose a latency cost that many applications can not afford. Even if applications are structured to tolerate inconsistent, non-atomic updates, latency budgets require minimizing application to database round trips.

Moving data processing closer to the data, into the storage engine satisfies these observations. That requires a rich data query language and the ability to batch multiple statements together. This is exactly the functionality that VoltDB’s stored procedure interface provides.

VoltDB as a Transactional Data Service

Large scale applications are built from composable services. These services interact via their public APIs. Whether SOAP + WSDL, REST + HTTP, or your other favorite interface definition, the core concept is the same: reduce large problems into coordinated smaller components. Give those smaller components public interfaces with sane, predictable behavior.

VoltDB stored procedures provide a data service API with clear transactional semantics. A VoltDB application is simply a transactional data service.

For example, complementing Vote procedure above with CreateContest, GetLeaderBoard and ArchiveContest produces an API to manage a vote tabulation capable of registering 1 million votes per second, continuously. This VoltDB application would exist beside the other services that comprise a modern web-scale application.

Ryan Betts
Software Engineer
VoltDB