Spring Transaction with Sybase

Question

I am using Spring in my Web Application , with the underlying database as Sybase.

I have 3 complex stored procedures to be executed. The procs , have create table and drop table commands to hold temporary result sets.

The tables are created in the user db space , rather that in the tempdb space. Hence, I am faced with the need to ensure that the entire service operation from the service bean , that would have DAO objects calling the stored procs, to be serialized. Does simply making the service bean method a Spring Transaction, ensure a solution to potential concurrency related problems in my case?

Another thing that I noticed is that, annotating my service method as @Transactional , made the sybase database throw an error : "Create table command cannot be executed within a transaction". Does this mean that Spring makes the entire database operation a transaction? I am really not clear about this , and any explanation would be welcome. Meaning if I have a stored proc named myproc . The sybase statement would be exec myproc. This,say, is executed by the DAOobject from the service method, annotated as @Transactional. Now does Spring make the database operation as "begin tran exec myproc end tran". My observation seems to suggest that. Please explain.

And also explain, if just annotation of @Transactional , will solve my concurrency issues. I , actually don't want 2 instances of my stored proc to be running on the database , at a time.

Answer 1

You've asked a number of questions at once, but I'll do the best I can to answer them.

• Marking a service as @Transactional associates it with the current JTA (Java Transaction API) transaction (or creates one if required)
• Depending on how your datasources are configured, JDBC connections will typically also be associated (enlisted) into the transaction
• If a connection is associated with a JTA transaction then anything that is executed on it will take place within a database transaction.
• In Sybase ASE, you cannot create (or drop) a table inside a transaction.

So, marking your service as @Transactional will prevent you from executing a proc that contains create table statements.

However, that won't solve the problem you're facing anyway. Marking something @Transactional, simply means that it executes inside a JTA transaction. And that means that it either commits, or rolls-back, but it doesn't guard against concurrent access.

You have a few options

• If you know that your application will only ever run on a single VM, then you can mark the code as serialized. This will make sure the VM only ever has 1 thread inside that code at a time.
• You can implement concurrency controls inside the proc, (e.g. use lock table), but that will require a transaction, which will prevent you from creating a table inside the procedure.
• Or you can redesign your application to not have to jump through all these hoops.

There are probably easier ways of achieving the outcome you're looking for - creating and dropping tables inside a proc, and then trying to prevent concurrent access to that proc is not a typical way of solving a problem.