From Newsgroup: comp.databases.theory

On Monday, 5 July 2021 at 11:05:19 UTC+10, Derek Ignatius Asirvadem wrote:

---------------------------------------------
-- To Progress This Thread --
---------------------------------------------

On Sunday, 4 July 2021 at 20:41:52 UTC+10, Derek Ignatius Asirvadem wrote:

Thus, in addition to
a. clarifying the purpose of your code and progressing it,
b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
__ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.

__ And of course [c] the solution.

Now, for this thread, [a] is cancelled. Please place such discussions in the relevant thread.

Please keep [c] in mind, as the goal, and proceed with [b] in order to progress in that direction.

These issues relate to Transactions, as we have had since 1965 (pre-Relational DBMS) and since 1984 (Relational DBMS), and which concepts and definitions cannot change. That was established entirely by the DBMS vendors, without any help from academia (with the exception of Codd in the post-Relational context).
The problem is, of course, the filth of academia imposing itself on reality, fifty years after these things were established. And the typical disgusting dishonesty, such as redefinition of established terms, usually without even the courtesy of declaring it. While insisting that they should not be concerned with implementation issues, they have contradicted themselves and implemented suite of programs for Anti-OLTP; Anti-SQL; Anti-ACID Transactions; and No Concurrency Control, fraudulently labelled "MVCC", using 10,000 enslaved young minds located across the planet.
These issues have been discussed here on c.d.t, with Nicola, the one academic who is attempting to cross the great chasm between their steadfast isolation from reality, and reality. For two years. Although I welcome it, and provide detailed answers, it has moved in fits and starts, and while progress is made, no clear directives are obtained.
In contrast, Dan has started this thread, which has a clear and finite goal [c], as per the title.
In order to:
__ clear up the confusion that has ensued,
__ define the whole context, in a consolidated manner
__ ie, identify all elements, and place them in the proper context,
__ maintain Sanity in the face of encroaching insanity, such that we can progress, hopefully to closure,
I have produced a document (cut-paste what I can, from lecture slides) which you may find helpful.
It shows:
__ the elements that we have discussed and closed,
____ which are fully articulated in Dan's GitHub thread
____ let's call that Chapter One
__ as well as the two elements [b] that need to be identified (redacted, but shown in proper position)
____ meaning that Chapter One is not complete
__ such that I can provide the Solution for the entire set of issues [c]; for the entire Chapter One
____ Let's call [c] Chapter Two
__ https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Transaction%20Sanity.pdf
If anything is not crystal clear, please ask a question.
As you may notice, it covers our Chapter One, as progressed thus far, it is waiting for the completion, and then Chapter Two.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Dan, Nicola

This week is super-busy for me, I will be offline.

I endeavoured to clean things up, so that you can proceed unhindered, and to restate the particular steps required to progress this thread to closure.
__ identify two more Concurrency problems that need to be prevented in an OLTP context.
__ then, next week, I will provide the one solution that prevents both.

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

From Newsgroup: comp.databases.theory

Dan, Nicola
This week is super-busy for me, I will be offline.
I have endeavoured to clean things up, so that you can proceed unhindered. I have also provided a doc that illustrates the problems in *Full Context*, hopefully to avoid dealing with fragments out of context. To restate the particular steps required to progress this thread to closure:
__ identify two more Concurrency problems that can't be prevented by SQL or ACID Transactions, that need to be prevented in an OLTP context (items that are well-known since 1965)
__ then, next week, I will provide the one solution that prevents both.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Dan, Nicola

On Monday, 5 July 2021 at 22:00:03 UTC+10, Derek Ignatius Asirvadem wrote:

I am back, please forgive the delay. It is madness over here. Worse lockdown conditions in two years. Nothing like a plandemic and a fear-mongering media, slaves of Big Pharma.
Two weeks and no activity. Come on, guys, this thing needs interaction. Proposal. Question. What if.
We have left the me, me, me-centred toilet, we have stopped looking in the mirror and repeating "I am a chick magnet". We have entered the big bad world of reality, where conflicts are no big deal because we are social, civilised people, and we have civilised ways resolving conflicts. No need to stay at home and clutch the obsolete private version of the database.
OLTP (learned some, more to go)
ACID (learned somne, more to go)
Transactions (good start)
SQL
We know about Phantoms; Anomalies; Deadlocks. We can dismiss "Serialisation Anomimimilies" as the particularly stupid way that people who think they have invented the wheel collide with the reality of the road.
What else could go wrong ?
What precisely does [D]urable mean ?
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Guys and dolls, lays and germs, boys and girls

On Friday, 23 July 2021 at 22:36:02 UTC+10, Derek Ignatius Asirvadem wrote: Two weeks and no activity. Come on, guys, this thing needs interaction. Proposal. Question. What if.

We know about Phantoms; Anomalies; Deadlocks.

We did, from 1965. There are four categories of error that we know about and have solutoins for, those are two out of four.
The academics found out about it in somewhere in 2002.

We can dismiss "Serialisation Anomimimilies" as the particularly stupid way that people who think they have invented the wheel collide with the reality of the road.

That is not one of the four categories, it is a unique feature of PissMyselfGross. MySQL and Oracle do not have it. But hey, the academics have written about 100 papers about it, so they think they invented it (in denial of the fact that other MV-non-CC program suites Ingres/Oracle/MySQL had had it for 20 years before they noticed).

What else could go wrong ?

Two down, two to go.

What precisely does [D]urable mean ?

https://www.ibm.com/docs/en/cics-ts/5.4?topic=processing-acid-properties-transactions

