Skip to Content

JDBC sender transaction level serializable

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jul 07, 2011 at 03:04 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 07, 2011 at 03:38 PM

    >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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Aug 24, 2016 at 12:47 AM

    Hi Chris,

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

    Regards,

    Siva.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.