Nicola

On Wednesday, 23 June 2021 at 09:52:25 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 22 June 2021 at 20:35:05 UTC+10, Nicola wrote:

On 2021-06-19, Derek Ignatius Asirvadem wrote:

______
Client
There is ordinary interplay between the Client and the Server. The client may now be a GUI or a webpage or middleware in an app server.

The VALIDATE block [1] is repeated in the client.

I assume that this means validating data without interacting with the database, e.g., to check that the input data is within valid ranges,
etc.

No.
Any GUI or input system has to do those checks on input fields, that is fundamental, not usually mentioned.

Let’s say there are 20 WIDGETS currently in the inventory:
__ why would you allow an user to purchase 6 WIJJETS [that do not exist] ??? __ Why would you allow an user to purchase 100 WIDGETS [that are not in stock] ???

What does normal GUI interaction mean to you ? Why does it exclude database interaction ? Why would you NOT restrict the user to choosing only those Parts that do exist in the database, and to a purchase quantity that is currently in stock ?
Otherwise the GUI is insane, the user interaction is insane.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Nicola

On Tuesday, 22 June 2021 at 20:35:05 UTC+10, Nicola wrote:

On 2021-06-19, Derek Ignatius Asirvadem wrote:

______
Client
There is ordinary interplay between the Client and the Server. The
client may now be a GUI or a webpage or middleware in an app server.

The VALIDATE block [1] is repeated in the client.

I assume that this means validating data without interacting with the database, e.g., to check that the input data is within valid ranges,
etc.

No.
Any GUI or input system has to do those checks on input fields, that is fundamental, not usually mentioned.

Let’s say there are 20 WIDGETS currently in the inventory:
__ why would you allow an user to purchase 6 WIJJETS [that do not exist] ??? __ Why would you allow an user to purchase 100 WIDGETS [that are not in stock] ???

Why would you let users enter any old nonsense all the way through the GUI; then at the end try it all against the database (the first failure would return, the second and subs would not even get checked); then find out that it failed; then try the whole
rigmarole again ???

Why would you NOT check for validity at each and every field (as the user progresses through the fields) ??? Did you not genuflect to the Commandment ? Why is the Client (GUI or whatever) excluded from being part of the system ?

By:

The VALIDATE block [1] is repeated in the client.

I mean the VALIDATE block [1] is repeated in the client. All of this:

[Modified Slightly for the GUI Context]

__________________
1. VALIDATE Block
__ Proper Preparation Prevents Poor Performance.

We know we are locking rows, implicitly, by virtue of the SQL commands we issue, but it is the server that does the locking and the resolution.
• We know that Reads are locked for the duration of the Statement, and Writes are locked until the COMMIT/ROLLBACK.
__• We can also request a Read to be locked until COMMIT/ROLLBACK, but that requires a Transaction to be opened, and a server that [is genuine OLTP and therefore] allows it.
<<<<

Code:
• Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• On any failure, error message; clear field; allow re-entry.
• Check NOT_EXISTS all rows that must not exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• On any failure, error message; clear field; allow re-entry.
• Transaction NOT yet started
• All locking is transient and very short duration
• TransactionLog is not touched
<<

When complete (eg. hitting the <Save> button), execute the Transaction stored proc.

