Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
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. –  a_horse_with_no_name 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 at 14:39
    
It was ODBC. I'm answering this below –  Chris Lively May 6 at 16:56

3 Answers 3

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)
share|improve this answer
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. –  Chris Lively Sep 24 '12 at 19:16
3  
Does Progress support with (nolock)? I though that was a Microsoft (Sybase) only thing. –  a_horse_with_no_name 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
  NO-LOCK,
  EACH pub."family-guardian" OF pub.name
    WHERE alphakey = 'somevalue'
  NO-LOCK:
  DISP alphakey, "first-name", "last-name".
END.
share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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