You are viewing a plain text version of this content. The canonical link for it is here.
Posted to slide-user@jakarta.apache.org by David St-Onge <da...@ixos.de> on 2003/12/01 11:13:26 UTC

Nested Transaction with MS SQL?

Hi everybody!

I have difficulties getting Slide to run in nested-transactional mode with
MS SQL Server 2000 (SelectMethod=cursor) and Tomcat 4.1.

I first tried with JDBCDescriptorsStore to find out it was not possible
because this store is based on AbstractSimpleService, which doesn't allow
simultaneous transaction contexts.

Then I tried with J2EEDescriptorsStore and after with AbstractRDBMSStore,
but without success.

I have a test program that writes (PUT) several files in the file folder,
reads (GET) them, and then deletes (DELETE) them one by one. My test program
works perfectly well as long as I only launch one instance at a time.

However, when I try to run two simultaneous instances of my test program,
eventually, a PUT or a DELETE ends up with failure. Both instances operate
in the same folder but with files having different names (a timestamp in the
filename ensures that).

The time it takes to get a failure is random. The most two common failures I
get are the followings:

1- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Transaction (Process ID 80) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the
transaction.

2- HTTP Response 409 (Conflict) - (No information in any log file)

Is there perhaps a problem using MSSQL with Slide in nested-transactional
mode? Has anybody done that? I'm not a database expert but I know MSSQL
doesn't behave like Oracle. I'm using MSSQL with the default transaction
isolation (read committed), which could be the source of my deadlocks. The
alternative is to set the transaction isolation to read uncommitted,
therefore allowing dirty reads (which eventually causes other failures.)
With Oracle, if a thread locks a table, a select statement of another thread
targeting the same table would neither suspend nor allow a dirty read. The
select would simply read the data as it was before the transaction. But
maybe this has nothing to do with my problem.

Has anybody any clue what goes wrong? Any help would be greatly appreciated.

Thanks!
David


Re: Nested Transaction with MS SQL?

Posted by Oliver Zeigermann <ol...@zeigermann.de>.
Hi David,

I do not know which version of Slide or stores you use. We have a 
version merged together from other previous implementations I am 
confident we will move to the main CVS location as the standard RDBMS 
store even this week. Apart from that, I can tell there certainly can be 
deadlocks when you have multiple transactions as in any other 
application. So, from that point of view, there is nothing wrong with 
what you observe.

On the other side, of course it is undesirable to have deadlocks and the 
DB design really enforces them. I was thinking of a mechanism that locks 
on a higher level to detect possible deadlocks as early as possible or 
prevent them in the first place. Unfortunately, this will not be 
available before Slide 2.1 if at all.

Oliver

David St-Onge wrote:

> Hi everybody!
> 
> I have difficulties getting Slide to run in nested-transactional mode with
> MS SQL Server 2000 (SelectMethod=cursor) and Tomcat 4.1.
> 
> I first tried with JDBCDescriptorsStore to find out it was not possible
> because this store is based on AbstractSimpleService, which doesn't allow
> simultaneous transaction contexts.
> 
> Then I tried with J2EEDescriptorsStore and after with AbstractRDBMSStore,
> but without success.
> 
> I have a test program that writes (PUT) several files in the file folder,
> reads (GET) them, and then deletes (DELETE) them one by one. My test program
> works perfectly well as long as I only launch one instance at a time.
> 
> However, when I try to run two simultaneous instances of my test program,
> eventually, a PUT or a DELETE ends up with failure. Both instances operate
> in the same folder but with files having different names (a timestamp in the
> filename ensures that).
> 
> The time it takes to get a failure is random. The most two common failures I
> get are the followings:
> 
> 1- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]Transaction (Process ID 80) was deadlocked on lock resources
> with another process and has been chosen as the deadlock victim. Rerun the
> transaction.
> 
> 2- HTTP Response 409 (Conflict) - (No information in any log file)
> 
> Is there perhaps a problem using MSSQL with Slide in nested-transactional
> mode? Has anybody done that? I'm not a database expert but I know MSSQL
> doesn't behave like Oracle. I'm using MSSQL with the default transaction
> isolation (read committed), which could be the source of my deadlocks. The
> alternative is to set the transaction isolation to read uncommitted,
> therefore allowing dirty reads (which eventually causes other failures.)
> With Oracle, if a thread locks a table, a select statement of another thread
> targeting the same table would neither suspend nor allow a dirty read. The
> select would simply read the data as it was before the transaction. But
> maybe this has nothing to do with my problem.
> 
> Has anybody any clue what goes wrong? Any help would be greatly appreciated.
> 
> Thanks!
> David
> 
> 




---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org