Enqueue Server Locking versus DB Locking

[Former Member]

Hello everyone,

I have some doubts about locking in the WAS.

According to help.sap.com there are two ways of locking data in a DB table:

1. Use the Enqueue Server;

2. Use DB locking.

I have developed a Session Bean which has a simple method to always update the same DB table record.

Then I developed a client (portal app) invoking the bean method within a loop:

Something like:

for (int i=0; i<10000; i++) {
  //Call bean method to update always the same table row with a diferent number
}

Then I put two clients to run concurrently to see the result;

And the result was:

1. About 50% of LockExceptions when I was using the enqueue server (using JDBC to get the connection and using the TableLocking API to lock the data).

2. 0% of locking collisions when I was using database locking.

The point is:

I am doing something wrong?

It seems that when locking with the DB (even with the lowest isolation level) the updates are much more faster than using the Enqueue Server since no collisions occur with DB locking and about 50% of collisions occur with the Enqueue Server.

What are the advantages of using the Enqueue Server then? Only for portability reasons?

Furthermore, in the case I am developing an application without user interaction, how should I deal with the LockExceptions? By trying again after some period of time? In that case, can you suggest a good/best alghoritm to implement that delay inside a session bean business method (as for my low understanding of EJBs one cannot manipulate threads inside a EJB, isn't it?)?

Thanks a lot for your time,

Diz

[Former Member · Accepted solution]

Hi, Diz!

How do you access the database - relational or via JDO? Do you use Open, Native or Vendor SQL?

Did you already have a look into the <a href="http://help.sap.com/saphelp_nw04s/helpdata/en/5b/5eea3d5506fb34e10000000a114084/frameset.htm">Locking Adapter Service</a>?

Enqueue Service should provide the same locking comfortability as developers are used in ABAP - business transaction spanning, vendor independent a.s.o.

Regards,

Thomas

[Former Member · Accepted solution]

Hi Thomas,

Thanks for your answer.

To answer your questions:

> How do you access the database - relational or via JDO?

Relational

> Do you use Open, Native or Vendor SQL?

Vendor SQL

There was something wrong in my earlier post which is in the case I am using DB locking, lock collisions also occur but much less than when using the Enqueue Server.

What I find odd is that such a high percentage of lock collisions occur when using the Enqueue Server. It seems that the performance is bad.

In ABAP when I wanted to lock records on a table I created a lock object and than one could lock simply by calling a lock function module which had a parameter "wait=X";

In java I don't think there is such a mechanism, right? If we receive a lock exception we have to somehow put the thread to wait for some time but how should one do this with a session bean? By using some kind of while loop and checking the system time?

Thanks

Diz

[Former Member · Accepted solution]

Hi Diz,

The isolation level controls the level of the db locking which will apply to the bean.

The different database has different default isolation level which affects the performance. For instance, some has Serializable level as the default.

JDBC also can specify the isolation level. Especially the vendor SQL is used. For instance, in Oracle, if you use "select ... For Update", it will lock the involved row or you can change the transaction isolation level by set the level with java.sql.Connection.setTransactionIsolation().

Dennis

[Former Member · Accepted solution]

Hi Dennis,

Thanks for your answer.

Yes, what you say is true but how do you usually deal with lock exceptions when one instance tries to update a DB record which is already locked by other instance (suppose it's a background program without user interaction so you can not display an error message to the user)?

Thanks,

Diz

[Former Member · Accepted solution]

You will get an exception if you set the timeout for the waiting when a db lock occurs. Retrying is not a bad idea.

Actually personally I think the above case should be prevented in the first place because it indicates there is a design or implementation issue. For instance the database access should be very short and no transaction should hold the lock for more than a fixed time. This is a performance issue needed to be adjusted on BOTH the app and the database end. Especially it implies there may be programatic defects in the code.

I like and use the proactive solution. I am using an ORM tool to do the necessary checking before committing. For example, in a transaction of updating an employee's salary, the transaction first gets the salary and does the adjustments (hopefully it's an increment!:-) and before committing the change to the database, the transaction checks the salary on db and if anything is different from the one it holds, an exception is thrown out. By doing, it reduces the db lock (the lock only occurs when the actual databate commit happens. There is no need holding the record durning the transaction).

Hopefully I was not going so far.

Dennis

[Former Member]

Hi Diz IsMi,

Can You just let me know what kind of session bean you have used, for Tablelocking API, stateless or Stateful.

As I have also tried to use TableLocking API to lock Particular Row using Stateless Session Bean with Lifetime as User session but as it is stateless so no usersession is maintained, and record is permenently locked in the Table if it is not unlocked explicitly.

Can tell me how you have implemented this feature.

Thanks in Advance,

Shruti.