[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6522
  • Last Modified:

ASE running out of locks

I have an application which i migrated from EA server to weblogic. it is using sybase database.
Now applicataion runs fine on Sybase ea server but throwing an error on weblogic when application is updating one table.
That table is huge it has totalrow 166957279.

Error is :
run():SQLException

com.sybase.jdbc2.jdbc.SybSQLException: ASE has run out of LOCKS. Re-run your com

mand when there are fewer active users, or contact a user with System Administra

tor (SA) role to reconfigure ASE with more LOCKS.

 

        at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)

        at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)

        at com.sybase.jdbc2.tds.TdsResultSet.nextResult(TdsResultSet.java:510)

        at com.sybase.jdbc2.tds.TdsResultSet.next(TdsResultSet.java:296)

        at com.sybase.jdbc2.jdbc.SybResultSet.next(SybResultSet.java:137)

        at weblogic.jdbc.wrapper.ResultSet_com_sybase_jdbc2_jdbc_SybResultSet.ne

xt(Unknown Source)


Please help me resolve this error
0
gpw7368
Asked:
gpw7368
  • 2
  • 2
2 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
It is easy enough to resolve this. You either need to configure your ASE for more locks, use fewer locks, or release the locks more quickly.

I am guessing that because this worked under EA server but not under Weblogic, Weblogic is doing things differently in terms of transaction mode (chained vs unchained) or isolation level for the queries. Your efforts are best spent looking into these - probably connection properties - rather than just blindly adding more locks in ASE.

That said, you add more locks in ASE using

   sp_configure "number of locks", [x]

You will need SA privileges to be able to do this. However this is unlikely to be your answer because if as I suspect Weblogic has some poor connection defaults for handling transactions and locks, you might need enough locks for your entire table, which is not practical.

If there's really nothing to be done at the Weblogic side there are some other things we could try at the ASE side to make the best of a poor situation (changing lock promotion thresholds, perhaps), but the simple fact that it worked under EA Server and not under Weblogic tells me your problems (and the fix) both will be found in Weblogic.
0
 
bretCommented:
If you have control of the source code, you could resolve this error by having the application do an explict LOCK TABLE command prior to the update, to ensure that it only uses a single table lock for the update.
0
 
gpw7368Author Commented:

Lock type = sh_page.
is table lock is better than this?

Also now it is not throwing out of locks error anymore.  But it is pretty slow. It is inserting one row at every 45 seconds on avg when running on weblogic. Weblogic is supposed to be faster than EA server.

I dont understand why it is so slow on weblogic and so fast on ea server. The application code and database both are same.  The application is running on weblogic since friday and it hasnt completed yet.
While on easerver it only takes 3-4 hours.

Please help me with this..thanks
0
 
Joe WoodhousePrincipal ConsultantCommented:
Running the same code through two different middle layers does not mean the same SQL is being sent to the Sybase back end.

Almost certainly there are connection profiles and transaction management settings you will need to look at more closely in Weblogic.

A table lock isn't "better" than a page lock - there is no one size fits all here. It might've helped you here because it would allow the table to be locked with just one lock rather than many, which would help resolve running out of locks. It could cause other problems for you though.
0
 
bretCommented:
The problem with using table locks is that can prevent concurrent access to data in the table - however, if your process is using up all available locks, it is probably locking most or even all the table anyway.

As Joe said, the application may well be sending somewhat different sql to the server.  You can have a look at what is being sent by using the MDA tables monProcessSQLText and monSysSQLText.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now