JDBC sender transaction level serializable

[former_member296836]

Dear all,

we have the problem, that between SELECT and UPDATE statement new rows were inserted into table.

So we set the flag PI_Status from 0 to 1 to more messages than we have picked up with SELECT.

I set the transaction level to serializable, but the problem still seem to exist.

Can anyone tell me, if the lock of the table is only for select or for the combination of SELECT and UPDATE.

In doc I found following:

The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You can ensure this is the case by using an identical WHERE clause.

and

*serializable

You must only lower the isolation level where necessary and as far as necessary. To avoid data inconsistencies in the database when the isolation level is lowered, ensure that multiple database transactions cannot access the database simultaneously.*

But even if I use serializable, the database seems not be locked.

Any suggestions?

Thanks

Chris

[Former Member]

Hi Chris,

Can you please let me know if resolved this issue? i am facing same issue.

Regards,

Siva.

[baskar_gopalakrishnan2]

>Can anyone tell me, if the lock of the table is only for select or for the combination of SELECT and UPDATE.

Yes, it will lock your table for the combination of select and update. For every message it will treat the combination as one transaction

Setting Isolation level = serializable should make you data consistent. But if your jdbc drivers does not support transaction then try ticking the option database auto commit enabled. See how that helps.

[former_member296836]

Dear all,

how to determine if the JDBC driver supports serializable???

Is there a way an admin could check in which mode we do the select statement

on database?

Regards

Chris

[Former Member]

Hi Chris,

See if you can Execute a Stored Procedure in Sender JDBC Adapter, where you get an exclusive lock to those records which you are selecting and updating. This could be instantaneous, without any time gap between Select and Update.

"Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement "

@ [Configuring Sender JDBC Adapter|http://help.sap.com/saphelp_nw73/helpdata/en/44/747eda12dd3676e10000000a114a6b/frameset.htm]

Regards

Praveen K

[former_member296836]

Unfortunatelly it looks like, we are not able to use stored procedures if the database is

Oracle like it is in my case.

But as mentioned in the blog: /people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

we can use table functions. What is the difference to stored procedure?

Is it possible to execute a SELECT and a UPDATE statement in same table function?

EDIT: Serializable does not seems to be supported by DB. Because I selected this isolation level

on advanced tab, but there was still a row that was updated even the select statement

does not picked it up.

Regards

Chris

Edited by: Christian Riekenberg on Jul 8, 2011 4:05 PM

[Former Member]

Hi,

Did you use rownum and ORDER BY in the SELECT or UPDATE statement? if not give it a try.

SELECT *

FROM <table>

WHERE rownum <= 100

ORDER BY <column>;

Regards,

Francis

[Former Member]

Yes you are able to use SPs. It won't work ONLY if you use the JDBC driver for Oracle Database 10g version "10.1.x ". In case you have jdbc driver on 10.1.x, it is recommended to update.

Table functions are used to create custom tables on the runtime, which is not required on your scenario.

The information from the mentioned thread was retrieved from the note below:

941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

"5. Oracle Database 10g 10.1.x JDBC Driver

The JDK 1.1.x, 1.2 and 1.3 versions (classes111.zip,

classes12.zip, classes12.jar) of the driver are not compatible

with the SAP XI JDBC Adapter. Use the JDK 1.4 driver

(ojdbc14.jar) instead. For details, refer to Oracle MetaLink note

1. 203849.1.

Invoking Oracle stored procedures from within a JDBC sender

channel is not supported as Oracle does not return a ResultSet in

response to the query."

[former_member296836]

Did you use rownum and ORDER BY in the SELECT or UPDATE statement? if not give it a try.
SELECT *
FROM <table>
WHERE rownum <= 100
ORDER BY <column>;

Dear Francis,

Thanks for your reply, but I don't understand how this should help. If I select 12 messages in SELECT

statement, the rownum won't change anything and the UPDATE function could change 13 messages

(if one was added meanwhile).

Yes you are able to use SPs. It won't work ONLY if you use the JDBC driver for Oracle Database 10g version "10.1.x ". In case you have jdbc driver on 10.1.x, it is recommended to update.
> 
> Table functions are used to create custom tables on the runtime, which is not required on your scenario.
> 
> 
>        Invoking Oracle stored procedures from within a JDBC sender
>        channel is not supported as Oracle does not return a ResultSet in
>        response to the query."

I didn't know the usage of table function. Thanks for this info.

So, iI will have a closer look at the JDBC driver and the version of Oracle DB, but it is said, that

it doesn't work before, because Orcale does not return a ResultSet. Does it change in 10g 10.2.x JDBC Driver ??

Thanks

Chris

[Shabarish_Nair]

we are also using the same and it works for us.

It is DB dependent so it might be a good idea to check with the DB admin if the serialization is supported.