WebSphere and locking issues

Using WebSphere on a Sybase ASE database can cause some problems with regard to locking in ASE. The default transaction isolation level for ASE is 1 (read committed), however the default behaviour of WebSphere is to use isolation level 2 (repeatable read). This has the effect that even rows that are read will be kept locked until the transaction has finished. In turn, this can lead to a high change of sessions blocking each other or the need to raise to ASE configuration parameter "number of locks" to a very high number (more than 1 million is not uncommon). To resolve this the WebSphere developers need to override the isolation level.

Note: to see what isolation level a running spid is using do this:

dbcc traceon(3604)
go
dbcc pss(0,<spid>)
go

Look in the output for value after "pisolation" (somewhere halfway in the output).