cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Sender processing on Oracle

Former Member
0 Kudos

We have a runtime situation where the sender adpater issues an update on rows that have not been selected on the select clause... ! it operates outside the LUW (transaction). I have tried both Serialization, read_commited isolation levels

Query SQL statement = select * from <t_tranfer> where transfer_status = 'R'

Update SQL statement = UPDATE <t__transfer> set status = 'S' WHERE status = 'R'

The problem occurs occassionally when the select takes a few seconds to reach PI and then by the it comes back to the database to issue the UPDATE, there are already other rows inserted with 'R' that inadvertently get reset to 'S' also ... thence these later ones never reach PI and processed further...

Any ideas on how address this runtime behaviour? Thanks,

Mustafa

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Mustafa,

Use the below select / update statement:-

SELECT <fields> FROM <t_transfer> WHERE status = 'R' and message_id >= (select min(message_id) from <t_transfer> where status = 'R') and message_id <= (select min(message_id)+ 499 from <t_transfer> WHERE status = 'R' )

UPDATE <t__transfer> SET status = 'S' WHERE status = 'R' and message_id >= (select min(message_id) from <t__transfer> where status = 'R') and message_id <= (select min(message_id)+ 499 from <t__transfer> where status = 'R')

I have taken arbitrary number of records as 500.

This will make sure that only those records are updated which has been selected. Also, put isolation level as serializable in Sender JDBC channel.

Regards.

Former Member
0 Kudos

We eventually discovered that by adjusting the "isolation level" to Serialization AND including the primary key on the Select Query, the correct Updates are getting applied...

Since these errors occur rarely - will need monitor over time to gauge whether it's a permanent fix

Thanks everyone for valuable input

GabrielSagaya
Active Contributor
0 Kudos

check mughda's reply