Nicola

> On Wednesday, 16 June 2021 at 18:57:23 UTC+10, Nicola wrote:
> > On 2021-06-14, Derek Ignatius Asirvadem wrote:
> >
> >> 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

They are not secrets in the sense that they are natural progressions of the /RM/, due to a deeper understanding of it. I am sure other serious boutique consulting houses have done at least some of those things. Perhaps not with proper labels and standardisation.

But sure, I answer questions. As deeply as the person is capable of receiving. Which on this forum is only you.

However, this subject is something that absolutely every single programmer should know, not just those developing code to run against an OLTP RDB or “OLTP” RFS. It is a programming construct, a Batch Job. Running against an OLTP database. The DayEnd or MonthEnd procedure at a bank. Move OldKey to NewKey. Some minor deployment requirements, but the construct is the same. I am happy to give full details in public. Particularly because I am shocked that SQL coders do not know this.

The OLTP database and particularly its API (set of ACID Transactions) are already designed for low contention; high concurrency. Think: cars running along the streets of a city. A transaction that affects 10K rows is like a train running along surface streets (no rail tracks, we have one TransactionLog file that cars and trains; boats and planes, all fight over), across the city. That is what contention is. It requires perceiving the whole, as well as the parts. (You guys are trained to think about parts only, and one-at-a-time.)

Cars are allowed max 1 trailer; trucks max 16 trailers; trains max 100 trailers.

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

If you have Soft Deletes, use that.
<<<<

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

Yes.
Code WHILE loops. One WHILE loop per level. SQL has INSERT-SELECT, it is Set Processing, very fast.

Limiting the affected set is simple, in MS or Sybase syntax:
__ SET ROWCOUNT { 0 | n }

Some people would code a cursor. It is pure laziness, same as CTEs. Massive overhead, and totally unnecessary. I don’t allow a cursor on my servers.

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

Yes.
OldKey rows.

> > If you have implemented *Optimistic Locking*
>
> It's interesting that you mention optimistic locking (have you ever
> mentioned in before in this group?),

I don’t keep track of such details, so I can’t say.

Optimistic Locking is a science that existed from the early 1960’s, it is an essential part of OLTP. I have just Relationalised and SQLised it, and made it the OLTP Standard. Which in turn is an essential part of Open Architecture. There are components that have to be in the tables, and of course code components in every ACID Transaction. Science is truth, and truth does not change, it has not changed since 1973 [when I came to know of it]. I openly credit IBM Mainframe CICS/TCP (their transaction server, that you guys think is “batch”). That is Transaction Control Process.

Academia do not know that. So the imbecile Stonebraker read the IBM manuals and thought, with his great charisma, he could do better. So I know, from the bits I have read in this forum, that you guys have a fantasy version of “Optimistic Locking”.

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

Well, I don’t have a problem with what you have picked up. The thing that needs to be understood is, you have picked up whatever I stated, but from your pre-existing mindset. First, noting the above ridiculous notion of “Optimistic Locking”, that consists of a few notions (not standards) from Optimistic Locking.

Second there is no such thing in reality as “pessimistic locking”. Or that “2PL” is “pessimistic locking”, it is absurd. I can only think that the idiots who said so, in those grand papers posed such a silly thing as a Straw Man argument, to make “2PL” systems look bad.

No. Optimistic locking has nothing to do with “2PL” locking. The former is a construct deployed in tables and ACID Transaction code. The latter is the resource contention resolution mechanism on the server, quite removed from the former.

MVCC is not optimistic. It may well be “optimistic” to those precious darlings who chant the mantra /Readers don't block writers, writers don't block readers/, which exists only in the imagination, not in reality, and certainly not in their precious MVCC database that keeps hanging up despite all the users, together now, chanting the mantra /Readers don't block writers, writers don't block readers/.

So no, coming from that mindset, due to the quite different notions attributed to terms (see how the pig poop eating freaks destroy science), reading my posts, which may not deal with the issue directly, you will have some incorrect understanding.

Yes, I confirm, the only valid contention resource resolution mechanism in an OLTP database, is Ordinary Locking (science unchanged since the 1960’s, steadily advanced in the chip design sector). Which is not pessimistic, no matter how much you call it that. Now you guys call that “2PL” or strict “2PL”. (I don’t know how strict has anything to do with it. Concerning locks and security, it is binary: either you have it or you don’t.) So for me, 1PL or “2PL” is strict, we can’t strictify it any further.

I may have been lazy here in the past, by not rejecting the term “2PL”. From the little I do know, it is yet another mad construct on the academic-only collective imagination. It is not a good idea to place that label onto me, because the definition is too loose and unscientific, and I do somewhat more than that. Properly explained, I would not use stupid terms such as “expanding” and “shrinking”, no, they are two code blocks. In this farcical “definition”:
__ https://en.wikipedia.org/wiki/Two-phase_locking
half those categories do not exist, even describing them is hysterical.

Eg. in “C2PL”, the imbecile acquires locks “before” the transaction, without realising that the only way to acquire locks is “INSIDE* a transaction. Cesspool.

Eg. 1PL [to the OLTP Standard] eliminates deadlocks, that is one declared guarantee. These freaks call it “2PL” and state that deadlocks can happen, and elsewhere that it causes deadlocks.

Eg. I will bet you, you guys can’t even define a deadlock, let alone guarantee preventing it.

Ok, according to the latrine, Sybase; DB2; and MS are “SS2PL”, not “S2PL”.

And of course 1PL is much more, more than the total collective imagination of “SS2PL” is among the academics.

On that wiki page, the particular odour of fæces is, that of a female pig, a sow with full teats. All 12 piglets wrote that one page together, right between those warm teats. It is just a fanciful write-up of various narrow views of Ordinary Locking, which are self-serving. “Scientifically” defined Straw Men, the purpose of which is to *AVOID* understanding Ordinary Locking.

Actually, it is an attack on ACID, because any description of Transaction that are not ACID, is anti-ACID. They don’t even mention it, because the entire page; all descriptions, fail the [A]tomic requirement. And thus they can write 100 papers about how MVCC is sooooo much better than those Straw Men. And continue denying reality. Pffft.

The only valid papers on Locking were written by IBM and Britton-Lee/Sybase engineers. Stonebraker is a fantasist, a cult leader, not a scientific person by any measure. Kool Aid and mantras. Maybe some chakra balancing. Believe me, his downward dog never got up.

Yes, so all the high-end servers are Ordinary Locking or “1PL” or “SS2PL”, whether it contains a database or not. Separately, on the database and Transaction code side, we implement Optimistic Locking, for OLTP, not because the server is OL/1PL/SS2PL.

Even the label SS2PL is stupid. The piglet states that it is one phase, but hysterically goes on with the 2PL label.

For the sane, ACID requirements, as translated into SQL:
__ Locks are automatic, acquired after BEGIN TRAN, until COMMIT TRAN
__ Reads are locked for the duration of the Read operation
__ Writes are locked until COMMIT
__ Nothing can be locked outside a Transaction.

Cheers
Derek