Therefore, the Xact call when issued at the end of the GUI interaction has been VALIDATED before the attempt. While the user was fiddling around (between the presentation of the GUI dialogue and the instant the <Save> button was pressed, no rows are
locked; no contention is inflicted on other users, due to invalid attempts or worse, transactions partly executed before being rolled back.

If that is NOT done in the Client, the user is encumbered with entering any old nonsense; attempting it against the database (via the Transaction only); finding out that something or other failed (one at a time); and retrying; retrying; retrying. The
notion is ridiculous.

how do you ensure that
nothing happens between the instant VALIDATE completes and the instant
BEGIN TRAN is executed?

That duration between instants is:
a. not relevant [assuming you understand the above], and
b. microseconds, because it is between two consecutive steps inside
a contiguous stored proc, which is running as object + plan code
(compiled and a full resource plan established), and

That guarantees that interference is unlikely, but not impossible.

(Well, if it “guarantees that interference is unlikely” then it is impossible, if it is “not impossible” then it is not a guarantee.)

So what. The code is interrupted at that point. It is a normal possibility, catered for: if the code segment is interrupted at that or any other point, the Transaction is rolled back and an error status is returned to the caller. At that particular
point, no rows locked; Transaction not started. If at a later point, Transaction started and partially complete, then rolled back. So what. Ordinary fare. Why is that point of possible failure more important than any other point, why do we need to “
ensure nothing happens between end-VALIDATE and BEGIN-TRAN ?

Otherwise, you would not need this:

* BEGIN TRAN
• TransactionLog activity commenced for this xact
• Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• ROLLBACK & RETURN on any failure.

Huh ?
I need that because I love God and His Commandments; because I do not attempt actions that will fail; I am sociable and do not inflict resource-locking on other users unless I really have to. I need to separate the VALIDATE & EXECUTE blocks, because the
VALIDATE blocks prepares but does not change anything, and the EXECUTE block performs the changes in a structured Transaction, only if the VALIDATE block succeeds.

OTOH, if you are concerned about data in the database changing between the point in the VALIDATE block that checks a particular row, and the point the EXECUTE block that updates the relevant row, then yes, that is not catered for in the above description.
However, that [and one other condition) is covered by Optimistic Locking.

c. harmless because no resources are held.

That is an interesting approach. It is not obvious, IMO, that validating
the data the way you suggest is advantageous (I am not saying that it is not, mind you!).

And there is a VALIDATION being done in the Client, the duration of which cannot be controlled due to user interaction.

It is not merely advantageous, which of course it is, it is demanded by the OLTP Standard.

Whether it is or not, it seems to depend on a number of
factors, such as number of concurrent transactions, duration of transactions, number of touched resources, etc.

???
It is advantageous is all circumstances. The more active and contentious the system is, the more advantageous the Standard is.

Validation introduces an overhead, but it prevents transactions to start
and acquire locks unless there is a very good chance to acquire them
all, so overall it may be a win.

Yes.
I would not call it an “overhead”.
Checking the depth of the pond before diving in, or checking that a Part exists before ordering it, is not “overhead”, it is a demanded part of the task.

To summarize, you could implement transactions in at least three ways:

(1) just BEGIN TRAN; INSERT/UPDATE/DELETE; END TRAN:
(2) BEGIN TRAN; SELECT FOR UPDATE; INSERT/UPDATE/DELETE; END TRAN;
(3) VALIDATE, then (2).

And in your experience (3) is the best approach.

Can you suggest any other method, that locks the smallest number of resources, AND locks said resources for the shortest duration, AND only when those resources are sure to be touched ?

No, not merely my experience.
a. the way it was done, and the way it is still done, on “legacy” mainframe systems.
b. the way it is done on genuine OLTP systems (otherwise they have no right to the /OLTP/ label). These are well-known and published methods. Even for the totally ignorant, upon using a genuine OLTP server, they would read it in the manuals, the same
way that the insanity of MVCC is discussed in the PusGres manuals, but with honesty not fantasy. Ie. specific methods for reducing contention within the particular product are given.

[1][2] would be a very stupid app, that allows the user keep banging away at things that do not exist.

[3] is not best. And it certainly fails the OLTP requirement. You need:
4. VALIDATE all intended changes IN THE CLIENT during user interaction
__ upon <Save> execute the Transaction sp
____ which performs [3]
______ which performs [2]

________

OLAP Reports

Why do you worry about locks in OLAP reports ?
Who cares.
Read locks are extremely short, and transient (held for the duration
of the read operation [Statement], not the Transaction). We can’t control Read
locks, even implicitly

Are you saying that OLAP queries run in a lower isolation level (say,
READ COMMITTED)?

Of course. That is what that ISOLATION LEVEL[ READ COMMITTED ] is there for.

Not just OLAP queries, but all SELECTs outside a Transaction. That [ISOLATION LEVEL] is an ANSI SQL requirement, within the ACID requirement. We exclude [wait for completion of] uncommitted changes, but we do not need SERIALISED, in fact we want
massively parallel, and we want the whole result set to be integral within itself (all internal dependencies intact and true).

Did you not read the link I gave for the Sybase Lock Manager ? Page 2, bottom left, /Resolution/ table. I do not refer you to some doc unless it is directly related to the item at issue in the post.

In the Sybase world, for decades, we do not bother with REPEATABLE READ, it defaults to SERIALISABLE, which although it is a higher level of ISOLATION, works perfectly. In 2000, with the introduction of DRL/DPL data structures to provide improved speed
for RFS (SAP is RFS), yes, they implemented REPEATABLE READ.

For understanding. Many systems in the MS world do their OLAP queries with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the result set has no integrity, and (b) contains uncommitted changes which may disappear.

For the MVCC world, they have no concept of READ COMMITTED/UNCOMMITTED, because they have only the false privatised version of the entire database, firmly secured between their big toes, which has only nominally “committed” changes, that has no
relation to the version of other users. That nominally “committed” data blows up at COMMIT TRAN, just check the PooGres manuals re all the problems that are not supposed to happen in the Stonebraker fantasy, that happen, even to people who dutifully
sing the mantra.

But still, that is not Optimistic Locking. And in this post, there is nothing about Optimistic Locking.

Agreed.

Add Optimistic Locking, and the methods given in this thread would be complete.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Nicola

On Tuesday, 22 June 2021 at 20:35:05 UTC+10, Nicola wrote:

On 2021-06-19, Derek Ignatius Asirvadem wrote:

______
Client
There is ordinary interplay between the Client and the Server. The
client may now be a GUI or a webpage or middleware in an app server.

The VALIDATE block [1] is repeated in the client.

I assume that this means validating data without interacting with the database, e.g., to check that the input data is within valid ranges,
etc.

No.
Any GUI or input system has to do those checks on input fields, that is fundamental, not usually mentioned.

Let’s say there are 20 WIDGETS currently in the inventory:
__ why would you allow an user to purchase 6 WIJJETS [that do not exist] ??? __ Why would you allow an user to purchase 100 WIDGETS [that are not in stock] ???

What does normal GUI interaction mean to you ? Why does it exclude database interaction ? Why would you NOT restrict the user to choosing only those Parts that do exist in the database, and to a purchase quantity that is currently in stock ?
Otherwise the GUI is insane, the user interaction is insane.

For decades, GUIs have had Drop-Down fields, which are obtained from the db upon launching the dialogue. And fields that are searched-and-filled as the user types a few characters, which are obtained from the db.

Why would you design a GUI that does NOT interact with the db in order to validate its intent ... before it executes its intent ???

Why would you let users enter any old nonsense all the way through the GUI; then at the end try it all against the database (the first failure would return, the second and subs would not even get checked); then find out that it failed; then try the whole
rigmarole again ???

Why would you NOT check for validity at each and every field (as the user progresses through the fields) ??? Did you not genuflect to the Commandment ? Why is the Client (GUI or whatever) excluded from being part of the OLTP system, and therefore
subject to OLTP Standards ?

Why would you allow an user to even dream about a Part that you do not have in the inventory [WIJJET] ?

By:

The VALIDATE block [1] is repeated in the client.

I mean /the VALIDATE block [1] is repeated in the client/. All of this, (modified Slightly for the GUI Context):

__________________
1. VALIDATE Block
__ Proper Preparation Prevents Poor Performance.

We know we are locking rows, implicitly, by virtue of the SQL commands we issue, but it is the server that does the locking and the resolution.
• We know that Reads are locked for the duration of the Statement, Read Locks are transient.
• This is interactive, thus duration cannot be controlled, thus no locks held <<<<

Code:
• Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• On any failure, error message; clear field; allow re-entry.
• Check NOT_EXISTS all rows that must not exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• On any failure, error message; clear field; allow re-entry.
• Transaction NOT yet started
• No locks held
• TransactionLog is not touched
<<

When complete (eg. hitting the <Save> button), execute the Transaction stored proc.

Therefore, the Xact call when issued at the end of the GUI interaction has been VALIDATED before the attempt. While the user was fiddling around (between the presentation of the GUI dialogue and the instant the <Save> button was pressed, no rows are
locked; no contention is inflicted on other users, due to invalid attempts or worse, transactions partly executed before being rolled back.

If that is NOT done in the Client, the user is encumbered with entering any old nonsense; attempting it against the database (via the Transaction only); finding out that something or other failed (one at a time); and retrying; retrying; retrying. The
notion is ridiculous.

how do you ensure that
nothing happens between the instant VALIDATE completes and the instant
BEGIN TRAN is executed?

That duration between instants is:
a. not relevant [assuming you understand the above], and
b. microseconds, because it is between two consecutive steps inside
a contiguous stored proc, which is running as object + plan code
(compiled and a full resource plan established), and

That guarantees that interference is unlikely, but not impossible.

(Well, if it “guarantees that interference is unlikely” then it is impossible, if it is “not impossible” then it is not a guarantee.)

So what. The code is interrupted at that point. It is a normal possibility, catered for: if the code segment is interrupted at that or any other point, the Transaction is rolled back and an error status is returned to the caller. At that particular
point, no rows locked; Transaction not started. If at a later point, Transaction started and partially complete, then rolled back. So what. Ordinary fare. Why is that point of possible failure more important than any other point, why do we need to “
ensure nothing happens between end-VALIDATE and BEGIN-TRAN ?

Otherwise, you would not need this:

* BEGIN TRAN
• TransactionLog activity commenced for this xact
• Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__• ROLLBACK & RETURN on any failure.

Huh ?
I need that because I love God and His Commandments; because I do not attempt actions that will fail; I am sociable and do not inflict resource-locking on other users unless I really have to. I need to separate the VALIDATE & EXECUTE blocks, because the
VALIDATE blocks prepares but does not change anything, and the EXECUTE block performs the changes in a structured Transaction, only if the VALIDATE block succeeds.

OTOH, if you are concerned about data in the database changing between the point in the VALIDATE block that checks a particular row, and the point the EXECUTE block that updates the same particular row, then yes, that is not catered for in the above
description. However, that [and one other major problem, that can be eliminated) is covered by Optimistic Locking.

c. harmless because no resources are held.

That is an interesting approach. It is not obvious, IMO, that validating
the data the way you suggest is advantageous (I am not saying that it is not, mind you!).

And there is a VALIDATION being done in the Client, the duration of which cannot be controlled due to user interaction.

It is not merely advantageous, which of course it is, it is demanded by the OLTP Standard.

Whether it is or not, it seems to depend on a number of
factors, such as number of concurrent transactions, duration of transactions, number of touched resources, etc.

Is not every request to the server “dependent” on those same factors ??? DO you have a method of calling the server such that those loads do NOT affect the Transaction ??? Maybe the MVCC fantasy of a private version of the entire database. No,
this is real life.

The duration of transactions is minimised, to the absolute minimu, by the OLTP Standard. In case it is not clear, unlike the MVCC asylum:
a. we do not hold locks outside a Transaction, and
b. we do not BEGIN TRAN except to execute a Transaction, and
c. we do not allow user interaction after a BEGIN TRAN.

It is advantageous is all circumstances. The more active and contentious the system is, the more advantageous the OLTP Standard is.

Validation introduces an overhead, but it prevents transactions to start
and acquire locks unless there is a very good chance to acquire them
all, so overall it may be a win.

Yes.
I would not call it an “overhead”.
Checking the depth of the pond before diving in, or checking that a Part exists before ordering it, is not “overhead”, it is a demanded part of the task.

To summarize, you could implement transactions in at least three ways:

(1) just BEGIN TRAN; INSERT/UPDATE/DELETE; END TRAN:
(2) BEGIN TRAN; SELECT FOR UPDATE; INSERT/UPDATE/DELETE; END TRAN;
(3) VALIDATE, then (2).

And in your experience (3) is the best approach.

Can you suggest any other method, that locks the smallest number of resources, AND locks said resources for the shortest duration, AND only when those resources are sure to be touched ?

No, not merely my experience.
a. the way it was done, and the way it is still done, on “legacy” mainframe systems.
b. the way it is done on genuine OLTP systems (otherwise they have no right to the /OLTP/ label). These are well-known and published methods. Even for the totally ignorant, upon using a genuine OLTP server, they would read it in the manuals, the same
way that the insanity of MVCC is discussed in the PusGres manuals, but with honesty not fantasy. Ie. specific methods for reducing contention within the particular product are given.

As stated severally, about important matters (eg. God; truth) or about science (real science not the Date;Darwen; Fagin; etc filth that passes for Modern “science”), which is anchored in objective truth, I don’t have opinions, I can only provide
education.

[1][2] would be a very stupid app, that allows the user keep banging away at things that do not exist.

Yes, it is my experience since 1976. By the Grace Of God, working for system hosting companies; then the Canadian Trotting Registry; then Cincom, the fifth largest DBMS provider at the time. Meaning that I learned what NOT to do, and what TO DO
CORRECTLY. Truth is permanent, it cannot change ... if it changes, it is not truth. The OLTP Standard has not changed since the 1960’s. I have just rendered it in an SQL context.

Btw, neither ACID Transaction nor Transaction definition was invented by Jim Gray. No thanks, we had all that long before he was heard of. What he did do was standardise the Transaction Benchmark process, such that the benchmarks performed by the
vendors on their different SQL Platforms were the same code structure, and could be compared by customers and prospects, the Transaction Processing Performance Council. Obviously Oracle cheated like a bachelor party at a brothel, and had to be reined in.
In so doing, he coined the term ACID, for the Transactions that we did have. What he did do was good enough, he does not need to Big Note himself further (common problem with academics).

Eg. Because he was standardising from the user perspective, for millions of TPPC transactions per second, and not getting into the problems of Transaction Quality or Transaction Complexity, re the issues raised in this thread, he remained as clueless as
the academics of today. If that were not the case, he would have published the OLTP Standard as his own.
<<<<

[3] is not best. And it certainly fails the OLTP requirement, which is a system wide Standard. You need:
4. VALIDATE all intended changes -->IN THE CLIENT<-- during user interaction
__ upon <Save> execute the Transaction sp
____ which performs [3]
______ which performs [2]

+----------+

OLAP Reports

Why do you worry about locks in OLAP reports ?
Who cares.
Read locks are extremely short, and transient (held for the duration
of the read operation [Statement], not the Transaction). We can’t control Read
locks, even implicitly

Are you saying that OLAP queries run in a lower isolation level (say,
READ COMMITTED)?

Of course. That is what that ISOLATION LEVEL[ READ COMMITTED ] is there for.

Not just OLAP queries, but all SELECTs outside a Transaction. That [ISOLATION LEVEL] is an ANSI SQL requirement, within the ACID requirement. We exclude [wait for completion of] uncommitted changes, but we do not need SERIALISED, in fact we want
massively parallel, and we want the whole result set to be integral within itself (all internal dependencies intact and true).

Did you not read the link I gave for the Sybase Lock Manager ? Page 2, bottom left, /Resolution/ table. I do not refer you to some doc unless it is directly related to the item at issue in the post.

In the Sybase world, for decades, we do not bother with REPEATABLE READ, it defaults to SERIALISABLE, which although it is a higher level of ISOLATION, works perfectly. In 2000, with the introduction of DRL/DPL data structures to provide improved speed
for RFS (SAP is RFS), yes, they implemented REPEATABLE READ.

For understanding. Many systems in the MS world do their OLAP queries with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the result set has no integrity, and (b) contains uncommitted changes which may disappear.

For the MVCC world, they have no concept of READ COMMITTED/UNCOMMITTED, because they have only the false privatised version of the entire database, firmly secured between their big toes, which has only nominally “committed” changes, that has no
relation to the version of other users. That nominally “committed” data blows up at COMMIT TRAN, just check the PooGres manuals re all the problems that are not supposed to happen in the Stonebraker fantasy, that happen, even to people who dutifully
sing the mantra.

But still, that is not Optimistic Locking. And in this post, there is nothing about Optimistic Locking.

Agreed.

Add Optimistic Locking, and the methods given in this thread would be complete.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-22, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

OTOH, if you are concerned about data in the database changing between
the point in the VALIDATE block that checks a particular row, and the
point the EXECUTE block that updates the relevant row, then yes, that
is not catered for in the above description. However, that [and one
other condition) is covered by Optimistic Locking.

Yes, that is what I meant.

OLAP Reports

Why do you worry about locks in OLAP reports ?
Who cares.
Read locks are extremely short, and transient (held for the duration
of the read operation [Statement], not the Transaction). We can’t control Read
locks, even implicitly

Are you saying that OLAP queries run in a lower isolation level (say,
READ COMMITTED)?

Of course. That is what that ISOLATION LEVEL[ READ COMMITTED ] is there for.

Not just OLAP queries, but all SELECTs outside a Transaction. That [ISOLATION LEVEL] is an ANSI SQL requirement, within the ACID
requirement. We exclude [wait for completion of] uncommitted changes,
but we do not need SERIALISED, in fact we want massively parallel, and
we want the whole result set to be integral within itself (all
internal dependencies intact and true).

What does "integral within itself" mean? Transactions at READ COMMITTED,
even if they are read-only, suffer from some anomalies.

For understanding. Many systems in the MS world do their OLAP queries
with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the
result set has no integrity, and (b) contains uncommitted changes
which may disappear.

Correct. But queries at READ COMMITTED may view (committed) changes that
were not there when the transaction started. Do you accept that query
results may be off sometimes, or do you have some recipe to make them
avoid inconsistent reads and phantoms?

For the MVCC world, they have no concept of READ
COMMITTED/UNCOMMITTED, because they have only the false privatised
version of the entire database, firmly secured between their big toes,
which has only nominally “committed” changes, that has no relation to
the version of other users. That nominally “committed” data blows up
at COMMIT TRAN, just check the PooGres manuals re all the problems
that are not supposed to happen in the Stonebraker fantasy, that
happen, even to people who dutifully sing the mantra.

According to the manual (https://www.postgresql.org/docs/current/transaction-iso.html), in
PostgreSQL each isolation level correctly prevents the anomalies that
are not allowed by the standard to occur at that level, and in some
cases it prevents more (e.g., READ UNCOMMITTED is like READ COMMITTED).
What are you referring to when you mention "all the problems that are
not supposed to happen... that happen"?

But still, that is not Optimistic Locking. And in this post, there is
nothing about Optimistic Locking.

Agreed.

Add Optimistic Locking, and the methods given in this thread would be complete.

Ok.

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Nicola

On Thursday, 24 June 2021 at 07:59:50 UTC+10, Nicola wrote:

On 2021-06-22, Derek Ignatius Asirvadem wrote:

Sorry. Before I respond to the content, I need one clarification.

For understanding. Many systems in the MS world do their OLAP queries
with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the
result set has no integrity, and (b) contains uncommitted changes
which may disappear.

Correct. But queries at READ COMMITTED may view (committed) changes that were not there when the transaction started.

What “transaction started” ??? No transaction is involved.

The report connection would normally do SELECT [at] READ_COMMITTED which holds ReadLocks for the duration of the Statement, but in this abnormal case does SELECT [at] READ_UNCOMMITTED, holding no locks at all, and reading whatever is there on the disk,
including uncommitted changes.

You might be stuck in the mindset that in order to read at { READ_UNCOMMITTED | READ_COMMITTED, } you must open a Transaction. No, that is the MV-non-CC insanity, the Stonebraker fantasy. No, that is not ACID. No, we do not do that.

As per ACID, the real ACID, not the perverted form that MV-non-CC pretends, we only need to open a Transaction for { REPEATABLE_READ | SERIALISABLE }.

(I have to stretch, and be very careful, when I am writing these things ... from my everyday position of never having those problems; that insanity; that mindset. The insanity is foreign to me, but I am aware of it. Otherwise I would make serious
errors in understanding, and communication would be difficult (correcting simple errors). Please understand, you have to do the converse: from your everyday MV-non-CC insanity and fantasy, you need to think carefully about the Real ACID; Real Ordinary
Locking; Real Transactions. Not the Straw Men, which are burning slowly.)

----------

Separately. What “rows that were not there [when the transaction started]” ??? How can a transaction change a row that is not there ??? Or even have knowledge about a row that is not there ???

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-24, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Nicola

On Thursday, 24 June 2021 at 07:59:50 UTC+10, Nicola wrote:

On 2021-06-22, Derek Ignatius Asirvadem wrote:

Sorry. Before I respond to the content, I need one clarification.

For understanding. Many systems in the MS world do their OLAP queries
with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the
result set has no integrity, and (b) contains uncommitted changes
which may disappear.

Correct. But queries at READ COMMITTED may view (committed) changes that
were not there when the transaction started.

What “transaction started” ??? No transaction is involved.

Ok, now that is clear.

The report connection would normally do SELECT [at] READ_COMMITTED
which holds ReadLocks for the duration of the Statement,

Ok. So, I am back to a question I have already posed to you:

- OLAP queries run at READ COMMITTED;
- Each OLAP query is a single SELECT statement;
- Each SELECT holds read locks for the duration of the statement.

But the duration of the statement may be significant (minutes, hours,
...) Don't such queries create contention (other transactions cannot
update the data read by the OLAP query until the query is over)?

Separately. What “rows that were not there [when the transaction started]” ??? How can a transaction change a row that is not there
???

Of course it can't.

Or even have knowledge about a row that is not there ???

Of course it can't. But it can see something that was not there before,
and it appears at a later time. I explain what I mean with an example:

Time Transaction Operation
0 T1,T2 start transaction;
1 T1 select sum(X) from R;
2 T2 insert into R(X) values (100);
3 T2 commit;
4 T1 select sum(X) from R;

At READ COMMITTED, T1 at time 4 will see the record inserted by T2 at
time 2, even if the record did non exist when T1 began. The SELECT at
time 1 and time 4 would then return different results.

Of course, this is not an issue if the only queries you run at READ
COMMITTED are single statements—provided that the system guarantees statement-level consistency (it seems that some don't: https://stackoverflow.com/questions/4772475/read-committed-isolation-level-in-sql-server-for-a-single-statement).

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, June 24, 2021 at 7:33:00 PM UTC+10, Nicola wrote:

On 2021-06-24, Derek Ignatius Asirvadem <derek.a...@gmail.com> wrote:

Nicola

On Thursday, 24 June 2021 at 07:59:50 UTC+10, Nicola wrote:

On 2021-06-22, Derek Ignatius Asirvadem wrote:

Sorry. Before I respond to the content, I need one clarification.

For understanding. Many systems in the MS world do their OLAP queries >> > with READ UN-COMMITTED, to avoid Read Locks, but that means (a) the
result set has no integrity, and (b) contains uncommitted changes
which may disappear.

Correct. But queries at READ COMMITTED may view (committed) changes that >> were not there when the transaction started.

What “transaction started” ??? No transaction is involved.

Ok, now that is clear.

The report connection would normally do SELECT [at] READ_COMMITTED
which holds ReadLocks for the duration of the Statement,

Ok. So, I am back to a question I have already posed to you:

- OLAP queries run at READ COMMITTED;
- Each OLAP query is a single SELECT statement;
- Each SELECT holds read locks for the duration of the statement.

But the duration of the statement may be significant (minutes, hours,
...) Don't such queries create contention (other transactions cannot
update the data read by the OLAP query until the query is over)?

Separately. What “rows that were not there [when the transaction started]” ??? How can a transaction change a row that is not there
???

Of course it can't.

Or even have knowledge about a row that is not there ???

Of course it can't. But it can see something that was not there before,
and it appears at a later time. I explain what I mean with an example:

Time Transaction Operation
0 T1,T2 start transaction;
1 T1 select sum(X) from R;
2 T2 insert into R(X) values (100);
3 T2 commit;
4 T1 select sum(X) from R;

At READ COMMITTED, T1 at time 4 will see the record inserted by T2 at
time 2, even if the record did non exist when T1 began. The SELECT at
time 1 and time 4 would then return different results.

Of course, this is not an issue if the only queries you run at READ COMMITTED are single statements—provided that the system guarantees statement-level consistency (it seems that some don't: https://stackoverflow.com/questions/4772475/read-committed-isolation-level-in-sql-server-for-a-single-statement).

Nicola

Hi Nicola,

If you're running a query in the READ COMMITTED isolation level then a shared lock is acquired for the duration of the read operation on whatever resource the lock protects. That resource may be a row or a page.
If you have acquired a row-level lock then the lock will be released once reading the row has finished.
If you have acquired a page lock then the lock will be released once reading the page has finished.

A query running for hours under READ COMMITTED would be locking and unlocking rows or pages as it encounters and processes them.

As for your second part concerning the sequence of events described, you are quite right. This is precisely the phenomenon that the REPEATABLE READ isolation protects against.
When you run a query with REPEATABLE READ isolation, shared locks are acquired and held for rows / pages until the transaction is committed or rolled back.
So if you then tried to modify data in those rows or pages, which involves acquiring an exclusive lock for those rows or pages, it would block - the exclusive lock being incompatible with the shared lock - thus allowing you to repeat the REPEATABLE READ
and get the same result.

However, it's important to note that REPEATABLE READ isolation level does not acquire range locks (or predicate locks). This means that you can conceivably insert a new record that satisfies your REPEATABLE READ query's WHERE clause predicate.
For example, you could insert a new record that satisfies the REPEATABLE READ query predicate but ends up stored on a page that is not subject to a shared lock (the page might not have existed when you first ran the REPEATABLE READ query).
This is a phantom record, which SERIALIZABLE guards against using the aforementioned range (predicate) locks.

It's important to recognise that your REPEATABLE READ query - or any query for that matter - is executing in the context of a broader, shared, system. Seeing the outcome of newly committed transactions makes sense if you've chosen READ COMMITTED and
understand the concurrency phenomenon that it protects against.

Importantly, and often overlooked as far as MVCC disadvantages go, the data you read in an hours-long query running under MVCC is arbitrarily old data. If your query takes 2 hours to complete then the very last row processed by your query is potentially
2 hours out of date.

Daniel

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Nicola

On Thursday, 24 June 2021 at 19:33:00 UTC+10, Nicola wrote:

On 2021-06-24, Derek Ignatius Asirvadem wrote:

The report connection would normally do SELECT [at] READ_COMMITTED
which holds ReadLocks for the duration of the Statement,

Ok. So, I am back to a question I have already posed to you:

- OLAP queries run at READ COMMITTED;
- Each OLAP query is a single SELECT statement;

1. Well, I guarantee that in my databases, because it is 100% Predicates.
Even high-end massive report tools do that, noting the intermediate “universe” that maps an RFS into something readable. And then it executes a single SELECT.
But boffins writing SQL on a non-SQL suite of programs, do not have a hope in hell. So I can see that what is vanilla to us, is news to you.

2. Not only OLAP, any SELECT runs at READ COMMITTED.
OLAP query do not *have* to be a single SELECT statement

- Each SELECT holds read locks for the duration of the statement.

Yes.
SQL/ACID requirement, that MV-non-CC suites cannot support.

But the duration of the statement may be significant

Why ???

I don’t have a single report in my entire SQL life that exceeds 12 seconds, and some of them are “complex” (20+ tables; subqueries; derived tables) and massive (tables have billions of rows). I kinda pride myself on sub-second response times, even
for reports.

In the course of my operations, sure, I have replaced reports that run in 30 mins with a Rdb+single-SELECT that runs in under 10 seconds. Many, many times.

On one occasion I got thrown out of the building by the big boss because his report that ran in 15 mins on Friday, after I re-built the DataStructures on the server, ran in under 1 second on Monday. He was sure that my project failed, that the report
was wrong. I begged him to compare the report content, but he would not. I had to buy his secretary a box of Lindt chocolate, get her to print out both Friday and Monday reports, and get her to show them to him personally. The ungrateful sob did not
even apologise, he just had security re-instate my security pass.
<<<<

I accept that in the MS world, where they are used to longer times, sure, their reports would go to 90 mins. I have replaced 2 of those and brought it down to under 10 secs.

(minutes, hours, ...)

Not in the commercial SQL world.

Sure, in idiotic suites of programs, written by 10,000 undergrads spread across the planet, all singing the Stonebraker mantra to maintain their collective fantasy, hours and days are “normal”. That is the price of NOT having an architecture, of
writing filth and pretending that it is a server. 1,000 or 2,000 TIMES slower than a commercial SQL Platform is “normal”.

So no, the issue simply does not apply to us. But I accept that is “normal” for the academics and freeware users. Just wait until you get to the benchmark that you were planning.

Take a look at this Benchmark I did against Oracle (MV-non-CC). Where Sybase returned in 2.16 secs, Oracle had to abandon the benchmark after 120 MINUTES.
__ https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Benchmark%20101204.pdf

Don't such queries create contention (other transactions cannot
update the data read by the OLAP query until the query is over)?

Correct.
So what (see above).
No one cares about waiting a few seconds.

1. Did you not read the /Sybase Lock Manager/ link, page 2 table at bottom left ?

2. I have stated that if you want to get into this, there is a Server Monitoring report that just happens to expose this problem, and the considerations, and with more granularity than “contention” ... but you have not taken it up.
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

The Read Locks are precisely this, SELECT queries (not OLAP, but that makes no difference) contending with OLTP transactions. Do you not care, that after fixing a SAN Config problem, the Read Locks dropped by 91%, or what that means at the coal face.
Or stated in converse, why Read Locks were 1,100% higher when the SAN was mis-configured.

In any case, take an extremely quick look at the report, in [§ Selection] at the top, just three lines:
__ Write Locks = up 10%
__ Read Locks = down 91%
__ Commits = no change (confirming the load is the same)

At minimum, I am confirming that (a) OLAP & SELECT queries *DO* hold Read Locks for the duration of the Statement, (b) that that is so fleeting; so irrelevant, in a normal commercial SQL server (Server Architecture).

Separately. What “rows that were not there [when the transaction started]” ??? How can a transaction change a row that is not there
???

Of course it can't.

Or even have knowledge about a row that is not there ???

Of course it can't. But it can see something that was not there before,
and it appears at a later time. I explain what I mean with an example:

Time Transaction Operation
0 T1,T2 start transaction;
1 T1 select sum(X) from R;
2 T2 insert into R(X) values (100);
3 T2 commit;
4 T1 select sum(X) from R;

At READ COMMITTED, T1 at time 4 will see the record inserted by T2 at
time 2, even if the record did non exist when T1 began. The SELECT at
time 1 and time 4 would then return different results.

Sure. But that is the classic demonstration for requiring ISOLATION LEVEL REPEATABLE_READ, which requires a Transaction to be open.

1.
It is still a matter of understanding on your part.
(The PusGres link you gave is pure mind-numbing filth. You are still thinking about a SELECT that holds “transactional” locks. It does not (except in that oozing filth). )
Change all occs of [T1] to [S1], signifying that it is a SELECT, not a Transaction.
Remove T1 at Time[0] because we do not start a Transaction for a SELECT
If READ_COMMITTED is used (default on DB2; Sybase; MS), a SELECT [no Transaction]:
__ at Time[1] does not obtain the inserted row
__ at Time[4] obtains the inserted row

A SELECT does not have to be repeated, period, full stop, end of story.

2.
But if a SELECT does get repeated, then the context must be some Logical Unit of Work; a Transaction.
Since it is now a Transaction, ISOLATION LEVEL SERIALIZABLE applies (you do not get to choose).
(Using your original example, not the changes ala [1])
Add Time[5] T1 commit
The result is, T2 does not start until Time[5].
The inserted row does not get inserted until the T1 Transaction completes.

It would be an exceedingly stupid thing to do, repeating a SELECT in a Transaction, instead of saving the value at the first SELECT. But academically, as a theoretical possibility, sure. And SQL/ACID handles it, no problem at all.

Of course, this is not an issue if the only queries you run at READ COMMITTED are single statements—provided that the system guarantees statement-level consistency

Yes.
(Although the example does not suffice, I do know what you mean.)

(it seems that some don't: https://stackoverflow.com/questions/4772475/read-committed-isolation-level-in-sql-server-for-a-single-statement).

Come on.

1. Can you not tell the difference between a platform that provides Statement level consistency, and has done so for 3 decades (at the time of the question), and a bug, that in some cases it fails ? A bug does not prove that Statement-level consistency
is not provided by the product, it proves only the existence of a bug. You are grasping at straws.

2. Both the seeker and the two responders are idiots. They are genuflecting to the (then) new MV (definitely no CC in that, but the pre-existing CC in the pre-existing “1PL”), the awe of the mystical and magical. With no understanding whatsoever.

3. The answer is actually quite wrong for Sybase & DB2, and MS/SQL (I seriously doubt that MS/SQL has changed for the answer to be correct).

At read committed level shared locks are released as soon as the data is read (not at the end of the transaction or even the end of the statement)

No, at read committed level shared locks are released at the end of the statement.
The reference to “end of transaction” is stupid, good for confusing the confused further.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-24, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

On Thursday, 24 June 2021 at 19:33:00 UTC+10, Nicola wrote:

On 2021-06-24, Derek Ignatius Asirvadem wrote:

The report connection would normally do SELECT [at] READ_COMMITTED
which holds ReadLocks for the duration of the Statement,

Ok. So, I am back to a question I have already posed to you:

- OLAP queries run at READ COMMITTED;
- Each OLAP query is a single SELECT statement;

1. Well, I guarantee that in my databases, because it is 100% Predicates. Even high-end massive report tools do that, noting the intermediate “universe” that maps an RFS into something readable. And then it executes a single SELECT.

I did not mean to imply otherwise. I am perfectly aware of how
expressive SQL can be on well-designed databases.

2. Not only OLAP, any SELECT runs at READ COMMITTED.

Yes. I focused on OLAP to emphasize that I have "complex" queries in
mind.

- Each SELECT holds read locks for the duration of the statement.

Yes.
SQL/ACID requirement,

Fine.

that MV-non-CC suites cannot support.

Trivially, as MVCC does not use locks.

But the duration of the statement may be significant

Why ???

I don’t have a single report in my entire SQL life that exceeds 12
seconds, and some of them are “complex” (20+ tables; subqueries;
derived tables) and massive (tables have billions of rows). I kinda
pride myself on sub-second response times, even for reports.

Fair (and funny anecdote you have there).

(minutes, hours, ...)

Not in the commercial SQL world.

Sure, in idiotic suites of programs, written by 10,000 undergrads
spread across the planet, all singing the Stonebraker mantra to
maintain their collective fantasy, hours and days are “normal”. That
is the price of NOT having an architecture, of writing filth and
pretending that it is a server. 1,000 or 2,000 TIMES slower than
a commercial SQL Platform is “normal”.

So no, the issue simply does not apply to us. But I accept that is “normal” for the academics and freeware users. Just wait until you
get to the benchmark that you were planning.

Take a look at this Benchmark I did against Oracle (MV-non-CC). Where
Sybase returned in 2.16 secs, Oracle had to abandon the benchmark
after 120 MINUTES.
__ https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Benchmark%20101204.pdf

I remember finding that benchmark some time ago and trying to
reproduce it in PostgreSQL, based on the DDL I found here:

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/DDL%20101201.txt

and the query found here:

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Subquery%20SUM%20ShowPlan.txt

My only noteworthy remarks at the time were:

1. As bad as Oracle might be, it can't be so slow. It sounds like the
benchmark was performed by Sybase people and/or no effort was made to
optimize on the Oracle side.

Anyway, I don't care about Oracle.

2. 488MB for the 2M records of CustomerTransaction seems quite a lot.
I've got ~147MB, including indexes (well, one index), in PostgreSQL.

I have just repeated my experiment. The above query takes ~2.4s with
cold data and ~800ms with hot data on my laptop. Ok, that's flash
storage and a laptop one decade newer than the one in your document. But
it's stock PostgreSQL with default parameters (which are extremely conservative), with no physical tuning applied. So, all in all it's not
so bad for the price.

For the sake of completeness, and possibly for comparison and further discussion, at the end of this message I report my benchmark.

Don't such queries create contention (other transactions cannot
update the data read by the OLAP query until the query is over)?

Correct.
So what (see above).
No one cares about waiting a few seconds.

There are many applications where waiting more than a few hundreds
milliseconds might be problematic. For example, waiting ten seconds
makes a web app unacceptably slow.

1. Did you not read the /Sybase Lock Manager/ link, page 2 table at bottom left ?

Yes.

2. I have stated that if you want to get into this, there is a Server Monitoring report that just happens to expose this problem, and the considerations, and with more granularity than “contention” ... but
you have not taken it up.
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

The Read Locks are precisely this, SELECT queries (not OLAP, but that
makes no difference) contending with OLTP transactions. Do you not
care, that after fixing a SAN Config problem, the Read Locks dropped
by 91%, or what that means at the coal face. Or stated in converse,
why Read Locks were 1,100% higher when the SAN was mis-configured.

I have no doubt that, given your experience, you are able to achieve
great improvements on every system you touch. My questions are for
better understanding on my part, not to tell you that you should run
things differently.

Nicola

--------------------------------------------------------------------------------
--
-- Benchmark for Tony Andrews: DDL for Tables Used
-- PerformanceDBA 28 Nov 2010
-- Adapted by NV for PostgreSQL on 2021 Jun 25
--
-- Original code from:
--
-- https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/

-- NOTE: data types are inferred (not given in the original code).
create domain CustomerId as integer;
create domain _DescriptionSmall as text;
create domain _Description as text;
create domain "_Date" as date;
create domain _UserStamp as timestamp;
create domain _TimeStampSmall as timestamp;
create domain _IntSmall as smallint;
create domain _MoneySmall as numeric(7,2);
create domain TransactionCode as char
constraint valid_transaction_code
check (value in ('C','D'));

-- Population: 1000 Random CustomerIds
create table REF_Customer (
CustomerId CustomerId not null , -- SMALLINT
ShortName _DescriptionSmall not null ,
FullName _Description not null ,
CreatedDate "_Date" not null ,
UserStamp _UserStamp not null ,
TimeStamp _TimeStampSmall not null
);

alter table REF_Customer
add constraint U_CustomerId
primary key (CustomerId);

alter table REF_Customer
add constraint UC_ShortName
unique (ShortName);

-- Population: 2M rows
-- distributed evenly across the 1000 CustomerIds
-- 2000 per CustomerId: 1000 Credits; 1000 Debits
create table CustomerTransaction (
CustomerId CustomerId not null , -- SMALLINT
SequenceNo _IntSmall not null ,
Date "_Date" not null ,
TransactionCode TransactionCode not null , -- CHAR(1)
Amount _MoneySmall not null
);

alter table CustomerTransaction
add constraint UC_PK
primary key (CustomerId, SequenceNo);

-- Insert some randomly generated data, distributed as specified:
with words(word) as (
select unnest(string_to_array(pg_read_file('/usr/share/dict/words')::text,E'\n'))
)
insert into REF_Customer(CustomerId, ShortName, FullName, CreatedDate, UserStamp, TimeStamp)
select row_number() over (),
word as ShortName,
word || word || word || word as FullName,
(now() - '1 day'::interval * round(random() * 3650))::date as CreateDate,
now() - '1 day'::interval * round(random() * 3650) as UserStamp,
now() - '1 day'::interval * round(random() * 3650) as TimeStamp
from words
order by random()
limit 1000;

-- Insert 2M rows, 2000 per customer, uniformly distributed over {C,D}
insert into CustomerTransaction(CustomerId, SequenceNo, Date, TransactionCode, Amount)
select CustomerId,
generate_series(1,2000) as SequenceNo,
(now() - '1 day'::interval * round(random() * 3650))::date as Date,
case when random() < 0.5 then 'C' else 'D' end as TransactionCode,
random() * 10000 as Amount
from REF_Customer;

analyze; -- Update the stats

--------------------------------------------------------------------------------

-- Main PostgreSQL settings:
-- shared_buffers = 128MB
-- maintenance_work_mem = 64MB
-- temp_buffers = 8MB
-- work_mem = 4MB

select * from ref_customer limit 3; ┌────────────┬───────────┬──────────────────────────────────────┬─────────────┬──────
──────────────────────┬────────────────────────────┐
│ customerid │ shortname │ fullname │ createddate │ userstamp │ timestamp │
├────────────┼───────────┼──────────────────────────────────────┼─────────────┼──────
──────────────────────┼────────────────────────────┤
│ 182577 │ skippet │ skippetskippetskippetskippet │ 2021-06-25 │ 2021-06-25 21:00:59.401317 │ 2021-06-25 21:00:59.401317 │
│ 57408 │ downness │ downnessdownnessdownnessdownness │ 2021-06-25 │ 2021-06-25 21:00:59.401317 │ 2021-06-25 21:00:59.401317 │
│ 132294 │ orocratic │ orocraticorocraticorocraticorocratic │ 2021-06-25 │ 2021-06-25 21:00:59.401317 │ 2021-06-25 21:00:59.401317 │
└────────────┴───────────┴──────────────────────────────────────┴─────────────┴──────
──────────────────────┴────────────────────────────┘

select * from customertransaction limit 10; ┌────────────┬────────────┬────────────┬─────────────────┬─────────┐
│ customerid │ sequenceno │ date │ transactioncode │ amount │
├────────────┼────────────┼────────────┼─────────────────┼─────────┤
│ 182577 │ 1 │ 2017-07-08 │ C │ 71.73 │
│ 182577 │ 2 │ 2012-10-02 │ C │ 9443.97 │
│ 182577 │ 3 │ 2011-10-31 │ C │ 5950.48 │
│ 182577 │ 4 │ 2013-04-12 │ C │ 6720.09 │
│ 182577 │ 5 │ 2016-04-16 │ C │ 3002.87 │
│ 182577 │ 6 │ 2013-01-14 │ D │ 175.58 │
│ 182577 │ 7 │ 2015-03-30 │ D │ 567.78 │
│ 182577 │ 8 │ 2015-11-06 │ D │ 9538.73 │
│ 182577 │ 9 │ 2012-01-16 │ D │ 5603.86 │
│ 182577 │ 10 │ 2013-04-30 │ C │ 7631.24 │
└────────────┴────────────┴────────────┴─────────────────┴─────────┘

-- Adapted from https://wiki.postgresql.org/wiki/Disk_Usage
select table_name, pg_size_pretty(total_bytes) as total
, pg_size_pretty(index_bytes) as index
, pg_size_pretty(table_bytes) as table
from (
select *, total_bytes-index_bytes-coalesce(toast_bytes,0) as table_bytes from (
select c.oid,nspname as table_schema, relname as table_name
, c.reltuples as row_estimate
, pg_total_relation_size(c.oid) as total_bytes
, pg_indexes_size(c.oid) as index_bytes
, pg_total_relation_size(reltoastrelid) as toast_bytes
from pg_class c
left join pg_namespace n on n.oid = c.relnamespace
where relkind = 'r'
and relname in ('ref_customer', 'customertransaction')
) a
) a; ┌─────────────────────┬────────┬────────┬────────┐
│ table_name │ total │ index │ table │ ├─────────────────────┼────────┼────────┼────────┤
│ ref_customer │ 248 kB │ 112 kB │ 128 kB │
│ customertransaction │ 147 MB │ 47 MB │ 100 MB │ └─────────────────────┴────────┴────────┴────────┘

-- Disable parallel query execution
set max_parallel_workers_per_gather = 0;

-- Query adapted from https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Subquery%20SUM%20ShowPlan.txt
explain analyze
SELECT ShortName,
(
SELECT SUM (Amount)
FROM CustomerTransaction
WHERE CustomerId = c.CustomerId
AND TransactionCode = 'C'
) as NumCredit,
(
SELECT SUM (Amount)
FROM CustomerTransaction
WHERE CustomerId = c.CustomerId
AND TransactionCode = 'D'
) as NumDebit
FROM REF_Customer c;

┌────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on ref_customer c (cost=0.00..10745234.88 rows=1000 width=74) (actual time=1.873..817.176 rows=1000 loops=1) │
│ SubPlan 1 │
│ -> Aggregate (cost=5372.61..5372.62 rows=1 width=32) (actual time=0.437..0.437 rows=1 loops=1000) │
│ -> Bitmap Heap Scan on customertransaction (cost=43.68..5370.09 rows=1006 width=6) (actual time=0.079..0.333 rows=1000 loops=1000) │
│ Recheck Cond: ((customerid)::integer = (c.customerid)::integer) │
│ Filter: ((transactioncode)::bpchar = 'C'::bpchar) │
│ Rows Removed by Filter: 1000 │
│ Heap Blocks: exact=13732 │
│ -> Bitmap Index Scan on uc_pk (cost=0.00..43.43 rows=2000 width=0) (actual time=0.075..0.075 rows=2000 loops=1000) │
│ Index Cond: ((customerid)::integer = (c.customerid)::integer) │
│ SubPlan 2 │
│ -> Aggregate (cost=5372.58..5372.59 rows=1 width=32) (actual time=0.379..0.379 rows=1 loops=1000) │
│ -> Bitmap Heap Scan on customertransaction customertransaction_1 (cost=43.68..5370.09 rows=994 width=6) (actual time=0.061..0.277 rows=1000 loops=1000) │
│ Recheck Cond: ((customerid)::integer = (c.customerid)::integer) │
│ Filter: ((transactioncode)::bpchar = 'D'::bpchar) │
│ Rows Removed by Filter: 1000 │
│ Heap Blocks: exact=13732 │
│ -> Bitmap Index Scan on uc_pk (cost=0.00..43.43 rows=2000 width=0) (actual time=0.057..0.057 rows=2000 loops=1000) │
│ Index Cond: ((customerid)::integer = (c.customerid)::integer) │
│ Planning Time: 0.209 ms │
│ Execution Time: 817.397 ms │
└────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────┘

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-24, Daniel Loth <daniel.j.loth@gmail.com> wrote:

Hi Nicola,

Hi Daniel,
great to see someone else diving in! I hope you will last :)

Thanks for your comments. In particular:

Importantly, and often overlooked as far as MVCC disadvantages go, the
data you read in an hours-long query running under MVCC is arbitrarily
old data. If your query takes 2 hours to complete then the very last
row processed by your query is potentially 2 hours out of date.

Sure, that's a fair criticism of MVCC.

But if Derek can keep all his queries under 12 seconds with an old (as
in "mature") 2PC system, don't you think that a modern MVCC system can
achieve the same or better performance under the same load?

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-25, Nicola <nicola@nohost.org> wrote:

I have just repeated my experiment. The above query takes ~2.4s with
cold data and ~800ms with hot data on my laptop.

There's a bias in my test, because, the way it is generated, the
transaction data is physically sorted by CustomerID. After correcting
the bias, I'm still around 3s anyway for "Subquery SUM", with no
physical optimization. This query performs better:

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Inline%20SUM%20ShowPlan.txt

taking ~1.2s.

To insert data in random order:

insert into CustomerTransaction(CustomerId, SequenceNo, Date, TransactionCode, Amount)
with T as (
select CustomerId,
generate_series(1,2000) as SequenceNo,
(now() - '1 day'::interval * round(random() * 3650))::date as Date,
case when random() < 0.5 then 'C' else 'D' end as TransactionCode,
random() * 10000 as Amount
from REF_Customer
)
select * from T order by random();

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Saturday, 26 June 2021 at 06:25:28 UTC+10, Nicola wrote:

On 2021-06-24, Daniel Loth wrote:

Hi Daniel,
great to see someone else diving in! I hope you will last :)

