From Newsgroup: comp.databases.theory

Nicola

On Wednesday, 4 August 2021 at 19:23:53 UTC+10, Derek Ignatius Asirvadem wrote:

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

First, your questions
__ are classic Straw Man arguments, in that academics are clueless about real world; the industry, which is confirmed by their declaration that they are divorced from implementation concerns, and which is evidenced in every instance, such as this thread,
__ eg. locking vis-a-vis Transactions, let alone how a commercial SQL Platform handles locking and Transactions,
__ so they think that the problems THEY HAVE, such as in their pathetic "2PL", which is a fat band-aid on top of their schizophrenic MV-non-CC
__ are very very real, due to
____ a. their isolation from reality, their comfort in their tiled cells, their universe of discourse
____ b. being UNSKILLED AND UNAWARE (please read some real science)
____ c. hundreds of articles circle-jerking about their fantasies, which are passed off as academic papers
____ d. books that celebrate that filth, passed off as textbooks
__ leave you in that silly place, where you think the sane have the problems that the insane have, and worse, you try to impose those insane problems on the sane.
No.
1. The sane do not have those insane problems
2. And we do not have to answer how and why we do not have those problems
__ If we do, we confirm that those problems are real, and degenerate into insanity
__ and fail to do the thing that is begging: to raise the insane out of their insanity.
Thus it is for you to put your insane problems, your Straw Man questions aside, step out of the asylum, and find out how we do things in the real world, such that we simply do not have those insane problems. Again, I commend you for being the only academic to do so. But your progress, over the last ten years of interaction with me, is very slow. Keep going.
Of course, the best thing to do, is install SAP/Sybase ASE and obtain some experience working with serious databases (not classroom exercises); locking; and Transactions. Find out what undamaged, un-perverted humans have been doing during your fifty years of isolation from reality. You have started that. Excellent.
__ In that regard, I am happy to assist. Ask me a direct question, and I will give you direct advice.
__ No open-ended question, no discussion
____ just look at the long-drawn out discussion that I have entertained, this thread; the IDEF1X/Incomplete Specialisation; etc
That will take ten years or so. And, as evidenced, good, considered advice is something that your dismiss immediately, you want to do things your way. In the meantime, as evidenced, you will continue with your Straw Man questions. Please do the following at minimum:
1. Read the Transact-SQL Users Guide
____ at least ch 20 Transactions: Maintaining Data Consistency and Recovery
2. Then read my guide to the Lock Manager
____ https://www.softwaregems.com.au/Documents/Article/Sybase%20Lock%20Manager/Sybase%20Lock%20Manager.pdf
All my Sybase docs are condensed, intended for Sybase DBAs. I have just updated it, and added a bit of detail, imp[roved the clarity, so as to be relevant for novices.
Remember, this is a serious Lock Manager, not comparable to your 2PL filth, which has to be asserted because you guys position commercial SQL Platform Lock Managers as your "2PL" filth, and insist that we have your insane problems. It is so mature and secure, so brilliant in architecture, that it has not changed since 1984. Extended, yes (eg. to handle new Lock Types to support SAP files, eg. add row locks; etc), but changed, no. So read these docs with a fresh mind, to not take your academic baggage with you.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

1. Read the Transact-SQL Users Guide
____ at least ch 20 Transactions: Maintaining Data Consistency and Recovery

2. Then read my guide to the Lock Manager
____ https://www.softwaregems.com.au/Documents/Article/Sybase%20Lock%20Manager/Sybase%20Lock%20Manager.pdf
All my Sybase docs are condensed, intended for Sybase DBAs. I have
just updated it, and added a bit of detail, imp[roved the clarity, so
as to be relevant for novices.

Remember, this is a serious Lock Manager, not comparable to your 2PL
filth, which has to be asserted because you guys position commercial
SQL Platform Lock Managers as your "2PL" filth, and insist that we
have your insane problems. It is so mature and secure, so brilliant
in architecture, that it has not changed since 1984. Extended, yes
(eg. to handle new Lock Types to support SAP files, eg. add row locks;
etc), but changed, no. So read these docs with a fresh mind, to not
take your academic baggage with you.

I have read a couple of documents (the T-SQL guide and the Locking and Concurrency Control manual). My takeaways:

- yes, it is a serious lock manager (I did not expect anything less from
a high-end commercial product).

- I did not find any concept that you would not find in a database
systems' textbook (and no, the Alice's book is not such a textbook).

- Call it what you like, but ASE/Sybase uses what is known as "rigorous
2PL" to implement repeatable read and serializable:

https://help.sap.com/viewer/a08646e0736e4b4b968705079db4c5f5/16.0.3.7/en-US/a8ea3fd4bc2b1014b6569e800f6bba42.html.

"Applying exclusive locks [...] until the end of the transaction.
Applying shared locks [...] until the end of the transaction".
Textbook definition of rigorous 2PL.

- It uses index-locking to prevent phantoms. Again, no surprise and
pretty much standard textbook material.

- There is a section dealing exactly with the question I have posed:
"Locking for Select Queries at Isolation Level 1"

https://help.sap.com/viewer/a08646e0736e4b4b968705079db4c5f5/16.0.3.7/en-US/a8eb04a3bc2b1014bef8884d8400b0ab.html

Btw, with a mention of how that affects joins. That, plus this (which
is about SQL Server and has some inaccuracies, but overall I think it
is relevant):

https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level

makes me conclude that in general you do not have statement-level
consistency at read committed in SQL Server or ASE.

ASE is a fine implementation (*), based on concepts that have been very
well known in the academic community for a long time (not to say that
they are obsolete! On the contrary!). What's not in the textbooks is the specific implementation details and system-dependent guidelines that
a manual is expected to provide. Granted, the devil's in the details.
But good graduate students would have no problems grasping such details
(or those of any other system), capitalizing on their academic baggage.

Nicola

(*) Known to the academics. E.g., some time in the '90s, Sybase was used
for lab exercises at Stanford.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the lenght of the answer, which yet again has to be an explanation for you, please read this newspaper article. Explain in one or two sentences, what it the most important item that you (as a teaching professor) can glean from the article. In case it needs to be said, it is not a trick question, it will indicate to me just how much I have to explain.
__ https://www.dailymail.co.uk/tvshowbiz/article-9876165/Naked-Afraid-viewers-spot-man-rifle-protecting-contestants-Africa.html
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Nicola

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the lenght of the answer, which yet again has to be
an explanation for you, please read this newspaper article. Explain
in one or two sentences, what it the most important item that you (as
a teaching professor) can glean from the article.

To me that stuff reads as fake as the fake it claims to depict.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Wednesday, 11 August 2021 at 06:14:10 UTC+10, Nicola wrote:

On 2021-08-10, Derek Ignatius Asirvadem wrote:
Nicola

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the lenght of the answer, which yet again has to be
an explanation for you, please read this newspaper article. Explain
in one or two sentences, what it the most important item that you (as
a teaching professor) can glean from the article.

To me that stuff reads as fake as the fake it claims to depict.

Yes, of course the tv show is fake.
Yes, of course the concept is fake (Big Brother, "progressed" 15 years).
That is obvious.

That aside, did you glean anything of value from the article ?

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

From Newsgroup: comp.databases.theory

Nicola

On Wednesday, 11 August 2021 at 06:14:10 UTC+10, Nicola wrote:

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

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the length of the answer, which yet again has to be
an explanation for you, please read this newspaper article. Explain
in one or two sentences, what it the most important item that you (as
a teaching professor) can glean from the article.

Another simple question, clarifying only, in order to reduce my labours.

Given your detailed question, AND the example you have cited, at what point in time, do you suggest that the COUNT() would be correct ? I am not asking for a long answer here, just clarifying you query. A few words would be enough.

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

From Newsgroup: comp.databases.theory

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

Nicola

On Wednesday, 11 August 2021 at 06:14:10 UTC+10, Nicola wrote:

On 2021-08-10, Derek Ignatius Asirvadem wrote:
Nicola

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the lenght of the answer, which yet again has to be
an explanation for you, please read this newspaper article. Explain
in one or two sentences, what it the most important item that you (as
a teaching professor) can glean from the article.

To me that stuff reads as fake as the fake it claims to depict.

Yes, of course the tv show is fake.
Yes, of course the concept is fake (Big Brother, "progressed" 15 years).
That is obvious.

That aside, did you glean anything of value from the article ?

No.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Nicola

On Wednesday, 11 August 2021 at 06:14:10 UTC+10, Nicola wrote:

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

On Sunday, 8 August 2021 at 23:52:04 UTC+10, Nicola wrote:

In order to reduce the length of the answer, which yet again has to be
an explanation for you, please read this newspaper article. Explain
in one or two sentences, what it the most important item that you (as
a teaching professor) can glean from the article.

Another simple question, clarifying only, in order to reduce my labours.

Given your detailed question, AND the example you have cited, at what
point in time, do you suggest that the COUNT() would be correct
? I am not asking for a long answer here, just clarifying you query.
A few words would be enough.

Count() would return the correct result if and only if the returned
value is among the values that some serial execution of the same set of committed transactions would have returned.

If transaction T1 is scanning a table to count its rows, and
concurrently transaction T2 removes one row and adds two, the only
values T1 should be able to compute are N (the number of rows in the
table before the T1 and T2 starts executing) and N+1. That is because
there are two possible serial executions:

- T1;T2, in which case T1 would count N rows;
- T2;T1, in which case T2 would count N+1 rows.

I wouldn't talk about some "point in time when count() is correct".
Logically, the count is (should be) an atomic (so, logically
instantaneous) operation, so whenever T1 commits, that's when the count
becomes visible, and its result should be correct at that point.

Read committed permits an execution in which:

1. T1 starts scanning the table, counting the row that T2 will delete;
2. T2 executes and commits;
3. T1 keeps scanning the table, now counting the rows that T2 has added.

Then, T1 would return N+2, where the table never had N+2 records.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Given your detailed question, AND the example you have cited, at what
point in time, do you suggest that the COUNT() would be correct
? I am not asking for a long answer here, just clarifying you query.
A few words would be enough.

If transaction T1 is scanning a table to count its rows, and
concurrently transaction T2 removes one row and adds two, the only
values T1 should be able to compute are N (the number of rows in the
table before the T1 and T2 starts executing) and N+1. That is because
there are two possible serial executions:

False. We are not serialised.

We are discussing READ COMMITTED,

Ok, I have misunderstood your question. The above is a very informally
stated definition of correctness of a schedule (i.e., serializability).

So, if you ask at what point in time a result of a query run at READ
COMMITTED is correct, the answer is: in general, never. Note that this
answer doesn't rule out situations in which you can guarantee a correct
result even at that level.

1. [S1] starts scanning the table, counting the rows, oblivious to
other activity, by virtue that it declaratively runs at READ
COMMITTED.
2. T2 executes and commits;
3. [S1] keeps scanning the table, without regard to other activity

Which somehow is rCLincorrectrCY to you.

Yes, according to the definition of "correctness" (which you affirm to
reject) as equivalence to a serial schedule.

Sorry, what is the definition of "correctness" according to the
Standards?

Count() would return the correct result if and only if the returned
value is among the values that some serial execution of the same set of
committed transactions would have returned.

I reject that as a definition, Sybase; DB2; MS rejects that as well.

Then, again, please provide a definition of "correctness". Because you
want to be confident that your queries produce "correct" results, don't
you?

Since anything can happen concurrently during the execution of a query
at READ COMMITTED, how can you tell apart the result of a query
returning an integer from

select cast(rand() * 1000000) as int)

?

Wherein COUNT at READ COMMITTED works perfectly.

Ok if you are referring to other ways of counting. But if you mean that counting by scanning the table at READ COMMITTED works perfectly, please elaborate on what "works perfectly" means.

But there is an important second point: anyone who has even pedestrian knowledge of SQL on a genuine SQL Platform, would know that [separate
to the count changing constantly because the table is active], that
that is not a rCLproblemrCY, that that is not the way to obtain a COUNT on
a large table.

Sure. But it still illustrates the point, which is about the kind of
issues you may run into when running queries at READ COMMITTED. It is
not suggesting that you *should* run those queries at READ COMMITTED.
Counting was not the best example; but you can replace the query with
some other computation.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2