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

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 … ;

and

start transaction;
select … ;
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 TRAN… COMMIT block?

Nicola

> On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
> > On 2021-08-03, Derek Ignatius Asirvadem 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 … ;
>
> and
>
> start transaction;
> select … ;
> commit;

It is possible that you are confused by the SQL syntax required. Or for the reason I mentioned, that is the freaky fantasy land of PissGriss, which is always in a “transaction” mode, wherein Transaction has been redefined.

The purpose of language and definition of terms is to communicate a concept in one word, instead of an explanatory sentence, each time. Things get very confused when people use a word but mean quite different things. Which is the standard practice of academics in this field: they use private definitions and re-definitions and re-framing. All disgustingly dishonest, and a demonstration, ready evidence, that they cannot communicate with the real world that they claim to be theorising about.

Thus I will use only real world terms, and established SQL terms. As they apply to genuine SQL platforms, noting that the freeware is not SQL compliant, and PigPoopGres is the worst, fraudulently declaring in its manuals its redefinition of SQL terms, as “SQL”. In order for this exchange to be resolved in less than two years, please use standard definitions when you use technical terms.

1.
In SQL/ACID compliant platforms, there is the concept of ISOLATION LEVEL. The command is SET TRANSACTION ISOLATION LEVEL. That means that Transactions and a Transaction context is maintained, and the concerns are interference or isolation from Transactions.

THAT DOES NOT MEAN THAT THE CONNECTION THAT EXECUTES
__ SET TRANSACTION ISOLATION LEVEL
IS A TRANSACTION

(Unless you are Chinese ...)
When you are walking on the footpath, you do so because you are aware of vehicles, and you do not want to be hit by one. That does not mean that you are a vehicle. It does not mean that you will encounter a vehicle on the footpath. Indeed, you are walking on the footpath to avoid vehicles.
(Except Chinese, who walk in the road, for the purpose of exchanging their pathetic life for insurance payouts.)

2. In previous posts, you seem to understand that a Transaction is a block of SQL code that is commenced with:
__ BEGIN/START TRANSACTION
and concluded with:
__ COMMIT/ROLLBACK TRANSACTION

If you were using that concept in a loose way, or in case you have any doubt about what a Transaction is, this is the definition:
__ A Transaction is a block of SQL code that is commenced with:
____ BEGIN/START TRANSACTION
__ and concluded with:
____ COMMIT/ROLLBACK TRANSACTION

3. The corollary is this:
__ Any SQL code that is NOT bounded by BEGIN TRAN::COMMIT/ROLLBACK TRAN is not a Transaction.

Since SQL cannot prevent idiots from coding verbs that update the database within Transactions only, it permits INSERT/UPDATE/DELETE (which normal humans do only within a Transaction) outside a Transaction. Much like sex within the context of a sanctified marriage vs sex outside one: it cannot be prevented, but it is illegal; immoral; and the children are crippled due to being outside wedlock.

SQL requires that INSERT/UPDATE/DELETE is executed at ISOLATION LEVEL 3/SERIALIZABLE.

In order to affect INSERT/UPDATE/DELETE without the formal BEGIN::COMMIT TRAN bracket, for the duration of the INSERT/UPDATE/DELETE, the SQL platform has to switch into ISOLATION LEVEL 3/SERIALIZABLE, and after the Statement completes, it switches back to whatever the ISOLATION LEVEL was.

4.1 Note that this does not prevent (a) Phantoms (b) result set Anomalies [both of which are defined in the literature, and therefore it is not defined or redefined here).

4.2 Re the central theme of this thread, which is the prevention of (c) Lost Updates, and (d) Lost Currency, which terms are not defined in the literature, I have defined them in this thread, and in the Transaction Sanity doc.. Prevention of [c][d] is beyond SQL; beyond the definitions of ACID in freeware manuals; within the definition of ACID both historically, and in spirit and word. That is known as Optimistic Locking, which has a component in each database table, and a Formalised structure for the stored procedures that execute Transactions.

4.3 Generally, SELECT, both simple and complex is used for reporting, that is, consisting of multiple roes, and from multiple tables, wherein [a][b] is relevant.

Generally, Transactions affect few rows in each table, and consist of a chain of rows, reflecting the data hierarchy. Of course, far more resources on the server are engaged, in particular, contention management resources, and ACID implementation resources (except in fantasy anti-SQL freeware).