Yes !

But if Derek can keep all his queries under 12 seconds with an old (as
in "mature") 2PC system

Sorry if I wasn't clear. Almost all my SELECTs, complex or not, execute sub-second. Almost all even at customer sites. The problem is, on their production system, even though I am hired to reconfigure it, I rarely get the power to change everything
that requires correction, there is always some area (eg. SAN config) or some config parm (eg. auditing set up) that I cannot change. Thus I have a few SELECTs on customer systems, that take more than 1 sec, which I remember them well, the worst one
being 12 secs.

2PC

No.
Not even "1PL"; "2PL"; "C2PL"; "S2PL"; "SS2PL", which are Straw Man understandings of Ordinary Locking.
I am trying to give you the real deal on Ordinary Locking, to remove you from the filth that the MV-non-CC mob declare it to be.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Nicola

Great post.

On Saturday, 26 June 2021 at 06:17:37 UTC+10, Nicola wrote:

On 2021-06-24, Derek Ignatius Asirvadem wrote:

On Thursday, 24 June 2021 at 19:33:00 UTC+10, Nicola wrote:

On 2021-06-24, Derek Ignatius Asirvadem wrote:

2. Not only OLAP, any SELECT runs at READ COMMITTED.

Yes. I focused on OLAP to emphasize that I have "complex" queries in
mind.

