> On Friday, 25 June 2021 at 23:58:38 UTC+10, daniel wrote:
>
> My request

Just a bit of context. This is a “theory-only” forum that is dominated by “theoreticians”. None of who have progressed the science since Codd, 1970. For many years, I fought an ongoing battle, nay, a war, and they would argue like freaks, hair-splitting and other dishonest methods (anti-science). Over time, they have all fallen by the wayside, and thankfully the insane arguments have stopped.

Nicola is the single academic who is crossing the Great Dividing Range, the chasm between the contrived world of academia, and the real world. I stand for Codd; the /Relational Model/; science; theory+practice (not theory alone; not practice alone). Point is, I have 10 years history with Nicola, and a willingness to answer his questions, to help him cross that chasm.

What you are trying to do is excellent, to come up with a sp template for OLTP Transactions. Of course we have that, it has not changed since 1993, and it is commercial. We do not sell it stand-alone, it is given only with education, to SQL Developers. Usually heavy OO/ORM/OOP types who have already written filth that locks itself silly, or suffers data integrity problems (more visible in MV-non-CC systems), and who are now [after I rewrite their database, producing V2] forced to comply with OLTP Standards, that we supply, so that they produce V2 sps.

Therefore, the best engagement is for Xero to engage us, and obtain the full complement in the usual way. As you may be aware, per Australian law, what we sell to customers, cannot be provided free to others (it would make the sale price fraudulent).

Therefore, please understand, I was answering questions for an academic (who cannot use it commercially), way more than I am obliged to, with the focus on understanding the real Ordinary Locking system (instead of the Straw Man description that the Stonebraker cult declare it to be) vs the real MV-non-CC (instead of the hysterical myth that the cult declare it to be). The goal here is objective truth, not any opinion. Science, not politics. Academia is stuck in politics (market and promote 1960’s theory) and mythology, devoid of science. The market is stuck in a different set of imperatives (Chinese mentality: no standards; fast delivery; use imbeciles; cheep cheep). Academia in turn write for that ignorant market. Both sides are FORTY YEARS behind the technology.; the actual platforms.

Please understand, I can answer questions; I can argue the science (as distinct from the filth that passes for “science”), but I can’t give away the shop.

With that intro and caveat in mind ...

> Finally, I accept that there are a number of views on matters such as MVCC, its advocates, and so on.

Politics.

Science does not need advocates, science is not opinion, science is not consensus or citations. I stand for pre-Modern Science. Science is knowledge; certainty. The Four Laws of Thought, Causality; Composition.
__ https://www.softwaregems.com.au/Documents/Defence%20of%20the%20Church/Modernism/A%20Aristotle%20A_1.pdf
__ https://www.softwaregems.com.au/Documents/Defence%20of%20the%20Church/Modernism/G%20Glossary%20p161.pdf

> But it'd be great if the discussion remained clean and free of vitriol. If for no other reason than keeping it safe for viewing at work.

This forum existed before you were born. It is still one of the few fora that is not moderated; not censored: free speech rules. It is not open for a newcomer to dictate terms. The concept of a “safe space” to be safely insane is not provided here.

If statements on a forum, whether it be science or “science” or opinion, scares you, then by that very fact, you cannot practice science.

> It was written to run in a database configured such that 'snapshot isolation' is disabled and 'read committed snapshot isolation' is disabled.

That means the entire set of resources, that are required to host MV-non-CC (“snapshot isolation”) on the database, have bee ADDED. That is the massive resources to host the MV part, because the Ordinary Locking that pre-existed in the server, that provides the CC part, continues to be used. But this connection has disabled its participation in the massive additional MV resources.

> That is to say that this code has been written for use with a RDBMS that relies on a Lock Manager to guarantee isolation of transactions, not MVCC.

Not just “guarantee isolation of transactions”, which is one requirement of ACID, but the server provides all four requirements for ACID. But but but ACID is not merely a server-side concept, there are requirements on the client side, which is what is being played out here, explained here.

It does not consist of walking up to the db and inserting one row correctly.. The whole system has to be ACID, ie. comply with ACID requirements. At best that means the Open Architecture/OLTP Standard. Unchanged from the 1960’s (truth does not change). At worst it means complying with ACID partiallyand no OLTP. (Not complying with ACID deems the system outside this class of discussion).

> I've written such a stored procedure, and am hoping for feedback as to how closely it aligns with that description.

Ok. You have done a good job of reading some of my answers, and figuring out what the sp template should be, at least for a start. The problem is, you have picked up fragments, not the whole (which is understandable if you understand my intro).

You said RDBMS. That means it has to be Relational. The “keys” are hopeless, non-Relational, but let’s overlook that.

