Protop SQL activity - Rec Lck

[Den Duze]

Hi,

We are experimenting with SQL-access to our database and I started Protop SQL activity to look what is happening.
There I see after a request that the "Rec Lck" shows some value (combination of all Reads on the Tables).
Is that normal?
Does SQL always locks the records that it reads? What kind of lock is that?
Is that the same behavior as I should do the same query from Progress with no-lock?
Can I see that also somewhere in Protop? (So the same value under some "Rec Lck" column in some ProTop Screen)

 

[Stefan]

See the (default) isolation level on your ODBC data source. This can be:

0 - read uncommitted
1 - read committed
2 - repeatable read
3 - serializble

0 is the only level that does not attempt to lock anything.
1 is the default, this will attempt to lock a record to see if it has been committed and then release it
2. is a nightmare, this will lock the entire set
3 I think this is similar to 2

 

[Den Duze]

So the default is lock every record to read and then release the record again?
Isn't that different then what native Progress does?
So that is the reason why there exist some (no lock) to be added to the sql-string so the read does not stop/quit on a records that is locked by some other session (or I'm i wrong here)

 

[Stefan]

I'll just let the knowledge base do the talking: Progress KB - Transaction Isolation Level: Proper Syntax and Definitions

SQL access is very different from native access:
- any triggers you have defined on your database will also not fire
- index selection is different (cost based) (see Progress Customer Community)
- field data must fit the field format (see Progress KB - SQL-92: SQL Width and 4GL Field Formats)

 

[Den Duze]

thanks for the information.
Already runned against those 3 problems but now I also know the reason and if it's expected behavior

 

[Rob Fitzpatrick]

Can I see that also somewhere in Protop? (So the same value under some "Rec Lck" column in some ProTop Screen)

From context I'm not sure what you mean by "that". Can you please elaborate? Thanks.

 

[Den Duze]

I just saw that the SQL view shows multiple Record locks (Rec Lck).
As far as I know you do not have those when you make the same query from Progress (when using no-lock).
So maybe I'm wrong and Progress also makes those locks and then I wonder in what view I can see that

 

[TomBascom]

You are correct.

When a 4gl client executes a NO-LOCK query there are no record locks.

SQL-92 is different and if you don't provide the locking hints and set the isolation level appropriately you will get record locks.

You will also often see that SQL clients have an active transaction.

 

[tamhas]

One might note that the default in ABL is share-lock and one has to explicitly say "no-lock".