- Each SELECT holds read locks for the duration of the statement.

Yes.
SQL/ACID requirement,

Fine.

that MV-non-CC suites cannot support.

Trivially, as MVCC does not use locks.

Are you agreeing that MV-non-CC cannot support SQL/ACID ? Finally, hallelujah !!!

Or are you just stating that because it does not use locks, it holds no locks for the duration of the statement ? Pffft.

Take a look at this Benchmark I did against Oracle (MV-non-CC). Where Sybase returned in 2.16 secs, Oracle had to abandon the benchmark
after 120 MINUTES.
__ https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Benchmark%20101204.pdf

I remember finding that benchmark some time ago

Nice to know.

and trying to
reproduce it in PostgreSQL, based on the DDL I found here:

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/DDL%20101201.txt

and the query found here:

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Tony%20Andrews/Subquery%20SUM%20ShowPlan.txt

My only noteworthy remarks at the time were:

1. As bad as Oracle might be, it can't be so slow. It sounds like the benchmark was performed by Sybase people and/or no effort was made to optimize on the Oracle side.

No, no. Of course it was me on the Sybase side. On the Oracle side it was the famous Tony Andrews himself. The Oracle world is huge compared to the Sybase world. He is a hugely famous Oracle expert. He engaged me on StackOverflow about time series
data, that needed a subquery, which I said Oracle cacks itself, which lead to the benchmark.
__ https://stackoverflow.com/questions/4083464/design-database-relating-to-time-attribute
__ https://stackoverflow.com/questions/4375192/performance-of-different-approaches-to-time-based-data