Make the following changes and re-submit:
1. Attendance is dependent on Person, and Person is dependent on organisation.
__ Create table Organisation, PK ( OrganisationId )
__ Create table Person, PK ( OrganisationId, PersonId )

> insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)

2. It is an error, to attempt insertion of an Attendance row, for a Person that does not exist.

3. It is an error, to attempt insertion of an Attendance row, for a Organisation that does not exist.

> The code I share below is designed to run on SQL Server. I've tested it on SQL Server 2016, but I've kept the example simple and it should run on most versions.

IOW, it is a bit too simple to expose the issues that are relevant, and thus a possible solution.

> Finally, I accept that there are a number of views on matters such as MVCC, its advocates, and so on.

Yes. A mountain of filth that never gets resolved.

=========
> -- At this point we know there's a chance that we will
> -- succeed in inserting a row.

You know no such thing.
Knowledge is certainty, not chance; not probability.
A chance cannot be known, something known is not chance.

If and when you:
__ ensure that Organisation[ OrganisationId ] exists,
__ and Person[ OrganisationId, PersonId ] exists,
__ and Attendance[ OrganisationId, PersonId, AttendanceDate ] does not exist
then, and only then, there is a /good/ chance that the insert will succeed.

> -- Within a transaction:
> -- 1. Re-execute the validation query, but this time acquire

“Re-execute” and “again” are false.
When you get to contemplating the client side, there will be a VALIDATION structure. That to is not a “Re-execute” or “again” or a “triplicate”. If your car has been impounded, you phone the various pounds first, to find out which pound it is held in. You do not go to all the pounds. You must not attempt something that will fail. The only way to ensure that an attempt [within an Xact] will not fail, is to check first, outside the Xact. Therefore they are both demanded, one is not a duplicate; not an “again”; not a “re-“ of the other. When you go to the one pound that has the car, it is not “re-going” to the pound.

> -- Within a transaction:
> -- 1. Re-execute the validation query, but this time acquire
> -- an update lock while doing so

Further, the [“this time” do something else that you did not do the first time], proves that the two blocks are not “duplicate”; “again”; “Re-”.

> -- This KEY lock ensures that no other transactions running

Whether it is a Key lock; or Update Lock; or Intent Lock, is a platform-specific thing (other platforms will do the same thing, but have different locks, or different names for similar locks). In the Sybase & MS case, yes, a Key Lock is distinctly more efficient, more “thinking ahead”. So for our purpose, understanding and writing code for an ACID RDBMS, do not concern yourself too much about the platform-specific type of lock, do concern yourself THAT it is locked and that it is some form of Exclusive Lock.

Generically, it is an Intent lock, that you have found, and after the insert it is an Exclusive lock. Platform-specifically, it means SQL Server is ready and waiting to insert another row with that or similar Key, hence Key lock.

In Sybase it does yet another clever thing.

> -- This is due to the selection of an appropriate transaction
> -- isolation level - in this case serializable

No. After BEGIN TRAN, your ISOLATION LEVEL is set to SERIALIZABLE, the only one that is “suitable” for Transactions. You have no choice, there is no “selection”.

I will clarify the ACID definition in another post.

> guaranteeing the Isolation in ACID.

Again, that is the MV-non-CC mindset. Get rid of it. “Guaranteeing isolation” is not relevant, it is an idiotic concept, and only relevant for an idiot that first thinks he has a version of the database. Take that first idiocy away (it is a shared database, with just One Version of the Truth, One Version of Any Row), and the need for the second idiocy disappears. Stop thinking about a version of something that is removed from the reality of its existence (insanity), and start thinking about the reality of its existence (sanity).

Transaction Isolation is provided by an ACID compliant server, yes. It does not need a guarantee, and you should not rely on such a guarantee, you should rely on Transaction Isolation.

> -- Validation block

Change the SELECT to:

IF EXISTS (
____SELECT 1
________FROM dbo.Attendance
________WHERE OrganisationId = @OrganisationId
____________AND PersonId = @PersonId
____________AND AttendanceDate = @AttendanceDate;
____) RETURN 0

Further, a proper Error Message (“user defined”) should be raised. If you do not use RAISERROR and such messages, then you must have at least a pre-ordained set of RETURN_VALUES for all stroed procs (and Functions) that indicate success/failure to the caller. For this demonstration purpose, let’s use these RETURN_VALUES:
0 = Succeeded
7 = Row exists (that should not exist)
8 = Row does not exist (that should exist)
9 = Other failure

Therefore, in the SELECT above:
____) RETURN 7

> -- Transaction block

It is named the Execute block. The whole sp is the Transaction. And should be named as such Attendance_Add_tr.

Cheers
Derek