Transactions with Large Objects

Large objects are a special consideration when dealing with database transactions. Manipulating a large object (BLOB/CLOB) is considered a distinct step in a transaction. This has the following implications:

Autocommit is enabled

When autocommit is enabled, creating and inserting a large object is considered two steps. Consider the following example:

ByteArrayInputStream byteStream = new ByteArrayInputStream(buffer);
PreparedStatement p = c.prepareStatement("INSERT INTO blobTestValues(?)")) {
   p.setBinaryStream(1, byteStream);
   p.execute();
}

In this example we are inserting a single row into the table. Since the column we are inserting is a BLOB, this is two operations. First, JDBC needs to create the BLOB object on the server. This is a single operation and with auto-commit enabled, this is commited and the BLOB is now present on the server. Second, we insert the BLOB pointer into the table row. This operation is then committed. Any error on the INSERT does NOT rollback or dispose of the BLOB object that was created. Since the BLOB was dynamically created by the JDBC driver, you will lose all references to the object in the system. It can be cleaned up by a DBA running on the database system, but not by the JDBC application.

If you want to ensure the BLOB is not lost in this scenario you MUST using an explicit transaction like the following example shows:

ByteArrayInputStream byteStream = new ByteArrayInputStream(buffer);
c.setAutoCommit(false);
PreparedStatement p = c.prepareStatement("INSERT INTO blobTestValues(?)")) {
   p.setBinaryStream(1, byteStream);
   p.execute();
}
c.commit();

Autocommit is disabled

If autocommit is disabled then you are using explicit transactions and most large object operations will work as expected in between your transaction boundaries. However, you are free to commit/rollback the intermediate large object operations if you use an explicit Blob/Clob object.

c.setAutoCommit(false);
PreparedStatement p = c.prepareStatement("INSERT INTO blobTestValues(?)")) {
   Blob blob = c.createBlob();
   c.commit(); //Commits the blob creation
   p.setBlob(1, blob);
   p.execute();
}
c.rollback(); //rollback the insert, the blob survives