On the contrary, the Oracle side was heavily optimised, both logical and physical. As noted, TA even duplicated the CustomerTransaction table, to elevate the speed. And I had to downgrade the config on the Sybase side. There were a many of those, only
the huge one is mentioned in the doc.

Once he lost out on the Subquery SUM() = ABANDONED, he did not finish the remaining tests (we had agreed to 5 tests per benchmark), hence the empty slots Inline View SUM() on the spreadsheet.

Separate to the ABANDONED after 120 MINUTES, the other tests were

1 Subquery COUNT ___________ 3.0 x slower than Sybase
3 InlineView COUNT __________ 3.6 x slower than Sybase
3 InlineView COUNT Improved 4.8 x slower than Sybase
1 Subquery SUM _____________ Abandoned after 120 mins (Sybase executed in 2.16 secs)
3 InlineView SUM ____________ 26.4 x slower than Sybase
3 InlineView SUM Improved __ 36.4 x slower than Sybase

Not being honest, he tried posting falsities here. Which I had to destroy unfortunately:
__ https://groups.google.com/g/comp.databases.theory/c/IkJyS0MTzUs

Anyway, I don't care about Oracle.

No problem. I referred to it because it is the oldest and most mature MV-non-CC, and it too, does not comply with SQL or ACID.

2. 488MB for the 2M records of CustomerTransaction seems quite a lot.
I've got ~147MB, including indexes (well, one index), in PostgreSQL.

