[JDBC Sender] Send command LOCK table before SELECT statement

[former_member296836]

Dear all,

corresponding to my problem with rows that get inserted [before my update is executed|; the DB admin told me to send following command:

LOCK TABLE <tablename> IN EXCLUSIVE MODE

before I run the SELECT statement.

Is there any way to do this in JDBC sender adapter?

Regards

Chris

[stefan_grube]

I think you can write several statements in the select line.

I have not done this so far, but I use several statements in the update line in a life scenario, so I do not see a reason why this should not work for the select line also.

Maybe you could run a test?

[former_member296836]

I just did a test, but it doesn't work.

How did you seperate the commands? I used ; for this and it doesn't work.

Regards

Chris

[stefan_grube]

nothing, just space.

But I work with MS SQL. So I tested the command sequence in MS SQL studio first to see how it works.

Is there anything similar for Oracle DB?

[Former Member]

Hi

pls use select for update command insted of plain select ,When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records "for your changes only" as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.

ex SELECT * FROM <tablename> FOR UPDATE

[former_member296836]

Dear Sunil,

but SELECT FOR UPDATE just lock the rows I selected.

My problem is, that rows could be inserted that I did NOT selected!!!

So I can't use this.

@Shabarish

I can't use another level because the next lower level (Repeatable Read) allows phantom read. This I want to avoid.

Edited by: Christian Riekenberg on Aug 30, 2011 3:03 PM

[former_member296836]

Dear all,

still having same problem.

Does anyone how to analyse if the transaction isolation level is set?

Can we see this in log file of Oracle??

I think we have a problem with setting the isolation level (privilegs?) but

could not see the logs of Oracle. How to figure out such a problem? Do

we see anything in SAP PI logs?

Or from another point of view: What kind of privilegs does my Oracle

user need to be able to set the transaction isolation level???

Regards

Chris

Edited by: Christian Riekenberg on Aug 30, 2011 2:41 PM

[baskar_gopalakrishnan2]

LOCK TABLE <tablename> IN EXCLUSIVE MODE

before I run the SELECT statement.

Is there any way to do this in JDBC sender adapter?

No standard data structure for the above command. Only possible way is doing stored procedure to wrap lock table and select in one query.

[former_member296836]

Dear all,

- Serializable: seems not to be supported, because even if I select this, probllem occurs again

- Stored procedures: not supported by Oracle. So no option in my case.

So any other hints?

Regards

Chris

[Former Member]

What do you mean by Stored Procedures not being supported on Oracle? SPs are programs (on Oracle, PL/SQL) that contains database actions to be performed, most databases support this, like PL/SQL for Oracle, T-SQL for MSSQL Server, etc.

[baskar_gopalakrishnan2]

Oracle always supports stored procedure. Where did you get this information?

[former_member296836]

Dear all,

thanks for your replies.

Did anyone of you already use stored procedures in a JDBC sender channel?

Following SAP hint 941317 there are a lot of versions of DB and JDBC drivers where

the usage of stored procedures is not possible. Only point 6 in this hint seems to allow

a usage.

So wich versions (DB and JDBC driver) do you use?

Regards

Chris

[Shabarish_Nair]

Dear all,
> 
> - Serializable:  seems not to be supported, because even if I select this, probllem occurs again

Serializable is the highest option. you can try other options that is supported by the DB

[Former Member]

Dear Christian,

i suggest to have a close look at the oracle documentation for these szenarios (e.g. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i5702). We had exacly the same problem like you and solved it using the "serialized" - Mode.

When it works you will even get alerts from the jdbc adapter for the cases that that trancaction could't be serialized. (ORA-08177: Cannot serialize access for this transaction ).

I don't think that your oracle database does not support serialized mode. If you are not sure get in touch with your database experts. Perhaps they can also trace your XML statements and should find somthing like "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"

If it's still not working consinder from the link above:

"Serializable isolation permits concurrent transactions to make only those database changes they could have made if the transactions had been scheduled to run one after another. Specifically, Oracle permits a serializable transaction to modify a data row only if it can determine that prior changes to the row were made by transactions that had committed when the serializable transaction began.

To make this determination efficiently, Oracle uses control information stored in the data block that indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE.

Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a too recent transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block."

Again I would suggest to discuss these things with an oracle DB expert.

Best regards

Florian

[former_member296836]

Dear Florian,

thanks for your reply.

Unfortunatelly, the DB admin does not know how to trace it. Sad but true

So we used a workaround. I pick up all messages with status 0.

The other partner insert new rows with status 2.

In an intervall he checks if there are rows with status 0. If not, he change the

status of new lines from 2 to 0 and the messages will be picked up by the

JDBC adapter after this. Status will be set to 1 after I picked up the lines.

regards

Chris

[Former Member]

You can run it inside a Store Procedure, as per the Query Select description on the help page below:

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

http://help.sap.com/saphelp_nwpi71/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Also, if you use the "Serializable" mode for transaction isolation, would that still be required? Check "Transaction Isolation Level" section of the help page above.