JDBC Sender - ORA-08177 on UPDATE, but Table is updated - no message

[simon_bredel]

Hi,

I have a problem with JDBC Adapter:

I use a JDBC Sender adapter, polling every minute on an Oracle DB.

I use isolation level "serializable".

No what happens sometimes is, that the update statement fails because of "ORA-08177: can't serialize access for this transaction", so no message is forwarded to the IS. Curiously, the entries in the table are updated, so the JDBC Driver seems to update the data anyways.

Has anybody experience with similar problems? This is a really hard issue because no messages are transmitted althought the condition field in the table is updated and everything seems normal. Also the status light of the communication channel will be green again after a while, so it is really hard to trace.

System:

XI 3.0 SP20

ORACLE 8.1.7.4

I use the following statements with a rownum limit:

select ... from TABLE where CONDITION = '_to_send' AND rownum <= 20 order by NACHRICHT_ID

update TABLE set CONDITION = '_sending' where PRIMARY_KEY in (select PRIMARY_KEY from TABLE where CONDITION = '_to_send' and rownum <= 20)

(just to complete the picture...)

This message goes to BPM, where it is split into single messages. These messages are processed separately, and after an successfull processing an update statement is sent back to the DB:

update TABLE set CONDITION = '_processed' where PRIMARY_KEY = <PK of processed message>

So the condition "_sending" is an interim status for the messages sent to BPM, but not finally processed.

What happens is, that the receiver of the messages complained about missing messages. I could find some messages in the interim status, but no message has ever been sent.

I appreciate any help.

Thank you very much.

[bhavesh_kantilal · Accepted solution]

Well I am not too sure, but can you bring transaction level to Default?

XI will handle this implicitly and make sure select and update are in the same atomic transaction.

Maybe your DB has some issues when you make it Serializable.

Also, ask DB team to turn trace on to see what they get in their dumps. And check XI's j2ee logs for info.

Regards

Bhavesh

[simon_bredel · Accepted solution]

>

>

> Well I am not too sure, but can you bring transaction level to Default?

> XI will handle this implicitly and make sure select and update are in the same atomic transaction.

>

That was the way I tried in first place. This is not an atomic transaction. The JDBC adapter updated newly added rows (rows that were added between the time that the SELECT and UPDATE queries were executed) that were not yet transmitted. This problem made me switch the transaction level.

See also [SAP Note 831162 - 8. Transaction Handling (Sender)|https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes/sdn_oss_bc_xi/~form/handler%7b5f4150503d3030323030363832353030303030303031393732265f4556454e543d444953504c4159265f4e4e554d3d383331313632%7d]

>

> Maybe your DB has some issues when you make it Serializable.

>

> Also, ask DB team to turn trace on to see what they get in their dumps. And check XI's j2ee logs for info.

>

Maybe, but it works almost everytime. So generally, serializable seems to work with the DB. But I will try to contact my DB experts and check out if there are any traces that we can activate.

Thank you very much for your input.

[simon_bredel]

The AE is clustered in four server nodes. Could it be that the sender channel does start simultaneously on several nodes and causes trouble that way?

[Former Member]

Hi Simon,

did you solve this one?

I am facing a similar Problem.

Thanks

Matthias

[Former Member]

As you have used the Isolation level as serialiable whenever the transaction fails the data gets updated because once the transaction reaches the end of the adapter level processing " the update statemnets gets executed " Irrespective of whether the transaction is sucessful or failure."

Even I faced a similar problem ...

Try to change the Isolation level and get the message id generated using an UDF.

[simon_bredel]

As you have used the Isolation level as serialiable whenever the transaction fails the data gets updated because once the transaction reaches the end of the adapter level processing " the update statemnets gets executed " Irrespective of whether the transaction is sucessful or failure."

Curiously, the select statement does not throw the error, the update statement does. But even though the update statement does not work (ORA-08177), it works (rows in DB are updated). That is what causes headache to me.

Try to change the Isolation level and get the message id generated using an UDF.´

Sorry, but I do not completely understand what you suggest here.

My needs are:

- select rows that fulfill condition (limit to a max number per poll interval)

- update the selected rows to not fulfill the condition any more (to not double sent data)

- the update statement must update exactly the same rows that have been selected before (transaction)

or in other words:

I need to make sure the data is sent only once and exactly once.

If your idea can satisfy these needs, I kindly ask you to explain it more detailed.

Thank you very much.