Of Sequences, Insert All, and Prepared Statements

This post was almost titled "When is nextval Not the Next Value"

I was recently asked to help troubleshoot a constraint violation.  The developer was getting a unique constraint violation on a constraint they did not recognize (nor could I... thank you Hibernate, and your gosh-awful constraint naming).

Finding the constraint was simple enough. 

select * from dba_cons_columns where constraint_name='TC234467890'; 

I quickly saw this was a constraint on the ID column of the history table, which I knew was set from a sequence.  I asked to see the SQL that was failing.  They sent me part of their java code.

sql="insert all "

     + "into history (id, col1, col2,col3) "

     + " values (hibernate_seq.nextval,1,0, " + floatVar[i] + ") "

     + "into history (id, col1, col2, col3) "

     + " values (hibernate_seq.nextval, 1,0, " + floatVar[i+1] +") "

     + "into history (id, col1, col2, col3) "

     + " values (hibernate_seq.nextval, 2,0, " + floatVar[i+2] +") "

     + " select 1 from dual;";

executeUpdate(SQL);

After cringing at the lack of bind variables I saw that the Insert All was the culprit.  A quick test of the query in sqlplus confirmed it.  The code was attempting to increase performance by executing multiple inserts in a single call to the database.  This is admirable, but misguided.  Because all 3 inserts are part of the same SQL statement, the nextval of hibernate_seq is fetched at the first occurrence and re-used for each additional occurrence  in the same statement.  This caused the same ID value to be used for each of the 3 rows, which of course violates the primary key's unique constraint.

I advised them to break up the insert all into multiple calls to the database.  They did and that fixed the immediate issue. I then introduced them to prepared statements.  This developer was new to java, so there was no expectation that they were aware of it.

sql="insert into history(id, col1, col2, col3) values (hibernate_seq.nextval, ?,?,?)"

stmt=prepareStatement(SQL);

for (I=1;I<=4;I++)

{

  setFloat(1,floatVar[i]);

  stmt.execute();

};

The prepared statement approach to this has several advantages.

First, it fixes the sequence nextval issue.

Second, it allows more readable code by separating the structure of the query from the variables passed into it.

Third, it allows clearer type casting of data types.  This fixed the next issue the developer ran into due to implicit type conversions when the SQL was sent as a single string.

Fourth, and most important for performance, it allows the database to work more efficiently.  In the executeQuery approach, each SQL call is a unique string sent to the database.  Each is hard parsed and never re-used.  With prepared statements, the SQL is generic and common across all calls.  Hard parsing is not needed.  The value of variables are passed and bound to their location in the query at runtime.

Fifth, using bind variables prevents SQL injection... a topic for another day.