Yes, that figure is odd. Even with the duplicated table. I can’t check it now as that disk image is archived, and my current laptop is two generations newer.

I have just repeated my experiment. The above query takes ~2.4s with
cold data and ~800ms with hot data on my laptop. Ok, that's flash
storage and a laptop one decade newer than the one in your document. But it's stock PostgreSQL with default parameters (which are extremely conservative), with no physical tuning applied.

Excellent. The ‘hot’ figure is the one we want (test is logical not physical I/O).

And considering the next post, re Inline SUM() at 1.2 secs.

Excellent, it is approaching Sybase performance, noting the fact that this is SSD, and the benchmark 11 years ago was on HDD.

So, all in all it's not
so bad for the price.

All in all ? Nah. It is hopeless for the price. No one takes responsibility. Literally the definition of insanity (the premise of the Mental Health Act is that the person is not responsible for their actions).

Put another way, with technical precision, you pay zero for non-SQL and non-ACID. And the thing that you paid nothing for has great performance in single user mode.

Now if we bring it back to the context of locking vs MV-non-CC, run:
__ 10 x OLTP Transactions doing UPDATE CustomerTransaction randomly, and
__ 5 of those SELECTs
__ concurrently.

For the sake of completeness, and possibly for comparison and further discussion, at the end of this message I report my benchmark.

Don't such queries create contention (other transactions cannot
update the data read by the OLAP query until the query is over)?

Correct.
So what (see above).
No one cares about waiting a few seconds.

There are many applications where waiting more than a few hundreds milliseconds might be problematic.

