From Newsgroup: comp.databases.theory

On Saturday, 14 March 2020 19:02:27 UTC+11, Derek Ignatius Asirvadem wrote:

On Saturday, 14 March 2020 00:11:23 UTC+11, Nicola wrote:

MVCC has its drawbacks and some advantages, especially re concurrency
and performance, compared to 2PC.

2. MVCC has no advantages
(because we are using a comparative word) over the alternative which is Two Phased Locking (2PL). I will grant that academics are clueless about 2PL,

Two Phased Commit (2PC) is a totally different method and implementation. Where the Transaction (in a single client of course), has to modify resources that are resident in more than one SERVER, as a single Transaction, a single Logical unit of Work. 2PC is really a protocol or method, for the synchronisation across multiple servers that is required to obtain that result. The app has full control, it to make the calls.
Whereas 2PL (and MVCC hopefully) is completely internal to the single SQL server, and relevant to the resolution of resource contention for multiple active Transactions.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Saturday, 14 March 2020 19:02:27 UTC+11, Derek Ignatius Asirvadem wrote:

On Saturday, 14 March 2020 00:11:23 UTC+11, Nicola wrote:

MVCC has its drawbacks and some advantages, especially re concurrency
and performance, compared to 2PC.

2. MVCC has no advantages
(because we are using a comparative word) over the alternative which is Two Phased Locking (2PL). I will grant that academics are clueless about 2PL,

Two Phased Commit (2PC) is a totally different method and implementation. Where the Transaction (in a single client of course), has to modify resources that are resident in more than one SERVER, as a single Transaction, a single Logical unit of Work. 2PC is really a protocol or method, for the synchronisation across multiple servers that is required to obtain that result. The app has full control, it to make the calls.
Whereas 2PL (and MVCC hopefully) is completely internal to the single SQL server, and relevant to the resolution of resource contention for multiple active Transactions.
Two Phased Locking is not a very good name, but it is the name given to it by academics decades after the fact of its invention. Before that, during those decades, we just called it THE Lock Manager. There was only one way to resolve resource contention in those decades. (In 1981, when I was at Cincom, and intimately familiar with their locking strategies, I wrote a compete ISAM filing system for Wang 2200 MVP, with full locking, such that it allowed OLTP Transactions. Neither Cincom nor IBM nor I called it 2PL)
The "two phases" are internal to the server, and rarely understood except by capable performance tuners. They do not need exposure for this discussion.
Also not to be confused with Optimistic Locking, a demand of OLTP Standards, which is completely within the control of the DBA/Modeller (facilities must be placed in the created database) and the app developers (they must use the Standard OLTP Transaction Template). Although this is not essential to the MVCC vs 2PL discussion, this may need to be exposed because one needs to understand the way OLTP Transactions have worked, unchanged, since 1960, before one can appreciate the efficiency of /any/ resource contention mechanism.
And the lack of which (abject technical ignorance of ACID Transactions and the related requirements), is the motivation behind the academics push for MVCC in the low end of the market. They have this insane doctrine (dogma?) that they don't know, and they don't need to know, because God (the machine that has not been invented yet) will fix everything. Some day.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

I was recently asked a question about 2PL and the seeker wanted clarification, based on a reference to the ever-changing cesspool known as wikipedia. So I had to arm myself with an air sickness bag and read the damn thing.
____https://en.wikipedia.org/wiki/Two-phase_locking
God help us.
What a totally confused and self-contradictory entry. Without any reference to the real world methods actually provided and actually in use in RDBMS platforms, for over THIRTY YEARS. Ok, so the problem is that space is totally confused, and that is all that the "academics" and "theoreticians" that fill this space with pig poop know.
In order to maintain the scope of this thread and afford a fruitful discussion (whenever you are ready, Nicola), let me clarify that confusion.
1. <<Ordinary Locking>>
(not 2PL but commonly called 2PL) is required for OLTP (known by the imbeciles who are ignorant of OLTP as "low contention" or "high concurrency"), in any platform (HDBMS; NDBMS; RDBMS).
2. That means an <<Ordinary Transaction>>
in a single Server, resolving multiple concurrent Transactions. NOT a Distributed Transaction, that is distributed across multiple Servers.
3. Ordinary Transactions are <<ACID>> (since 1960).
That means the Server provides ACID, in order for the developer to code an ACID Transaction. (It is not possible to code an ACID Transaction on a non-ACID Server.)
4. A <<Distributed Transaction>>,
that is distributed across multiple Servers, requires (a) ACID in each Server, and (b) coordination between the multiple servers.
5. Because each Transaction in each Server is ACID, and Ordinary Transactions are closed with a COMMIT, the coordination protocol is called <<Two Phased Commit>>.
6. Two-Phased Commit does *NOT* require <<Two-Phased Locking>>. They are completely unrelated, but often confused because the idiots think that since the first two of three words is the same, oo oooo, the concept must be the same.
7. <<Two-Phased Locking>
not Two-Phased Commit, is required for Version-based program suites (none of them are servers by any stretch of the imagination) that provide some SQL features and fail to comply with the SQL Standard, such as Oracle and all the freeware/vapourware/nowhere suites, in order to provide some semblance of concurrency control (some fraction of [1] ). As evidenced here [corrections are mine]:

On Saturday, 14 March 2020 00:11:23 UTC+11, Nicola wrote:

MVCC has its drawbacks and some advantages, especially re concurrency
and performance, compared to 2PC [2PL]. Systems that implement MVCC sometimes
do also provide explicit lock mechanisms for the situations where
a 2PC [2PL]-like behaviour is required. The consensus seems to be that such applications are a minority and for the rest MVCC is adequate.

(I hasten not to digress, but this needs to be said. Consensus is not science, science is about facts, it does not need consensus. The consensus is false. The consensus is ignorant of the real world, where all applications require OLTP (Ordinary Locking). MVCC simply does not work. It does not work because
a. it denies the reality in the real world of databases,
b. it breaks the fundamental principle of a database, and
c. it obsesses about the non-reality in the "theoretical" world. They love their fantasies, the fantasies are very very important, given the abject ignorance of the real world.)
Therefore MVCC plus anything is also broken. 2PL is a massive add-on to the broken concept of MVCC, in a pitiful attempt to get the broken MVCC to work.
(There are no advantages to MVCC. I have asked Nicola to enumerate any claimed advantages, but there are none thus far, nothing for me to respond to.)
7. Repeating
<<Two-Phased Locking>
not Two-Phased Commit, is required for Version-based program suites that not SQL compliant , such as Oracle and all the freeware/vapourware/nowhere suites, in a feeble attempt to provide some semblance of concurrency control (some fraction of [1] Ordinary Locking).
----
The precise issue that is well-known and well understood in the high-end of the market (Sybase has 95% market share in financial institutions, but only 5% in the overall market), as implemented for THIRTY YEARS, that the sow-suckers have not touched in THIRTY YEARS, which is by evidence pathological denial of the real world (in both terms of OLTP requirements, and actual platform delivery) for THIRTY YEARS, is this.
- OLTP demands ACID (we have had that since the 1960's)
- Transactions require ACID (we have had that in DBMS since the 1970's; RDBMS since 1984)
- Versioning (MVCC) and ACID Transactions are contradictory concepts by virtue of the principles (separate to the fact that ACID Transactions are real, and MVCC is unreal -- a fantasy, that requires massive resources to erect, and thus is fundamentally opposed to OLTP -- )
--- if you have MVCC you cannot have OLTP or ACID Transactions
--- if you have OLTP or ACID Transactions, you cannot have MVCC
The evidence of over thirty years in the real world is:
- theoretically canonised pig poop such as Oracle or PissGrisNONsql which are MVCC does not have ACID. And cannot have ACID as long as they have MVCC.
- high-end SQL platforms such as Sybase and DB2/LUW have Ordinary Locking; OLTP; and ACID Transactions, with no MVCC. Genuine performance, without the erection of monstrous MVCC fantasies, that do not work, or the second set of monstrosities of 2PL that is required to shore up the first set.
As evidenced, the obsessed cretins, in pathological denial of the reality that their precious academically concocted method does not work (Stonebraker was classified as an academic), and has not worked for over THIRTY YEARS, and in pathological denial of the methods that do work in the real world, and has worked for SIXTY YEARS, throw more "ressoich" at their mountain of pig poop, in the steadfast insistence that what does not work, what has not worked for THIRTY YEARS, will work, some day. It is a massive and ongoing fraud.
Maybe in another thirty years.
Meanwhile, back at the farm, educated people, who are not crippled by the pig poop that the "academics" and "theoreticians" who allege they serve this space shameless market as "theory", provide OLTP; ACID Transactions; using Ordinary Locking, with no problem whatsoever. For over thirty years.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

This linked post is very important info for this thread. And at this point in the sequence.

____https://groups.google.com/d/msg/comp.databases.theory/umEPHEi5FA8/hsZiNXahCQAJ

The whole thread is relevant to those following the Open Architecture discourse.

Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Guys and dolls

This linked post is relevant to this thread, particularly at this point in the sequence. Please read.

____https://groups.google.com/d/msg/comp.databases.theory/umEPHEi5FA8/hsZiNXahCQAJ

That whole thread is relevant to anyone following the Open Architecture discourse.

Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Guys and dolls

This linked post is relevant to this thread, particularly at this point in the sequence. Please read.

____https://groups.google.com/d/msg/comp.databases.theory/umEPHEi5FA8/YuBGXzqiCQAJ

That whole thread is relevant to anyone following the Open Architecture discourse.

Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Saturday, 14 March 2020 00:11:23 UTC+11, Nicola wrote:

MVCC has its drawbacks and some advantages, especially re concurrency
and performance, compared to 2PC [Ordinary Locking].

Still not enumerated, still no evidence for the claim.
In my previous post, I have clarified, that the relevant comparison is: ____Ordinary Locking vs MVCC

Systems that implement MVCC sometimes
do also provide explicit lock mechanisms for the situations where
a 2PC-like behaviour is required.

Therefore, more accurately, because MVCC does not work and needs "2PC-like behaviour" [2PL] to not-work a little better, the comparison should be:
____Ordinary Locking vs MVCC + "2PC-like behaviour" [2PL]
The "academics"; "theoreticians"; "researchers"; "professors"; "lecturers", are all severely arithmetically challenged (not mathematically challenged, which they are, overall, but in this instance, arithmetically [addition; subtraction; etc] ).
Let's see if we can enlighten them a little bit.
ACID, OLTP & Ordinary Locking
- the principle is Serialisation
- maintains a single version of the Fact, a single version of each page
--- no additional versions, therefore no additional pages
--- no serialisation of additional versions, because there are no additional versions
- a simple locking strategy (please ask if you would like more detail)
--- which is performed by in-memory structures (hash tables; lock chains; etc) - minimal OLTP: works fine with demented databases and RFS
--- full OLTP requires SQL code to be constructed to the OLTP Standard
- no conflict resolution, because conflicts have been prevented
MVCC + 2PL -- [ACID, OLTP Not Possible]
- the principle is Isolation, not serialisation
--- which is stupid, because at the COMMIT TRAN, ooh oooh, the freaks have to serialise the entangled log jam of versions of pages
--- the deranged obsess about their subjective reality, which is:
--- a. in contradiction to the objective reality of the common database that they have their furry paws on, and
--- b. therefore they elevate the notion of ISOLATION (the [I] in ACID) to the level of deity, while
--- c. denying pathologically that all four components of ACID are integrated (cannot be isolated), and
--- d. denying pathologically that even in that isolated ISOLATION, the ISOLATION LEVEL required is SERIALISED. Which is guaranteed to bite them in the bum when they get to the COMMIT
- for every user that indicates an update intent (BEGIN TRAN), it maintains
--- a version of each page touched
--- a version of each page that a touched page is dependent upon
- at COMMIT time, it attempts to serialise the *Transaction* (not page), all the versions of pages touched by that Transaction, versus all the versions of pages that were either before or after the touched pages, which means a massive amount of work, on a massive data set, including resolution of conflicts
Arithmetic
Memory space for pages
- Single version of a page = 1
- Multiple versions of a page = n, where n is every active user, and at least greater than 1
Memory space for dependency pages
- Single version of a page = 0
- Multiple versions of a page = m, where m is at most every active user, and at least greater than 1
____ 1 < n
____ 1 < m
Memory space for Locking (OL)
- minimal: locks and lock chains = p
Memory space for Non-Locking (MVCC)
- zero, but no tickee, no workee = 0
Memory space for Non-Locking (MVCC) to not work a little better (2PL)
- minimal: locks and lock chains = q
____ p > 0
____ p <= q + 0
----
The arithmetic that is required to figure out that if they implemented Ordinary Locking in the first place, they would not have to support multiple false versions of the single version of the truth in the second place, and "2PC-like behaviour" [2PL] in the third place, is too much for the hackademicks and feareticians to contemplate. I will leave that for another post.
----

The consensus seems to be that such
applications are a minority and for the rest MVCC is adequate.

Fantasy, not fact.
That, as usual, allows the freaks to maintain their divorce from the real world, their pathological isolation in their ivory towers overflowing with pig poop, from what we have had since the 1960's, the reality that most systems are contentious on a small percentage of data. Which is again, evidenced by the masses of problems in MVCC systems. Which is what motivated the freaks to *ADD* "2PC-like behaviour" to MVCC.
This is precisely the reason that even a small data cache elevates the overall performance of a multi-user database. Noting of course that the freeware/vapourware/noware and Oracle have no cache.

Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-07-27, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

On Saturday, 14 March 2020 00:11:23 UTC+11, Nicola wrote:

MVCC has its drawbacks and some advantages, especially re concurrency
and performance, compared to 2PC [Ordinary Locking].

Still not enumerated, still no evidence for the claim.

It seems to me that you believe that systems based on MVCC cannot
provide true serializability (I qualify "serializability" with "true" to
make it clear that it means something different from the SERIALIZABLE
keyword in SQLrCothe latter having an ambiguous definition).

First, let us agree on what "true serializability" means: concurrent
execution C of transactions T1,...,Tn is (truly) *serializable* iff
execution C is equivalent to a serial execution S of T1,...,Tn, where "equivalent" means that (*)

(a) C and S contain the same operations (reads and writes);
(b) conflicting operations (r/w or w/w on the same object) appear in
the same order in C as in S.

(*) not an entirely accurate definition, but sufficient for our
purposes.

Traditionally, the way to achieve true serializability is 2PL plus
predicate locking. Elegant, relatively simple, and still relevant!
I love 2PL, just in case you think I want to dismiss it as obsolete or something.

Also traditionally, MVCC does *not* provide true serializability, but
only (snapshot) isolation. Your critique of such "classical" MVCC is
entirely justified.

Since about a decade (Cahill, "Serializable Isolation for Snapshot
Databases", 2009), though, it is known that MVCC can be modified so as
to provide true serializability, while still keeping its main
properties, e.g., that writers do not block readers and readers do not
block writers.

So, both 2PL and (modern) MVCC can be used to achieve true
serializability (that is, by definition, correctness). Compared to 2PL,
MVCC does not block a transaction in the presence of concurrent r/w or
w/w, so in this sense it is "better" than 2PL, because it increases concurrency. On the other hand, under MVCC a concurrent schedule is
equivalent to *the* serial schedule given by the ordering of the
timestamps of the transaction. So, there are interleavings of operations
that would be accepted under 2PL (=> they would produce a correct
result), but they are rejected under MVCC (i.e., they cause one or more transactions to rollback). So, MVCC also restricts the possible
interleaving of operations, thus *reducing* concurrency.

IMO, 2PL vs MVCC has no clear winner. In my previous comments I reported
what I have found in recent literature, i.e., that most recently
developed systems adopt MVCC rather then 2PL, and that the reason seems
that MVCC tends to perform better under certain (most?) loads.

Regarding my comments about user-defined locks in MVCC systems, that
refers to the (rare) situation in which, for some reason (usually,
speed), the developer decides to use weaker isolation than true
serializability and take the responsibility of managing concurrency.
When running transactions with ISOLATION LEVEL SERIALIZABLE in
PostgreSQL, you get true serializability and there is no need for locks.

You may have Oracle in mind when you think about an MVCC implementation.
That falls into "classical" MVCC, so that does not guarantee
correctness.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 30 July 2020 01:42:49 UTC+10, Nicola wrote:

Thanks for your response.
Before I respond in detail, which I will in a day or so, it appears to me that there is still a wide gap, between what I am trying to explain, and what you have understood from that. Which I take to be my responsibility to close.
Note that this gap is somewhat of a subset of the larger gap, that between what the real world (high-end platform suppliers; their theory-plus-practice engineers; and theory-plus-practice practitioners such as I) has actually had for forty years, and what the "theoreticians" and "academics" who, by the evidence, are totally ignorant of the real world, and steadfastly maintain that divorce, but theorise about their fantasy world. And the products that ensue from that theoretical world, that they impose on the low- and middle-end of the real world: hilarious freeware that purports to be modern but in fact is stone age.
You are one of the very few who has shown some interest in closing that gap. Perhaps it would reduce the need for writing papers that solve problems that we do not have, or that were solved forty years ago.
You have responded to a couple of detail points, but it appears to me that you have not appreciated the full context of my posts.
I am saying the edges or boundaries of the gap is still not being understood for what it is, and may need sharp definition. At the real world end, in hierarchic order, it is:
1. Shared Online Database Mindset (single version of the truth)
-- Online Transaction Processing Standard
2. ACID Transactions and a Transaction Mindset
3. Relational DBMS, Relational Data Modelling (IDEF1X)
4. Open Architecture Standard
5. Objects are pedestrian, simple, used to construct GUIs, and nothing but.
At the "theoretician" end, it is:
1. "I live in a fantasy world divorced from reality. I don't know what Online or Shared means. In my fantasy world, there is on the unholy trinity of Me; Myself; and I. No one else to be concerned about. The snapshot I have taken of the database is permanent. The database will not change while I fiddle around and figure out what to do with my precious snapshot."
That means multiple versions of falsity, because the database is in a state of change.
2. "I Dropped ACID." Famous words of the chief sow sucker.
"I don't know what ACID is, and I don't care to find out. Anything the platform suppliers do to supply ACID is fine with me. As far as theory is concerned, a transaction is:
__ any and all SQL commands between BEGIN TRAN and COMMIT TRAN"
That theory is one which is 100% theory-not-pratice because it cannot be placed in a practical situation, which is limited by the Laws of physics, such as applies to transaction size; concurrent users; etc. And therefore the required theory-for-practice is completely absent. For forty years (sixty years if taken from 1960 CICS/TPC).
3. 1960's Record Filing System, fraudulently credited to Codd (RM/T), placed in an SQL container for convenience (ease of access; backup & recovery; possible security; etc).
No data modelling.
No Standards.
"Because I did not invent them."
4. An OO, and later an OO/ORM Mindset.
"I have just one tool in my toolbox, an Object. Genuflect at the door, and bring me your oldest son for a holocaust, we can eat afterwards. The entire world can be perceived in terms of My Glorious Object. I can do Anything with My Precious Object. There is nothing that cannot be done via Some Wonderful Object.
"Oh crap, I just need it to be persistent, in order to enforce my picture-perfect Object that contains my ancient permanent snapshot (see, it is still in my hands, it has not changed) of the records, onto the real world that rendered the snapshot obsolete ages ago.
"At the end, we always glorify our great Saint of Porcine Recta, Saint Stonebraker himself, and spell out P E R S I S T E N T using pieces of pig poop, and V E R S I O N using chicken bones. And last, we glorify him for justifying and validating the cult of the unholy trinity."
"Oh crap, the OOs were not correct. No problem, I can change it in a month or three. Oh crap, that means the Glorious ORM that has never worked needs to be completely changed. No problem, I invented Refactoring."
"Never mind that the structure of the universe (real world) has not changed, that a Relational database that implements the facts about the universe does not need to change. I don't know anything about the real world or the science that implements it in a computer system. I am obsessed with My Glorious Object and My Persistent Snapshot."
Schizophrenia. Certifiable, pathological schizophrenia.
Plus delusions. Imposed on the young, so that the delusions are now an Ideology, The Ideology for the "theoreticians" and "academics" in this field.
----
Please see if you can appreciate that gap, and perhaps respond to some of it. ----
Clarification request.

(I qualify "serializability" with "true" to
make it clear that it means something different from the SERIALIZABLE
keyword in SQLrCothe latter having an ambiguous definition).

News to me. Forty years of platform suppliers supplying SQL-compliant platforms, thirty six years of me using SQL with no problem whatsoever, which means SQL has a definition for SERIALIZABLE that is not ambiguous. And now this, from academia. Please explain.
----
I will respond to the detail in your post in a day or two.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Friday, 31 July 2020 21:27:27 UTC+10, Derek Ignatius Asirvadem wrote:

Please see if you can appreciate that gap, and perhaps respond to some of it.

You may wish to familiarise yourself with a thread re Transactions. As usual, The "theoreticians" were so scared of admitting that there were clueless about the subject, despite my efforts to assist them, the thread did not progress very far, and they were able to reinforce their miserable ignorance and divorce from reality. Nevertheless, my posts therein does have relevant material, which neither you nor I should have to repeat in this thread.
The Theoretical Void in the Relational Database Space - Transactions
__ https://groups.google.com/forum/?oldui=1#!topic/comp.databases.theory/qqmnhu036FQ
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Friday, 31 July 2020 21:27:27 UTC+10, Derek Ignatius Asirvadem wrote:

On Thursday, 30 July 2020 01:42:49 UTC+10, Nicola wrote:

I am saying the edges or boundaries of the gap is still not being understood for what it is, and may need sharp definition. At the real world end, in hierarchic order, it is:
1. Shared Online Database Mindset (single version of the truth)
-- Online Transaction Processing Standard
2. ACID Transactions and a Transaction Mindset
...
At the "theoretician" end, it is:
...

I am saying, if we do not agree (have the same definitions for) that OLTP/ACID Transactions is absolute fundament, the backstop, and that the OO/ORM/MVCC crowd are against that, we are not going to get very far discussing subordinate issues such as "2PL" vs MVCC, or whether it works or not.
Even the name is a filthy lie. Sure, it has something to do with Multiple Versions, but it supplies nothing at all about Concurrency, in fact it denies Concurrency, and does not effect Control of any kind, on the Versions or the Concurrency. Thus five vendors have five completely different implementations of it. The honest name is Multiple Version Support, and should be branded as mutually exclusive with OLTP (not "2PL").
A scientific name would be Offline Version Support.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

I'll come back to the rest of your posts at a later time. For now:

On 2020-07-31, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Clarification request.

(I qualify "serializability" with "true" to
make it clear that it means something different from the SERIALIZABLE
keyword in SQLrCothe latter having an ambiguous definition).

News to me. Forty years of platform suppliers supplying SQL-compliant platforms, thirty six years of me using SQL with no problem
whatsoever, which means SQL has a definition for SERIALIZABLE that is
not ambiguous. And now this, from academia. Please explain.

What do you mean by "SQL-compliant"? ANSI standard? AFAIR, ISO/ANSI
standard does not formally define the term "serializable" (a formal
definition can be found in any decent database textbookrCosee also my
previous post) (*). And ANSI's semantics for SQL's statement ISOLATION
LEVEL SERIALIZABLE is not satisfactory, because it is stated in terms of
what anomalies (or "phenomena") should be prevented (dirty reads, non-repeatable reads, and phantom reads). It is not satisfactory
because, e.g., snapshot isolation does prevent those anomalies, yet it
does not provide serialization. Even worse, not even those phenomena are
well defined in the standard, or in the "classical" literature for that matterrCoa more rigorous taxonomy for transaction anomalies has been
proposed only after 1995 (see below) and especially in 2000 by Adya,
Liskov and Neil.

"A Critique of ANSI SQL Isolation Levels" (1995) provides probably the
first insight into the problems of the ANSI standard's definitions, and
it does the job much better than I could do in a few words. Despite that critique (and subsequent work, see cit. above), the standard has never
been changed.

That's why Oracle can claim that it provides an ANSI-compliant
SERIALIZABLE isolation level, and yet it does not guarantee "true serializability" (because, AFAICT, it provides only snapshot isolation).


Nicola

(*) The standard just says that "A serializable execution is defined to
be an execution of the operations of concurrently executing
SQL-transactions that produces the same effect as some serial execution
of those same SQL-transactions", but what "having the same effect" means
is left unspecified.
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Saturday, 1 August 2020 13:55:00 UTC+10, Derek Ignatius Asirvadem wrote:

Here, try this. I can define both Transaction, and ACID, in one sentence each, using technical English. The definitions will suffice as instruction to developers who code the apps that I design (have sufficed for thirty six years).

You are one of the very few academics who are trying to understand the real world. Now try defining Transactions and ACID. Without reference to the academic redefinitions, which are already proved to be pig poop, "ambiguous", "not satisfactory". Do the scientific thing, go back to first principles, refer to the practitioner definition from 1960 (or 1965 or 1980, but nothing after 1980, nothing written by an academic, no rCLliteraturerCY).

Come to think of it, in case you take this thread to conclusion, in order to reduce typing for both you and me, the order required for the definitions are:
1. Define OLTP
2. Define Transaction, which must be in the context of [1]
3. Define ACID, which must be in the context of [1][2].
To be clear, I am not asking you to define OLTP Standard, which guarantees the highest level of OLTP; no deadlocks; etc, which is commercial; already defined; and fixed. But in the progress of [1][2][3], you may come to appreciate that *A* standard is required for OLTP, and that the elements are not in the server or suite of programs pretending to be a server, but dependent upon the server or suite behaving a certain way. Which is why commercial DBMS make a claim to be an "OLTP Server" or not.
Caveat
A definition that ignores the Laws of Physics, ie. that is not practicable, is not acceptable. Eg. the theoreticians' infamous definition that a transaction is anything and everything between a BEGIN TRAN and a COMMIT TRAN.
If you wish to use a real world example, by all means, do so. Feel free to use this, which I recall you may already have some familiarity with:
__https://www.softwaregems.com.au/Documents/Documentary%20Examples/Order%20DM%20Advanced.pdf
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

The following thread is relevant to this one. It provides a discussion in an ACID Transaction context, which MVCC does not have, and cannot do (MVCC is Anti-ACID; MVCC is Anti-Transaction).
__ https://groups.google.com/g/comp.databases.theory/c/LSoYp9wrv0M

Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2