Locked Record.

[sreenathk]

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.

Thanks,
Sreenath

 

[doom1701]

I just did my first ASP.net page that connects to our Progress back end. We had some trouble with locks a while back, so I decided to split out my reads and writes with separate DSNs. I've got a read only (Read Uncommitted) DSN, and a RW (Read Committed) DSN.

Now, the real question I have is how the error can be handled--even if you separate out your DSNs, you still need to update that record at some point. Assuming that you can trap the error from the locked record when you try to update, you could generate a message for the user--or just do it the way most Progress developers seem to do it and just sit and wait...

 

[sreenathk]

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

 

[rajeshdsadalkar]

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

 

[RealHeavyDude]

Change the transaction isolation level to read uncommitted.

Heavy Regards, RealHeavyDude.

 

[Marian EDU]

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

[rajeshdsadalkar]

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

 

[Marian EDU]

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

SELECT * FROM customer WITH (READPAST NOWAIT);

 

[rajeshdsadalkar]

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.

 

[Marian EDU]

did you considered to change the order of where and with?

or, maybe even better you should have a nice read of the manuals... http://documentation.progress.com/output/OpenEdge102b/pdfs/dmsrf/dmsrf.pdf

 

[rajeshdsadalkar]

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