The context of my statement there is the OLAP and SELECTs. Outside that context, yes, we want millisec response. Since 2007, microsec resolution and metrics.

For example, waiting ten seconds
makes a web app unacceptably slow.

The metric at the bank is 2 secs.
The SG metric is 1 sec.

2. I have stated that if you want to get into this, there is a Server Monitoring report that just happens to expose this problem, and the considerations, and with more granularity than “contention” ... but you have not taken it up.
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

The Read Locks are precisely this, SELECT queries (not OLAP, but that makes no difference) contending with OLTP transactions. Do you not
care, that after fixing a SAN Config problem, the Read Locks dropped
by 91%, or what that means at the coal face. Or stated in converse,
why Read Locks were 1,100% higher when the SAN was mis-configured.

I have no doubt that, given your experience, you are able to achieve
great improvements on every system you touch.

Thanks.

My questions are for
better understanding on my part, not to tell you that you should run
things differently.

But that is not what I meant, what >>I<< did is not relevant. Given your [then] concern re Read Locks, and such being held for some duration, and such contending with OLTP Transactions, you may be interested in what that actually looks like, in terms of
metrics in a production server.

Admittedly that is a top-down view. Daniel has given a bottom-up view, and a further level of detail. I would not go there (further levels), not only are there many levels, and differences due to each implementation flavour, it is bottom-up, not good
for understanding. I am trying to get you to think top-down, for understanding.

Integrity within a ResultSet

Meaning:
1. (yes) no phantoms; no anomalies
where that would normally be obtained via REPEATABLE_READ, which demands a Transaction, and implies several SELECTS, I provide it in a single SELECT at READ_COMMITTED, no Transaction.

2. I provide Predicate level Integrity in the ResultSet, somewhat beyond [1]. Yes, the methods exploit platform-specific features, but it can be readily translated to any other Commercial RDBMS. This has more to do with performance issues, less to do
with SQL.

==========

To proceed with this thread then:
__ generally an understanding of OLTP, that is the LARGE SHARED DATA BANK context, rather than mere speed in a single-user context,
__ hopefully resolve my declaration that PissGriss does not support ACID
__ (ie. any MV-non-CC system cannot support ACID)
____ and therefore cannot provide OLTP
__ the two items that remain from my side:
___1 there are two major occurrences in OLTP that have not been mentioned yet, that must be covered before we can consider this subject complete (MV-non-CC types are totally ignorant about this)
___2 Optimistic Locking. We are dancing around it, without confirming its absolute need in OLTP.

Although you have no further questions re the Batch Transaction, that understanding is not complete, it can be completed only when the above outstanding elements are completed.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Derek, Nicola,

But if Derek can keep all his queries under 12 seconds with an old (as
in "mature") 2PC system

So having gone through much of the content that Derek has proffered over time (much of it on StackOverflow), I believe this is mostly owing to the meticulous normalisation that his databases implement.

Your ordinary database out in industry is often very sloppy.
For example, one of our databases at work has at least one table with at least 70 columns, and those columns are sparsely populated (that is, they are nullable and quite often null).
Suppose each of those 70 columns are, on average, 8 bytes each. Each row is therefore 560 bytes wide.

If we keep it simple and assume that the entire 8KiB of a data page is available for row data then we can fit 14 rows of 560 bytes on each data page. So that's a low data density to begin with.

Next, consider how many ways in which you might wish to query those 70 columns. There are many permutations, and supporting even a modest number of those permutations would involve a menagerie of indexes.

On each insert, all of these indexes must be updated.

On each update, all indexes that contain the modified column values must be updated.
If an update causes a page split in the clustered index, resulting in rows being moved to new pages, then this will necessitate updating all non-clustered indexes (because non-clustered indexes contain the clustered index key).

In the specific case of Postgres, I understand they had a write amplification issue a number of years ago. This meant that all updates involved updating all indexes, even where those indexes didn't strictly require an update.
Uber famously blogged about it as their reason for abandoning Postgres. You can read more about it here: https://eng.uber.com/postgres-to-mysql-migration/

For what it's worth, I accept that this is an implementation detail of Postgres and not something inherent in MVCC. Had they designed their MVCC in another way, they might have avoided the problem. I couldn't say with certainty though just how much work
they'd have to do to fix it. Who knows, maybe they have fixed it - Uber's blog post was published in 2016.

Finally, on each delete, all indexes must once again be updated.

Now let's consider what that index count might look like. In practice, it's not that unusual to see an un-normalised SQL Server database table with 10 or so indexes.
If you defer to consultants such as Brent Ozar (of 'Microsoft Certified Master of SQL Server' fame), they'll tell you that you should aim for 5 indexes.

Of course, in our table with 70 columns, it's extraordinarily unlikely that we'll be able to satisfy all of the desirable data access paths with indexes. So a trade-off is made.
We can do one of two things:

1. We can employ more indexes. Each index has its associated cost when performing an insert / update / delete operation, as described above.

2. We can employ fewer indexes. We face lower cost when inserting / updating / deleting data. However it's more likely that a user will run a query with a predicate not easily satisfied by one of the existing indexes, and the DBMS will be forced to
resort to a table scan (in physical terms, a clustered index scan).

---

So given the above, I'll contrast it with Derek's typical implementation and the characteristics as I see them.

Firstly, Derek's implementations are highly normalised. The tables in his models often comprise a primary key (natural key) and several attributes. Tables with fewer columns require far fewer indexes to satisfy the queries that you would perform on them.

In fact, he often mentions that his databases contain no null values. This is due to him modelling optional attributes in their own tables. That is, a table that he migrates the key of the parent table into plus that one additional optional attribute.
This table, comprising a key and one attribute, is in 6NF.

Suppose you have a 'Person' table with a nullable 'MiddleName' column. And suppose in practice that this table contains a million rows, but only 10% of Person records contain a non-null value for MiddleName.

When modelled as a single table with nullable column, there are 900,000 records with a null value.

Derek would lik

Sorry, just a follow-up concerning this remark:

If an update causes a page split in the clustered index, resulting in rows being moved to new pages, then this will necessitate updating all non-clustered indexes (because non-clustered indexes contain the clustered index key).

In fact, this is likely not true even in products using MVCC such as Postgres.

Cheers,
Daniel

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-26, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Trivially, as MVCC does not use locks.

Are you agreeing that MV-non-CC cannot support SQL/ACID ? Finally, hallelujah !!!

I am not sure that by ACID compliance we mean the same thing. You seem
to imply more than what the acronym stands for (or I am taking a narrow interpretation of it). But I am not excluding that you may be right.
Hopefully, we will be able to clarify this.

I have just repeated my experiment. The above query takes ~2.4s with
cold data and ~800ms with hot data on my laptop. Ok, that's flash
storage and a laptop one decade newer than the one in your document. But
it's stock PostgreSQL with default parameters (which are extremely
conservative), with no physical tuning applied.

Excellent. The ‘hot’ figure is the one we want (test is logical not physical I/O).

Ok.

Now if we bring it back to the context of locking vs MV-non-CC, run:
__ 10 x OLTP Transactions doing UPDATE CustomerTransaction randomly, and
__ 5 of those SELECTs
__ concurrently.

Time permitting, I'll try.

My questions are for
better understanding on my part, not to tell you that you should run
things differently.

But that is not what I meant, what >>I<< did is not relevant. Given
your [then] concern re Read Locks, and such being held for some
duration, and such contending with OLTP Transactions, you may be
interested in what that actually looks like, in terms of metrics in
a production server.

With the additional context you have provided, I'm fine.

To proceed with this thread then:
__ generally an understanding of OLTP, that is the LARGE SHARED DATA
BANK context, rather than mere speed in a single-user context,
__ hopefully resolve my declaration that PissGriss does not support ACID
__ (ie. any MV-non-CC system cannot support ACID)
____ and therefore cannot provide OLTP
__ the two items that remain from my side:
___1 there are two major occurrences in OLTP that have not been
mentioned yet, that must be covered before we can consider this
subject complete (MV-non-CC types are totally ignorant about this)
___2 Optimistic Locking. We are dancing around it, without confirming
its absolute need in OLTP.

Although you have no further questions re the Batch Transaction, that understanding is not complete, it can be completed only when the above outstanding elements are completed.

Good. Hopefully, we'll get to paint the full picture.

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2021-06-26, Daniel Loth <daniel.j.loth@gmail.com> wrote:

Hi Derek, Nicola,

But if Derek can keep all his queries under 12 seconds with an old (as
in "mature") 2PC system

So having gone through much of the content that Derek has proffered
over time (much of it on StackOverflow), I believe this is mostly
owing to the meticulous normalisation that his databases implement.

Sure, the whole discussion we are having assumes a proper database
design.

Your ordinary database out in industry is often very sloppy.
For example, one of our databases at work has at least one table with
at least 70 columns, and those columns are sparsely populated (that
is, they are nullable and quite often null).

That must be fun to work with!

Suppose each of those 70 columns are, on average, 8 bytes each. Each
row is therefore 560 bytes wide.

If we keep it simple and assume that the entire 8KiB of a data page is available for row data then we can fit 14 rows of 560 bytes on each
data page. So that's a low data density to begin with.

Next, consider how many ways in which you might wish to query those 70 columns. There are many permutations, and supporting even a modest
number of those permutations would involve a menagerie of indexes.

On each insert, all of these indexes must be updated.

A nightmare. But that cannot be saved by any carefully crafted
transactions.

In the specific case of Postgres, I understand they had a write
amplification issue a number of years ago. This meant that all updates involved updating all indexes, even where those indexes didn't
strictly require an update.
Uber famously blogged about it as their reason for abandoning
Postgres. You can read more about it here: https://eng.uber.com/postgres-to-mysql-migration/

