Select statement is locking a record

Question

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?

score 1 · Answer 1 · edited Sep 25 '12 at 05:21

1

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)

edited Sep 25 '12 at 05:21

marc_s

9,052
6
46
52

answered Sep 24 '12 at 16:14

Michael

113
1
5

score 0 · Answer 2 · answered Mar 20 '14 at 14:32

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.

score 0 · Accepted Answer · answered May 06 '14 at 17:01

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.

Select statement is locking a record

3 Answers3

Use 4GL