Durability
After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.
<<<<
The OO/ORM/OOP boffins and academics (lazy and vociferously ignorant) think that, oh, the platform or program suite supplies that, we need do nothing. It helps to blow your nose and smell the coffee. Then go outside and socialise with people. Who actually use an online shared database.
It is true that the system failures will be taken care of, by the platform/program suite, to the extent that redundancy; etc has been implemented by the customer. But what about the Transaction itself, is it [D]urable ?
Consider:
Person has a Credit Rating [A] which allows $10,000 overdraft
Person fails to keep agreements re covering their overdraft
Time[ 1 ]
Credit Manager intends to change Credit Rating [D]; $0 overdraft
He is using a GUI, the duration between retrieval & painting the data into the GUI, and the moment he hits the <Save> button in uncontrolled (cannot be controlled). Toilet break; take a phone call; check policy docs; etc.
Time[ 2 ]
Person has a balance of $5,000 DR (overdrawn)
Person phones bank clerk
Checks that he still has Credit Rating [A]
Changes his address
Bank officer is using a GUI, same issues.
Time[ 3 ]
Credit Manager hits <Save>, which COMMITS his changes.
Moves on to next task.
Time[ 4 ]
Bank officer hits <Save>, which COMMITS her changes.
Time[ 5 ]
N++Person attempts to withdraw $2,000 at the bank
Bank teller checks his account and status.
System states his balance is $5,000 DR (overdrawn), Credit Ratings [A]. Provides $2,000 in cash, new balance $7,000 DR overdrawn.
What the hell happened ? Is that a Durable Transaction ? No way. Not an acceptable system.
This, lays and germs, is known, since 1965, as the
-------------------------
-- Lost Update --
-------------------------
problem. The third of four categories of error, to be handled, in any OLTP system.
Note that the server/program-suite cannot prevent this. Not a genuine OLTP server, let alone one hundred programs cobbled together. Not even an ACID-compliant server, let alone a bunch of programs that implement a strange notion of rCLtransactionrCY.
The academics not only DONrCOT KNOW about this, or any other OLTP consideration, when I informed them 11 years ago (during my three years of hard labour at the Hey Presto, the Torrid Manifesto gulag), they did not understand it. The established /If I did not invent it, it does not exist/ syndrome that cripples academics. They had no interest in the solution.

Exclusions.
1. We are not arguing about the right or wrong way {all SQL in the GUI; Validate & Exec in the GUI, Xact sp in the database; any other method}. (We will have a resolution at the end.) Feel free to declare what it should be. Discuss.
2. LetrCOs not have an argument about this one item, because it is laborious, and I have done it to death about 30 times. In most cases, for reasons of simplicity, for an UPDATE, either the fragmented SQL code in the GUI xor the atomic SQL code in the Transaction sp, would UPDATE all attribute columns. So whether it is the Credit Manager changing this, or the bank officer changing that, it is writing all the attribute columns that are held in that client GUI.
The idea in the example above is, the bank officerrCOs changes over-wrote the Credit ManagerrCOs changes.
<<<<
Doc updated. More detail and a Data Model:
____ https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Transaction%20Sanity.pdf
I have not received any comments about the earlier version (two pages). Please feel free.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Hi Derek,
Welcome back. I'll reply to things you've wrote across both of your emails.
---

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).
---

Two down, two to go.

I know one is lost updates, though I see you've addressed it in your email.
I read the PDF you put together years ago: http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Transaction%20TTM%20101122.pdf
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.
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.
---
Regarding the Transaction Sanity document, I read through it when you first shared it two weeks ago.
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
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
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.
---
Cheers,
Dan
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Sunday, 25 July 2021 at 22:33:22 UTC+10, Derek Ignatius Asirvadem wrote:

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

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.

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.

...

<<<<

Put another way, I gave the freaks full definition of Lost Update, and the full definition of the solution Optimistic Locking, eleven years ago ... but the drooling idiots still have not got it. Codd and I suffer the same thing from them: the Not Invented Here Syndrome. We are not pure academics, masturbating over our fantasies that have no connection to reality. They ignore and suppress Reality; the genuine SQL platform implementations; Transactions; ACID; anything they did not invent themselves, even if it is fifty years after it has been implemented in Reality.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Hi Derek,

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.

So you're talking about notions of physical durability (i.e., my data is written to non-volatile storage and will endure) -- versus -- logic durability (i.e., my data is written and other users of this system who might be operating on the same data will not clobber it without being informed that my transaction has occurred in the period of time between them viewing the data and attempting to act upon it).

But how is it prevented ?

In Microsoft SQL Server the most robust way is using the ROWVERSION data type, which is just a very large monotonic integer that is automatically updated when modifying a record in a table with a column of type ROWVERSION. It's essentially timestamp based concurrency control.
Of course, you needn't use ROWVERSION specifically. You could conceivably use some other very large value (a GUID / UUID for example) so long as you a) update it on all occasions where you're modifying the data it protects and b) always check it before proceeding with a data modification.
---
Thanks for the background re: the TTM discussion. I came across it while looking at other stuff, and it was actually quite eye-opening at the time. In fact that's how I came across the notion of timestamp based concurrency control.
---

Question. What is the *BASIS* for the user having confidence that their Transaction will (should) work ?

In a well-designed system? The knowledge that the system won't allow them, or anyone else, to unknowingly overwrite someone else's changes.
In an abstract sense? Maybe they've had some degree of exposure to notions such as 'Durability' and, in laymen's terms, perceived it as something that will safeguard their modifications from data loss.
---

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 ?

The naive approach would be to acquire and hold locks for the duration of user activity right? That is, acquire and hold some kind of lock at the time a user opens a screen to view some data, and then release that lock when the user finishes with the screen.
Though in my personal experience, I haven't seen this.
I have instead seen:
1. No attempted lost update prevention at all. Last update wins.
2. Timestamp based concurrency control, but weakly implemented. For example, the notion exists but might not be used appropriately in all places in application code. Or alternatively, the breadth of data protected by a particular timestamp is too coarse or too fine. Or alternatively again, there is confusion about which timestamps protect which data from lost updates.
---
Cheers,
Dan
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Hi Derek,
Thanks for the response and continued discussion.
---

We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. Transaction Sanity/p4.
Was the Transaction Durable or not ?

No. Neither the User 1 or User 2 transactions make use of the timestamp based concurrency control.
---

Correct.
But that is not robust, it is a large overhead. A data-level TimeStamp (DATETIME datatype) is the most robust. We have had that since 1965, 1984 for RDBMS, forty years before MS implemented ROWVERSION.

