Reading from JDBC Blob after exiting Spring transaction

Question

I have the following schematic implementation of a JAX-RS service endpoint:

@GET @Path("...") @Transactional public Response download() { java.sql.Blob blob = findBlob(...); return Response.ok(blob.getBinaryStream()).build(); } 

The JAX-RS endpoint call will retrieve the Blob from the database (via JPA) and pass the result back to the HTTP client. The purpose of using Blob and stream instead of, for example, JPA naive BLOB in bytes [] means that all data should be stored in memory, and instead transfer directly from the database to the HTTP response.

It works as intended, and I really don't understand why. Is the Blob handler that I get from the database associated with both the underlying JDBC connection and the transaction? If so, I expected the Spring transaction to be completed when I return from the download () method, due to the inability of the JAX-RS implementation to later access the data from Blob in order to pass it back to the HTTP response.

Answer 1

I spent some time debugging the code, and all my assumptions in the question are more or less correct. The @Transactional annotation works as expected, transactions (both Spring transactions and DB transactions) are made immediately after returning from the load method, the physical connection to the database is returned to the connection pool, and the BLOB contents were obviously read later and send HTTP answer.

The reason this still works is because the Oracle JDBC driver implements functionality beyond what is required by the JDBC specification. As Daniel noted, the JDBC API documentation states that "The Blob is valid for the entire transaction in which it was created." The documentation only states that Blob is valid during the transaction, it does not indicate (as Daniel claimed and originally assumed by me) that Blob is invalid after the transaction is completed.

Using simple JDBC, retrieving an InputStream from two Blob in two different transactions from the same physical connection and not reading the Blob data before the transactions are completed demonstrates this behavior:

 Connection conn = DriverManager.getConnection(...); conn.setAutoCommit(false); ResultSet rs = conn.createStatement().executeQuery("select data from ..."); rs.next(); InputStream is1 = rs.getBlob(1).getBinaryStream(); rs.close(); conn.commit(); rs = conn.createStatement().executeQuery("select data from ..."); rs.next(); InputStream is2 = rs.getBlob(1).getBinaryStream(); rs.close(); conn.commit(); int b1 = 0, b2 = 0; while(is1.read()>=0) b1++; while(is2.read()>=0) b2++; System.out.println("Read " + b1 + " bytes from 1st blob"); System.out.println("Read " + b2 + " bytes from 2nd blob"); 

Even if both Blob were selected from the same physical connection and from two different transactions, both of them can be read completely.

Closing the JDBC connection ( conn.close() ), however, permanently cancels the Blob streams.

Answer 2

Are you sure the transaction advice is up and running? By default , Spring uses the proxy consultation mode. Transactional advice will only be performed if you registered an instance of Spring-proxied with your resource using the JAX-RS Application , or if you used aspectj weaving instead of the standard proxy mode.

Assuming that the physical transaction will not be reused as a result of the distribution of the transaction, using @Transactional in this download () method is generally incorrect.

If the transaction tip really runs, the transaction ends when it returns from the download () method. Blob Javadoc says: "The Blob valid for the duration of the transaction in which it was created." However, Section 16.3.7 of the JDBC 4.2 specification states: " Blob , Clob and NClob objects remain valid for at least the duration of the transaction in which they are created." Therefore, the InputStream returned by getBinaryStream () is not guaranteed to be valid for serving the response; Validity will depend on any warranty provided by the JDBC driver. For maximum portability, you should rely on Blob , valid only for the duration of the transaction.

Regardless of whether the transaction advice is running, you potentially have a race condition, because the underlying JDBC connection used to retrieve the Blob can be reused in such a way that it does not invalidate the Blob .

EDIT: Testing Jersey 2.17, it looks like the behavior of the Response build from the InputStream depends on the given MIME type of the response. In some cases, an InputStream read entirely into memory before sending a response. In other cases, the InputStream is sent back.

