We have a process which selects a record from a progress table, then issues an update against that record.

If we do not do the select, then the update works all day long. If we do the select, then the update times out.

The select query is pretty simple and looks like:

select fg."alphakey", n."first-name", n."last-name"
from pub.name n
inner join pub."family-guardian" fg
  on (fg."name-id" = n."name-id")
where fg."alphakey" = 'somevalue'

We've confirmed that if the above statement is not present then it works. However, if the above statement is present then the UPDATE fails. The update goes against the name table. Unfortunately, I don't have that code as it executes through a third party.

Two questions:

  • Is this normal for progress?
  • What is the best way to get around the issue?
  • I guess this is caused by a very limited locking model (readers block writers and writers block readers). Microsoft, Sybase and older DB2 versions behave the same way. It's easier to implement but really limits concurrency (and scalability). I don't know Progress. The usual workaround is to put the result of the SELECT statement into a temp-table or run that select with auto-commit enabled. Sep 25 '12 at 6:27
  • 1
    Is there any chance that someone may be connecting to the table through MS Access or programmatically via ODBC? These can cause table locking also.
    – The Dude
    Mar 20 '14 at 14:39
  • It was ODBC. I'm answering this below
    – NotMe
    May 6 '14 at 16:56

If dirty reads is not an issue in this scenario, you could try the table hint readuncommitted or nolock..

pub.name n WITH(NOLOCK)

join pub.[family-guardian] fg WITH(NOLOCK)
  • 1
    I guess I'm confused as to why the row would be locked to begin with. The select goes first, then the update. Also, even if it was locking against writes, why the delay? The first transaction is purely a select. So I would expect any locks obtained to be released when the transaction is done.
    – NotMe
    Sep 24 '12 at 19:16
  • 3
    Does Progress support with (nolock)? I though that was a Microsoft (Sybase) only thing. Sep 25 '12 at 6:25

Use 4GL

I think the best way to get around this locking issue is to use 4GL (sometimes called ABL). In 4GL the magic command is NO-LOCK and you use it after each table where clause. Sadly, 4GL does not support the SQL shortcut names that make joins handy. That said, the EACH ... OF syntax sort of replaces it - if your indexes are properly defined. If not, just remove the OF and put your condition in the where clause. Just be prepared for slower execution.

FOR EACH pub.name
  EACH pub."family-guardian" OF pub.name
    WHERE alphakey = 'somevalue'
  DISP alphakey, "first-name", "last-name".

We were executing our queries through the Progress OpenEdge ODBC drivers.

The Default Isolation Level was set on the driver to be Read Committed. We had made this choice not realizing that SELECTs caused some long running table locks, when all we were after was to make sure that we weren't getting any dirty reads.

So, we had two potential solutions. The first would be to modify all of our queries to use with (nolock) as suggested by Michael. The second was to change the ODBC driver to be Read Uncommitted.

As the driver setting was far easier to manage, we were going to be forced to us dirty reads no matter what and the only thing our application does is SELECTs off of that database, then our choice was clear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.