on 08-26-2010 4:29 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.