odbc locks connection progress

por | 27 septiembre, 2013
0 comentarios

Hi All,

We are connecting to the Progress Database from ASP.NET web application using the Progress ODBC Driver.

The isolation Level is set to READ COMMITTED IN THE Driver Properties.

We are trying to retreive the data based on certain conditions from one of the web pages.

However, if the record is locked in Progress by some other application we are not able to retreive any data and it gives the following error

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Failure getting record lock on a record from table.

what are the different options or solutions where we can read the data from the progress database.

If I change it to «READ UNCOMMITTED» then the select query works because there is no lock defined on the tables, but the insertion or updation fails for this instance.

 

  1. Well

    I was able to do this

    Driver properties are still READ COMMITED
    and I changed the SQL select statements to

    SELECT COLUMN_NAME FROM TABLE_NAME WHERE FIELD_NAME=’123′ WITH(NOLOCK)
    AND now if though the record is Locked by some other application it would still read the record.

    Yes we thought about creating 2 DSN’s one with READ_Commited and other one with Read_Uncommited. Since this solution seems to be working would be sticking on to One DSN

    Thanks,
    Sreenath

  2. rajeshdsadalkar

    rajeshdsadalkar New Member

    Hi Guys,

    I am facing same type of issue. I am just reading records from progress database using ODBC drivers.

    ODBC isolation properties –> READ COMMITED
    SELECT COLUMN_NAME FROM TABLE_NAME WITH(NOLOCK)

    Whenever there is lock in progess , i got expection in my c# code. I don’t understand how to handle this, please advise…

    rgds,
    Rajesh

  3. RealHeavyDude

    RealHeavyDude Well-Known Member

    Change the transaction isolation level to read uncommitted.

    Heavy Regards, RealHeavyDude.

  4. Marian EDU

    Marian EDU Member

    regardless of the DSN settings the transaction isolation level can be changed anytime with SQL statements…

    Code:
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  5. rajeshdsadalkar

    rajeshdsadalkar New Member

    Thanks for reply.

    I am intested in commited data from progress so i can’t change isolation level to ‘Uncommited’ , Any other way to avoid lock without change isolation level.

    rgds,
    Rajesh

  6. Marian EDU

    Marian EDU Member

    Then you can try READPAST hint to skip the records that are locked instead of waiting for transaction to complete… this works for READ COMMITED so no dirty records for you :)

    Code:
    SELECT * FROM customer WITH (READPAST NOWAIT);
  7. rajeshdsadalkar

    rajeshdsadalkar New Member

    Thanks lot.

    SELECT * FROM customer WITH (READPAST NOWAIT)

    Looks like thisa will work but where i am adding where condition it is giving sysntax error with hint. Any idea why where clause is not working with hint in progress.

    SELECT column1 FROM customer WITH (READPAST NOWAIT) where (column1 > 10)

    Please let me know.

  8. Marian EDU

    Marian EDU Member

  9. rajeshdsadalkar

    rajeshdsadalkar New Member

    changed order with and where clause , it work. Thanks lot.