1

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.

oortcloud_domicile
  • 840
  • 6
  • 21
  • 41
  • 1
    Just to add to the answer below, the locks can also be configured on a per table basis if you do not wish to make a server wide change. There is additional overhead when datarows is enabled, so if you are only having issues with a couple tables, you may want to only change those tables. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/X12029.htm – Mike Gardner Dec 22 '15 at 13:43
  • I would suggest asking the DB admin for all the info about the locking connection. Assuming this RDBMS is Sybase ASE and not ASA or other, locks can be queried in syslocks, where the connection id (spid) of the locking process can be obtained. From the spid some info is available in sysprocesses: starting time, user and some app dependent info. Gathering all, maybe this allows to identify the jboss transaction and infer conclusions. – Corral Dec 22 '15 at 21:04

2 Answers2

2

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
Andy Guibert
  • 41,446
  • 8
  • 38
  • 61
  • 1
    I would also suggest adding changing the locking scheme on individual tables via 'alter table' to your answer. There is additional overhead incurred using datarows, so if only a few tables are having issues, it may not be ideal to change the scheme on the whole server. Check the link in my comment above to get the syntax – Mike Gardner Dec 22 '15 at 13:45
  • @MichaelGardner Thanks! I've updated my answer with your suggestion. – Andy Guibert Dec 22 '15 at 13:57
2

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

RobV
  • 2,263
  • 1
  • 11
  • 7
  • hi there , is it possible you can check this question ? http://stackoverflow.com/questions/34995600/sybase-alternative-way-for-error-to-catch-error – Moudiz Jan 25 '16 at 17:48