Skip to Content

[JDBC Sender] Send command LOCK table before SELECT statement

Dear all,

corresponding to my problem with rows that get inserted [before my update is executed|JDBC sender transaction level serializable; 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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Jul 08, 2011 at 01:28 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 08, 2011 at 02:04 PM
    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Aug 30, 2011 at 12:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 30, 2011 at 12:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Aug 30, 2011 at 01:36 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded