> On Saturday, 24 July 2021 at 20:47:50 UTC+10, Daniel Loth wrote:
>
> Welcome back.

Thanks !

> I’ll reply to things you've wrote across both of your emails.

It appears you have read this post:

__ > On Friday, 23 July 2021 at 22:36:02 UTC+10, Derek Ignatius Asirvadem wrote:

but not this post:

__ > On Saturday, 24 July 2021 at 19:48:47 UTC+10, Derek Ignatius Asirvadem wrote:

Also, it may be that you do not have the updated doc (4pages), the earlier version was 2 pages. The Lost Update problem is fully defined with an example Data Model and detailed code.

__ https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Transaction%20Sanity.pdf

> ---
> > What precisely does [D]urable mean ?
> It means that the outcome of the transaction has been successfully committed.
> So it has been written to disk (and potentially one or more replica / follower nodes), and won't be lost should the system crash (or the power go out).

The IBM link I gave for reference:
__ https://www.ibm.com/docs/en/cics-ts/5.4?topic=processing-acid-properties-transactions
states:
__”After a Transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.”

1. There is no mention of COMMIT (although we know that “completes” means successful COMMIT).
2. The declaration is quite different, in that Durable means the Transaction persists, rather than about system failure, or how that i accomplished.

So did the example Transaction persist, or was it wiped out by a similar Transaction ?

- by your definition of [D]urable, the Transaction is fine, the Lost Update is unknown (happens all the time, and people are ignorant that it happens)..

- by the IBM definition, the Transaction failed, it did not persist, it is not [D]urable. The Lost Update is known.

But how is it prevented ?

> > Two down, two to go.
> I know one is lost updates, though I see you've addressed it in your email.

Correct.

> I read the PDF you put together years ago: http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Transaction%20TTM%20101122.pdf

Crikey. A bit of context is demanded.

>>>>
Eleven years ago, I had just come out of pure high-end consulting (ignorance of the madness that was happening in the 95% of the market), and entered into helping people outside my ambit. I had not had the pleasure of reading the literature (no need because Cincom/TOTAL and then Sybase/SQL Server [now Sybase ASE] were decades ahead of it). I had the starry-eyed view that Date; Darwen; Fagin; etc, were well-intentioned in their promotion of error as “relational”, and merely ignorant academics who were divorced from the industry due to the declaration that theory should not concern itself with implementation. So I took to TTM to engage and inform them, to clear the ignorance. That is, I too, believed in the marketing that they were the “curators” of the /Relational Model/, after all the SQL Committee were treating them as such, and making stupid changes.

In hindsight, that was my first crusade, I had made an awful lot of money and I wanted to give something back to my profession; I was responsible enough as a scientist, to correct errors; and I went to the source of those errors and addressed them directly.

It took three years of hard labour at the TTM gulag, for me to realise:
- they are stupid academics, caught up in their academic denial of reality, so that they can manufacture a collective subjective “reality”
- they practice, and teach, schizophrenia, not science, which they have no interest in
--- worse, they suppress science, and market schizophrenia. Eg. they argue for years about any particular subject, resolving nothing (no Four Laws of Thought)
- they never had any impact on the implementation platforms, and due to their evidenced capability, they never will
--- (the exception is of course Codd, but he was not one of them, he was typical of the scientists who worked for the DBMS implementation platforms, and progressed them. He had specific implementation goals. The /RM/ is a progression of HDBMS and NDBMS, not at all an entirely new and different thing. Yes, of course it is the first to have a defined theoretical basis; a mathematical definition; it is based on FOPC.)
- they hate Codd. They take every opportunity to deride him and demean his work. the /RM/, 80% of which is completely false and dishonest (eg. Straw Man arguments), and 20% stupidity (eg. expecting the /RM/ to teach database design and how to tie their shoelaces). Even an undergrad knows that a paper defines the proposal, not everything prior that the proposal depends upon.
- and thus that they were intentionally suppressing the Logical /RM/; intentionally articulating Physical 1960’s Record Filing Systems as “relational”, as a “progression of the /RM/“, and fraudulently using RM/T and the 1971 paper as a basis for that.
- at the top of the food chain, it was not the entrenched stupidity and ignorance of academics, which all of them suffer from and protect, but purposeful evil: they are propagating anti-science
- all the textbooks are written by such freaks, all the so-called professors teach such filth without examination, deeming themselves too, as schizophrenic anti-scientists
- to remain divorced from the theory for FIFTY YEARS; to remain ignorant of actual Standards and implementation platforms for FORTY YEARS, does not happen accidentally, or as a consequence of stupidity and ignorance, no, that happens only by design

During that three years, I innocently and diligently addressed each of the several idiocies that TTM is: “deferred constraint checking”; “headings”; “relvars”; etc. Then there was the business where they were going to “define” “transactions”. Oh wait, for FORTY YEARS at that point they divorced themselves from implementation concerns and had nothing to say about implemented SQL platforms that supplied ACID Transactions, but now (then) that they were defining a Torrid Manifestival (manifestation of fantasy) to replace SQL, they were going to define “transactions”. From scratch. Reinventing the wheel. On the pretended basis that it did not yet exist. An imaginary piece of string that connects unidentified verbs together.

So I tried desperately, to tell them about considerations in an OLTP context (rather than the isolated tiled room they insisted is “reality”). As always with academics, at first, they expressed interest and engaged, but less than half-way through they just stopped engaging. SO nothing was resolved, which is their end game, they need that non-resolution, so that they can circle-jerk and argue amongst themselves without producing a single thing as they have done for FIFTY YEARS (then FORTY YEARS).

They wanted to drop ACID, per per one of the chief sow-suckers papers, and they were fantasising just like they had dropped some, and it was a bad trip. But they were partying in the sow stall in company, so they had a lot of confidence in their collective fantasy. The only Dates he has had is with a 300 pound sow. Nothing real; nothing concrete or “concrete”; the “definitions” kept changing every time I brought up a point from Reality.

As you can see from the Transaction Grid, I was entertaining their fantasy (Green, “RM TTM/D”) and comparing it against the ACID Transactions in implementations since 1965. The “RM” in the heading is of course false: it was a concession at the time, as I had not yet determined their evil intent.