4.4 Re your question ...
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 … ;
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

SELECT ...
<<<<

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

5.
Therefore:
> start transaction;
> select … ;
> 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

SELECT ...
<<<<

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

Sorry. As you can see, it was purposely given in increments. Look at the date in the footer, which identifies the version.

I have updated again just now, to fix a cosmetic error.

> 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 call the whole stored procedure a Transaction, how do you refer
> to each BEGIN TRAN… COMMIT block?

???

1. Differentiating Stored Procs.
Every man in a prison is a prisoner, a convict. Some of them are murderers.. When I get them to sing as a group, I call “all”, when I get to the last verse, I call “just the murderers”.

Stored procs are used to perform myriad functions, not only Transactions.

Transaction stored procs are differentiated from other stored procs by the fact that only Transaction stored procs have (a) a formalised OLTP structure, (b) a BEGIN/START TRAN and a COMMIT/ROLLBACK TRAN, (c) do not return a result set, and (d) return a return-value.

Report stored procs are differentiated from other stored procs by the fact that only report stored procs create tempdb tables, and return a result set..

2. Differentiating Transactions.
The murderer is a murderer because he killed someone. All the convicts cannot be said to have killed someone. Nevertheless, all the men in prison are convicts. The distinction between a convict and a murderer is, the murderer killed someone.

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

First, I did not call the whole stored proc a Transaction. I can’t explain what I did not say.

I called A stored proc that contains a Transaction a Transaction stored proc.

Second, there is not an “each”, by decree, there is only one BEGIN TRAN...COMMIT block in a Transaction stored proc.

Third, the Transaction stored proc is differentiated from other stored procs by the fact that it contains Transaction verbs, BEGIN/START TRAN and COMMIT/ROLLBACK TRAN. That does not imply that the entire code contained in the Transaction stored proc is a Transaction, in the same way that a Report stored proc does not imply that the entire code contained produces a report. In programming, there is often a setup up; a PREPARE block; an completion block; an error handling block; etc.

Fourth, in a Transaction stored proc, the Transaction content is differentiated from the rest of the stored proc code by the BEGIN/START TRAN ... COMMIT/ROLLBACK TRAN bracket.

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

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 … ;
>>
>> and
>>
>> start transaction;
>> select … ;
>> 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 … ;
> 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

«PostgreSQL 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 … ;
>> 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

> 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

> 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 … ;
> >>
> >> and
> >>
> >> start transaction;
> >> select … ;
> >> 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 … ;
> > 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.

(Ok.
FYI. In Sybase the default for locks is Page, for both data pages and index pages. One can set to:
- data page (indices will be latches)
- data row)

> 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;

The example is not a relational table (no Key). At best it is a classroom exercise for record filing systems, and it depends on how good or bad the person who wrote the filing system was. What happens is irrelevant to an SQL context, or an ACID context, or an OLTP context. Thus it is not worthy of an answer.

Further, are you asking what an SQL Platform should do, or what Sybase does ?

Nevertheless, I will play along with you, answering for Sybase ASE.

> Assume that the query is executed using a nested-loop join.

Well, it can’t, there is no Key. It can do some other form of join, relevant to the Heap (no Key) structure.

But the important thing to understand here is, an “NLJ” (or other Join) in PoopGres is not at all comparable to an NLJ (or other Join) in Sybase or DB2 or MS.

> 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?

No. Just two.

For your example, the level of locking and the join type make no difference at all. If your file consists of only those two records, they will be on the same page, and cached in RAM. If your file has a million records, and you have just picked two random records, the two pages will be in memory.

I will assume the default isolation level for the SELECT: READ COMMITTED. The SELECT will not compete with itself, for locks or for pages on disk or for pages in memory.

The SELECT will contend with the UPDATE only if you can manage to obtain microsecond precision across the two connections. That means, in 99.999999999% of the cases, there will be no contention.

If (a) you have the ability to execute microsecond precision across your two connections, AND (b) your precision is correct, you will get the same result on every attempt.

If (a) you have the ability to execute microsecond precision across your two connections, but (b) your precision is incorrect, you will get a different result very rarely, and only if you will have to try it millions of times..

>>>>
In various benchmarks, on a genuine Relational database, using various table structures, many people have tried to produce contention, in this sort of way. It never succeeds, the effort is futile.

