Lock DB /Disable Update when Updating data in DB from JDBC receiver

[Former Member]

Hi Experts,

I have a requirement where the database is being used by more than one application on the legacy side,

So If I have to update the data from SAP thru SAP PI JDBC adapter into the same database.. is there any way  can lock the db or any other method hich will not allow addition to the table at the same instant?

And allow the update on the DB from the legacy side once the JDBC rcvr adapter has completed the task of updatign the data?

pls share your thoughts..

Regards,

Teja

[MichalKrawczyk · Accepted solution]

Hi,

can you use the isolation level setting in JDBC adapter ?

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

or do you need anything else ?

more on isolation levels:

http://en.wikipedia.org/wiki/Isolation_(database_systems)

Regards,

Michal Krawczyk

[Former Member · Accepted solution]

Thanks Michal,

This I assume will make sure that when PI JDBC rcvr is writign data to a table.. If I select the highest level of isolation that is "serializeable"... then no other application can read that data or modify that particular row...

Regards,

Teja

[Former Member · Accepted solution]

There is another important aspect, apart from "How to lock". The question is "Why locking" at all. Because that always makes trouble for other applications (e.g. timeouts), and may yourself give lower performance.

I hope these tables are not the transactional tables, but some kind of interface/staging tables which are not used by the operational users.

In your case, what is the reason for the locking question ? You want to make sure that you update the right data ? That can be normally solved with the right selection criteria.

Or you want to make sure the legacy application does not see your data until you are finished ? This is simply guaranteed by the transaction commit behaviour (set auto-commit to false).

The isolation-level defines what you see from the other transactions, not vice versa.

CSY

[Former Member · Accepted solution]

Hello Christian,

These are staging tables...

The scenario is as follows :

SAP data flows to Legacy DB, this DB is used by the hoe grown front end ...

If the front end operation delivers a call to the DB for the particular data whilst sap is updating the data in the DB in the absence of a lock ther emight be a display of incorrect data..

instead what we plan to do is give an error on the front end stating a lock and then a retry after a few seconds would successfully retreive the updated data from SAP ...

Would you suggest any work around for this?

Regards,

Teja