If you're using a clock-based timestamp such as DATETIME, instead of a server-wide monotonic integer (which is what the Microsoft ROWVERSION is), what do you do to handle the possibility of clock skew on the database server?
Do you do anything, or do you just take your chances? I recognise that the situation is extraordinarily rare, and you may typically dismiss it on that basis.
On the other hand, I noticed that the procedure 'Dumb_Withdraw_tr' actually takes @UpdatedDtm as a parameter, which conceivably is another source of clock skew when the caller specifies the time (or even lies about the time).
---

I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or rCLMVCCrCY notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of any single fact.

So what you're getting at, especially with the mention of paper-based, is: It's inconceivable that you could inadvertently clobber someone else's change to that single piece of paper without being cognisant of that. After all, they've written on the paper and put it back, and then you've picked it up and looked at it. Obviously being a single piece of paper, without duplicates, access to the piece of paper involves queuing up for it and waiting your turn.
---
I'll take a look at that updated PDF now, though I might not respond again until tomorrow.
Cheers,
Dan
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Hi all,
I am back, too, and trying to catch up with the discussion, but not
there yet.

Derek, I have read your Transaction Sanity document. When academics
discuss "transactions", they do so in the context of what eventually
becomes a start transactionrCa commit block in implementations. For
instance, they ask themselves: under what conditions are lost updates
(and other anomalies) be avoided when:

-- Transaction 1
start transaction;
-- Perform some operations, including updates
commit;

-- Transaction 2
start transaction;
-- Perform some operations, including updates
commit;

are executed concurrently? Admittedly, the examples that are usually
found in textbooks are quite misleading: the classical fund transfer is
used because the context is readily understood, but it should be taken
with a grain of salt, and certainly not as a way to suggest how a real
banking transaction should be implemented. It's naive to think that the
"naive solution" in your document is what a (good) teacher would suggest
as a practical way to solve the problem stated in that document. To make
the point clearer (and simplifying things a bit): the transaction theory
that we teach, and which you can find in (good) textbooks, is the theory
you would need to write a transaction manager for a DBMS (i.e., the
theory needed by Sybase developers), rather than the theory needed to
correctly implement the transactions for a banking system or any other application, which builds on top of the former and is seldom, if ever, discussed in textbooks. Hence, I am eager to hear what you have to say
about it. Even got SQL Server and Sybase installed to verify your claims
:)

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
Hi Derek,

Thanks for the response and continued discussion.

It is my pleasure.

---

We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. Transaction Sanity/p4.

Was the Transaction Durable or not ?

No. Neither the User 1 or User 2 transactions make use of the timestamp based concurrency control.

Yes, that is the back end, or developerrCOs understanding.
What is in the userrCOs mind, how does he perceive it ?

---

Correct.
But that is not robust, it is a large overhead. A data-level TimeStamp (DATETIME datatype) is the most robust. We have had that since 1965, 1984 for RDBMS, forty years before MS implemented ROWVERSION.

If you're using a clock-based timestamp such as DATETIME, instead of a server-wide monotonic integer (which is what the Microsoft ROWVERSION is), what do you do to handle the possibility of clock skew on the database server?

What skew ??? Where is this skew ???

Do you do anything, or do you just take your chances? I recognise that the situation is extraordinarily rare, and you may typically dismiss it on that basis.

I canrCOt answer the rCLskewrCY part directly because I donrCOt know what you mean. Re allowing a skew (if there was one), definitely not, I would not do that (we cater for extreme high transaction rates; race conditions; etc in high end Stock Trading environments). The Template has not changed since 1993, it required minor tweaks to cater for the new (additional) Data Storage structure for RFS type files when Sybase ASE 12 came out (IIRC 1999).
Second question, why choose a monotonic number that the server has to generate and maintain ?
(
The original Sybase 4.2 Datatype was TIMESTAMP. It is not a monotonic number but similar. It is a metric, in essence it is the number of writes to the database. A database-wide currency number. It can be compared [the purpose] but it cannot be examined. ROWVERSION is the bastard sonrCOs copy of his fatherrCOs method. ROWVERSION is a horrible name for it, it is just another magic number, that the server has to maintain, same as a GUID or UUID as used for RecordId.
TIMESTAMP is not deprecated, but it is ancient, no one I know uses it. Not becauee it does not work, but because it cannot be examined. A column eg. /TimeStamp/ or /UpdatedDtm/ (Datatype DATETIME, millisec resolution, no need for microsec resolution) is commonly used.
)

---

I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or rCLMVCCrCY notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of any single fact.

So what you're getting at, especially with the mention of paper-based, is: It's inconceivable that you could inadvertently clobber someone else's change to that single piece of paper without being cognisant of that. After all, they've written on the paper and put it back, and then you've picked it up and looked at it. Obviously being a single piece of paper, without duplicates, access to the piece of paper involves queuing up for it and waiting your turn.

Yes. Excellent parallels drawn. Keep going.
- In the paper system, the person (first-up in the queue) would have be cognisant.
- think: a library card system, arranged in 200 little drawers for convenience of physical access by many people
- In the automated system, the program has to be cognisant.
- In the paper system, the person (first-up in the queue for a particular card) has an idea of its data currency based on something, so that when he returns (with the intended update), he would be cognisant that the card has changed.
- In the automated system, the program has to do that.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
If you're using a clock-based timestamp such as DATETIME, instead of
a server-wide monotonic integer (which is what the Microsoft
ROWVERSION is), what do you do to handle the possibility of clock
skew on the database server?

What skew ??? Where is this skew ???

How do you handle a situation in which the system's clock starts
reporting an inexact time? It may not happen frequently, but it does
happen (say, the NTP daemon crashes, or gets the wrong time for some
reason). Or, if you have two servers writing data into the database,
their clocks might be off, say, by a few seconds.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Guys and dolls
Can we please get some progress here. We have not finished, and the Template is not complete.
Recap for the exact position that we are at.

On Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:

We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. Transaction Sanity/p4.

Was the Transaction Durable or not ?

No. Neither the User 1 or User 2 transactions make use of the timestamp based concurrency control.

Yes, that is the back end, or developerrCOs understanding.

What is in the userrCOs mind, how does he perceive it ?

What is in the userrCOs mind, how does he perceive it ?
Pages 4 and 5 in the Transaction Sanity doc.

I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or rCLMVCCrCY notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of any single fact.

So what you're getting at, especially with the mention of paper-based, is: It's inconceivable that you could inadvertently clobber someone else's change to that single piece of paper without being cognisant of that. After all, they've written on the paper and put it back, and then you've picked it up and looked at it. Obviously being a single piece of paper, without duplicates, access to the piece of paper involves queuing up for it and waiting your turn.

Yes. Excellent parallels drawn. Keep going.
- In the paper system, the person (first-up in the queue) would have be cognisant.
- think: a library card system, arranged in 200 little drawers for convenience of physical access by many people
- In the automated system, the program has to be cognisant.
- In the paper system, the person (first-up in the queue for a particular card) has an idea of its data currency based on something, so that when he returns (with the intended update), he would be cognisant that the card has changed.
- In the automated system, the program has to do that.

What is the *BASIS* for confidence that an update can be performed on a row, or library card ?
I teach this stuff, I cannot give you the Lecture Notes or the SG OLTP Template, but I have given you a Transaction Sanity doc, so that at least this thread and the issues herein can be fully understood, that it can progress to closure; the Template can be completed. You need to have an illumination, which means progressing your own thinking, not merely being told. Which is why I am provoking thought.
When you have that illumination, you will (a) realise the principle of an OLTP system, and (b) therefore, how false and incorrect the notion of multiple offline versions is.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Tuesday, 27 July 2021 at 21:37:57 UTC+10, Nicola wrote:

Hence, I am eager to hear what you have to say
about it.

Please feel free. Ask a specific question.

Even got SQL Server and Sybase installed

Well, I am certainly not going to dissuade you from learning about the real world; about commercial SQL Platforms; what SQL really is; what ACID Transactions mean. Good on you, for taking one more step out of the academic isolation from the all that. On the contrary, I encourage you, as the first academic who is trying to close the chasm, of FORTY YEARS of isolation from the real world.
But that will take six, maybe twelve months. And that will be severely limited by the academic mindset, that you think you know SQL; ACID; commercial platforms; etc, which is pure perversity, as I have detailed and evidenced in many posts. So please learn those subjects with fresh mind, with an accurate disposition: that you do not know those subjects. Otherwise it would be like a prostitute trying to learn about chastity.

... to verify your claims
:)

Well, if you enumerate even one or two of those declaration (that you re-frame as "claims"), which are not diminished by your re-framing, I can answer them at short notice, and refer you to manuals, etc. No need to wait six months, no need to download software and gear it up yourself. Please name the declarations that you have trouble accepting.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Monday, 2 August 2021 at 12:19:13 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 27 July 2021 at 21:37:57 UTC+10, Nicola wrote:

... to verify your claims
:)

Well, if you enumerate even one or two of those declaration (that you re-frame as "claims")

Yes, of course that is dishonest. In case it needs to be said, I am not saying that you are consciously being dishonest. I am saying, that is standard academic practice, that you are schooled in. The classic method they use: re-frame a declaration as something that it is not, and then either burn it, a Straw Man of your own creation, or at the least diminish it.
Further, you take declarations and absolute statements very lightly, as re-framed "claims". Even your own. You do not have the self-respect, or respect for the science, that is required to treat them with respect, to obtain resolution. You do not correct your declarations (I won't call them "claims") when they are proved false. Eg. the Movie Title thread. Eg. the paper you wrote declaring that it could *NOT* be modelled using the /Relational Model/, that it could only be done by resorting to physical Record Ids ala the anti-Relational RM/T.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Hi Derek,
---

Concerning clock skew

Nicola's reply about clock skew covers what I was getting at. Specifically, a clock moving backwards or forwards. Moving backwards means there is potential for you to see the same datetime twice.
You don't see it happen often, but every now and then it does. Oftentimes things like authentication services stop working because they're heavily reliant on relatively precise time keeping.
This is why I personally lean towards the SQL Server rowversion type mentioned. Rowversion is notably just a synonym for 'timestamp' as you've described Sybase 4.2 as having. Another Microsoft-ism. In fact, you can still use the 'timestamp' data type when writing the table DDL and that'll work fine, though the 'timestamp' keyword is technically deprecated.
I think the deprecation is because Microsoft's 'timestamp' is not 'timestamp' in the ISO standard sense. In SQL Server, a non-null rowversion is equivalent to binary(8). A nullable rowversion is equivalent to varbinary(8).
To make sense of the number expressed as binary(8), you can cast it to a bigint. At that point it becomes useful for concurrency control in much the same way that UpdatedDtm is.
---

Concerning durability and user perception

In the user's mind, you are right that their perception of the system is that it has not lived up to the guarantees of the ACID concept of durability. They won't recognise that it was written to disk for a fleeting moment before their changes were clobbered by a subsequent transaction.
They simply see the final outcome, which is that a subsequent user's actions have overwritten their own.
You also mentioned user training teaching users that they would expect to see their changes after performing a transaction, and teaching users that the durability property means that they can be confident that a reportedly committed transaction indicates that the changes made in their transaction will stick.
In the library card system, the basis for their confidence that they, and they alone, can update the physical card and return it to the drawer. And it's just one card, so their changes will be there when the next person takes it from the drawer.
And this is essentially what the lock management system does. It affords that mutually exclusive access to the library card.
Is that the basis you are asking for?
---

Lost currency on page 5 in the Transaction Sanity document

I read this. Basically, the problem is that I load a screen with a number of fields on it. I grab a drink. I come back 10 minutes later, make a minor change, and click 'Save'. In doing so I've just wiped out the work of a colleague who made a lot of changes during my 10 minute absence because what happened to be on my screen was the old, not-current, data when I clicked 'Save'.
It needn't be such a protracted period of time of course. The same could happen in mere seconds.
Lost update protection prevents this.
---

The template being unfinished

Is it unfinished due to the lack of lost update protection? Or is there something else missing?
As far as I can see, it's already doing a good job in terms of contention management.
I've posted a new GitHub Gist here: https://gist.github.com/DanielLoth/0599c2475368083acc9032d34f0919e1
This revision of the code contains lost update protection using the UpdatedDtm column in the Person table.
---
Cheers,
Dan
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Can we please get some progress here. We have not finished, and the
Template is not complete.

Recap for the exact position that we are at.

On Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:

We need to understand it forwards. His Transaction completed and
it persists ... and therefore he has confidence in the system. XOR
his Transaction completed, but it did not persist ... and
therefore he has no confidence in the system. Transaction
Sanity/p4.

Was the Transaction Durable or not ?

No. Neither the User 1 or User 2 transactions make use of the
timestamp based concurrency control.

Yes, that is the back end, or developerrCOs understanding.

What is in the userrCOs mind, how does he perceive it ?

Pages 4 and 5 in the Transaction Sanity doc.

I'd say that there are two Logical Units of Work (LUWrCoI think the term
was used in CICS), one by User 1 and one by User 2. When updating
a value, each user will assume that the value they have replaced is the
one they had previously read (if the system lets the update go through).

Each LUW consists of more than one database transactions. I refer to
each SELECT statement in that example as a (read-only, database)
transaction. Hence, some state must be maintained between database transactions, to make each LUW meet the user's expectations. In this
case, the state is the pair (@CreditRatingCode,@BirthPlace), i.e., the information initially retrieved by each user. Then, both TweedleDumb and TweedleDumber should check that such values have not been changed since
the corresponding previous SELECT, and do so before updating the record.

Rather than checking the values directly, a SELECT could also read
a version number of the retrieved record and pass it to TweedleDumb
(resp., TweedleDumber), which would check that the version number still
matches (by re-reading it), in which case it would update the record and increase the version number.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Daniel summarized Derek's approach as follows:

Derek in particular described his approach and in doing so expressed
two principles:

1. Only acquire exclusive (write) locks if the transaction will
potentially succeed.

2. Never acquire exclusive locks if embarking on the transaction is
futile (or put another way, don't start what you absolutely cannot
finish).

Consistent with those principles, the ideal was described as:

1. A validate block - Where we 'look before we leap' (to borrow that >expression). In this block we are reading only. We can use the READ
COMMITTED isolation level, thereby acquiring and releasing shared locks
in quick succession. No shared locks are held after the conclusion of
the statement (i.e., the select query in this case).

2. A transaction block - Where we check again and, if the conditions
are still right (i.e., other users might have changed the data), we can >proceed to do our work and then commit the transaction. In this block
we acquire write locks, and these locks are held for the remainder of
the transaction (until we commit or rollback).

Exercise 18.11 (and its solution) from Silberschatz's "Database System Concepts" seems relevant:

https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Tuesday, 3 August 2021 at 08:28:49 UTC+10, Nicola wrote:

On 2021-08-02, Derek Ignatius Asirvadem wrote:
Can we please get some progress here. We have not finished, and the Template is not complete.

Recap for the exact position that we are at.

On Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:

We need to understand it forwards. His Transaction completed and
it persists ... and therefore he has confidence in the system. XOR
his Transaction completed, but it did not persist ... and
therefore he has no confidence in the system. Transaction
Sanity/p4.

Was the Transaction Durable or not ?

No. Neither the User 1 or User 2 transactions make use of the
timestamp based concurrency control.

Yes, that is the back end, or developerrCOs understanding.

What is in the userrCOs mind, how does he perceive it ?

Pages 4 and 5 in the Transaction Sanity doc.

I'd say that there are two Logical Units of Work (LUWrCoI think the term
was used in CICS), one by User 1 and one by User 2. When updating
a value, each user will assume that the value they have replaced is the
one they had previously read (if the system lets the update go through).

Each LUW consists of more than one database transactions. I refer to
each SELECT statement in that example as a (read-only, database) transaction.

No. That is not a Transaction. You are still holding onto an incorrect notion that non-transactions are somehow Transactions. That contradicts (a) reality, (b) ACID, (c) SQL ACID compliance. If you keep calling a chichuahua a tiger, you will remain confused. Yes, I know, that is the PissGress mindest, their totally incorrect way of handling their offline versions; their additional 2PL locking, and for that they have redefined "transaction" ... they are always in some hysterically stupid "transaction" mode. It is false.

Hence, some state must be maintained between database
transactions, to make each LUW meet the user's expectations. In this
case, the state is the pair (@CreditRatingCode,@BirthPlace), i.e., the information initially retrieved by each user. Then, both TweedleDumb and TweedleDumber should check that such values have not been changed since
the corresponding previous SELECT, and do so before updating the record.

Rather than checking the values directly, a SELECT could also read
a version number of the retrieved record and pass it to TweedleDumb
(resp., TweedleDumber), which would check that the version number still matches (by re-reading it), in which case it would update the record and increase the version number.

Yes.
One correction. LUW was the original CICS term, and it is still used in IBM/DB2. But it means Transaction, not PoopDePoopGres "transaction", not whatever you reframe it to mean. LUW means a single database Transaction, controlled according to ACID.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola
Thanks for yours.

On Tuesday, 3 August 2021 at 17:58:56 UTC+10, Nicola wrote:

Derek in particular described his approach and in doing so expressed
two principles:

1. Only acquire exclusive (write) locks if the transaction will >potentially succeed.

2. Never acquire exclusive locks if embarking on the transaction is
futile (or put another way, don't start what you absolutely cannot >finish).

Thanks, I appreciate credit as much as the next person, but I can't when it is not due. The entire method was established by IBM in the 360 CICS/TPC systems (Codd's employer). In 1979 I merely provided a rendition for our Cincom/TOTAL NDBMS customers. In 1993 I merely provided a rendition in SQL. I will take credit for that.
Other high-end providers have similar methods, possibly not quite as high concurrency as mine. I know no one else who guarantees high performance Transaction scalability and zero deadlocks.

Consistent with those principles, the ideal

It is not merely an ideal, it is a Formal Method, proved in millions of systems in the real world.
Established 1965.
Commonly used in OLTP systems: HDBMS; NDBMS.
Provided in SQL platforms since 1984.
Provided by high end database suppliers since 1993 (that I know of).

was described as:

1. A validate block - Where we 'look before we leap' (to borrow that >expression). In this block we are reading only. We can use the READ >COMMITTED isolation level, thereby acquiring and releasing shared locks
in quick succession. No shared locks are held after the conclusion of
the statement (i.e., the select query in this case).

In Sybase; MS; DB2, shared locks are held only during the READ operation (pertaining to a page or row), and released. Not the Statement.

2. A transaction block - Where we check again and, if the conditions
are still right (i.e., other users might have changed the data),

We check Data Currency for the Transaction (the first time, not "again").

we can

proceed to do our work and then commit the transaction. In this block
we acquire write locks, and these locks are held for the remainder of
the transaction (until we commit or rollback).

Yes.
Additionally, the precise notions of [A]tomicity, [C]onsistency, and [D]urability, as it applies Transactions.

Exercise 18.11 (and its solution) from Silberschatz's "Database System Concepts" seems relevant:

https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf

No.
They are stuck in the usual academic mindset, the examples are particularly about 2PL, which is an MV-non-CC artefact, not relevant to, or used in SQL OLTP Platforms, because we do not have 2PL. They do not understand or reference the "1PL" Lock Manager we have in the real world.
We definitely do not "re-execute" a Transaction, the Validate block is not a [paraphrased] "first execution without holding locks". The notion of re-executing a Transaction [note the compute-intensive !] is stupid. The Validate block is a separate formal article.
The "tree protocol" is in the Transaction, not in the server. They incorrectly worry about "acquiring locks that are not needed", blissfully ignorant of the fact that they are needed precisely for the "tree protocol", in order to (a) block at the highest level, and thus engage the fewest locks, and (b) the idiots do not realise that if they do not acquire the required locks, they are open to, they have created, a deadlock.
There are further errors, lack of precision, throughout. I won't enumerate it here. Eg. there is no wait for lock acquisition [compared with disk wait], it is practically instantaneous (all articles are in memory; nothing is on disk). There is a Lock Wait, which is a different thing, the server puts the task to sleep until the lock is available, and then wakes it up.
The usual superficial treatment of implementation concerns by academics. Granted, it is better the the Date; Darwen; Fagin pig poop, but it is in the same category, it just smells less offensive. Why don't they use this textbook across all universities, instead of the filth they do use, such as the "Alice Book".
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

On Tuesday, 3 August 2021 at 08:28:49 UTC+10, Nicola wrote:
I refer to each SELECT statement in that example as a (read-only,
database) transaction.

No. That is not a Transaction. You are still holding onto an
incorrect notion that non-transactions are somehow Transactions.

Ok, let me understand: what are the differences in behaviour between

select rCa ;

and

start transaction;
select rCa ;
commit;

?

One correction. LUW was the original CICS term, and it is still used
in IBM/DB2. But it means Transaction, not PoopDePoopGres
"transaction", not whatever you reframe it to mean. LUW means
a single database Transaction, controlled according to ACID.

So, wrt to your Transaction Sanity example (latest revision), User
1 executes one LUW/Transaction, which starts at time T3. And User
2 executes one LUW/Transaction starting at time T4. Besides, the SELECTs
at T1 and T2, respectively, are not part of those Transactions. Is that
right?

If you call the whole stored procedure a Transaction, how do you refer
to each BEGIN TRANrCa COMMIT block?

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola
Noting that what you are used to, the PoopDePooGres "sql" is *not* SQL, and certainly *not* a programming language, but we have had SQL since IBM released it into the public domain. It is the Relational data sub-language defined by Codd. Of course, each SQL Platform supplier has extensions. In contrast the freeware has substitutions, and a whole pile of extensions that are irrelevant, that ensure that the code is not portable.
With a view to learning what actually SQL is, that it is a full programming language, and specifically what SAP/Sybase Adaptive Server Enterprise [ Transact-SQL ] is, please erase all your notions of SQL; ACID; Transactions that you have acquired, and start with a fresh and open mind. The obstacle to learning this is, as always, any attitude that you know the subject matter, and eg. you just need to learn the Sybase syntax. In particular, do not attempt to perform a task in Sybase in the pig poop way, find out how to do it in the normal commercial SQL way.
1. Visit this page
__ https://help.sap.com/viewer/product/SAP_ASE/16.0.4.0/en-US?task=whats_new_task
2. Select [ Download PDFs ] at top right
3. Choose the manuals you want, and download them. Read them from cover to cover. On the train or whatever.
4. I recommend the following, in order.
__ Installation & Upgrade Guide
__ Transact-SQL Users Guide
__ Reference/Building Blocks
__ Reference/Commands
__ Reference/Utility (especially isql)
__ Admin/System Admin Guide/Volume 1
Feel free to ask me questions.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
Ok, let me understand: what are the differences in behaviour between

select rCa ;

and

start transaction;
select rCa ;
commit;

Because you have not specified an ISOLATION LEVEL, and because SQL has default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.

Therefore:

select rCa ;

means:

SQL

-- ISOLATION LEVEL 1/READ COMMITTED
-- Locks will be held for the duration of each Read operation (page or row) -- Phantoms and result set Anomalies may occur

Note that that means the SELECT is not in a Transaction.

Ok. For understading: let us assume that locks are row-based. Consider
this instance:

Person
Name Age
--------
John 34
Bob 34

And this query:

select P1.Name, P2.Name
from Person P1, Person P2
where P1.Name <> P2.Name
and P1.Age = P2.Age;

Assume that the query is executed using a nested-loop join. Besides,
suppose that an update is concurrently performed:

update Person set Age = 35 where Name = 'John';

Is it correct that in this situation the query can produce at least
three possible results?

1. If the update is performed after the select, then the result is:

John, Bob
Bob, John

2. If the update is performed before the select, the result is empty.

3. But the update can happen *during* the select, in which case the
result may be:

John, Bob

This happens when at the first iteration of the outer loop (John, 34)
is locked (so the update is blocked), then the inner loop scans the
table and outputs (John,34, Bob,34), and only after that the update
takes place (in the meantime, the lock on (John,34) will have been
released). Hence, at the second iteration of the outer loop, the
inner scan will not find anything else to join with (Bob,34).

Is this right? Are locks under these circumstances actually just
latches?

You are correct that in a system such as PostgreSQL there is nothing
like that: the select would see a snapshot of the table at time it is
executed, and would not see any changes by concurrent commands.

https://www.postgresql.org/docs/current/tutorial-transactions.html

-2PostgreSQL actually treats every SQL statement as being executed within
a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped
around it.-+

start transaction;
select rCa ;
commit;

means:

SQL

START TRAN
-- ISOLATION LEVEL 3/SERIALIZABLE
-- Locks will be held until end of Transaction, the COMMIT/ROLLBACK TRAN
-- Phantoms and result set Anomalies will not occur

Hence, (3) is prevented.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Wednesday, 4 August 2021 at 10:15:03 UTC+10, Derek Ignatius Asirvadem wrote:

On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:

So, wrt to your Transaction Sanity example (latest revision),

I will assume page 7, because that is the solution I gave, Optimistic Locking.

User 1 executes one LUW/Transaction, which starts at time T3.
And User 2 executes one LUW/Transaction starting at time T4.

Yes.

Besides, the SELECTs
at T1 and T2, respectively, are not part of those Transactions. Is that right?

Yes.
As per the legend at the bottom of page 4.
Green is resident and executed in the client app.
Blue is resident and executed in the server, the Transaction stored proc. The delay between [T1] and [T3] for User 1, and between [T2] and [T4] for User 2, cannot be controlled due to being user interaction.

If you wish to evaluate and contemplate the difference, eg. what would happen if the Transactions started at [T1] and [T2] respectively, that is the [Na|>ve Solution] given on page 5.
Note that it:
- breaks the [A]tomic property
--- (half in the client, the rest in the server Transaction Stored proc]
--- or half in one code segment in the client, the rest somewhere else in the client)
- is absolutely prohibited in an OLTP environment because it holds locks during user interaction, which is a period that cannot be controlled.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Wednesday, 4 August 2021 at 22:05:01 UTC+10, Derek Ignatius Asirvadem wrote:

On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:

Basically, in your post you are telling me that your mickey mouse system does not do what it should not do. Ok.

And then you are trying to say that the low-level concerns that 10,000 mickey mouse developers have, in their herd of hundreds of programs that have no architecture and handle miserable o/s files, should be of concern to 200 PhD level engineers who eliminated those concerns via a server in 1984, and which has no o/s files. No.

This is just one of the great differences between freeware (including Orable) and commercial SQL Platforms. You get what you pay for.

The problem is, consistently, the insane insist that the sane should have their insane problems. No, they are not real to us. They are real only in the land where people expand vast amounts of energy to make their fantasies real, physicalising their fantasies.

It is that, and actually, it is worse. You have the academics' Straw Man notion of what a server does; the Straw Man notion of what an Ordinary Lock Manager does (the academics' fantasy "2PL"); the Straw Man notion of the problems that might, just might, happen. In denial of FORTY YEARS of commercial systems that do not have such idiotic problems. I just burn them as you present them.
Over some course of time, as you progress across the great chasm, you will realise that all the academic notions about the real world, are Straw Men, they have the explicit purpose of making the real world look bad, which is the foundation for making their insane fantasies look good. You can burn them yourself.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:

On 2021-08-04, Derek Ignatius Asirvadem wrote:

On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:

Ok, let me understand: what are the differences in behaviour between

select rCa ;

and

start transaction;
select rCa ;
commit;

Because you have not specified an ISOLATION LEVEL, and because SQL has
default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.

Therefore:

select rCa ;

means:

SQL

-- ISOLATION LEVEL 1/READ COMMITTED
-- Locks will be held for the duration of each Read operation (page or row)
-- Phantoms and result set Anomalies may occur

Note that that means the SELECT is not in a Transaction.

Ok. For understading

I'll try to rephrase my question, which basically asks if you have statement-level consistency of SELECT queries at READ COMMITTED in
Sybase.

A SELECT statement, which runs at READ COMMITTED, holds (let's say,
page) locks for the duration of each read operation, and not until the
end of the statement. Assume that the SELECT statement is a complex
query accessing many records over a non-negligible time-span (seconds),
and that it is run concurrently with several (serializable) update
operations on the same data.

(1) Is it possible (in Sybase) that the SELECT query returns an
incorrect result, where by "incorrect" I mean a result that is
impossible to obtain when the query is run in a serial context, that is,
in absence of any other concurrent operation?

(2) If the answer to (1) is negative, where does such a correctness
guarantee come from? If, on the contrary, the answer to (1) is
affirmative, can you please describe a situation in which that happens?

I'd say that the answer to (1) is affirmative. For instance, during
a (block) nested-loop join a certain number of pages must be read into
memory buffers in the outer loop. Later on, it may be necessary to evict
such pages from the memory buffers to make room to another block of
pages. Later on, it may be necessary to read the evicted pages again in
the inner loop (think of join of a table with itself). What prevents
concurrent transactions to modify those pages in between the two reads?

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Because you have not specified an ISOLATION LEVEL, and because SQL has
default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.

Btw, why does the transaction isolation level even matter? A single
SELECT statement (not enclosed in BEGIN TRANrCa COMMIT) is not
a transaction.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Exercise 18.11 (and its solution) from Silberschatz's "Database System
Concepts" seems relevant:

https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf

No.

They are stuck in the usual academic mindset, the examples are
particularly about 2PL, which is an MV-non-CC artefact, not relevant
to, or used in SQL OLTP Platforms, because we do not have 2PL. They
do not understand or reference the "1PL" Lock Manager we have in the
real world.

We definitely do not "re-execute" a Transaction, the Validate block is
not a [paraphrased] "first execution without holding locks". The
notion of re-executing a Transaction [note the compute-intensive !] is stupid. The Validate block is a separate formal article.

What the exercise describes is a concurrency control protocol within the
DBMS. So, it's the system that "first execute[s] the transaction without acquiring any locksrCa" and then "rerun[s] the transaction using 2PL".
That's transparent for the user.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 5 August 2021 at 00:34:46 UTC+10, Nicola wrote:

On 2021-08-04, Derek Ignatius Asirvadem wrote:

On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:

On 2021-08-04, Derek Ignatius Asirvadem wrote:

On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:

Ok, let me understand: what are the differences in behaviour between >> >>
select rCa ;

and

start transaction;
select rCa ;
commit;

Because you have not specified an ISOLATION LEVEL, and because SQL has >> > default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.

Therefore:

select rCa ;

means:

SQL

-- ISOLATION LEVEL 1/READ COMMITTED
-- Locks will be held for the duration of each Read operation (page or row)
-- Phantoms and result set Anomalies may occur

Note that that means the SELECT is not in a Transaction.

Ok. For understading

I'll try to rephrase my question, which basically asks if you have statement-level consistency of SELECT queries at READ COMMITTED in
Sybase.

I have already answered this in detail in this thread. Please read.
Further, it is clearly identified in my Transaction Sanity doc page 2.

A SELECT statement, which runs at READ COMMITTED, holds (let's say,
page) locks for the duration of each read operation, and not until the
end of the statement. Assume that the SELECT statement is a complex
query accessing many records over a non-negligible time-span (seconds),
and that it is run concurrently with several (serializable) update operations on the same data.

(1) Is it possible (in Sybase) that the SELECT query returns an
incorrect result, where by "incorrect" I mean a result that is
impossible to obtain when the query is run in a serial context, that is,
in absence of any other concurrent operation?

(2) If the answer to (1) is negative, where does such a correctness guarantee come from? If, on the contrary, the answer to (1) is
affirmative, can you please describe a situation in which that happens?

a. You need to read up on what SQL/Isolation Level/REPEATABLE READ means.
b. Then form your question properly, using established specific technical terms.
c. Exclude personal or subjective definitions re "correct>

I'd say that the answer to (1) is affirmative. For instance, during
a (block) nested-loop join a certain number of pages must be read into memory buffers in the outer loop. Later on, it may be necessary to evict such pages from the memory buffers to make room to another block of
pages. Later on, it may be necessary to read the evicted pages again in
the inner loop (think of join of a table with itself). What prevents concurrent transactions to modify those pages in between the two reads?

Nice speculation.
This thread is about /Stored procedure structure in RDBMS using Lock Manager for transaction isolation/, and the real world of high end implementations. I don't have the inclination to argue speculation against speculation, endlessly.
Further, I have explained your repeated Straw Man arguments and dismissed them. I am dismissing this one as such.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 5 August 2021 at 00:46:30 UTC+10, Nicola wrote:

On 2021-08-04, Derek Ignatius Asirvadem wrote:

Because you have not specified an ISOLATION LEVEL, and because SQL has >> > default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.

Btw, why does the transaction isolation level even matter? A single
SELECT statement (not enclosed in BEGIN TRANrCa COMMIT) is not
a transaction.

???

A single
SELECT statement (not enclosed in BEGIN TRANrCa COMMIT) is not
a transaction.

1. I have laboured to inform you of that. You are on record stating the opposite. Thank God that you have finally got it.
2. If you are now arguing, you are arguing against yourself. If you are not arguing, good, the question is closed, and you have confirmed that you understand the question; the answer; and that it is closed.

Btw, why does the transaction isolation level even matter?

1. It is funny, because in your other questions in this thread, you seem to understand that, you have the answer. But somehow you have lost that knowledge, in order to ask this question. I don't have the qualifications the are required to assist you.
2. It is not I, but the SQL Committee that declares the requirement, for [Transaction] isolation Level.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 5 August 2021 at 07:40:10 UTC+10, Nicola wrote:

Exercise 18.11 (and its solution) from Silberschatz's "Database System
Concepts" seems relevant:

https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf

No.

They are stuck in the usual academic mindset, the examples are particularly about 2PL, which is an MV-non-CC artefact, not relevant
to, or used in SQL OLTP Platforms, because we do not have 2PL. They
do not understand or reference the "1PL" Lock Manager we have in the
real world.

We definitely do not "re-execute" a Transaction, the Validate block is
not a [paraphrased] "first execution without holding locks". The
notion of re-executing a Transaction [note the compute-intensive !] is stupid. The Validate block is a separate formal article.

What the exercise describes is a concurrency control protocol within the DBMS. So, it's the system that "first execute[s] the transaction without acquiring any locksrCa" and then "rerun[s] the transaction using 2PL". That's transparent for the user.

Yes, I know all that. It is pathetically idiotic. They understand Two Phased Commit (server protocol) and they are trying to apply it to Transactions. They are in the same category of ignorant drooling idiots as the freaks in TTM, trying to re-define and re-frame Transactions according to their hysterical fantasies
I was not giving a full review of the book, I just identified a couple of their idiocies.
This thread is about OLTP Transactions, objective truth that has not changed, since 1965. It is not about the hysterical speculations of academics, who as evidenced know nothing about implementations, and declare their isolation from the real world as an elitist badge. You keep bringing that filth in, I keep throwing it out. You can stop any time, the result will be the same.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 5 August 2021 at 11:50:32 UTC+10, Derek Ignatius Asirvadem wrote:

On Thursday, 5 August 2021 at 00:34:46 UTC+10, Nicola wrote:

This thread is about /Stored procedure structure in RDBMS using Lock Manager for transaction isolation/, and the real world of high end implementations. I don't have the inclination to argue speculation against speculation, endlessly.

Ok, let me understand: what are the differences in behaviour between

Ok. For understading

I'll try to rephrase my question, which basically asks if you have statement-level consistency of SELECT queries at READ COMMITTED in
Sybase.

I have already answered this in detail in this thread. Please read.

With OP's stated goal in mind, when you read the thread, notice how much effort Daniel put into the thread, and that he achieved completion; closure; resolution. Notice how much you put into it, and whether that effort progressed in the direction of the goal.
Further, Daniel will remember the increments in his effort, and the final Template. Will anyone remember the drawn out back-and-forth that achieved precisely nothing ?
Ok, I grant that the constant questioning and argument from the peanut gallery is a programmed response from academia, the classic erection of Straw Man arguments, which are speculations about things that they do not know; that they do not understand. So yes, that was an achievement. Albeit it a perverse one, because it had nothing to do with the goal, and everything to do with perverting it.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2