The way I run OLTP benchmarks is, I run 100 or 200 threads that perform a complex UPDATE, against 50 threads that perform a complex SELECT, on the same tables with 16 million rows each. TPC style. Contention is produced, but it is miniscule. If the UPDATE is in a proper OLTP Transaction structure, the contention is even less, due to the stored proc being compiled and Query-Planned (resource planned). The SELECT is automatically Query-Planned after the first execution (refer the Statement Cache).
<<<<

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

Those are the only two possibilities. You will get them in a predictable sequence, qualified as per my comments above.

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

There is no such thing as “during”. The UPDATE runs at SERIALIZABLE, the SELECT runs at READ COMMITTED. Except for microsecond precision, which in any case only affects the sequence ( [1][2] xor [2][1] ), there is no contention to be had, the attempt does not, cannot, cause contention. The SELECT reads the UPDATED row XOR the SELECT reads the un-UPDATED row.

Hence, now, the join type is even less relevant.

> 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?

No. That is low-level concerns relevant to a mickey mouse file handling system written by idiots who have never kissed a girl. It it irrelevant in an SQL ACID context.

(Again, you do not know ACID, you think you know ACID from the anti-ACID redefinitions in academic papers and in PiggyGross. The answers to your recent questions are just /how does ACID handle this/, but you don’t realise it.)

> Are locks under these circumstances actually just
> latches?

No. They are row level locks.

Latches are for internal structures (in memory), which includes B-Tree non-leaf levels, and for changing the internal structure on a page (not the data; not the rows) only. The latter is rare, the former is only when the B-Tree is expanded (a level is added or a page is split).

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

An ACID compliant SQL platform does the same.

The difference is, we don’t make a song and dance about it, and we certainly do not agonise over such low-level concerns.

> «PostgreSQL actually treats every SQL statement as being executed within
> a transaction.

Yes, I was trying to explain that to you. Many times.

>If you do not issue a BEGIN command, then each individual
> statement has an implicit BEGIN and (if successful) COMMIT wrapped
> around it.»

Which means, categorically, that it fails to comply with SQL, and it fails to comply with ACID. Because a SELECT is not a Transaction, it should be run at the SET isolation level, not at 3/SERIALIZABLE. Further, there is nothing to COMMIT.

I have been trying to tell you that too, many times. Thanks.

----

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 enrgy to make their fantasies real, physicalising their fantasies.

Cheers
Derek

> 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

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 … ;
>> >>
>> >> and
>> >>
>> >> start transaction;
>> >> select … ;
>> >> 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 … ;
>> > 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

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 TRAN… COMMIT) is not
a transaction.

Nicola

>> 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 locks…" and then "rerun[s] the transaction using 2PL".
That's transparent for the user.

Nicola

> 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 … ;
> >> >>
> >> >> and
> >> >>
> >> >> start transaction;
> >> >> select … ;
> >> >> 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 … ;
> >> > 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

> 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 TRAN… COMMIT) is not
> a transaction.

???