On that document, there was another six pages to go, to achieve closure (either a fixed definition of TTM/“transactions”, xor them conceding that ACID Transactions were established since 1965, and again in the 1980’s on SQL platforms, and it was unshakable.
<<<<

Please ignore that doc. It had a specific purpose, a drawn-out discourse and argumentation re TTM/“transactions”.

Please be assured, I am giving you a complete discourse in this thread, as requested, without the interference of the TTM or academic insanity. This thread and the doc I have given here, is far more detailed, and will be complete at the end.

But the cat is out of the bag. The problem now is, you have knowledge of the label of the problem (third of four categories), and even the label for the solution (for all four), but you do not yet understand the problem properly, which is an essential requirement for coding for OLTP without errors (just coding per a Template does not work, understanding it is essential). I am giving the course I give to customers, minus the slides and a bit of proprietary stuff. The course has not changed since I first created it in 1993, it is stable, it has a sequence, and the sequence has a purpose: incremental learning and thus full comprehension.

> Now I'm not entirely sure if it's in the same class of problem as anomalies / deadlocks / lost updates, but since you've spoken about sociable behaviour already I'm going to go out on a limb and say 'contention management'.
>
> With proper contention management leading to higher performance, and poor contention management (e.g.: gratuitously acquiring locks needlessly) leading to low performance.

Yes, correct.

Not only acquiring and holding more locks than necessary, but reducing (a) the locks held, (b) the lock duration, (c) holding locks at the highest level, thus holding fewer locks, and (d) preventing deadlocks with an Access Sequence.

----

I see that the four categories of OLTP considerations/problems well-known since 1965, and solved 1965 for mainframes; 1984 for SQL platforms, is not entirely clear. I will clarify that in the next version of the Transaction Sanity doc. To identify that here, and mark the progress of this thread:

1. Phantoms & Anomalies
This category is well-known because it was documented in the IBM/CICS/TCP; Britton-Lee; Sybase; MS; DB2 manuals. So I have not expanded it here. Even the academics got to know about it about two decades afterward, it appears in their “literature”.

2. Lost Update
Defined somewhat in my response to TTM. Defined in full in the Transaction Sanity doc. We need to continue the interaction until it is fully understood.

Question. What is the *BASIS* for the user having confidence that their Transaction will (should) work ?
Stated otherwise, why did the Lost Update (page 4) happen ?
How do the naïve developers who use an OLTP/SQL platform (especially if they have only MV-non-CC+application locking mindset) prevent Lost Updates ?

3. <Not Identified>
[B.2] in the Transaction Sanity doc.
Interaction and discourse to proceed when [2] is complete.

4. DeadLock
Explained, covered, solution given.

----

> And I suppose the answer to that is 'optimistic locking', which we've discussed.
> Optimistic locking being achieved by the retrieve-validate-execute pattern we've also discussed.

Yes, we have discussed it some. No, it is not complete. The Template is about 75% complete.

> Regarding the Transaction Sanity document, I read through it when you first shared it two weeks ago.

Great.
Note, it was 2 pages, it is now 4 pages, please download and read.

> I'm already reasonably familiar with some of the items described.
>
> For example, I know the bit about 'Client Application (Multiple)' ties in with your Open Architecture document here: http://www.softwaregems.com.au/Documents/Article/Application%20Architecture/Open%20Architecture.pdf

Excellent.

> I'm also reasonably across the notion of fully constraining the data using declarative constraints, and the methods that can be used to achieve this (e.g.: enforcing exclusive basetype-subtype using check constraint, as documented here: http://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Subtype.pdf

Excellent.

That means you understand that the notion of logic (“business rules”; “database rules” “transaction logic”; etc) that is deployed in the client or middle tier is hysterically stupid (but heavily prescribed by the academics, who are, as evidenced, clueless about SQL). The database in completely self-contained; entirely independent; a single recovery unit.

> The notion of 'ResultSet (multi-statement) Integrity' I'm not so sure about.
> That's probably the one concept that I haven't encountered elsewhere in your writing. Or at least I haven't encountered it named as such.

Phantoms & Anomalies
We don’t want rows that appeared at the top of a ResultSet to have disappeared or changed by the time the server reads the rows at the end of the ResultSet.

__ Statement level integrity
I think you understand that.

__ ResultSet level integrity
We do not want disappeared or changed rows specially if the row at the end is a SUM() of rows prior, such as in a typical bank statement or invoice. Eg. the bank statement or invoice is achieved by
____ SELECT detail rows account and period
____ UNION
____ SELECT SUM() for account and period
In a report, as distinct from the bank statement for external publication, one may list all accounts.
Thus integrity of rows is required across Statements that form a single ResultSet.

The *SQL* prescribed method is:
__ ISOLATION LEVEL 2 REPEATABLE READ

But that is not reasonable unless a Transaction is opened, and we don’t want to go into full transaction mode for a SELECT, so the way each SQL Platform implements that is slightly different, but the contention reduction is vastly different. Without having to think too much, most people use:
____ BEGIN TRAN -- now we are holding locks until COMMIT
____ SET TRANSACTION ISOLATION LEVEL 2 -- default is 3
____ SELECT ...

Sybase (and MS, at least in the older versions that I have used) allows *SELECT* AT ISOLATION REPEATABLE READ, thus even in a transaction, one can reduce the Isolation Level on each SELECT. For Relational tables (genuine Clustered Index),
Sybase & MS executes Level 2 at Level 3, regardless of a Transaction being opened or not.
Basically, within a Transaction, one can request a lower degree of locks; outside a Transaction, one can request a higher level.

The freeware and Oracle do strange things, as they are neither SQL nor ACID compliant.

__ ResultSet level integrity/SG
Most of our customers are large Aussie banks (require the bank statement report of all accounts). Having internal knowledge and proprietary methods, we provide ResultSet integrity at READ COMMITTED, meaning less locking, and a lower degree of locks, outside a Transaction.

Transaction Sanity doc updated (minor clarifications made as per above).

Cheers,
Derek