Here is my test case:

 @Path("test") public class MyResource { @GET public Response getIt() { return Response.ok(new InputStream() { @Override public int read() throws IOException { return 97; // 'a' } }).build(); } } 

If the getIt () method is annotated with the annotation @Produces(MediaType.TEXT_PLAIN) or no @Produces , then Jersey tries to read the entire (infinite) InputStream in memory, and the application server will eventually exit due to lack of memory. If the getIt () method is annotated using @Produces(MediaType.APPLICATION_OCTET_STREAM) , the response is sent back.

So, your download () method can only work because the drop is not being passed back. Jersey may read all memory in memory.

Related: How to stream endless InputStream with JAX-RS

EDIT2: I created a demo project using Spring Boot and Apache CXF:
https://github.com/dtrebbien/so30356840-cxf

If you run the project and run at the command prompt:

  curl 'http: // localhost: 8080 / myapp / test / data / 1'> / dev / null

Then you will see the output of the log as follows:

  2015-06-01 15: 58: 14.573 DEBUG 9362 --- [nio-8080-exec-1] org.apache.cxf.transport.http.Headers: Request Headers: {Accept = [* / *], Content- Type = [null], host = [localhost: 8080], user-agent = [curl / 7.37.1]}

 2015-06-01 15: 58: 14.584 DEBUG 9362 --- [nio-8080-exec-1] org.apache.cxf.jaxrs.utils.JAXRSUtils: Trying to select a resource class, request path: / test / data /1
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] org.apache.cxf.jaxrs.utils.JAXRSUtils: Trying to select a resource operation on the resource class com.sample. resource.MyResource
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] org.apache.cxf.jaxrs.utils.JAXRSUtils: Resource operation getIt may get selected
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] org.apache.cxf.jaxrs.utils.JAXRSUtils: Resource operation getIt on the resource class com.sample.resource.MyResource has been selected
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSInInterceptor: Request path is: / test / data / 1
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSInInterceptor: Request HTTP method is: GET
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSInInterceptor: Request contentType is: * / *
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSInInterceptor: Accept contentType is: * / *
 2015-06-01 15: 58: 14.585 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSInInterceptor: Found operation: getIt

 2015-06-01 15: 58: 14.595 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Creating new transaction with name [com.sample.resource.MyResource.getIt]: PROPAGATION_REQUIRED, ISOLATION_DEFAULT;  ''
 2015-06-01 15: 58: 14.595 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Acquired Connection [ProxyConnection [PooledConnection [ org.hsqldb.jdbc.JDBCConnection@7b191894 ]]] for JDBC transaction
 2015-06-01 15: 58: 14.596 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Switching JDBC Connection [ProxyConnection [PooledConnection [ org.hsqldb.jdbc.JDBCConnection@7b191894 ]]] to manual commit
 2015-06-01 15: 58: 14.602 DEBUG 9362 --- [nio-8080-exec-1] osjdbc.core.JdbcTemplate: Executing prepared SQL query
 2015-06-01 15: 58: 14.603 DEBUG 9362 --- [nio-8080-exec-1] osjdbc.core.JdbcTemplate: Executing prepared SQL statement [SELECT data FROM images WHERE id =?]
 2015-06-01 15: 58: 14.620 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Initiating transaction commit
 2015-06-01 15: 58: 14.620 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Committing JDBC transaction on Connection [ProxyConnection [PooledConnection [ org.hsqldb.jdbc.JDBCConnection@7b191894 ]]]
 2015-06-01 15: 58: 14.621 DEBUG 9362 --- [nio-8080-exec-1] osjdDataSourceTransactionManager: Releasing JDBC Connection [ProxyConnection [PooledConnection [ org.hsqldb.jdbc.JDBCConnection@7b191894 ]]] after transaction
 2015-06-01 15: 58: 14.621 DEBUG 9362 --- [nio-8080-exec-1] osjdbc.datasource.DataSourceUtils: Returning JDBC Connection to DataSource
 2015-06-01 15: 58: 14.621 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Invoking handleMessage on interceptor org.apache.cxf.interceptor.OutgoingChainInterceptor@7eaf4562

 2015-06-01 15: 58: 14.622 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Adding interceptor org.apache.cxf.interceptor.MessageSenderInterceptor@20ffeb47 to phase prepare-send
 2015-06-01 15: 58: 14.622 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Adding interceptor org.apache.cxf.jaxrs.interceptor.JAXRSOutInterceptor@5714d386 to phase marshal
 2015-06-01 15: 58: 14.622 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Chain org.apache.cxf.phase.PhaseInterceptorChain@11ca802c was created.  Current flow:
   prepare-send [MessageSenderInterceptor]
   marshal [JAXRSOutInterceptor]

 2015-06-01 15: 58: 14.623 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Invoking handleMessage on interceptor org.apache.cxf.interceptor.MessageSenderInterceptor@20ffeb47
 2015-06-01 15: 58: 14.623 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Adding interceptor org.apache.cxf.inte rceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor@ 6129236d to phase prepare-send- ending
 2015-06-01 15: 58: 14.623 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Chain org.apache.cxf.phase.PhaseInterceptorChain@11ca802c was modified.  Current flow:
   prepare-send [MessageSenderInterceptor]
   marshal [JAXRSOutInterceptor]
   prepare-send-ending [MessageSenderEndingInterceptor]

 2015-06-01 15: 58: 14.623 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Invoking handleMessage on interceptor org.apache.cxf.jaxrs.interceptor.JAXRSOutInterceptor@5714d386
 2015-06-01 15: 58: 14.627 DEBUG 9362 --- [nio-8080-exec-1] oacjinterceptor.JAXRSOutInterceptor: Response content type is: application / octet-stream
 2015-06-01 15: 58: 14.631 DEBUG 9362 --- [nio-8080-exec-1] o.apache.cxf.ws.addressing.ContextUtils: retrieving MAPs from context property javax.xml.ws.addressing.context .inbound
 2015-06-01 15: 58: 14.631 DEBUG 9362 --- [nio-8080-exec-1] o.apache.cxf.ws.addressing.ContextUtils: WS-Addressing - failed to retrieve Message Addressing Properties from context
 2015-06-01 15: 58: 14.636 DEBUG 9362 --- [nio-8080-exec-1] oacxf.phase.PhaseInterceptorChain: Invoking handleMessage on interceptor org.apache.cxf.inte rceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor@ 6129236d
 2015-06-01 15: 58: 14.639 DEBUG 9362 --- [nio-8080-exec-1] oacthttp.AbstractHTTPDestination: Finished servicing http request on thread: Thread [http-nio-8080-exec-1,5, main ]
 2015-06-01 15: 58: 14.639 DEBUG 9362 --- [nio-8080-exec-1] oactservlet.ServletController: Finished servicing http request on thread: Thread [http-nio-8080-exec-1,5, main ]

I have trimmed the magazine output for readability. It is important to note that the transaction is completed and the JDBC connection is returned before the response is sent. Therefore, the InputStream returned by blob.getBinaryStream() is optionally valid and the getIt () method may cause undefined behavior.

EDIT3: The recommended practice of using Spring @Transactional annotation is to annotate the service method (see Spring @Transactional Annotation Best Practice ). You may have a service method that finds blob and passes blob data to an OutputStream response. The service method can be annotated using @Transactional so that the transaction in which Blob is created remains open for the duration of the transfer. However, it seems to me that this approach could lead to a denial of service vulnerability through a "slow read" attack . Since the transaction must remain open for the duration of the transfer for maximum portability, numerous slow readers can lock your database tables (databases) by opening transactions.

One possible approach is to save the blob to a temporary file and stream back the file. See How to use Java to read from a file that is being actively written? for some ideas on reading a file while it is being written at the same time, although this case is simpler because the blob length can be determined by calling the Blob # length () method.

Answer 3

I had a similar problem and I can confirm that, at least in my situation, PostgreSQL throws an Invalid large object descriptor : 0 with autocommit when using the StreamingOutput approach. The reason for this is that when Response returns from JAX-RS, the transaction ends and the streaming method is executed later. Meanwhile, the file descriptor is no longer valid.

I created some helper method, so that the streaming part opens a new transaction and can transmit Blob. com.foobar.model.Blob is just a return class that encapsulates blob, so the full object should not be com.foobar.model.Blob . findByID is a method that uses projection onto a blob column and only selects that column.

So, StreamingOutput JAX-RS and Blob work under JPA and Spring transactions, but they need to be configured. The same goes for JPA and EJB, I think.

 // NOTE: has to run inside a transaction to be able to stream from the DB @Transactional public void streamBlobToOutputStream(OutputStream outputStream, Class entityClass, String id, SingularAttribute attribute) { BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream); try { com.foobar.model.Blob blob = fooDao.findByID(id, entityClass, com.foobar.model.Blob.class, attribute); if (blob.getBlob() == null) { return; } InputStream inputStream; try { inputStream = blob.getBlob().getBinaryStream(); } catch (SQLException e) { throw new RuntimeException("Could not read binary data.", e); } IOUtils.copy(inputStream, bufferedOutputStream); // NOTE: the buffer must be flushed without data seems to be missing bufferedOutputStream.flush(); } catch (Exception e) { throw new RuntimeException("Could not send data.", e); } } /** * Builds streaming response for data which can be streamed from a Blob. * * @param contentType The content type. If <code>null</code> application/octet-stream is used. * @param contentDisposition The content disposition. Eg naming of the file download. Optional. * @param entityClass The entity class to search in. * @param id The Id of the entity with the blob field to stream. * @param attribute The Blob attribute in the entity. * @return the response builder. */ protected Response.ResponseBuilder buildStreamingResponseBuilder(String contentType, String contentDisposition, Class entityClass, String id, SingularAttribute attribute) { StreamingOutput streamingOutput = new StreamingOutput() { @Override public void write(OutputStream output) throws IOException, WebApplicationException { streamBlobToOutputStream(output, entityClass, id, attribute); } }; MediaType mediaType = MediaType.APPLICATION_OCTET_STREAM_TYPE; if (contentType != null) { mediaType = MediaType.valueOf(contentType); } Response.ResponseBuilder response = Response.ok(streamingOutput, mediaType); if (contentDisposition != null) { response.header("Content-Disposition", contentDisposition); } return response; } /** * Stream a blob from the database. * @param contentType The content type. If <code>null</code> application/octet-stream is used. * @param contentDisposition The content disposition. Eg naming of the file download. Optional. * @param currentBlob The current blob value of the entity. * @param entityClass The entity class to search in. * @param id The Id of the entity with the blob field to stream. * @param attribute The Blob attribute in the entity. * @return the response. */ @Transactional public Response streamBlob(String contentType, String contentDisposition, Blob currentBlob, Class entityClass, String id, SingularAttribute attribute) { if (currentBlob == null) { return Response.noContent().build(); } return buildStreamingResponseBuilder(contentType, contentDisposition, entityClass, id, attribute).build(); } 

I also need to add to my answer that there may be a problem with the behavior of Blob in Hibernate. By default, Hibernate combines the complete object with the database, also if only one field has been changed, i.e. If you update the name field and also have an untouched Blob image , the image will be updated. Worse, before merging, if the object is detached, Hibernate must retrieve the Blob from the database to determine the dirty status. Since blobs cannot be bytes compared (too large), they are considered immutable, and equal comparisons are based only on a reference to the blob object. A reference to an object from the database will be another reference to the object, so although nothing has been changed, blob is updated again. At least that was the situation for me. I used the @DynamicUpdate annotation in essence and wrote a user type that treats blob differently and checking if it needs to be updated.