Data Inconsistency during JDBC Adapter Select and Update Query

[shankul_saxena]

Hi All,

I have query regarding the execution of SELECT and UPDATE query in sender JDBC channel. Consider an example below in a table with 100 records and flag= unread:

1. At 10:00:00 AM, SELECT query reads the 100 records with flag='unread'

2. At 10:00:01 AM, some program has inserted 10 new records with flag='unread'

3. At 10:00:02 AM, Update query executed and updated the 110 records with flag='unread'

This will leads to the data inconsistency as it has to be updated only 100 records but it is updating 110 records. How can we ensure that Update query only update those records which were read by the SELECT query.

Regards,

Shankul

[former_member190293 · Accepted solution]

Hi Shankul!

831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 / PI 7.3 JDBC Adapter:

8. Transaction Handling (Sender)

Q: If I have the following configured in a JDBC Sender Select Query: SELECT column FROM TABLENAME WHERE FLAG = "TRUE" Update Query: UPDATE TABLENAME SET FLAG = "FALSE" WHERE FLAG = "TRUE" How do I know that the JDBC adapter will not update newly added rows (rows that were added between the time frame which the SELECT and UPDATE queries were executed) that were not read in the initial SELECT query?

A: The SELECT and the UPDATE are run in the same DB transaction, i.e. both statements have the same view on the database. Make sure that both statements use the same WHERE clause. An additional requirement for the correct operation of this scenario is the configuration of an appropriate transaction isolation level on the database (i.e., repeatable_read or serializable). You might also consider using a "SELECT FOR UPDATE" statement instead of a plain SELECT statement to ensure proper locking on the database. "SELECT FOR UPDATE" is not supported in MS SQL database. In this case please make use of an appropriate transaction isolation level on the database. For more details please contact your DB vendors. If your source database is DB2 for IBM i, please read SAP Note 2295028.

Regards, Evgeniy.

[apu_das2]

I have faced this issue previously in one of our project.

Solution is -

1. Use Serializable in the adapter.

2. The JDBC table from where you are fetching data should use INITRANS and ROWDEPENDENCIES clause at the time of table creation.

This will ensure your data consistecy.

Thanks,

Apu

[nidhi_srivastava22]

Hi Shankul,

Agreed with Evgeniy & Apu.

1) In your channel ->advanced tab -> "Transaction Isolation level" set to serializable.

2) Make sure your WHERE clause is same for SELECT and UPDATE statements.

Thanks,

Nidhi Srivastava

[shankul_saxena]

Hi All,

We are using the approach to select the records from the database and update the flag to P(Processing) and post an Idoc.

Once the Idoc gets posted, an ABAP proxy will collect all primary keys from line items of Idoc and send it to PO. PO will create multiple update queries and send them in a batch mode for execution.

In response, there can be a mix of successful and unsuccessful records.

For successful records, proxy will not send data for successful ones and only send for error records as E(error) in a flag along with error description in a field. PO will update those error records in database along with error description so that they can be corrected before next run.

Regards,

Shankul