CODEWITHSUNDEEP
jakarta-eejbossejbsybasedatabase-locking
oortcloud_domicile
oortcloud_domicile

Reputation: 850

Locks not releasing in Sybase ASE

My App runs on EJB 1.1 on Jboss 2.4 and uses Sybase. Lately we are seeing an issue where there are indefinite locks on a particular table and that caused the succeeding queries to wait on the locks to get released and therefore whole app fails. The DB admin claims that the app is not releasing any locks and we have not made any changes to the code that deals with that particular table. My question is are there any common issues with Jboss 2.4 that needs to be checked or any specific areas to look for?

The query i run is just a simple select/update statements with read_committed transaction isolation. I get the connection from jboss pool and execute the select or update and then close connection,statement and resultset.

Why is the problem occurring randomly?

I am sorry I am unable to provide any code.

Upvotes: 1

Views: 2460

Answers (2)

RobV
RobV

Reputation: 2378

Sounds like there may be an open transaction which does not commit? Query master..syslogshold to get clarity.

Upvotes: 2

Andy Guibert
Andy Guibert

Reputation: 42946

I had the same symptoms using two transactions at the same time for Sybase ASE.

Hard to give a firm answer without seeing any of your code and only reading about the symptoms, but adjusting your locking scheme should do the trick.

Suggested fix:

By default, the locking scheme for Sybase ASE is "allpages", which I have found to be prone to deadlocking. To resolve deadlocks, use a more fine-grained locking mechanism (such as "datarows").

To configure lock scheme:

sp_configure "lock scheme", 0, datarows

References:

Edit:

In the comments Michael Gardner pointed out that this can be changed on a per-table basis, which gives you the control of not needing to change the locking scheme on your entire server, and will incur less locking overhead if you know which tables are giving you problems.

alter table table_name lock datarows

Upvotes: 2

Related Questions