For update issue

[Former Member]

I got a scenario in which i update ecc by pulling data from database.

For that i am using a BPM.

while pulling data, i got a select with join to get data from couple of tables...Then immediately i update the selected rows by changing flags to show rows are selected.

But before update triggers, my database is added with couple of new records. So delay makes to updated newly added records too.

So basically i update unselected records.

If i can use for update in select, i can slove this issue.

But select is a join sql, so i can't use for update.

How can i solve this issue?.

[Former Member · Accepted solution]

hi .. though i dont know exactly...u can logically lock the selected rows by means of flags... so that "update" wont change any unselected records...

pls query if doubt

[Former Member · Accepted solution]

Here is more details...

i got a select with joins.....

then i got a update...which is used to update selected rows.

-

---

records get inserted to database.......

-

---

there is a delay in my select and update.....

So unselected rows gets updated....

-

---

i can solve this easy by using for update.....but i got a join in select...so i can't use for update.

-

---

solution?.

[Former Member · Accepted solution]

Can you send your Select and Update SQL statements??

Are you using stored procedure or dynamic SQL for your select and update.

Satish

[Former Member · Accepted solution]

see i have a logical solution ...try to implement it and do share ur experience...

before selecting particular rows.........set the flag of those rows with say "going_to_update"...then select those rows ...... and while updating check if these rows hava a flag "going_to_update"... and update it only if its the flag is "going_to_update"..... and then u set the flag "updated"

pls pardon me if the logic seems absurd..

pls query further

[bhavesh_kantilal]

Hi,

Which adapter are you using Sender or Receiver?

From you question, I think a Receiver, but still not sure.

Anyways, coming to the question, in your Sender / Receiver JDBC adapter , select Adavanced Mode and under Transaction Isolation Level , Select Repeatbale Read or Serializable. This will solve your problem.

<i>Transaction Isolation Level

There are different levels of database transactions known as isolation levels. The isolation level determines how transactions running in parallel can influence each other. The options correspond to the JDBC constants:

&#9679; Default (default setting of the respective database)

&#9679; None

&#9679; read_uncommitted (weakest setting)

&#9679; read_committed

&#9679; repeatable_read

&#9679; serializable (strongest setting)</i>

http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards

Bhavesh

[Former Member]

thanks bhavesh.

I am using JDBC adapter as Sender.

Please elaborate....i want to learn all possible ways solve this issue.

Message was edited by:

Ash

[bhavesh_kantilal]

My previous reply holds good for Sender JDBC adapter also.

The transaction Level if set to either <b>Repetable_Read or Serializable</b> will esnure that the database gets locked between Select and Update query getting exectued on the Sender JDBC adapter. This way no new data will occure between Select and Update.

Regards

Bhavesh