From Newsgroup: comp.databases.theory

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

So think that out. Write an ACID Transaction (no I am not being
silly, I realise you canrCOt on your non-SQL platform, so you have to
think in SQL terms, for an SQL platform). A stored proc, that:
- navigates the *levels* of the Tree,
- and loops,
- executing max 100 INSERTs per BEGIN::COMMIT for the new Key,

Let me call this T1...

- then executing max 100 DELETEs per BEGIN::COMMIT for the old Key.

...and this T2.

Why is the state after T1 has committed and before T2 starts considered
valid? Wouldn't a query performed between T1 and T2 get an inconsistent
view of the data?

Another question: if T1 requires 200 INSERTs instead of 100, you would
split it in two. Again, how can you consider the intermediate state
(after the first 100 INSERTs, but before the remaining ones) valid?

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Tuesday, 15 June 2021 at 08:08:25 UTC+10, Derek Ignatius Asirvadem wrote:

On Tuesday, 15 June 2021 at 03:39:09 UTC+10, Nicola wrote:

On 2021-06-14, Derek Ignatius Asirvadem wrote:

So think that out. Write an ACID Transaction (no I am not being
silly, I realise you canrCOt on your non-SQL platform, so you have to think in SQL terms, for an SQL platform).

A Caveat that is really important. In my years of dealing with developers who are used to the Stonebraker/MVCC mindset, it is clear that they do not grasp ACID. They are so used to the Oracle/PusGres *redefinition* of ACID (fraud), to fit their MVCC mindset, for the purpose of making their MVCC somewhat palatable, that they cannot grasp that they do not know ACID. That state of ignorance allows them to argue that MVCC is comparable to rCLACIDrCY. MVCC is not comparable to ACID, it is the polar opposite.
Flat and absolute declaration: MVCC systems cannot provide ACID, in particular the ISOLATION LEVELs. The consequence is, MVCC systems cannot provide any level of genuine OLTP.
So, even though the current line of questioning is about Batch Transactions, it may be hindered by an incorrect understanding of ACID.

Write an ACID Transaction

Sorry, no, it is a Batch Transaction with no regard to ACID.

A stored proc, that:
- navigates the *levels* of the Tree,
- and loops,
- executing max 100 INSERTs per BEGIN::COMMIT for the new Key,

Let me call this T1...

- then executing max 100 DELETEs per BEGIN::COMMIT for the old Key.

...and this T2.

We can call it T1; T2, but we donrCOt want to confuse that with a normal ACID Transaction T1; T2, because it is not, it is a Batch Transaction. Whereas in ACID, we are dealing with {Add|Drop|Mod} a logical Atom of data (RM-compliant db = coincident with Logical Atom; non-compliant db = does not have Logical Atom), here we are moving an entire tree, of many levels, therefore State in the ACID sense will not apply.

We may be better off calling it B1; B2.

Whereas T1; T2 are ACID Transactions, and State means database state; Consistency per all Constraints, ie. Logically Atomic, B1: B2 are physical chunks (delimited by an arbitrary number), in a physical *move* operation. It is harmless ACID-wise because the OldKey tree is ACID-compliant, and we are not affecting that in any way, we are faithfully carrying that over into the NewKey tree.
Such operations are not foreign to a DBA or developer, because they are used to *moving* large chunks of data in their normal day-to-day work. Eg. moving a logical xor physical subset of a table from Production to Development for test purposes. For logical (Atomic) purposes, that means not one table but a set of tables (a branch of a tree). Such operations may be new to some readers.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Monday, 14 June 2021 at 14:35:13 UTC+10, Derek Ignatius Asirvadem wrote:

On Monday, 14 June 2021 at 01:13:54 UTC+10, Nicola wrote:

I'd like to do some benchmarks.

Great idea. Excellent ...

*Benchmark* strongly implies an ability to monitor the server, all metrics that pertain to performance (in general), and the particular metrics that pertain to the particular benchmark. Freeware has no such thing, so you will be working in the dark, with little idea re what is happening under the covers.
For contrast, take a glimpse of what is available in commercial SQLs.

0. Monitoring products
Additional licence fee, not shown. Feel free to search the internet.

There are quite a few.
0.a
Bradmark is probably the best of breed. Great graphical representation with drill-down, etc. Note that we have had these for thirty years, ten years before the open source anti-market produced you-get-what-you-pay-for rCLdatabase systemsrCY.
__ https://www.bradmark.com/products/survSybaseASE.html
AFAIK, none of the players provide support for freeware. This is not to say that there is not a market (there is, there are suckers born every minute). The reason is, there is nothing, no performance stats produced, to monitor. Worse, whatever does exist, keeps changing with every major version. Just think about how rCLtransactionrCY and rCLtransactionalrCY issues have changed in the last ten years, and we are still nowhere near ACID.
0.b
Sybase used to have a full-blown Monitor Server, that sat on the same box as the DB server. Heavy duty like you would not believe. Perfect for benchmarks. Slowly made obsolete as 3P products gained market share.

1. Raw Stats: text, various forms. Free.
Voluminous, not shown. Can be ascertained from the following [which are summaries].

Generally two types:
1.a
Internal counters, reported as requested (eg. 24 x 60 mins), zero overhead. This has been available from the beginning, and this is what I use to produce [2].
1.b
Monitoring & Diagnostic Access. A database in the server, that collects [1.a] and exposes them as Relational tables. Overhead is 5-15% depending on what is collected (configurable). Eg. collection of execuing SQL can be heavy.

2. Raw Stats Formatted, especially for various types of comparison.

__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

I have given mine for comparison. Do you have a link or reference for monitoring PusGres ?

rCLDltrCY is the delta, between that column-of-stats and the first column-of-stats, as a percentage, with {-|+|x}.

For a quick overview, just scan that column: existence of a double-digit value means the metric is worth examination; positive/negative is good/bad thing, depending on the metric of course.
rCLSelectionrCY
At the top of the page. These are selected rCLKey Performance IndicatorsrCY or an executive summary.
Note that the load is identical (two Mondays chosen to ensure that). But the activity within the server is quite different. The first column shows the activity due to the SAN fault, the second shows the activity after the correction:
__ Read Locks reduced by 91%
__ Server/Context Switch reduced by 21%
__ Unix/Context Switch reduced by 24%
__ and of course far less CPU usage, at both levels

For freeware & Oracle, due to not having a Server Architecture, and instead deploying hundreds or thousands of programs running on Unix, the first avenue of rCLperformance monitoringrCY and even rCLproblem diagnosisrCY, is via Unix monitoring:
-- top
-- vmstat

rCLHost SystemrCY
The unix/vmstat metrics are at the bottom of the page.
Sybase is a dedicated server, meaning that it is designed for tight integration (even binding) with the o/s and hardware, and nothing else should be run on the box. This box additionally hosts runs a small MySQL and a few other small things, and the degree to which they allow me (Level 3 support) to tightly integrate with the o/s is limited. Point being, performance is reasonable, but nowhere near best possible for the box. Eg. I can improve throughput significantly, and of course I would cancel the Monitor db.
rCLSelectionrCY
At the top of the page. These are selected rCLKey Performance IndicatorsrCY or an executive summary.

rCLEnginerCY is a single Unix Process. The Sybase ASE server comprises a grand total of FOUR Engines in this instance, and serves hundreds of active connections (doctors; nurses; medical images; client histories; etc). In contrast, freeware and Oracle would have hundreds of Unix Processes and no concept of Engine.

For those who labour over performance tuning of Oracle or PusGres, because it lacks a genuine Architecture diagram, here it is. Please donrCOt say that I only help the top end, that I donrCOt help the bottom-feeders. They are identical, just substitute the Oracle component names with the PusGres equivalents:
__ https://www.softwaregems.com.au/Documents/Article/Oracle%20Circus/Oracle%20vs%20Sybase.pdf
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Nicola

On Tuesday, 15 June 2021 at 03:39:09 UTC+10, Nicola wrote:

The State that is relevant is the State of the whole tree, either the
whole tree is in the OldKey xor the whole tree is in the NewKey.

Would you mind letting me understand with an example (I'd like to grasp
the overall idea, which I have not yet; I am not asking you to reveal
the secret recipes of your shop) from this document:

https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/Giannis/RoomMonitor%20DM%20V0_51.pdf

Suppose that, for some reason, one needs to update one HotelChain,
changing the value of HotelChain from ABC to H-ABC. If I understand
correctly, you would start by (optimistically) locking the record with HotelChain = ABC.

What would you do next? Insert the new record with key H-ABC into
HotelChain, then insert zero or more records referencing H-ABC into
Hotel, then into HotelNetwork, ... (up to 100 inserts), commit; more
inserts (up to 100), commit, etc.?

How would you delete the old records then? With transactions of up to
100 DELETEs each, starting from the bottom of the hierarchy and
navigating the hierarchy up?

If you have implemented *Optimistic Locking*

It's interesting that you mention optimistic locking (have you ever
mentioned in before in this group?), because my understanding was that
you believe that the only correct way to control concurrency was strict pessimistic locking (strict 2PL). This may have been a misunderstanding
on my part.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

Suppose that, for some reason, one needs to update one HotelChain,
changing the value of HotelChain from ABC to H-ABC. If I understand
correctly, you would start by (optimistically) locking the record with
HotelChain = ABC.

Yes.
And leave it locked for the duration, it will be the last deleted.

(optimistically)

Because I know that you guys do not understand Locking, let alone
Optimistic Locking, note this this is not an act within that [generic, theoretical] framework. This is a data-level lock (if you have row
locking, and a row locking command, you do not have ACID *and* you
have broken a cardinal rule of OLTP).

Let me see whether we are on the same tune. While you are performing the
batch transaction we are talking about, another transaction may also
attempt to update the record with HotelChain = ABC (and specifically
update the HotelChain field). Is it correct that this second transaction
will be allowed to perform the update, and that first (batch
transaction) will detect at the next commit that the "top" row has been overwritten, and rollback (at least rollback the running ACID
transaction)?

Because that is my understanding of "optimistic". But the rest of your
post, (and the remark above) make me doubt that we agree on this
meaning.

Except from the point above, "batch delete+insert" is clear enough.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2