> A single
> SELECT statement (not enclosed in BEGIN TRAN… 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

> 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 locks…" 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

> 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

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

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.

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

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

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

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

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

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

Nicola

> On Wednesday, 11 August 2021 at 19:14:58 UTC+10, Nicola wrote:
> > On 2021-08-10, Derek Ignatius Asirvadem wrote:
> >
> >> 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.

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

----

> I wouldn't talk about some "point in time when count() is correct".

Well, you are implying it. And the drooling imbecile that you cite (science doesn’t need citations; fantasy does) makes it quite explicit. You can’t have it both ways.

We are discussing READ COMMITTED, therefore T1 is definitely not a Transaction. Your naming it as such will confuse you. By the Grace of God, your fraudulent use of technical terms does not confuse me. This naming of non-transactions as “transactions” is a particular fixation of yours.

I will continue the exchange on the basis of the scenario you have described, which is [S1] executing a COUNT, on an online shared database, and [T2] inserting1 and deleting 2 rows in the same table, concurrently, meaning that [T2] happens at a point in time that is after the [S1] first read and before the [S1] last read.

Note, that if [T1] was a Transaction, then it would be SERIALIZABLE, and the desperately fantasised “problem” would not occur.

Note, in Sybase; DB2; MS, you can alternately NOT open a Transaction, but instead, SELECT ... AT ISOLATION REPEATABLE READ, and your dearly beloved fantasy of a “problem” would not occur. Which of course, the freeware herds cannot.

----

> Logically, the count is (should be) an atomic (so, logically
> instantaneous) operation,

On which planet or multiverse or pubertal fantasy does that happen ? Rhetorical. Don’t answer that. Please.

Get real. That is an idiotic proposition. In reality, there is no such thing. And please, we do not need to hear about what your perception of “logic” is.
_ in the real world, SQL and ACID, count is not atomic or “atomic”. Count is count.
___ if you want atomic, you have to open a Transaction
___ you have not opened a Transaction
___ therefore, in the real world, your proposition is stupid, fantasy, in contradiction of the real world
_ in the real world, nothing is instantaneous.
___ reading a large table is definitely not instantaneous
___ (I accept, that in the asylum, the knuckle-dragging freaks fantasise, that the girls whom they repulse desire them; that the non-instantaneous is “instantaneous”; that the moon is made of cream cheese; etc.. They are legally said to be not responsible, because they cannot handle reality.)
___ you cannot transform something that is physically non-instantaneous into “instantaneous”, with or without a note from your mother, with or without good drugs or happy gas
___ (you can, if and only if you are in the asylum, and if and only if you have taken your medication)
___ it is not “logically instantaneous”, the proposition is logically absurd, because it is in denial (schizophrenic) of reality, that AS DESCRIBED BY YOU, the table is in an online shared database, AND it is being changed

----

> so whenever T1 commits, that's when the count
> becomes visible, and its result should be correct at that point.

1.
Corrected as defined above:
> so whenever [S1] commits, that's when the count
> becomes visible, and its result should be correct at that point.

Corrected further:
Since we are discussing READ COMMITTED, and [S1] is not a Transaction, it cannot COMMIT (you can, but it does nothing, it only satisfies your fixation on your fantasy).

REPEATING
>>>
Note, that if [T1] was a Transaction, then it would be SERIALIZABLE, and the desperately fantasised “problem” would not occur.

Note, in Sybase; DB2; MS, you can alternately NOT open a Transaction, but instead, SELECT ... AT ISOLATION REPEATABLE READ, and your dearly beloved fantasy of a “problem” would not occur. Which of course, the freeware herds cannot.
<<<<<

2.
There you go, stating a point in time, while rejecting the notion of a point in time.
You said:
> I wouldn't talk about some "point in time when count() is correct".
Now you are saying:
> its result should be correct at that point.

Look, I am happy to help, but I really do not have the qualifications that are required for the kind of help that you need.

This is a public newsgroup. It might be a good idea to stay away from computers. You will be so much more successful at running a pig farm, and so much happier too.

----

> Read committed permits an execution in which:
>
> 1. T1 starts scanning the table, counting the row that T2 will delete;
Corrected:
> 1. [S1] starts scanning the table, counting the row that T2 will delete;

a.
You are confusing the present time (while rejecting a point in time !!!) with some event, at a specific point in time, which point in time is before the point in time that [S1] completes.

b.
There is no reason (Logic) why [S1] should know about or care about what [T1] is doing.

c. Corrected further:
> 1. [S1] starts scanning the table, counting the rows, oblivious to other activity, by virtue that it declaratively runs at READ COMMITTED.

REPEATING
>>>
Note, that if [T1] was a Transaction, then it would be SERIALIZABLE, and the desperately fantasised “problem” would not occur.

Note, in Sybase; DB2; MS, you can alternately NOT open a Transaction, but instead, SELECT ... AT ISOLATION REPEATABLE READ, and your dearly beloved fantasy of a “problem” would not occur. Which of course, the freeware herds cannot.
<<<<<

----

> 2. T2 executes and commits;
> 3. T1 keeps scanning the table, now counting the rows that T2 has added.
Corrected:
> 3. [S1] keeps scanning the table, without regard to other activity

Which somehow is “incorrect” to you. Only in some sick fantasy.

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

Which point in time is that “never”, given your rejection of a point in time ?

By the time you read the result set (one row with the count), the table count would have changed. By the time you execute the SELECT again, the table count would have changed yet again.

I would say that the point at which “never” occurred, never occurred, but you are incapable of understanding that (not the English, your English is great, but the meaning).

Corrected
> Then, [S1] would return N+2

So freaking what. It is a table COUNT at READ COMMITTED, in an online shared database, on a table that is known to have concurrent activity.

----

> 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.
Why ?
Because we do not need an idiotic definition from the asylum.
Because we already have a definition in (a) SQL, and (b) ACID, and have had, for FORTY YEARS.
Wherein COUNT at READ COMMITTED works perfectly.

Before the freaks in the asylum concocted their fantasies, according to the only thing they can understand, which is single-threaded (“serialised”) operation in the herd of bloated, flatulent programs (“server”), executing on a “snapshot” of the database, which is by definition a fantasy, that never existed.

> - T1;T2, in which case T1 would count N rows;
> - T2;T1, in which case T2 would count N+1 rows.
Corrected:
> - [S1];[T2], in which case [S1] would count N rows;
> - [T2];[S1], in which case [T2] would count N+1 rows.

Nonsense.

You contradict yourself.

You said S1 and T2 ran concurrently. Now you are saying that according to your fantasy (not the reality of S1 & T2 running concurrently, which you said you understood), about how a sow excretes its poop while you are under it, you can only understand single-threaded execution (“serialised” !!!) with Statements that are not atoms by definition, magically transformed into “atoms”.

You can’t make this stuff up, you have to eat pig poop.

Nicola

> On Wednesday, 11 August 2021 at 18:45:32 UTC+10, Nicola wrote:
> > On 2021-08-10, Derek Ignatius Asirvadem wrote:
> >> 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 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 is 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.

__ https://www.dailymail.co.uk/tvshowbiz/article-9876165/Naked-Afraid-viewers-spot-man-rifle-protecting-contestants-Africa.html

The main thing to glean from this article is this. Look at the comments. From the evidence, there exists people who actually think that the obviously fake “reality” shows, on a machine that reproduces coloured pixels on a screen, from a film that captures paid actors doing some activity, is “real”. And they are heart-broken to have their fantasy punctured.

Why is that ? Why is it that humans who are capable enough to write comments in English on the twitter-verse (for twits), have the stupefying thought that fantasy is “real”.

Because at this time, we have had decades (centuries, if you include the sabotaged philosophy and ape-man “science”) of people in authority erecting fantasies, and then treating them as real. This includes academics, who make a “science” out of fantasising. This includes all university students who are taught the art of fantasising, as “science”, by their professors.

A few years ago, I went back to uni and did two semesters, in order to determine for myself why our new hires were schizophrenic, why they give really really really good reasons for NOT doing the job they were hired to do, that they said that were very good at. The evidence is, they teach schizophrenic thinking as “normal”, as an art, as “science”.

Science is about knowledge, knowledge of reality.

Fantasy occurs in the imagination, not in reality. Fantasy is anti-science.. But they have made a “science”out of it.

What we are dealing with here is the COLLECTIVE fantasy. That is, “taught” and maintained and believed by a bunch of asylum dwellers, and therefore they re-inforce each other, and therefore the fantasy is made very very very “real”. Same as a male fantasising that he is female, which is in and of itself evidence of insanity, but made acceptable because many degenerates have the same fantasy, a COLLECTIVE fantasy. And now weaponised to make war on humans.

----

Here, you are the academic, pushing a ridiculous fantasy, as “science”, citing idiotic circumstances in the tiny corners of the Bell curve, that do not apply (or apply only to idiots who are ignorant of the commands that eliminate the ”problem”).

The Stonebraker religion (make no mistake, it is a de facto religion, faith-based, not science-based) is that in a multiverse somewhere, there is a special place for special-needs people, where:
1. everyone recites a mantra “readers do not block writers, writers do not block readers”, prayerfully, without ceasing
2. denies the reality that the database is an online shared resource that is changing all the time due to activity of hundreds of users
__ (that therefore requires concurrency control in order to provide integrity)
3. erects a fantasy that the database is a ”snapshot”
__ (which is physically impossible, because as Daniel has pointed out, by the time the reader has passed the first few pages, those pages are out-of-date, obsolete, and no longer in the snapshot that the snapshot is supposed to be)
____ SAME AS YOUR POINT, RE YOUR PATHETIC CITED EXAMPLE
____ OF COUNT NOT PERFORMING AS THE FANTASY “COUNT”
____ YOU CAN SEE THAT IT IS “TRUE” FOR THE FANTASY
____ BUT YOU CAN’T SEE THAT IT IS TRUE FOR REALITY
____ IT IS CULTIVATED SCHIZOPHRENIA
4. taken at a point in time
__ (which you /now/ say is not possible, “never happened”, but evidently you cannot see that it is a general rule about the ”snapshot”).
5. Which of course is a grand lie, because they themselves have to implement a primitive 2PL (not the “2PL” which is used to attack 0PL), in order to control concurrency which they say is controlled in their mythological “MVCC”, but it not. that breaks their own mantra, readers block writers sometimes, writers block readers sometimes, and they have to deal with it.

Anyone with half a brain would realise that if they implemented a proper Lock Manager, they could eliminate the massive burden of making the fantasy “real”, the storage of masses of version. Which the commercial SQL Platforms have done for FORTY YEARS. But the evidence is, they do not have even half a brain, instead they repeat their mantra, prayerfully, without ceasing.

By the way, we do not have “read locks” and “write locks”, the notion is too primitive, it exists only in primitive 2PL that is used to make MV-non-CC work, even just a little. Again, not comparable to a real Lock Manager.

----

The only true snapshot is a single frame, as in a hardcopy photograph, or a JPEG file. “See, that was the moment in time that my wife and I sipped a cocktail on the beach in Bali.” In database terms, the only true snapshot is a single page.

Take the production of a movie. They carefully make sure that the frames are NOT related; that they are NOT a sequence (in order to build the plot), they are quite aware that it is a roll of film, that they fabricate, that it is not reality (it is a story, remember). No problem, everyone knows that when they go to the cinema, they have to “suspend belief” and entertain the movie as “real”, that is what they are paying for.

In the real world, there is no snapshot of more than one frame.

But the lunatic asylum treats the entire roll of film (moving picture) as a “snapshot”. Yes, it exists as a fabrication, copying a roll of film may well be an “atomic” task. No, the first frame is unrelated to the 42nd frame, etc. No, the content of the film does not exist in reality (it was produced according to a script, employing paid actors).

Applying that snapshot notion to a database that does exist in reality is pure insanity, it is not a snapshot but a “snapshot”. The first few pages may be true, but by the time the reading mechanism gets to the next few pages, they are out-of-synch with the first few, and totally unrelated when it gets to the last few pages.

Think about this, a database backup in not the “snapshot”, it cannot be, it is a real series of single frames (pages). It is not taken at a “point in time”, because no such thing exists. Each frame (page) is taken at a different point in time. They are quite aware of that when it pertains to the database backup, but they hysterically deny that when the concoct their “snapshot” at a “point in time”.

You do not get it, that that “problem” you propose that happens at READ COMMITTED only because you are too ignorant to execute it at REPEATABLE READ, happens all the time in your fantasy “snapshot” of the “database” at “point in time”, that never happened. But you insist that your fantasy “snapshot” (which never actually happens when you make-real your fantasy as in a real database backup) should happen in your re-defined notion of reality.

You can’t make this stuff up.

----

Oh, yes the you can, and you do, and you teach it as “education”. It is, as evidenced, indoctrination into insanity, in particular, schizophrenia.

As evidenced in the newspaper article, you actively contribute to the common problem of COLLECTIVE insanity, of people thinking that the COLLECTIVE fantasy is somehow “real”. And they are shocked when the fantasy is punctured.

Whereas you double down, and try to (a) re-inforce the unreal fantasy, and (b) impose the idiotic problems that only happen in the fantasy, onto reality. Further (c) you still can’t understand online shared access (let alone concurrency control or multi-threaded architecture), you have to “serialise” (single-thread) your collection of frames (pages), in order to get some sense out of it, so (d) you impose that hysterically stupid “solution” for the “problem” that we do not have, onto us.

When you were a child, did you not hear the children’s’ story, replete with a nursery rhyme, about the three men in pointed hats that stood on a bridge in a small village, looked down at the river, and claimed that the moon had fallen into the river, and life on earth was about to end ? The first verse was funny, even silly. But the second verse, when the men said they could extract the moon from the river and prevent the disaster, if only the villagers paid them some money, identified the evil.

As with all children’s fables, it is not a fairy tale, it is a teaching story, with deep meaning.

Cheers
Derek