DB locking due to transactions?

[Gavi Raaghav]

We are using SQL server2000 and WAS5.1.
The default transaction level is required new as we have not set any specific TL.We are only using SLSB.
One common problem which we are encountering is that Db locking happens very frequently and the MSDTC service needs to be restarted to kill the locking process. The locking process cannot be traced as it shows up as an orphan process.Once the table is locked all the connections hitting it gets hanged and eventually the connection pool gets exhausted.

What could be the possible solutions for it? One more thing noticable over here is that since we are not using res-ref for DB i belive the defualt isolation level used is repeatable read.

Any pointers will be highly appreciated.

[Roger Chung-Wee]

The default transaction level is required new as we have not set any specific TL.

Never use any defaults for transaction attributes. I'm not a WAS man, but I'm a bit dubious whether the default transaction attribute is RequiresNew. And if it is, I'm also doubtful whether it's always the most appropriate.

As for your connection problem: ensure that each method that must run in a new transaction declares a local Connection variable and always closes that Connection before the method ends. This will cause any DB locks to be freed and the Connection to be returned to the pool at the end of the transaction.

[Reid M. Pinchback]

Agreed, not a good idea. Case in point, JBoss and Weblogic have *different* defaults for transaction handling on beans that don't declare their transactionality.

I'd take a look at how many connections you are allowing in your pool. If it is a sufficiently large number, and your lack of transactionality is causing connections to not get released (committed) then eventually your database would run out of processes/threads to handle client connections. The "fix" isn't to change the number of connections; I just wouldn't be surprised to hear that you are allowing a lot of connections. The fix is to put in the appropriate transactionality settings.