We help IT Professionals succeed at work.

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 :

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
Watch Question

Principal Consultant
Most Valuable Expert 2012
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.
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.



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
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

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.

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.