sql transaction support

Poster Content
nk4um User
Posts: 7
March 2, 2005 09:44sql transactions
Thanks Pete,
I look forward to trying the transactional SQL component as soon as its ready.

Thanks again.

Franco
Like · Post Reply
nk4um Moderator
Posts: 901
February 25, 2005 10:28
Having looked at the JDBC Connection API - we can very easily support the commit/rollback in the sqlBatch service. In addition we can probably optimize sqlBooleanQuery, sqlQuery by setting readonly.

We need to get the connection pooling work done RSN - we''ll ensure these updates get done too. Probably next week.

Thanks for the suggestions.

Pete
Like · Post Reply
nk4um User
Posts: 7
February 25, 2005 00:54SQL transaction support
The only features I feel are missing from the sqlxxxx services (sqlBatch only really) is transaction
support. I do beleive that transaction support is not vendor specific.
I don''t see how any non trivial SQL based/backed application can be developed
without such support.
The executeBatch JDBC feature seems an ideal vehicle to interact from an XML
pipeline. An XML document can be used
to represent a dataset of any complexity. It then needs to
be persisted into an RDBMS as an atomic set. XSLT is used
to translate the document into a set of SQL statements. But
then these statements need to be persisted as a single unit.
if the Nth statement fails, the entire set needs to be rolled back otherwise
the DB is in an inconsistent state.

The problem you mention regarding RESTfull invocations I don''t beleive is
really a problem. (not for us anyway). I think what you mean (correct me if
I am wrong) is that there may be situations where a transaction lasts for a
number of RESTfull service invocations. This situation (in particular) with
RDBMS interactions should be avoided if at all possible. I do see that your static references could be used when the situation arose (but these could cause scalability issues when db connections are limited). What we do need
though is a guarantee that a single RESTfull service invocation (which may
alter a number of RDBMS tables) is consistent. It either succeeds or fails.
If it fails the system needs to be left in a consistent state - probably to
that which existed prior to the failed call. So the
''execute SQL batch'' and ''commit/rollback'' need to be performed in the one RESTfull service call.
The easiest way I can see this being done would be be to encapsulate the entire batch
sql statements into a transaction (in your java code), just by issueing a ''commit'' after the
executeBatch command, and a ''rollback'' in any exception handling code.

Thanks again for your time and quick responses.


Like · Post Reply
nk4um Moderator
Posts: 901
February 24, 2005 14:19
The sqlXXXXX services are a set of tools for RDBMS interaction which are implemented using the JDBC API. The intent is to provide a common plain vanilla feature set - therefore there are no vendor specific features presented.

All of the services are hosted by a single accessor in the mod-db module...

org.ten60.rdbms.accessor.RDBMSAccessorImpl

It should be straightforward to modify this class to provide the features you need.

If you think there are features that are not vendor specific that we should add to the vanilla services please let us know.

FYI. We have work scheduled to improve connection pooling and provide static references to connections. At the moment there are rare situations in which consecutive RESTful invocations of the sql services fail because they are not being performed on the same connection. This can occur if the connection has been recreated after earlier being ejected from a heavily loaded cache.

One scenario which can highlight this is performing operations using mySQL temporary tables. MySQL directly ties the temporary table to the connection - if, because of the RESTful service invocation, you perform consecutive operations on two different connections the second will have no access to any temporary tables created on a previous connection. Using static references to the connections in the RDBMSAccessorImpl will resolve this.
Like · Post Reply
nk4um User
Posts: 7
February 24, 2005 03:49sql transaction support
Hello again,
Can you tell me if there is any transaction support within any of the SQL/RDBMS accessors.

That is, how can I specify when to perform a ''commit'' or ''rollback''?

I have tried sqlBatch accessor and noticed there does not seem to be any way of rolling back the entire batch if any SQL fails. What seems to happen is that all SQL that succeeds prior to an exception are commited, and all subsequent SQLs after the failed SQL are ignored.
I am using oracle 8.1 and its doco seems to indicate that upon any sql exceptions being raised from its executeBatch command, it should be possible
to issue a rollback. I believe that in my situation the Oracle Autocommit setting is ''off''.
Is there a way of specifying an ''autocommit'' setting? say in the configuration file.

Thanks again,
Franco
Like · Post Reply