Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: Setting isolation in select

    Hello all.

    Here is my scenario:

    Informix Driver 3.82.0000 2.81.TC2

    --I cannot update this driver, large amount of stations, hard apps,.... --

    I've got a select in a odc connection and this select returns me a lot of locked rows.

    So I must run this select in dirty read level and I cannot modify any value of the connection so the isolation leves has to be set inside the select.

    The select syntax is like this:

    {SET ISOLATION TO DIRTY READ} Select field1, field2, field3, case when field4=value1 then value1 else value2, (select field5 from othertable where othertable.field1=value) and so on (with several case-like fields)....
    from table1
    inner join table2 on field6=field7
    inner join table3 on field8=field9
    and several inner join
    and several left join
    where
    (simple conditions...)

    This select returns me an Error: Could not do a physical-order read to fetch next row

    Anyway the "{SET ISOLATION TO DIRTY READ}" sentence before the select keyword always worked for me but in this select it doesn't and i don't know why.
    I've tried to put "{SET ISOLATION TO DIRTY READ}" before the subselect too, at the joins..., but nothing works.

    Any suggestions?

    Thank you in advance.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    I may be wrong, but why do you state the isolation set statement between { } ?
    Did you try taking the { } off ?

    Set isolation is valid for the SESSION running, therefore the sub queries should understand it.

    did you try running the same query from dbaccess?
    Are you monitoring the locks, in order to check where your query is hitting against a lock?

    Eric

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •