Locked row without error: A bug or a feature?

Knut Dybendahl
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

It depends....

What's the lock isolation level? Is the db set up to allow 'dirty reads'?

Have a look at the manual pages:

Locking Support on Microsoft SQL Server - I have a hunch your solutions is in the driver settings...

Knut

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Knut

Isolation level is set to "rc" (Read Commited)

When I do the SQL by SQL/data I got a -3
When the key is completed I got a -11 after retrieve
But when using a search profile, I got no error at all

Running the SQL-profiler shows me, that a timeout occurred but with no reaction by UnifAce

Ingo

Like

Knut Dybendahl
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Check the MS-SQL client connector - there's a server version and a client version - they behave differently.

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

There is only on connection to database 🙂

Client → ODBC → SQL-Server

I did change the iso from ru to rc (and vis a vis)
Read one row by key and SQL/data react as assumend

But retrieving a hitlist only reads rows until the locked row

The "lock:timeout" when profiling shows me exact 3 secs as set by "mlw=3"

Ingo

Like

Knut Dybendahl
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

sorry - check the odbc definition - there are normally two 'ms-sql' dlls / drivers you can use...

Try swapping them... uf uses the same name - it's the connection from the ms-sql odbc connector

I'm talking about..

Knut

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Sorry, just have a telco about another "problem" 🙂

Knut, do you mean odbc-32 and odbc-64 ?
Or the UnifAce DLLs to connect to ODBC?

BTW:

1. Check the UnifAce DLLs (versions) is a good point, maybe this could solve parts of the problem.

2. Our customers may have diffent UnifAceand DLLs versions running. Not easy to update them 🙂

Ingo

Like

Knut Dybendahl
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Neither - in the odbcad32.exe settings - where you define the odbc datasource for uf to use when you connect

to MS-SQL - there are normally 2 MS-SQL 'drivers'

Try it...

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Is that what you mean 🙂

I can give it a try ...

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Nope, neither "SQL Server" nor "SQL-Server Nativ Client" will solve it

Ingo

Like

Knut Dybendahl
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Knut

Maybe this "snapshot isolation" does it.
But its generate an awful overhead on heavy used databases.
Sometime, my boss and our customers asked for "iso ru" and then I have to explain, why it is not a good idea 🙂

For me it looks like a bug, that UnifAce does not fetch the "lock:timeout" error but cpnvert this into a "end of hitlist" error, which in turn will not give any clue after a retrieve.

@uniface: Any options to fetch such errors?

Ingo

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 10, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Ingo,

Is your locking mode in Uniface on that table set to "Cautious"? I suppose YES.

With an isolation level "Read Committed" and "cautious" locking mode in Uniface the RDBMS is giving back a recordset including last committed version of the record currently under lock.
The lock itself is part of a running transaction not yet committed so it does not make any sense the RDBMS is giving you a signal on it until you will try to change something on that specific record; in this last case is giving you an error translated from Uniface driver to $status -11 or -10.

So, YES...IMHO it's a feature and not a bug.

I give you two suggestions:
1) Let's play with isolation levels as Knut is suggesting but you will end up sticking to "Read Committed" because is the more reasonable compromise.
2) Let's have a look in MS SQLserver to snapshot transaction management, working on top of "Read Committed" isolation level; it's a new feature delivered initially with SQL2008 then stabilized with SQL2012 and later. We ended up configure one of those snapshot in an SQL2012 installation with high concurrency, solving our issues but having as side effect more pressure on TempDB management.

I've made a study on locking modes through various DBMS in 2017 (Oracle / MS SQL Server / PostgreSQL mainly but also DB2 / MariaDB / MongoDB / CouchDb ) and the results were: this is still an area where DBMS differentiate themselves a lot. If you search through the internet you discover the ANSI isolation levels got high criticism and there are discussions running to heavily improve them to solve real user needs. The reference point about best locking criteria DBMS is, as of today, MVCC compliant databases, being Oracle the "leader" of them, having implemented MVCC since the beginning.

Hope it helps.

Gianni

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Gianni

I already switch from "ru" to "rc" and back, just to check how UnifAce behave.
I also did a read/lock and as expected, UnifAce uses "with(rowlock)"

That's all okay

But when I lock a row (from outside UnifAce by UPDATE) and read a hitlist by UnifAce, things going worse.
As expected, the DBMS answers this with a Lock:Timeout on the row in question (say row X).
The stored procedure returns to UnifAce and the hitlist in UnifAce is build with all rows up to X without X

No further notification, that the hitlist is not completed and/or a row is locked.

Say, you want to get the account balance and read all booking sentences with a object id=123 (not primray key)
On database there are 100 rows with this criteria but the 50th is locked by another application or UnifAce session.
What do you get ...

Only the booking sentences 1 up to 49.
No clue, that there is an error or more rows to come

And that's not nice 😔
Ingo

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

With a SQL editor I submit exact the same parameters to the stored procedures then UnifAce does

declare @p1 int
set @p1=-1
declare @p2 int
set @p2=0
declare @p5 int
set @p5=20484
declare @p6 int
set @p6=8194
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output, ....
exec sp_cursorfetch @p2,2,0,1

And the result is:

Msg 1222, Level 16, State 51, Line 1
Lock request time out period exceeded.

The DBMS is returning an error and UnifAce doesn't translate this into $status/$dberror

grumbel

Ingo

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

BTW: To calm down and relax 🙂

>https://www.youtube.com/watch?v=AGfBTRkN0y0

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Ingo,

yes, I agree with you: if the DBMS engine gives back a "occurrence locked" signal to the Uniface driver terminating the select the Uniface driver MUST give back that signal to the application with $status -11.

This could be a Uniface issue...but...this is exactly the point bringing you to a deeper question:
should in your opinion a simple read/select be locked/terminated by an existing lock on a selected row?

If you want to get the higher concurrency from your DBMS the answer should be NO!

When I made my study beginning 2017 the starting point to achieve the highest concurrency possible was defined as:

READ (SELECT) should NOT be locked from any WRITE (INSERT/UPDATE/DELETE).

In other words doing a simple select without any lock involved the DBMS should give us last committed version of ALL occurrences corresponding to where clauses without any lock signal. This definition was made because those locks are part of living transactions that could or could not be committed and ALL other users should be bothered only if they try to change locked occurrences.

I discovered the locking model we were aiming to correspond to the MVCC model; this model seems to be the one major DBMS are supporting or going to support more and more:
- Oracle is MVCC compliant since its inception
- PostgreSQL is MVCC compliant since many years (but with some changes during last versions)
- SQLServer is declared MVCC compliant since SQL2005, using snapshot configurations (read_committed_snapshot and allow_snapshot_isolation)
- SAP Hana, DB2, mySQL/MariaDB are moving towards MVCC model too

At the end of my study we decided to implement read_committed_snapshot on SQLServer; it is regularly working since then but as side effects it required to monitor TempDB size because in the actual implementation SQLServer is maintaining its "version store" into TempDB and sometimes TempDB size quickly grows unexpectedly.

I've checked Uniface drivers on Oracle, SQLServer and PostgreSQL: those specific configurations "MVCC like" are all supported.

This just as a piece of experience.

I hope it helps.

Gianni

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi freaks

Just found an awful bug or a great feature, I don't know ...
Say, there are 500 rows in database under a given profile
Row 200 may be locked by an update on database

Now retrieve all rows in UnifAce with the profile.
UnifAce will read up to 199 rows and then return.
$status is positiv 199 and not -11 for a locked row.

Okay, if the hitlist is shown to a customer, the is maybe a nice thing.
But under the hood as a service programm is very dangerous to go on without notification.

Is this a bug or a feature?
How to check, if there are more rows to come?

And no, I don't want to lock all rows that I read just to check if the hitlist is completed or not

Ingo

PS: UnifAce 9.704 on MS SQL

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Ingo,

I write almost ONLY account software! 🙂

The default isolation level for me/us is "READ COMMITTED", so everyone read only committed updates.

First case:
- the first session is started, the money transfer is compiled, but only when it is stored and committed is updating the bank account balance.
- the second session is reading the committed bank account balance, so there are two cases:
1) if the first session (already started) has not yet stored and committed or it will rollback the compiled transaction it is reading the OLD, already in place, bank account balance
2) if the first session has stored and committed the compiled transaction it is based on the NEW, just updated, bank account balance

#1 Correct? I think yes.

Second case:
With "read committed" the second clerk does NOT read account records loaded from the first clerk until they are committed. So, he/she cannot report to the boss wrong informations!

#2 Correct? I think yes.

I feel you are basing your description on a "READ UNCOMMITTED" isolation level, also because you mentioned "ru" in your previous post. I do NOT think this is the better choice for accounting software.

If this is the case I firmly suggest to move to "READ COMMITTED" as an initial improvement. Note however while doing so there could be some adjustments to be done for a coherent transaction management.

Gianni

P.S.: my relaxing music (used with a very low music level) is:
https://www.youtube.com/results?search_query=spyrogyra
and my preferred album of them is "Incognito" (1982),
containing "Old San Juan" my preferred song... 🙂

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Gianni

We do "Read Commited" at customer site
Just for testing purpose, I switch this to "ru" 🙂

So nobody should see database rows not commited.
The draw back with the way UnifAce handels locks on hitlists without a retrieve profile is, that one not even see commited rows.
And more worse I got no hint about it 😔

Ingo

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Ingo,

Sorry but, I do not fully understand what you mean with this sentence:

"... The draw back with the way UnifAce handels locks on hitlists without a retrieve profile is, that one not even see commited rows. And more worse I got no hint about it 😔"

In Uniface, when a session commit a change, other sessions are seeing updated data:
- after a reload if they tried to change an updated occurrence
- after next retrieve
Yes, Uniface as of today has no broadcasting mechanism to automatically distribute "on the fly" updated occurrences. I've seen this mechanism in place on other tools.

Gianni

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

It's the core of the problem 🙂

Say, the primary key is FLD_01 and FLD_02

Populate the table with FLD_01=4711 and FLD_02 = 1 up to 99

Session 1:
You make up a retrieve profile with a not completed primary key.
FLD_01.aentity/init = 4711

Session 2:
another session holds a (exclusive/update) lock on FLD_01=4711 and FLD_02=50

Session 1:
retreive/e "aentity"
setocc "aentity",-1

What does one expect to return and show up in the hitlist

a) FLD_02 = 1 .. 49
b) FLD_02 = 1 .. 99
c) Error, could not read FLD_02=50

a) Is the current solution by UnifAce
    Dangerous, as you not notified about 50 locked and 51..99 missing
b) Would be the result, when using "read uncommited"
    But you got uncommited data
c) is my expected result
    Stop exection an d report to the surface/error log

Ingo

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Ingo,

definitively you are in the same situation like we were in 2017:
read are locked by (potential) write!

You could definitively ask Uniface to improve the MSS driver but at that time I had the feeling there should be something not completely clean/limpid in the programming at low level using MSS libraries.

We solved:
- deciding our solution should be your b) but with "commited read" as isolation level, where we get the last committed version of record 50 in your example, NOT the uncommitted version!
- configuring SQLServer using latest MVCC functionalities: "Read committed" plus snapshot support (read_committed_snapshot in our case). Uniface is supporting this specific isolation mode with "iso:ss".

However I warn you there was two side effects we discovered in a later stage, being able somehow to maintain them under control:
1) At application level:to solve some deadlocks in some circumstances we forced a lock on the ONE table when a lock was generated from user/application editing on the MANY table.
2) At DBMS management level: SQLserver is maintaining its "version store" into TempDB; when a transaction is freezing (usually unfortunately during nights when batch jobs are running) the TempDB is starting to collect ALL transactions and grows its size; we solved improving batch jobs consistency and monitoring TempDB size.

IMHO, based on my experience, probably Oracle still remain on top when high concurrency is the key point.

Hope it helps.

Gianni

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Ingo Stiller
Author
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Gianni

I asked UnifAce (create a call) on this issue.
Lets wait what is there conclusion 🙂

Ingo

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 11, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

OK, please keep us folks updated! 😉

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!

Like

+3

Gianni Sandigliano
Participating Frequently
Forum|Forum|6 years ago
September 28, 2019

Hi Gianni

Ever do account software? 🙂
Lets have the following szenario:

One desk offizier starts a transaction about acount side transfer of money.
But he did not commit
Now he starts a second session and start the credit transfer to his bank account
The proper account record is aval (locked but avail)
After the transfer to the bank is done, he rollback the first session.

Nobody see the account record wich lead to the credit transfer

Another szenario
One clerk creates mass of account records by a weekly run. This run is still ongoing without a commit.
Another clerk runs a report against the database.
Wow we got thousend of euros at the end of the week, I have to report my boss.
But then there went something wrong at the first session and the bookings will be rolled back.

How's to blame ... 🙂

Ingo

Hi Ingo,

I've seen it was recognized as bug #32003 in the default isolation level of MSS.

Gianni

PillarOfWisdom1: Inject yourself with some fantasy to NOT die of realityPillarOfWisdom2: The secret for a long healthy life is: eat half, walk double, laugh three times and love without measure! (Chinese saying)PillarOfWisdom3: Barcollo ma non mollo!