For what it's worth, I accept that this is an implementation detail of Postgres and not something inherent in MVCC. Had they designed their
MVCC in another way, they might have avoided the problem. I couldn't
say with certainty though just how much work they'd have to do to fix
it. Who knows, maybe they have fixed it - Uber's blog post was
published in 2016.

I doubt that things have changed. But, as you say, that is not the only possible implementation of MVCC (and likely not the best).

Now let's consider what that index count might look like. In practice,
it's not that unusual to see an un-normalised SQL Server database
table with 10 or so indexes.
If you defer to consultants such as Brent Ozar (of 'Microsoft
Certified Master of SQL Server' fame), they'll tell you that you
should aim for 5 indexes.

Of course, in our table with 70 columns, it's extraordinarily unlikely
that we'll be able to satisfy all of the desirable data access paths
with indexes. So a trade-off is made.
We can do one of two things:

Or, rather than patching the unpatchable, redesign the database.

So given the above, I'll contrast it with Derek's typical
implementation and the characteristics as I see them.

Firstly, Derek's implementations are highly normalised. The tables in
his models often comprise a primary key (natural key) and several
attributes. Tables with fewer columns require far fewer indexes to
satisfy the queries that you would perform on them.

Correct.

In fact, he often mentions that his databases contain no null values.

I agree with his choice. There are lots of reasons to avoid NULLs, from
the most theoretical to the most practical.

This is due to him modelling optional attributes in their own tables.
That is, a table that he migrates the key of the parent table into
plus that one additional optional attribute. This table, comprising
a key and one attribute, is in 6NF.

Suppose you have a 'Person' table with a nullable 'MiddleName' column.
And suppose in practice that this table contains a million rows, but
only 10% of Person records contain a non-null value for MiddleName.

When modelled as a single table with nullable column, there are
900,000 records with a null value.

Derek would likely model the relationship like this:
Person -> { PersonKey, Attribute1, ..., AttributeN }
PersonMiddleName -> { PersonKey, MiddleName }

I can't speak for Derek, but in this specific example I'd leave
MiddleName as an attribute of Person, just making not NULLable
(actually, part of a key). When a person has no middle name, you may
record an empty string. One compelling reason to keep MiddleName with
Person is that it is used to identify a person.

As a general approach, however, what you say is correct: optional
attributes (and multi-valued attributes, such as the phone numbers of
a person) are separate, dependent, facts of Person.

Person would contain 1 million rows that do not comprise a 30 to 50
character MiddleName column. In SQL Server, each nullable column has
2 bytes of bookkeeping information when the column is null. These must
be processed for every select query executed on the table.

PersonMiddleName would contain 100,000 records. You'd only ever query
these records when you specifically require a Person's MiddleName, so
they don't impose a cost when querying the Person table.
The PersonMiddleName table would also only require a single index to
support those queries, that index being the clustered index.

If we scale this idea up to hundreds or even thousands of nullable
columns across a database then they impose a significant cost.

I am not sure how you would incur into such costs, unless you need to
produce tables with thousands of columns.

Secondly, and perhaps most importantly, MVCC produces a great deal of
litter. Old versions of a row are retained in pages. In my example
above there are 14 rows per page. This is already very low density
because each row requires so much space.

However those 14 rows might additionally include former versions of
a logically equivalent row (that is, former versions of some row for
a given key).

So if there are 10 versions of a particular row on that data page, and
that data page can hold 14 rows, then the page actually contains at
most 5 distinct rows (1 row versioned 10 times, and at most 4 other
distinct rows).

This means that each page of data processed might provide only minimal utility for that processing cost.

So an MVCC-based system is very likely making less progress with each
page that it reads from disk.

That's sensible.

An MVCC-based system is also very likely deriving less utility per GiB
of RAM.

In the sense that buffers can potentially be filled with old records?
Yes, that's reasonable.

Thirdly is the meticulous transaction control. All transactions live
within the database, implemented as procedures.

Again, I agree with Derek on this. Of course, we are talking about
*database* transactions, not *business* transactions (e.g., buying an
item on an online shop is one business transaction, but it is carried
out through several database transactions).

I can think of at least two benefits:

1. A database administrator has the complete picture on-hand. They can readily optimise the database as required because they have
a definitive view of all queries and can determine the indexes
required to best satisfy them.

2. Misbehaving client code can't unwittingly start a transaction,
acquire a number of exclusive locks, and then retain those locks for
a prolonged period of time (thus blocking other users of the system).
And well-behaved client code written with the best intentions can't diligently start a transaction, acquire a number of exclusive locks,
and then unwittingly fall prey to CPU scheduler preemption for an
arbitrarily long period of time.

There are also security benefits: better access control, application of
the least privilege principle, etc.

I say to my students: look at the installation instructions of any
open-source web app. For sure they'll tell you that you should
absolutely give tight permissions to the web directory, that it should
not be owned by root, etc. Then, they go on by making you create
a database and a database user (or, in the best cases, two) to own it.
Then, the web app reads and rides across the database as that user. That
is the moral equivalent of chmod 777 on the file system.

Then, there's the matter of correctness: only the operations on the
database that preserve consistency should be allowed.

On this matter, I posted a new discussion last night (my time) titled
'Stored procedure structure in RDBMS using Lock Manager for
transaction isolation'.

That's a good summary of our previous discussion. Regarding your
request, the expert is Derek!

Fourthly, the databases are modelled and their performance measured
with a whole-of-system mindset.

Many software developers jump immediately to 'de-normalisation' (I've
used the phrase 'un-normalised' above because most of them don't
actually start from a normalised model) because on a micro-level basis
they can write a query that is demonstrably faster for one query.

Of course, that's just one query. What developers often overlook is
the hundreds or thousands of other queries that must co-exist with
this one query.

In my view, this is why MVCC is seen to be working reasonably well by
many in the industry.

Can you elaborate on that? Do you mean that MVCC works well only when
your goal is to micro-optimize, but it is not suitable to obtain
system-wide good performance?

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Nicola,

Can you elaborate on that? Do you mean that MVCC works well only when
your goal is to micro-optimize, but it is not suitable to obtain
system-wide good performance?

Yes, happy to elaborate by way of anecdote.

Joe Developer discovers that he can examine a query plan in a tool like SQL Server Management Studio. He compares two queries:

select FirstName, MiddleName, FamilyName
from Person
where PersonId = 1;

and

select p.FirstName, pmn.MiddleName, p.FamilyName
from Person p
left join PersonMiddleName pmn
on p.PersonId = pmn.PersonId
where p.PersonId = 1;

And rightfully concludes that the former is faster. The former query produces a plan comprising a single clustered index seek. The latter comprises two clustered index seeks, so twice the cost.
Though note that twice the cost, in this example, really means 1 logical read for the former and 2 logical reads for the latter. So twice as expensive, but really that extra expense is marginal.

What this developer doesn't consider is that we might only actually need to select the middle name as part of two or three queries, and that these queries that do require the middle name actually comprise a very small proportion of the workload that the
DBMS must handle.
The developer, an individual working on a task, is focused on their own work and very easily takes a myopic view with little thought for next month, let alone next year or the next five years.

This behaviour is what leads to the aforementioned 70 column table, and the perception that de-normalization leads to higher performance. One person does it, someone else follows their lead, and then it becomes the norm.

Developers make other poor choices too. All of the time, really. Often due to it being expedient to do so due to perceived or real pressure to deliver.

For example, they might write code that updates a substantial number of records within a single statement (which has been touched on in recent discussions here).
They test it locally and it's fine, because they're working with a small set of records locally.
But in production environments with a substantial volume of data this creates an untenable situation in the context of a database that uses a Lock Manager, because the writers-block-readers situation arises.

A series of often short-term expedient decisions are taken and death spiralling ensues. Developers inevitably reach a point where they struggle with slow transactions, excessive blocking, deadlocks.
MVCC is seen as a get-out-of-jail-free card when improper transaction management leads to excessive blocking in a database with a Lock Manager.

This creates a perverse situation where MVCC is seen to be better because it avoids the untenable: A database that might effectively be single-user-only until the aforementioned writers-block-readers situation subsides and normal service resumes.
You could say that MVCC provides a safety net. A poorly behaved query won't necessarily bring the system to its knees. At least not for the same reason as a system relying on a Lock Manager, anyway.

In the Microsoft world, the recommendation nowadays from a number of highly regarded thought-leaders (such as Brent Ozar) is to enable 'snapshot isolation' and 'read committed snapshot isolation' by default (i.e., SQL Server's MVCC).
Pragmatic DBAs accept that developers are going to make a mess of it, and they recognise that in many companies developers are going to be making database design decisions because those companies reject ideas such as Change Advisory Boards having to
approve things. The idea being that CAB is a handbrake on development velocity. And I recognise and accept that a CAB does slow things down in practice (though for the purpose of due diligence).

In this context I would agree that MVCC can work very well in spite of some of the issues previously discussed. But not because of technological superiority when compared with a RDBMS using a Lock Manager.

But to circle back to my remarks about utility, I don't think MVCC can physically outperform a system that uses a Lock Manager.

Reduced utility from resources due to more data pages being required to store the same content (i.e., remnant versions of rows take space).
This means more pages to cache in RAM and thus more contention for this finite RAM.
In turn, more cache churn. Page Life Expectancy is lower, so it's more likely that a page won't be cached when it needs to be accessed. So then there's additional IO pressure.

With those physical realities in mind, I'd expect a database using locking to perform better when implemented properly.

However, I imagine that if you have a database hosted on a system and that database fits entirely in RAM (and has plenty of headroom) then perhaps they perform comparably.

Cheers,
Daniel

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)