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
gpw7368Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java App Servers

From novice to tech pro — start learning today.