I ran some tests today and have found that using the SERIALIZED transaction isolation level gives the desired result. Not committing the transaction until the stream has been read prevents another connection from interfering.
On Wednesday, November 14, 2012 11:09:49 AM UTC, Matt Kelly wrote: > > Hi, > > I am developing an application in which a variety of data is stored in an > H2 database. This data includes some image files and (potentially) large > blocks of text. The application uses a client/server architecture so that > it can be used with a local database or connect to a server using RMI. The > interface of the server is the same regardless of whether the server is > local or remote. When the server is local the application instantiates the > server in the same JVM and the server connects to the H2 database with an > embedded connection. When the server is remote it is a separate > application, containing an embedded H2 database, that multiple clients can > connect to via RMI. The local and remote servers share a lot of > functionality so they both inherit from an abstract server class, the > remote server contains extra stuff to handle multiple users and network > streaming. I am using RMIIO to stream data to and from the remote server, > which means it is easiest to store text in BLOBS since RMIIO does not wrap > Readers. > > In the remote server I am using a connection pool to access H2. A > connection is established per client so that they can maintain separate > transactions. I would like to be able to stream data while executing other > queries as the same user, mainly in order to retrieve images from the > database in the background. I am not sure how this works in terms of > transactions. > > If I execute a query to retrieve a BLOB and obtain an InputStream by > called ResultSet.getBinaryStream(), when does the table get locked and > unlocked? Should I create a DB connection for the stream and not commit the > transaction until the stream has been closed? I want to eliminate the > possibility of the BLOB being modified or deleted by another DB connection > while it is being read. > > If I haven't provided sufficient information, please let me know. > > Matt > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/fN4viOCfeSYJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
