Hi everyone,

There have been a few interesting discussions lately concerning MVCC.

Out of those discussions came some interesting conversation concerning the ideal approach for structuring a procedure in systems that use lock managers as their means of guaranteeing the Isolation in ACID.

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

Consistent with those principles, the ideal 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).

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

While checking again, this time we simultaneously acquire an exclusive lock.
In the code I'm sharing below, I use the 'with (updlock)' hint as described in the other discussions I refer to above.

---

My request:

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

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.

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

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.

Finally, I accept that there are a number of views on matters such as MVCC, its advocates, and so on.
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.

Cheers,
Daniel

---

A link to view the code on GitHub: https://gist.github.com/DanielLoth/6a8777dd978b8d00dbe5d6fa880fed59

---

The code:

create procedure dbo.AddAttendance_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as

set nocount on;

------------------------------------------------------------
-- Validation block
--
-- Determine whether or not a row already exists
-- for the key {OrganisationId, PersonId, AttendanceDate}.
------------------------------------------------------------
set transaction isolation level read committed;

declare @RowExists bit = 0;

select @RowExists = 1
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;

if (@RowExists = 1) return 0;

------------------------------------------------------------
-- Transaction block
--
-- At this point we know there's a chance that we will
-- succeed in inserting a row.
-- However we might conceivably be racing another session
-- that is calling this procedure for the same key
-- {OrganisationId, PersonId, AttendanceDate}.
--
-- Within a transaction:
-- 1. Re-execute the validation query, but this time acquire
-- an update lock while doing so (using hint 'with (updlock)').
-- 2. If the row is still absent, we can now safely insert
-- our new row.
-- This is due to the selection of an appropriate transaction
-- isolation level - in this case serializable - that
-- guarantees no other transaction can insert this row
-- while we hold our update lock.
-- With the isolation level set to serializable, the session
-- that we are running our query in (using hint 'with (updlock)'
-- has acquired a KEY lock from the lock manager.
-- This KEY lock ensures that no other transactions running
-- within other sessions can insert a row with this primary
-- key (the Isolation in ACID).
------------------------------------------------------------
set transaction isolation level serializable;

begin transaction;

select @RowExists = 1
from dbo.Attendance
with (updlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;

if (@RowExists = 0)
begin
insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
values (@OrganisationId, @PersonId, @AttendanceDate);

commit;
end
else
begin
rollback;
end

return 0;

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

> On Sunday, 27 June 2021 at 08:09:42 UTC+10, Derek Ignatius Asirvadem wrote:
> > On Friday, 25 June 2021 at 23:58:38 UTC+10, daniel wrote:

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

The point is this. You are thinking bottom-up. That is plain wrong in any kind of software development exercise. But you are forced into thinking bottom-up in the MV-non-CC context, it is consequent to that insanity. Give up the MV-non-CC insanity, and the bottom-up thinking that is demanded can then be released, so that you can start thinking as a human being, as a s/w developer, about reality. Which means top down; hierarchy; composition.

Thus Key Lock or Intent Lock does not matter, what matters is that YOU have observed the reality of the database (as distinct from hysterically pretending that the database and the hundreds of concurrent users does not exist, that the only thing that exists is your version), and YOU have locked the resources that YOU plan to rely upon, top-down.

Cheers
Derek

Hi Derek,

Thanks for responding.

---

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

I should clarify that I'm here in my own capacity, and not in my capacity as an employee.
Realistically, the only way I could obtain a thorough understanding of your methods as an individual is if they were published in a book.

The company I work for pursues agile software practices, emphasising ideals such as fast time-to-market, as do many of our competitors and just about any company with a Silicon Valley-like ethos.

I don't think the pursuit of agile software practices would be compatible with the conditions necessary to implement your ideal.
But I certainly think, in my capacity as an individual, that the knowledge that you've disclosed is valuable.

Having said that, I realise that 'Attendance' being the table might lead you to think 'Time and Attendance', a concept very reasonably associated with Xero's product offering.

Actually, in this case, attendance is a shooter being present at a recognised sporting shooters club on a given date. NSW AU legislation and regulations for shooting clubs mandate a register of attendees be kept. This is merely a list of people who attend club premises on a given day.

At present, this is not something that I've commercialised. I would be open to commercialising a club management application in the future, but honestly to date I haven't found the time.

---

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

Absolutely. That's why I was quite happy to put together the SQL to make the answer a 'Yes, this is broadly consistent' or 'No, this is not consistent' proposition.

---

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

Fair enough. I was a little dishonest anyway when couching the reasons for this request.
In practice I'm an early-thirties Aussie, so there's very little that's going to offend my sensibilities. I was more concerned with deterring others from participating in the discussion.

---

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

I'm afraid that can't be helped for my part. Microsoft added snapshot isolation years ago, but it is an opt-in feature.

I can say that resource utilisation is markedly different when MVCC is turned on. Specifically, MVCC results in a marked uptick in TempDB activity.

Can we work on the premise that when MVCC is not enabled in SQL Server then there is no penalty imposed?

---

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

Agreed. I've looked at your document about Open Architecture, and accept the assertion that encapsulating all transactions within the database as stored procedures is the only way to guarantee that all constraints hold true at all times.

In sharing the example code above I was hoping to keep this discussion quite focused.

I agree that the procedure initially provided would not even be called if another procedure, perhaps named 'Attendance_Get_tr', indicated that the attendance on a given date had already been recorded.

---

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

Hopefully the explanation above - that this is actually unrelated to 'Time and Attendance', but instead related to a requirement for sport shooting clubs to maintain a register of club attendees - changes your judgement concerning the key.

The full DDL for the Attendance table is:

create table dbo.Attendance (
OrganisationId int not null,
PersonId int not null,
AttendanceDate date not null,

constraint UC_Attendance_PK
primary key clustered (OrganisationId, PersonId, AttendanceDate),

constraint Person_Indicates_Attendance_FK
foreign key (OrganisationId, PersonId)
references dbo.Person (OrganisationId, PersonId)
);

---

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

Certainly. I'll share the additional DDL.

For ease of viewing, I've published all of the code below on GitHub too:
https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa

---

The full DDL for the Person table is:

create table dbo.Person (
OrganisationId int not null,
PersonId int not null,
GivenNames nvarchar(50) not null,
FamilyName nvarchar(50) not null,
CreatedDtm datetimeoffset(0) not null,
UpdatedDtm datetimeoffset(0) not null,

constraint UC_Person_PK
primary key clustered (OrganisationId, PersonId),

constraint Organisation_Hosts_Person_FK
foreign key (OrganisationId)
references dbo.Organisation (OrganisationId)
);

---

The full DDL for the Organisation table is:

create table dbo.Organisation (
OrganisationId int not null,
OrganisationName nvarchar(100) not null,
CreatedDtm datetimeoffset (0) not null,
UpdatedDtm datetimeoffset (0) not null,

constraint UC_Organisation_PK
primary key clustered (OrganisationId)
);

---

This personal project was actually designed in an effort to understand and incorporate your methodology. So you'll probably recognise a number of characteristics that you've touched on in various PDF documents shared on StackOverflow.

Now that I've shared these additional elements, does that change your feedback in any way?

---

Next, the stored procedures. This time I've included two: 'Attendance_Add_tr' and 'Attendance_Get_tr'.

---

create procedure dbo.Attendance_Get_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as

set nocount on;
set transaction isolation level read committed;

select OrganisationId, PersonId, AttendanceDate
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;

return 0;

---

create procedure dbo.Attendance_Add_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as

set nocount on;

------------------------------------------------------------
-- Validation block
------------------------------------------------------------
set transaction isolation level read committed;

if not exists (
select 1
from dbo.Person
where OrganisationId = @OrganisationId
and PersonId = @PersonId
) begin
return 8; -- Person does not exist
end

if exists (
select 1
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
) begin
return 7; -- Attendance on the given date has already been recorded.
end

------------------------------------------------------------
-- Execute block
------------------------------------------------------------
set transaction isolation level serializable;

begin transaction;

insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate
where not exists (
select 1
from dbo.Attendance
with (updlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
);

if @@ROWCOUNT <> 0
begin
commit;
end
else
begin
rollback;
end;

return 0;

---

Hopefully the code provided above fills in a few blanks and is closer to the mark.

Cheers,
Daniel

Daniel

> On Sunday, 27 June 2021 at 13:16:42 UTC+10, daniel wrote:

> Actually, in this case, attendance is a shooter being present at a recognised sporting shooters club on a given date. NSW AU legislation and regulations for shooting clubs mandate a register of attendees be kept. This is merely a list of people who attend club premises on a given day.

I am a shooter. NSW B grade. I resist A grade, you know where that goes.

> > 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.
> I'm afraid that can't be helped for my part. Microsoft added snapshot isolation years ago, but it is an opt-in feature.
>
> I can say that resource utilisation is markedly different when MVCC is turned on. Specifically, MVCC results in a marked uptick in TempDB activity.
>
> Can we work on the premise that when MVCC is not enabled in SQL Server then there is no penalty imposed?

Yeah, sure. Depends on whether you will benchmark this or not.
I was making the distinction:
1. Server level.
If MV-non-CC is implemented, a whole pile of resources need to be *ADDED*, allocated and configured. Hence the “uptick in tempdb usage”; CUP Usage; etc. It can’t be turned off, but it can be removed..

2. Connection level.
If [1] has been done, then within those limits, one can enable snapshot isolation.

> ---
> > 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).
> Agreed. I've looked at your document about Open Architecture, and accept the assertion that encapsulating all transactions within the database as stored procedures is the only way to guarantee that all constraints hold true at all times.

AND do not GRANT INSERT/UPDATE/DELETE to anyone
AND it provides the best processing speed; least contention;l best concurrency
(Yes, of course there is more, such as the db design.)

> I agree that the procedure initially provided would not even be called if another procedure, perhaps named 'Attendance_Get_tr', indicated that the attendance on a given date had already been recorded.

That is excellent. That is what the GUI would be calling (as an example). Even a simple SELECT, running as compiled and QT-ed code, runs faster that “dynamic SQL” in the client. Not to mention, the security hole is closed.

The Transaction suffixes are:
_tr - straight, self-contained transaction
_utr - utility [Modular Code block] called from a _tr
_btr - batch transaction (as detalied in the other two threads with Nicola)
- not a transaction

You can remove the suffix.

> ---
> > 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.
> Hopefully the explanation above - that this is actually unrelated to 'Time and Attendance', but instead related to a requirement for sport shooting clubs to maintain a register of club attendees - changes your judgement concerning the key.

The judgement cannot be changed, because the crime is real. The point is, we can overlook it for this purpose.

The NSW Firearm Licence requirement is 10 attendances per annum. The purpose is to ensure the licence and the guns are actually being used. The club has to record attendance and actual range practice (as opposed to signing the register, and having a yarn with the good old boys). We can skip that, and leave it as you have modelled.

> ---
> > 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 )
>
> Certainly. I'll share the additional DDL.

Great work.

> For ease of viewing, I've published all of the code below on GitHub too:
> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa

Since you are sharing it via a link to github, you can skip posting it here..

> ---
>
> This personal project was actually designed in an effort to understand and incorporate your methodology. So you'll probably recognise a number of characteristics that you've touched on in various PDF documents shared on StackOverflow.

Indeed. You are a good and faithful follower, you have picked up quite a lot.

> Now that I've shared these additional elements, does that change your feedback in any way?

Yes, great progress.

The thing is this. And both Nicola and you have this problem. Not because you are stupid, you are not, but due to the programming that passes for “education” that has been imposed on you. You have been trained to (a) think in terms of fragments, not integration, not atoms. And that means (b) bottom-up, instead of top-down. So you have noticed and copied things correctly, but the overall need, and the integration within the Atom, is not complete.

> ---
>
> create procedure dbo.Attendance_Get_tr
> @OrganisationId int,
> @PersonId int,
> @AttendanceDate date
> as
>
> set nocount on;
> set transaction isolation level read committed;
> select OrganisationId, PersonId, AttendanceDate
> from dbo.Attendance
> where OrganisationId = @OrganisationId
> and PersonId = @PersonId
> and AttendanceDate = @AttendanceDate;

IF @@ROWCOUNT != 1
____ RETURN 1 -- common not_exists
ELSE
___ RETURN 0

> ---
>
> create procedure dbo.Attendance_Add_tr
> @OrganisationId int,
> @PersonId int,
> @AttendanceDate date
> as
>
> set nocount on;
>
> ------------------------------------------------------------
> -- Validation block
> ------------------------------------------------------------
> set transaction isolation level read committed;

(No locks held.)

IF NOT EXISTS (
___ SELECT 1
_______ FROM Organisation
_______ WHERE OrganisationId = @OrganisationId
___ )
___ RETURN 9 -- Organisation does not exist

> select 1
> from dbo.Person
> where OrganisationId = @OrganisationId
> and PersonId = @PersonId
> )
> begin
> return 8; -- Person does not exist
> end
>
> if exists (
> select 1
> from dbo.Attendance
> where OrganisationId = @OrganisationId
> and PersonId = @PersonId
> and AttendanceDate = @AttendanceDate
> )
> begin
> return 7; -- Attendance on the given date has already been recorded.
> end
>
> ------------------------------------------------------------
> -- Execute block
> ------------------------------------------------------------
> set transaction isolation level serializable;

(Redundant, as explained, but harmless.)

> begin transaction;

The Validate block did not hold locks.
Now in the Execute block, you have to obtain Intent locks (HOLDLOCK), in the prescribed order, the hierarchy of Organisation; Person. This is the critical issue wrt eliminating various problems (that CAN be eliminated). Do:
__1 If not exists Organisation[ OrganisationId ] return 9
____ HOLDLOCK

__2 If not exists Person[ OrganisationId, PersonId ] return 8
____ HOLDLOCK

__3 If exists Attendance[ OrganisationId, PersonId, AttendanceDate ] return 7
____ HOLDLOCK

This is not a repeat of the Validate Block because you are now holding locks, on purpose. Forget Disneyland, we have a Lock Manager, we know its components, and we are engaging it, consciously.

The reason for this is, if there is a conflict with any other active Transaction, it will be blocked at the highest level in the tree, and NOT proceed further (eg. to the next statement). This is by design, desired. If you do not go AHA!, if you do not appreciate the brilliance of this, please contemplate, and then ask /specific/ questions.

Remember, dismiss the notion of “isolating the transaction” which is a consequence of the insane Stonebraker mantra. Love the world you are in. Embrace the Lock Manager, he is your friend. The Transaction is real, the rows are real, the changes are real, they cannot be isolated from reality. What we want to do here is deal with that, in the fastest way possible (minimum lock duration), and in the most sociable manner (least conflict, but if conflict encountered, block at the highest level, thereby holding the fewest locks, and affording the antagonist to do the same).

Daniel

In the /No CASCADE in Commercial SQL .../ thread,
> On Monday, 28 June 2021 at 11:26:56 UTC+10, Derek Ignatius Asirvadem wrote:
>
> -- Transaction Processing --
> -- Transaction • ACID Properties --
> -- Transaction • ACID • Consistency --
> -----------------------------------------------------
> -- Transaction • ACID • Isolation --
> -----------------------------------------------------
>
> //The intermediate state of a transaction is invisible to [or isolated from] other transactions.
> As a result, transactions that run concurrently appear to be serialized.//
>
> ----------
>
> If you understand this, you will understand that there is only one level of depth in Transactions, either one Transaction or all Transactions in a system. The notion of “nested transactions” is hysterically absurd.

Therefore, for the next increment of the OLTP/Transaction/ACID sproc template, as the first item in the Validate Block, add:

IF @@TRANCOUNT > 0 OR @@TRANCHAINED
___ -- if xact is opened by caller (this xact would be nested), or if every verb is SET to be an xact
___ RETURN 99

Of course, we use “user defined” error messages, and RAISERROR, so that the client side is alarmed and wakes up properly. And a fixed set of ReturnValues. No need for that here, in the simple template context, where ReturnValues as indicators are enough, but be sure to put that into your server, if you want the full OLTP/Transaction/ACID context. Our Error Messages for this area are as follows. %1 is the sproc name:

>>>> Sybase Transact-SQL, the original, convert to MS Transact-SQL
EXEC sp_addmessage 20003, '%1!: is a transaction, which is Atomic. It has been called from within an open transaction, which would render it a non-transaction. That is not allowed.', "us_english", FALSE, "REPLACE"

EXEC sp_addmessage 20004, '%1!: A transaction has been opened by the caller (but is declared NOT to be open).', "us_english", FALSE, "REPLACE"

EXEC sp_addmessage 20005, '%1!: A transaction has NOT been opened by the caller (but is declared to be open).', "us_english", FALSE, "REPLACE"

EXEC sp_addmessage 20006, '%1!: is an utility transaction, it must be called from within a open transaction.', "us_english", FALSE, "REPLACE"
<<<<

That last one is of course for any [_utr], which checks that @@TRANCOUNT = 1, and which has no BEGIN TRAN. [_utr] code blocks exist for the purpose of Normalising code, aka Code Modularisation, aka DRY. This is not relevant to your template, but in the full OLTP/Transaction/ACID context, it must be known, and handled properly.

Cheers
Derek

Hi Derek,

I've posted some updated code here incorporating your feedback: https://gist.github.com/DanielLoth/a272a601e38c9c6f2c002956b08d534e

Microsoft recommend using the 'throw' operation instead of the older 'raiserror'. So I've incorporated the notion of an error, but it's in its own function.
Transaction chaining - implicit transactions - is also a little different in MSSQL. So I've incorporated it as a 'set option' clause.

---

> The reason for this is, if there is a conflict with any other active Transaction, it will be blocked at the highest level in the tree, and NOT proceed further (eg. to the next statement). This is by design, desired. If you do not go AHA!, if you do not appreciate the brilliance of this, please contemplate, and then ask /specific/ questions.

It makes sense. It would remove the possibility of deadlock by ensuring that all locks are consistently acquired in the same order.

Also, even if for a moment there is contention (say, on dbo.Person in my example) it'll keep progressing immediately afterwards with a relatively good prospect of still succeeding.

Or if the circumstances have changed (e.g.: the transaction that blocked it actually removed the Person row), it'll quickly detect that the row is not there once unblocked, rollback, and then return control to the caller.

---

Question: What context are these two used in?

> EXEC sp_addmessage 20004, '%1!: A transaction has been opened by the caller (but is declared NOT to be open).', "us_english", FALSE, "REPLACE"
> EXEC sp_addmessage 20005, '%1!: A transaction has NOT been opened by the caller (but is declared to be open).', "us_english", FALSE, "REPLACE"

The other two made a lot of sense. But I'm not sure what you mean by 'declared to be'.

---

Cheers,
Daniel

Danno

> On Tuesday, 29 June 2021 at 22:59:49 UTC+10, Daniel Loth rote:
> Hi Derek,
>
> I've posted some updated code here incorporating your feedback: https://gist.github.com/DanielLoth/a272a601e38c9c6f2c002956b08d534e

Good work.

> Microsoft recommend using the 'throw' operation instead of the older 'raiserror'. So I've incorporated the notion of an error, but it's in its own function.

Horrible. MS has always had a special way of doing things, which is always more work; more code that can break; more things to go wrong; more things to administer. Have they taken away “user defined” error messages ? What happened to good old backward-compatibility.

> Transaction chaining - implicit transactions - is also a little different in MSSQL. So I've incorporated it as a 'set option' clause.

Perfect.

> ---
> > The reason for this is, if there is a conflict with any other active Transaction, it will be blocked at the highest level in the tree, and NOT proceed further (eg. to the next statement). This is by design, desired. If you do not go AHA!, if you do not appreciate the brilliance of this, please contemplate, and then ask /specific/ questions.

> It makes sense. It would remove the possibility of deadlock by ensuring that all locks are consistently acquired in the same order.

Yes. That is the only way to eliminate deadlocks. An overall Access Sequence, which has to be defined at the data modelling level. Here, it is a simple stepping through the hierarchy.

>>>>
That is at the code level.
80% of preventing deadlocks is in the data model. The /RM/ requires the dat to be arranged in trees (Directed Acyclic Graphs); no circular references.. But too many binary relations cause problems as well. Again, the need for an overall Access Sequence.
With the 1960’s Record Filing System that the pig poop eaters keep marketing and teaching as “relational”, and their forced circular references, the “database” ends up like a spiders web. You might be familiar with that. Guaranteed deadlocks.
<<<<

> Also, even if for a moment there is contention (say, on dbo.Person in my example) it'll keep progressing immediately afterwards with a relatively good prospect of still succeeding.

The prospects are always good, given the Validate block in both the client and the sproc.

Blocking is not the problem. Again, forget the hysterical mantra. In a restaurant with 50 seas and two toilets (washrooms to my American friends), we want the doors to have exclusive locks, not no-locks, not shared-locks. (I am not interested in evaluating marginal cases.) Blocks are the civilised way to avoid conflict, by letting one connection through and blocking the others.

The problem is not blocking (blocking works perfectly), but (a) connections that are blocked (blockers and blockees) while holding a large number of locks, and (b) block duration, wherein we want millisecs (whereas lock duration is microsecs). Two or three of those guys, and you have railway trains running on surface streets. Prevention is simply limiting transaction size, which means destroying the GUI designers notion of a fat and “clever” GUI. Some developers scream like stuck pigs when they hear that implicit transactions are banned, others have seizures when the myopic “persistence” is cancelled (what, no CRUD ?!??!). Again, more affected by the data model than the code.

Being blocked means a connection level context switch in the engine, which is the most expensive operation.

The behaviour you describe is intended.

You can elevate that code by set a maximum wait time for locks; rolling back; etc.

> Or if the circumstances have changed (e.g.: the transaction that blocked it actually removed the Person row), it'll quickly detect that the row is not there once unblocked, rollback, and then return control to the caller.

Exactly.

The behaviour you describe is intended.

There is more to that, which we will get into shortly.

I trust you appreciate, in serious subjects, learning has to be in increments.

> ---
>
> Question: What context are these two used in?
> > EXEC sp_addmessage 20004, '%1!: A transaction has been opened by the caller (but is declared NOT to be open).', "us_english", FALSE, "REPLACE"
> > EXEC sp_addmessage 20005, '%1!: A transaction has NOT been opened by the caller (but is declared to be open).', "us_english", FALSE, "REPLACE"
> The other two made a lot of sense. But I'm not sure what you mean by 'declared to be'.

Mistake, copy-paste error. I should have excluded that. You can ignore it..

But since it has been exposed, I will explain what I can without giving away the shop. Basically, I have a deeper level of Transaction control. If one implements such, it has to be immune to the nuances of Transaction control provided in the platform. Sometimes I have to work on a system in which the developers are hostile: they have implicit transactions and all manner of non-white filth, it will take six months to get them to replace it according to Standards. So the Transactions that my guys write have to operate in that mixed environment for a while, sometimes sabotaged by the calling code doing nasty things. My template catches all that, and these are the messages. The setting (declaration) is in connection memory, not in @variables or @@variables.

At the banks, they call it “hardening” the server, making it immune to mischief or hack. SG complies with various finance industry standards, all the way down to the code.

That is probably enough for a guy like you to roll your own.

Cheers
Derek

Danno

This covers generic SQL, the context of this thread.

Specific SQL (eg. your MS/SQL) is covered in your github thread.

Please take my comments as coming from a hard-core [nothing breaks] Rdb supplier, with rigid standards ... either existing standards plus our Extensions that are required to make them complete (eg. IDEF1X), or where there are no standards, our proprietary SG Standards.
__ Eg. the Open Architecture Standard has existed since Britton-Lee (inventor of the Client/Server Architecture), subsequently Sybase (published Client/Server Architecture), published it in the early 1980's, but it is not promoted; not well known, so we have maintained it and made it more precise. It has not changed since 1993.
__ Eg. the OLTP Standard (within the Open Architecture Standard) is pretty much SG alone, AFAIK. No doubt there are a few other high-end suppliers who have something similar, but it is not published. Much of it is considered proprietary.

We work in financial markets, thus we have additional Standards and Methods, again with SG Extensions (eg. Security & Auditing in the server), which are not included here, as this is a generic, not vertical-specific, exchange..

SQL
We work in Sybase, now SAP/Sybase, only, and deliver code in that flavour of SQL only ... but with a generic SQL intent, which means the older definitions of the SQL Standard, specifically dismissing that late additions because they are insane (promoted and imposed by the pig sucker brigade [Date; Darwen; Fagin; MV-non-CC groupies; etc] ). The developer staff at the target system are free to translate that delivery into their particular flavour of SQL.

Obviously that means commercial SQL Platforms, it excludes the pretend "SQLs"; the freeware; etc, and Oracle (it has no ACID, its declaration of SQL compliance is false, one if forced to use non-set processing [refer my posts re the Sybase vs Oracle benchmark] ).

Yes, we know MS/SQL very well (it is the bastard son of Sybase). No, we do not keep up with the changes.

Cheers
Derek

Danno

The high-end market have always had, and we have never left, Ordinary Locking.

The MV-non-CC market, that has never worked, but is heavily propagandised by the academics, (all of whom live in the asylum by insistent choice, safely removed from reality), has caused the high-end SQL Platform suppliers to **ADD ON** Snapshot Isolation. (Minus present company, of course.)

It is not a binary choice, because the freaky thing cannot work, thus it is an Add-On to the thing that does work.

/
Although that is a more honest label than the false "MVCC" because "MVCC" does not have any Concurrency Control, my label /Offline Version Support/ is more accurate still. I don't accept the notion of /Isolation/ because it is schizophrenic: Sybase and MS erect the /Isolation/ fantasy for the poor souls that can't handle reality of a shared database, at additional resource cost, because nothing is free, not even in the asylum ... but at COMMIT time, the fantasy has to be committed to reality ... at which point, oopsey doopsey poopsey that fantasy Offline Version gets thrown out for being too stale. Hint the real OLTP Transactions blew the doors off the snapshot while the developers were adoring their snapshot; the train left the station; the ship has sailed; the carriage changed back into a pumpkin.
/

The real problem, the core issue, is not MV-non-CC. It is the complete lack of understanding of Transactions; ACID Transactions; what the frog **ONLINE** means; what Online Transaction Processing means. Such is the state of academics, and has been for FIFTY YEARS. Such are the developers who code for MV-non-CC. Enabled by the pig poop eating academics and teachers. Empowered schizophrenia. Those developers, with their steadfast refusal to engage with reality, whose fantasy of safe isolation is their existential reality. That is the problem.

I think you understand some of that, which is why you are here. But not all of it.

If you have an interest in the MV-non-CC issues as it relates to Ordinary Locking, please read the /MVCC Advantages & Disadvantages thread/
__ https://groups.google.com/g/comp.databases.theory/c/f474bCuvZ_A/m/tPseucr6AQAJ

Cheers
Derek

Hi Derek,

Lots of stuff here. I probably won't be able to reply to all of it tonight.

---

> Horrible. MS has always had a special way of doing things, which is always more work; more code that can break; more things to go wrong; more things to administer. Have they taken away “user defined” error messages ? What happened to good old backward-compatibility.

Having given it some thought, I don't think there's any need for XACT_ABORT in the code I've written. I've explicitly handled rollback. So it's probably quite safe for me to use RAISERROR and add the messages to sys.messages as originally proposed. Something for tomorrow.

---

> Yes. That is the only way to eliminate deadlocks. An overall Access Sequence, which has to be defined at the data modelling level. Here, it is a simple stepping through the hierarchy.

I've actually put an image of my data model diagram in that GitHub gist comment section. It's a work-in-progress, but it's an indication of what I'm going for with it. So far I think it has reasonable hierarchy to it. There are no cycles or anything like that.

I can't say for sure if I've modelled the 'Location' stuff right, and I'm kinda unsure about the 'Participation' stuff too.

'Participation' in this context being a lawfully valid participation. In NSW, that would be one of: Officiated a match OR participated in an endorsed competition at another club OR firearms safety training OR target practice.

Also, the keys - I'm not sure how correct they are at this point.

So yeah, work in progress only. Not finished, probably not entirely correct either in terms of what I've already got there.

The vision, in a nutshell, is club management software with support for:
1. Scheduling / Timetabling (events on ranges, and the participants)
2. Competition planning and scoring (squadding, range duties, etc)
3. Membership management
4. Compliance reporting based on an arbitrary Reporting Year (Financial Year / Calendar Year / Club Reporting Year)
5. General reporting (e.g.: in support of forward planning, the number of matches shot in a Quarter might be a proxy for how many new targets and target patches are required for next Quarter).

And further to that, I'd like the compliance elements expressed as rows of data rather than elements of the schema (i.e., I don't want to have columns specific to AU-NSW and AU-SA).

So far I've found it relatively tricky to model it in a way that it's all nicely integrated.

---

> But since it has been exposed, I will explain what I can without giving away the shop. Basically, I have a deeper level of Transaction control. If one implements such, it has to be immune to the nuances of Transaction control provided in the platform. Sometimes I have to work on a system in which the developers are hostile: they have implicit transactions and all manner of non-white filth, it will take six months to get them to replace it according to Standards. So the Transactions that my guys write have to operate in that mixed environment for a while, sometimes sabotaged by the calling code doing nasty things. My template catches all that, and these are the messages. The setting (declaration) is in connection memory, not in @variables or @@variables.

> At the banks, they call it “hardening” the server, making it immune to mischief or hack. SG complies with various finance industry standards, all the way down to the code.

That makes sense now. I hadn't considered hostility or saboteurs when looking at them before.

---

> The real problem, the core issue, is not MV-non-CC. It is the complete lack of understanding of Transactions; ACID Transactions; what the frog **ONLINE** means; what Online Transaction Processing means. Such is the state of academics, and has been for FIFTY YEARS. Such are the developers who code for MV-non-CC. Enabled by the pig poop eating academics and teachers. Empowered schizophrenia. Those developers, with their steadfast refusal to engage with reality, whose fantasy of safe isolation is their existential reality. That is the problem.

I agree that there's a dearth of good content out there. For example, I'm not aware of any book that adequately covers. A lot of the mainstream books wind up going down the same path: Poor practice, little to no consideration of methodical design, and ultimately yielding a so-called database with little to no integrity.

MVCC is perceived as an advantage because it allows tools like ORMs to get away with murder (e.g.: deleting 50,000 rows, or aggregating 200,000 rows in the context of a browser's HTTP request, or just about anything else that a developer tests with just 10 rows of data locally before shipping it to production).

If development practice were markedly better then I think people would recognise it for all of its disadvantages and reconsider their decision to use it.

---

Regarding Sybase: I've never worked with it personally. If I'm honest, I find SQL Server pretty good because the associated tooling is pretty good. For example, I'm pretty fond of DacFx and DACPACs and the declarative approach that they afford you when working with databases. Declarative in the sense that you tell it you want your table to have these columns / constraints / keys / indexes, and it figures out what it needs to do to bring a target database into alignment with it.

---

Regarding the Open Architecture standard: I'll have to look up the original sometime. I've only really looked at your PDF (this one: http://www.softwaregems.com.au/Documents/Article/Application%20Architecture/Open%20Architecture.pdf)

---

Cheers,
Daniel

On 2021-06-27, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> For ease of viewing, I've published all of the code below on GitHub too:
>> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa

Thanks. That's my attempt at porting to PostgreSQL:

https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9

I see that you have continued your discussion on Github, but I haven't
kept up.

> __3 If exists Attendance[ OrganisationId, PersonId, AttendanceDate ] return 7
> ____ HOLDLOCK

Why does this need holdlock, too? Locks on Organisation and Person have
already been acquired at this point:

if exists (
select 1
from dbo.Attendance
with (holdlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
)
begin
rollback;
return 7; -- Attendance on the given date has already been recorded.
end

> Nicola, note how that Batch Xact fits here. It needs to lock only the
> highest row in the hierarchy, and that will prevent any other OLTP
> Xact from getting into the tree.

I have to review our previous discussion. I'll come back to you if
I need futher clarifications.

Certainly, with these new threads I better understand your criticisms.

Nicola

> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
> > On 2021-06-27, Derek Ignatius Asirvadem wrote:

> >> For ease of viewing, I've published all of the code below on GitHub too:
> >> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa

> Thanks. That's my attempt at porting to PostgreSQL:
>
> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9

Great. I can see a lot a value in such a port, no argument, but the purpose is unclear. If I knew the purpose, my responses would be more specific.

> I see that you have continued your discussion on Github, but I haven't
> kept up.

Well, re this thread & subject, I think we are keeping the discussion here, the code (for you and Daniel) there. There is a separate discussion re Dan’s DM there (the interface is better.)

Dan, if it is ok with you, please do the same.

Nicola, no time now to look at your GitHub entry, I will get back to you with comments/questions. Comments on the code only there, comments on structure and requirement here.

> > __3 If exists Attendance[ OrganisationId, PersonId, AttendanceDate ] return 7
> > ____ HOLDLOCK
>
> Why does this need holdlock, too? Locks on Organisation and Person have
> already been acquired at this point:
> if exists (
> select 1
> from dbo.Attendance
> with (holdlock)
> where OrganisationId = @OrganisationId
> and PersonId = @PersonId
> and AttendanceDate = @AttendanceDate
> )
> begin
> rollback;
> return 7; -- Attendance on the given date has already been recorded.
> end

In chronological order.
- In Sybase at IL[3], it is an automatic HoldLock (hold page until end of Xact)
--- therefore if the Attendance row exists, the lock on the page is held until end of Xact
- MS/SQL was stolen from Sybase at V4.9.2, but MS have rewritten the codeline several times, so while they are identical to a great degree, especially re the syntax, but at this level of detail they may not be. So when discussing with Dan, to be clear, I indicated to him that we want a HOLDLOCK in MS equivalent terms:. It is purely documentary. That is for Organisation; Person; and Attendance, after the BEGIN TRAN.

Put another way, in order to obtain a HOLDLOCK, one has to have Isolation Level 3, which means one has to have a Transaction open.

And now, because you have ported the code, it is an indicator to you, to do whatever is equivalent in your program suite.

__ https://help.sap.com/viewer/e0d4539d39c34f52ae9ef822c2060077/16.0.4.0/en-US/ab3e9a97bc2b10149c02ff1c52f92cb4.html

> Certainly, with these new threads I better understand your criticisms.

Good. Please appreciate, when I give a course to developers, it is a fixed 3-day affair, with a full set of lecture notes, max six people. Sometimes 6 x half-days. For mucho lira. Of course that is a full course on OLTP, before we get to Sybase, before they can understand and use the SG OLTP Standard & sp template. I can’t do that here, I am answering questions only. So it is gearing you up in Transactions & OLTP, the hard way.

It is not “criticism” [of MV-non-CC]. It is a tutorial to drag academics and developers out of the MV-non-CC/PusGres mindset, into the Transaction Processing that we have had since the 1960’s (pre-Relational) and the 1980’s (Relational platforms).

Eg. if you cannot see that Ordinary Locking use no additional resources (to the existing Data Storage & Cache), whereas MV-non-CC uses:
- masses of extra storage at every level [versions of rows], spread across the entire scope of data storage ... Dan has indicated further detail
- note the horrendous pressure on indices, especially active ones
- all of which has to be processed [sometimes re-read] at COMMIT time
--- which means re-reading many times for many COMMITS
- masses of additional memory in the cache: to whatever level the pos caches pages (as I understand it, these are duplicated across the run-time threads, but that is a separate issue consequent to not having an architecture), it has to use more cache
- obviously, for all that, far more processing power is consumed
- *AND*
- has to clean all dirty storage up
--- Vacuum
--- Shrink/Expand
--- Broom
--- Witch
--- etc
I have failed.

Erecting a fantasy (da stale versions “held” privately) is massively expensive. Maintaining a fantasy that is massively expensive, is even more expensive.

Whether it is done properly (added resources configured for version storage, on top of an OLTP server with Ordinary Locking & full ACID), or mickey mouse style (freeware with fantasy only, later with 2PL added, later still wit Predicate Locks added; etc), erecting a fantasy is stupid, and massively expensive. This is where the Stonefreaker cult has lead you; all academics; 10,000 developers for PoopGres; and all developers who use MV-nonCC program suites. Never mind reality, let’s all sing the Stonefreak mantra, because it feels good.

Cheers
Derek

> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
>
> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9

Discussion, not code.

Nicola, would you like differences between SQL and the PoopGres syntax discussed here, or on GitHub ?

----------

> -- From https://www.postgresql.org/docs/current/xproc.html:
> -- A procedure can commit or roll back transactions during its execution (then
> -- automatically beginning a new transaction),

So that is not a Transaction. That states that the freaky thing is always in a "transaction" mode of its own definition. This tells me that nothing has changed since V10. Functions (oopsey, the now sort-of stored procedure thingees) are "twunsackshunal". START TRANSACTION still does Fanny Adams.

And now, security xor "transactional-ness" is a binary choice.

God help me.

Now I understand better, much earlier in the thread, when you said somethings, wherein you assumed even reads were "transactional". That is really dangerous.

Do not, under any circumstances, think that that is SQL. No, it is PSuckDeadBearsGres only.

Cheers
Derek

On 2021-07-03, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
>> > On 2021-06-27, Derek Ignatius Asirvadem wrote:
>
>> >> For ease of viewing, I've published all of the code below on GitHub too:
>> >> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
>
>> Thanks. That's my attempt at porting to PostgreSQL:
>>
>> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
>
> Great. I can see a lot a value in such a port, no argument, but the
> purpose is unclear. If I knew the purpose, my responses would be more
> specific.

For now, it's just to see to what extent PostgreSQL supports that kind
of coding. Stored procedures were added recently, so that is just my
exploration of the feature.

Whether it makes sense to code that on in a similar way is still unclear
to me. It's a one page procedure performing several lookups just to
insert a record, a step which in PostgreSQL would conflict only with
a similar insert—and the duration of the conflict would be short. Also,
one purpose of your template is to avoid deadlocks, which in MVCC cannot
happen anyway. In fact, adding explicit locking might introduce the
possibility of deadlocks.

>> Why does this need holdlock, too? Locks on Organisation and Person have
>> already been acquired at this point:
>> if exists (
>> select 1
>> from dbo.Attendance
>> with (holdlock)
>> where OrganisationId = @OrganisationId
>> and PersonId = @PersonId
>> and AttendanceDate = @AttendanceDate
>> )
>> begin
>> rollback;
>> return 7; -- Attendance on the given date has already been recorded.
>> end
>
> In chronological order.
> - In Sybase at IL[3], it is an automatic HoldLock (hold page until end of Xact)
> --- therefore if the Attendance row exists, the lock on the page is held until end of Xact
> - MS/SQL was stolen from Sybase at V4.9.2, but MS have rewritten the
> codeline several times, so while they are identical to a great degree,
> especially re the syntax, but at this level of detail they may not be.
> So when discussing with Dan, to be clear, I indicated to him that we
> want a HOLDLOCK in MS equivalent terms:. It is purely documentary.
> That is for Organisation; Person; and Attendance, after the BEGIN
> TRAN.
>
> Put another way, in order to obtain a HOLDLOCK, one has to have
> Isolation Level 3, which means one has to have a Transaction open.

Ok.

> And now, because you have ported the code, it is an indicator to you,
> to do whatever is equivalent in your program suite.
>
> __
> https://help.sap.com/viewer/e0d4539d39c34f52ae9ef822c2060077/16.0.4.0/en-US/ab3e9a97bc2b10149c02ff1c52f92cb4.html

Thanks.

>> Certainly, with these new threads I better understand your criticisms.
>
> It is not “criticism” [of MV-non-CC]. It is a tutorial to drag
> academics and developers out of the MV-non-CC/PusGres mindset, into
> the Transaction Processing that we have had since the 1960’s
> (pre-Relational) and the 1980’s (Relational platforms).

First, the "inconvenient truth"—to put it as in some recent paper
[0]—i.e., the issues you and Daniel mention about MVCC, is well known
also in academia. That is not a reason to abandon the technique, rather
a push towards further research to improve it or to find contexts in
which it might provide some advantage—which may not be the conventional
disk-based storage model which you seem to assume.

Second, the only point I wanted to make when we first started talking
about MVCC is that it is possible to obtain serializable schedules with
(an extension of) that protocol, which you did (do) not want to accept.

Then, if you want to argue that in the wider "ACID/OLTP context" that
does not matter because the performance sucks, long-running transactions
work with stale data, perfectly reasonable schedules can never happen
under MVCC, isolation is excessive, etc., then we have little to argue
about yet because I agree with you and Daniel on all those points.

Nicola

[0] Rethink the Scan in MVCC Databases, SIGMMOD'21
https://dl.acm.org/doi/pdf/10.1145/3448016.3452783

On 2021-07-03, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
>>
>> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
>
> Discussion, not code.
>
> Nicola, would you like differences between SQL and the PoopGres syntax
> discussed here, or on GitHub ?

I'd rather keep the discussion here.

> ----------
>
>> -- From https://www.postgresql.org/docs/current/xproc.html:
>> -- A procedure can commit or roll back transactions during its execution (then
>> -- automatically beginning a new transaction),
>
> So that is not a Transaction. That states that the freaky thing is
> always in a "transaction" mode of its own definition. This tells me
> that nothing has changed since V10. Functions (oopsey, the now
> sort-of stored procedure thingees) are "twunsackshunal". START
> TRANSACTION still does Fanny Adams.

Can you elaborate on that? If I do:

start transaction;
-- Some stuff
commit;

select ...; -- (1)

insert ...; -- (2)

start transaction;
-- Some stuff
commit;

don't (1) and (2) constitute each a transaction?

> And now, security xor "transactional-ness" is a binary choice.

I don't understand what this means.

Nicola

> On Saturday, 3 July 2021 at 20:28:55 UTC+10, Nicola wrote:
> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
> >> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
> >
> >> That's my attempt at porting to PostgreSQL:
> >>
> >> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
> >
> > Great. I can see a lot a value in such a port, no argument, but the
> > purpose is unclear. If I knew the purpose, my responses would be more
> > specific.
>
> For now, it's just to see to what extent PostgreSQL supports that kind
> of coding. Stored procedures were added recently, so that is just my
> exploration of the feature.
>
> Whether it makes sense to code that on in a similar way is still unclear
> to me. It's a one page procedure performing several lookups just to
> insert a record, a step which in PostgreSQL would conflict only with
> a similar insert—and the duration of the conflict would be short.

Sure, that is “true for you” in the fantasy world of MV-non-CC. Remember, it has no Concurrency Control. Every verb is a false “COMMIT”. I can’t see any sense in just coding it, without trying anything. Hence, /what is the purpose ?/

OTOH, if you want to see what it is like to operate in reality, the world of a single shared resource, which means Controlling Concurrency (and Currency), you have to accept the mechanism of a Lock Manager (or produce something new). Then yes, you have to code lock commands, same as everyone else who has to elevate their code in PissGriss after suffering horrendous problems with vanilla MV-non-CC.

> In fact, adding explicit locking might introduce the
> possibility of deadlocks.

Yes, because you are forced to stop thinking like a 5-year-old girl, and to start thinking like a 7-year-old boy.
Yes, because all deadlocks are created; written by the developers (hint: there are no deadlocks in the distribution media).
Yes, because you have to be responsible (as opposed to the Disneyland MV-non-CC groupies, who are NOT responsible, which is the premise of the Mental Health Act ... I do not use terms such as /schizophrenic/ lightly).

> which in MVCC cannot
> happen anyway.

It is your code, you have to choose:
__ either meaningless code that uses MV-non-CC and is Anti-Transaction Processing; Anti-ACID, Anti-SQL;
__ or code attempting to reach the first rung of Transaction Processing (we know it cannot get to even the third rung), and because MV-non-CC is a golden farce, and because they have added a “2PL” lock mangler as well as a Predicate Lock mangler, to go beyond the farce, you *do* need to code locking commands.

Which one cannot do on an SQL Platform, it is stupid and dangerous, and therefore not permitted.

> Also,
> one purpose of your template is to avoid deadlocks, which in MVCC cannot
> happen anyway.

Bringing that back to me.
Yes, it is one purpose, but very secondary. The purpose is:

1. To maintain Currency of data (one version of the truth, the latest)
__ (FYI. You have dived deep into this one, and we have closed many holes, but it is not complete, there are two holes that are well-known by us since 1965, that have not been identified by you or closed by me providing the solution.)

2. To allow efficient sharing of data (that one version of the truth)
__ (FYI. There is some appreciation, but not a proper understanding, you do not have the method yet.)

3. To determine conflicts at the earliest point, and to block at the earliest point.
__ thereby holding the least locks, and blocking for the smallest duration.

4. Obviously, that means some sequence in each Transaction.
__ The more Xacts that use *A* sequence, the better. That progresses to collections of Xacts that use *A* particular Sequence. Deadlocks are prevented within each Sequence.
__ If there is a single overall Sequence, deadlocks are prevented across the system.

See the problem, working from the bottom, up. That is why proper education (eg. our 3-day course) is top-down: name all the problems; then demonstrate the solution to all. Here we have worked bottom-up (no problem, that is the result of answering questions), we have some understanding of the method, which may give false confidence, and we still have not even identified all the holes; what the method does. It is incomplete but you all are ignorant that it is incomplete.

Thus, in addition to
a. clarifying the purpose of your code and progressing it,
b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
__ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.

__ And of course the solution.

> > It is not “criticism” [of MV-non-CC]. It is a tutorial to drag
> > academics and developers out of the MV-non-CC/PusGres mindset, into
> > the Transaction Processing that we have had since the 1960’s
> > (pre-Relational) and the 1980’s (Relational platforms).
>
> First, the "inconvenient truth"—to put it as in some recent paper
> [0]—i.e., the issues you and Daniel mention about MVCC, is well known
> also in academia.

At what date ?
If it was post-1965, it is decades too late, and proves my position.

> issues

No, just one issue.
The other issues all remain unknown to academics.

> That is not a reason to abandon the technique, rather
> a push towards further research to improve it or to find contexts in
> which it might provide some advantage—which may not be the conventional
> disk-based storage model which you seem to assume.

I love it.
Ingres never worked but Oracle will
Oracle never worked but PonGres will
Pongres.x never worked but PonGres.x+1 will
Pongres.x+1 never worked but PonGres.x+2 will
Pongres.x+2 never worked but PonGres.x+3 will
Pongres.x+99 never worked but PonGres.x+100 will
And now, ta da, it might work on the fourth moon of Jupiter, where they have no persistent storage.

You can’t make this stuff up.

> Second, the only point I wanted to make when we first started talking
> about MVCC is that it is possible to obtain serializable schedules with
> (an extension of) that protocol, which you did (do) not want to accept.

It is not that I do not accept it, it is that it is totally irrelevant. I am saying it may well be “true for you” in the mathematical sense, and there may well be 30 good papers on it, but it is total bunkum, because the problem is not a problem that is generic to MV-non-CC systems or to OLTP, it is specific to the MV-non-CC flavour of PoopGres.

1. We don’t not have “serialisation” problems, therefore we do not need a “serialisation schedule” to fix the “serialisation” problems that we do not have.

2. Sure, all Disneyland fantasy groupies, defined as such because they have MV-non-CC, have *A* problem with resolving their morass of the multiple versions across the entire scope of data storage, at COMMIT time (as opposed to “serialisation” problems). That is, at the point where fantasy has to be reconciled with reality, there is a problem. All MV-non-CC program suites *except PoopGross* have solved the problem without reference to “serialisation” problems, decades before academics created their “serialisation” problems.

3. Now for PoopGres, which is driven by academics. Instead of understanding that IL[3 SERIALIZABLE] means Transactions *appear to be* serialised, which means internal non-server methods of resolution, which [2] have, the idiots take it that Transactions must be serialised. Stupidity on top of fantasy. Then they are so involved in their own “superiority”, which is the hallmark of their total ignorance of reality; of the requirement for internal non-server resolution, that they come up with “serialisation schedules”, and even a “taxonomy of serialisation problems”. All of which make the fantasy more “real”, and all of which have no bearing on reality. But it does produce the usual raft of papers, and citations, and work for the idle.

It just provides evidence for what I have stated severally, that academics are clueless about (a) reality, (b) Transaction Processing, (c) the problems to be addressed in Transaction Processing, and (d) instead have a contrived notion of [a][b][c], and (e) have wonderful Alice-in-Wonderland notions about their notions, fantasy upon fantasy. Of course, they need that to confirm their fantasy world, because it is fragile, and needs constant validation.

Meanwhile, back at the farm [1], that actually produces something relevant to reality, we do not have [2] or [3], or the “solution” for [3].

It is like you are professing that drug xyz cures syphilis, and marketing it to us, who do not go to brothels, and thus cannot contract it, and thus have no need for it.

> Then, if you want to argue that in the wider "ACID/OLTP context" that
> does not matter because the performance sucks, long-running transactions
> work with stale data, perfectly reasonable schedules can never happen
> under MVCC, isolation is excessive, etc., then we have little to argue
> about yet because I agree with you and Daniel on all those points.

Ok. Welcome to the club, I hope you enjoy your stay.

In order to gain experience with a real SQL platform, why don’t you install MS/SQL or Sybase. Both provide free versions. Your students can walk into a job due to having experience with actual SQL platforms, instead of ancient anti-experience on fantasy program suites. Why maintain unscientific loyalty to this known-to-be-broken, and known to be non-SQL, and known-to-be pathetically lacking 1980’s Platform features, let alone modern platform features, for decades.

> On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
> >
> >> -- From https://www.postgresql.org/docs/current/xproc.html:
> >> -- A procedure can commit or roll back transactions during its execution (then
> >> -- automatically beginning a new transaction),
> >
> > So that is not a Transaction. That states that the freaky thing is
> > always in a "transaction" mode of its own definition. This tells me
> > that nothing has changed since V10. Functions (oopsey, the now
> > sort-of stored procedure thingees) are "twunsackshunal". START
> > TRANSACTION still does Fanny Adams.
>
> Can you elaborate on that? If I do:

Sure.
Before I do ...

> start transaction;
> -- Some stuff
> commit;
>
> select ...; -- (1)
>
> insert ...; -- (2)
>
> start transaction;
> -- Some stuff
> commit;
>
> don't (1) and (2) constitute each a transaction?

What definition of "transaction" are you using ?

Cheers
Derek

On 2021-07-04, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
>> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
>> >
>> >> -- From https://www.postgresql.org/docs/current/xproc.html:
>> >> -- A procedure can commit or roll back transactions during its execution (then
>> >> -- automatically beginning a new transaction),
>> >
>> > So that is not a Transaction. That states that the freaky thing is
>> > always in a "transaction" mode of its own definition. This tells me
>> > that nothing has changed since V10. Functions (oopsey, the now
>> > sort-of stored procedure thingees) are "twunsackshunal". START
>> > TRANSACTION still does Fanny Adams.
>>
>> Can you elaborate on that? If I do:
>
> Sure.
> Before I do ...
>
>> start transaction;
>> -- Some stuff
>> commit;
>>
>> select ...; -- (1)
>>
>> insert ...; -- (2)
>>
>> start transaction;
>> -- Some stuff
>> commit;
>>
>> don't (1) and (2) constitute each a transaction?
>
> What definition of "transaction" are you using ?

A logical unit of processing that is executed atomically (either all the
operations are carried out till the end or the database is not affected
in any way), does not violate the integrity constraints, does not
interfere with other transactions (in a way that produces results that
are incompatible with any serial execution of the same transactions),
and whose results are guaranteed to be persistent, unless the
transaction fails for some reason.

Syntactically, anything between "start transaction" and "commit". Single
commands are run as if enclosed between "start transaction"/"commit".

If I understand correctly, by "transaction" you mean what is executed by
the whole stored procedure, which according to your sketched template
consists of several "transactions" in the sense above.

Nicola

> On Monday, 5 July 2021 at 02:02:33 UTC+10, Nicola wrote:
> > On 2021-07-04, Derek Ignatius Asirvadem wrote:
> >> On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
> >> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
> >> >
> >> >> -- From https://www.postgresql.org/docs/current/xproc.html:
> >> >> -- A procedure can commit or roll back transactions during its execution (then
> >> >> -- automatically beginning a new transaction),
> >> >
> >> > So that is not a Transaction. That states that the freaky thing is
> >> > always in a "transaction" mode of its own definition. This tells me
> >> > that nothing has changed since V10. Functions (oopsey, the now
> >> > sort-of stored procedure thingees) are "twunsackshunal". START
> >> > TRANSACTION still does Fanny Adams.
> >>
> >> Can you elaborate on that? If I do:
> >
> > Sure.
> > Before I do ...
> >
> >> start transaction;
> >> -- Some stuff
> >> commit;
> >>
> >> select ...; -- (1)
> >>
> >> insert ...; -- (2)
> >>
> >> start transaction;
> >> -- Some stuff
> >> commit;
> >>
> >> don't (1) and (2) constitute each a transaction?
> >
> > What definition of "transaction" are you using ?
>
> A logical unit of processing that is executed atomically (either all the
> operations are carried out till the end or the database is not affected
> in any way), does not violate the integrity constraints, does not
> interfere with other transactions (in a way that produces results that
> are incompatible with any serial execution of the same transactions),
> and whose results are guaranteed to be persistent, unless the
> transaction fails for some reason.
>
> Syntactically, anything between "start transaction" and "commit". Single
> commands are run as if enclosed between "start transaction"/"commit".

All this time (two years) that we have been discussing these things, the context has been:
__ database, specifically one that complies with Codd's /Relational Model/;
__ SQL, specifically the Standard
__ ACID Transactions, which were available in the very first SQL Platforms, and identified as a requirement in the very first publication of SQL (because ACID Transactions existed in pre-Relational DBMS Platforms). We have specifically discussed ACID, which is a particular /implementation/ of Transaction, that is required for SQL compliance.

Note that the first flavour of SQL that was available to the public was, and still is, "Transact-SQL".

Therefore I am at a total loss as to how you come up with that gobbledegook, oink oink oink, as a "definition" of "transaction", eg. you do not reference ACID.

Words have meaning, they are used to communicate effectively. At least for people who have a language (that excludes the Chinese (all sub-races); all tribal people; etc). I thought you were Italian, which is derived from Latin, a beloved language of mine, and from the same root (Indo-European group of languages) as that which we are using, English.

Definitions of words (the meaning) do not change. (If it changes it is not a definition.)

If you attempt to change a definition, that has been established sine 1965, you need a kind of help that I am not qualified to provide.

Please identify which definition (established; within the context we are in; not personal) of "transaction" you are using. And please stick to it (do not switch to another definition). So that our conversation can progress.

(If you wish to discuss the insanity that non-SQLs and "emerging non-database systems" have for "transactions", you really should open a separate thread, and declare the context. In this thread, it is ANSI/ISO/IEEEE SQL, and ACID only.)

Yes, I am quite aware that your pretend-SQL has no Transactions. Yes, I understand that the academics (this time including you) seek to confuse the definition of various things in SQL, such that their filth can be twisted into being perceived as the SQL things. Sorry, I will not participate in that. Further, I thought you wished to leave that asylum, that you were crossing the chasm, and reaching for the real world; reality; definitions that do not change; implementation. No, you can't go back to the asylum. No, you can't hold the asylum mindset in the real world.

> If I understand correctly, by "transaction" you mean what is executed by
> the whole stored procedure, which according to your sketched template
> consists of several "transactions" in the sense above.

I did not say anything, I just gave SQL Verbs. I did not define or redefine anything re "transaction", the notion of harbouring private definitions in a science, that has definitions, is too freaky for me. Please do not put words in my mouth. If you interpret something I wrote, please be responsible and own that interpretation.

I will not respond to that para, until you have indicated what definition for "transaction" you are using.

Cheers
Derek

> On Monday, 5 July 2021 at 08:01:02 UTC+10, Derek Ignatius Asirvadem wrote:

> the context has been:
> __ database, specifically one that complies with Codd's /Relational Model/;
> __ SQL, specifically the Standard
> __ ACID Transactions,

> In this thread, it is ANSI/ISO/IEEEE SQL, and ACID only.

Further, note that Daniel started this thread, and the context [further to the above] is SQL platforms using a Lock Manager (not MVCC), and how to construct Transactions therein, properly.

I am happy to entertain discussion about non-SQL fraudulently proposed as "SQL" to a degree, and non-Transaction in program suites that do not have Transactions, to a degree. But I will not stand for anyone imposing novel definitions, because that introduces confusion (which we, at least Dan and I, specifically reject), and it will sabotage the discussion.

Rather than answering this and the previous post in argumentative terms, re the low level issues, you may choose to rollback to the point where we were **NOT** confused, and move forward from that point.

Cheers
Derek

> On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
>
> > And now, security xor "transactional-ness" is a binary choice.
>
> I don't understand what this means.

Whatever is meant by this:
> -- «A SECURITY DEFINER procedure cannot execute transaction control statements
> -- (for example, COMMIT and ROLLBACK […])»

and this:
> 118 security invoker -- “security definer” is not compatible with commit/rollback :(

Since the “development team” is spread across the galaxy (geographic as well as intellectual), in order to make any progress at all, each actual team develops one feature, in isolation from the rest of the codeline, and then anti-integrates it into the codeline, thus typically, in every progressive release of the program suite, the **use** of one feature destroys the use of some other feature. Or two. Common problem in freeware. Non-existent in commercial SQL Platforms.

Add to that, the ever-changing notion of; and implementation of, Transactions; functions; stored procs; security; etc, and you have mickey mouse squared; insanity squared.

Thus “security” implementation breaks the "transactional-ness" implementation, and the "transactional-ness" implementation breaks the “security” implementation, thus it is a binary choice.

Cheers
Derek

Nicola

------------------------
-- Checkpoint --
------------------------

This thread has been hijacked (I am not saying that you did it on purpose, but as evidenced, this is the insanity that is academia; endless distractions; redefinition of standard termss; etc). I am resetting it, so that its initial purpose can be rendered, for implementation purposes, by normal people. My apologies for allowing it to get hijacked in the first place.

The title of this thread, started by Dan, for a particular purpose, is:
__ Stored procedure structure in RDBMS using Lock Manager for transaction isolation
It is not complete, we must not get distracted or hijacked or confused.

I accept that you do not submit to the Four Laws of Thought, and thus what you teach and practice is not science, but Modern “science”.. I am rigid scientist. You need to accept that Dan and I submit to the Four Laws, that he is seeking a finite (not never-ending) discussion with a view to forming an implementation Standard for SQL/ACID Transactions in an OLTP environment (reduced or eliminated contention).

__________

> On Monday, 5 July 2021 at 08:23:54 UTC+10, Derek Ignatius Asirvadem wrote:
> > On Monday, 5 July 2021 at 08:01:02 UTC+10, Derek Ignatius Asirvadem wrote:
>
> > the context has been:
> > __ database, specifically one that complies with Codd's /Relational Model/;
> > __ SQL, specifically the Standard
> > __ ACID Transactions,
> > In this thread, it is ANSI/ISO/IEEEE SQL, and ACID only.
>
> Further, note that Daniel started this thread, and the context [further to the above] is SQL platforms using a Lock Manager (not MVCC), and how to construct Transactions therein, properly.
>
> I am happy to entertain discussion about non-SQL fraudulently proposed as "SQL" to a degree, and non-Transaction in program suites that do not have Transactions, to a degree. But I will not stand for anyone imposing novel definitions, because that introduces confusion (which we, at least Dan and I, specifically reject), and it will sabotage the discussion.
>
> Rather than answering this and the previous post in argumentative terms, re the low level issues, you may choose to rollback to the point where we were **NOT** confused, and move forward from that point.

Retracted.

Note, all your posts in this thread, following this:
>>>> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
have nothing to do with this thread, and should have been posted under the relevant thread:
__ MVCC, Advantages & Disadvantages
or a perhaps new thread, such as:
__ How can I implement OLTP/SQL/ACID in my non-OLTP, non-SQL, non-ACID, non-Concurrency Controlled, pitiful suite of programs that keep changing.

This thread is for Dan's initial stated purpose only. And anyone who has similar desires. Or who wishes to genuinely understand it for what it is, not one who imposes redefinitions from the asylum.

Dan, in order to preserve sanity, such that we can progress, and close this thread, please ignore such posts.

> > Please identify which definition (established; within the context we are in; not personal) of "transaction" you are using. And please stick to it (do not switch to another definition). So that our conversation can progress.

I retract that request. The definitions that apply for sanity in general, and for this thread in particular, are those that have been established, since 1965:
__ OLTP (Online shared resource)
____ OLTP/ACID Transaction (pre-Relational)
__ SQL (ANSI/ISO/IEE SQL only)
____ SQL/OLTP (commercial SQL Platforms only)
____ SQL/ACID Transaction (ACID in the Relational context)

Please keep redefinition of standard terms, and any questions about such, in the relevant thread.

> On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
>
> That's my attempt at porting to PostgreSQL:
>
> https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9

Noting that which I stated previously, if and when you propose a purpose for that, I will respond.

Now I state further, that submission is irrelevant to this thread.

If and when you write code in your pretend-SQL non-Transaction, non-Concurrency Controlled, suite of programs, that is a genuine attempt to implement whatever portions of a Transaction that you can implement in it, it might be a relevant submission. And in any case, please post that in the relevant thread:
__ MVCC, Advantages & Disadvantages

This thread is strictly OLTP/SQL/ACID/Transactions using a Lock Manager (not Snapshot Isolation, because Snapshot Isolation cannot provide ACID).

The deeper problem is, the schizophrenic notion that the database is not shared resource (sorry, you are not the only user; it is not single-user); the database is not online (sorry, it is online, it keeps changing); and thus you can grab and hold a version (sorry, it is stale from the moment you grab it). Yes, of course, all that should be ventilated, in order for you to join the rest of us in the real world, but not here, in the relevant thread, please.

> > Nicola, note how that Batch Xact fits here. It needs to lock only the
> > highest row in the hierarchy, and that will prevent any other OLTP
> > Xact from getting into the tree.
>
> I have to review our previous discussion. I'll come back to you if
> I need futher clarifications.

Heeded. Awaited. Please choose carefully, whether such discussion should be here in this thread, or in the subject thread:
__ No CASCADE in Commercial SQL & Benchmark Considerations

---------------------------------------------
-- To Progress This Thread --
---------------------------------------------

> On Sunday, 4 July 2021 at 20:41:52 UTC+10, Derek Ignatius Asirvadem wrote:
>
> Thus, in addition to
> a. clarifying the purpose of your code and progressing it,
> b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
> __ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.
>
> __ And of course [c] the solution.

Now, for this thread, [a] is cancelled. Please place such discussions in the relevant thread.

Please keep [c] in mind, as the goal, and proceed with [b] in order to progress in that direction.

I repeat, the context for this thread, as per initial declaration, is:
__ OLTP (shared online database, upon which Transactions are processed)
____ implies a Lock Manager to (a) determine, and (b) resolve, Concurrency issues
____ SQL (standard-compliant, which means commercial only, and excludes Oracle)
______ ACID Transactions

Cheers
Derek

> On Monday, 5 July 2021 at 11:05:19 UTC+10, Derek Ignatius Asirvadem wrote:
>
> ---------------------------------------------
> -- To Progress This Thread --
> ---------------------------------------------
> > On Sunday, 4 July 2021 at 20:41:52 UTC+10, Derek Ignatius Asirvadem wrote:
> >
> > Thus, in addition to
> > a. clarifying the purpose of your code and progressing it,
> > b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
> > __ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.
> >
> > __ And of course [c] the solution.
>
> Now, for this thread, [a] is cancelled. Please place such discussions in the relevant thread.
>
> Please keep [c] in mind, as the goal, and proceed with [b] in order to progress in that direction.

These issues relate to Transactions, as we have had since 1965 (pre-Relational DBMS) and since 1984 (Relational DBMS), and which concepts and definitions cannot change. That was established entirely by the DBMS vendors, without any help from academia (with the exception of Codd in the post-Relational context).

The problem is, of course, the filth of academia imposing itself on reality, fifty years after these things were established. And the typical disgusting dishonesty, such as redefinition of established terms, usually without even the courtesy of declaring it. While insisting that they should not be concerned with implementation issues, they have contradicted themselves and implemented suite of programs for Anti-OLTP; Anti-SQL; Anti-ACID Transactions; and No Concurrency Control, fraudulently labelled "MVCC", using 10,000 enslaved young minds located across the planet.

These issues have been discussed here on c.d.t, with Nicola, the one academic who is attempting to cross the great chasm between their steadfast isolation from reality, and reality. For two years. Although I welcome it, and provide detailed answers, it has moved in fits and starts, and while progress is made, no clear directives are obtained.

In contrast, Dan has started this thread, which has a clear and finite goal [c], as per the title.

In order to:
__ clear up the confusion that has ensued,
__ define the whole context, in a consolidated manner
__ ie, identify all elements, and place them in the proper context,
__ maintain Sanity in the face of encroaching insanity, such that we can progress, hopefully to closure,
I have produced a document (cut-paste what I can, from lecture slides) which you may find helpful.

It shows:
__ the elements that we have discussed and closed,
____ which are fully articulated in Dan's GitHub thread
____ let's call that Chapter One
__ as well as the two elements [b] that need to be identified (redacted, but shown in proper position)
____ meaning that Chapter One is not complete
__ such that I can provide the Solution for the entire set of issues [c]; for the entire Chapter One
____ Let's call [c] Chapter Two

__ https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Transaction%20Sanity.pdf

If anything is not crystal clear, please ask a question.

As you may notice, it covers our Chapter One, as progressed thus far, it is waiting for the completion, and then Chapter Two.

Cheers
Derek

Nicola

Now that I have dismissed the dishonesty of redefined terms, and restated the boundaries for this thread:
__ OLTP (the outer-most context, and beyond what can be expected from SQL or ACID)
____ which had and has ACID Transactions
____ which is required any kind of Online Transaction Processing in an Online Database
__ SQL (ANSI/ISO/IEEE SQL, not the freeware filth that is presented as “SQL”)
____ which has ACID Transactions, and thus specific requirements for compliance
____ and therefore SQL syntax for it

.... I can answer your question, otherwise the answer would have been laborious.

> On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
> > On 2021-07-03, Derek Ignatius Asirvadem wrote:
> >
> >> -- From https://www.postgresql.org/docs/current/xproc.html:
> >> -- A procedure can commit or roll back transactions during its execution (then
> >> -- automatically beginning a new transaction),
> >
> > So that is not a Transaction. That states that the freaky thing is
> > always in a "transaction" mode of its own definition. This tells me
> > that nothing has changed since V10. Functions (oopsey, the now
> > sort-of stored procedure thingees) are "twunsackshunal". START
> > TRANSACTION still does Fanny Adams.
>
> Can you elaborate on that?

PoopDiPoopGres totally, utterly, abysmally, does not comply with SQL/ACID. (We know from previous discussions that it is not SQL compliant in any way shape or form, we know from the Syntax that it is not SQL, now we know that it is not ACID compliant in any way; shape; or form.)

You guys have the most ridiculous notions of /Transaction/, and of /ACID/.

“automatically beginning a new transaction” means that Transactions are *NOT* started by START TRAN and ended by COMMIT/ROLLBACK TRAN, as required for SQL compliance.

“automatically beginning a new transaction” means it has some sort of private “transaction” mode, that it is always “in”, contrary to the SQL requirement.

Therefore, START TRAN, which is required to start a Transaction, and switch from whatever Isolation Level the connection is in, to IL[3 SERIALIZABLE], as required for SQL compliance, does not happen.

Further, it has no Concurrency Control (the [CC} in “MVCC” is a bald-faced lie). Therefore it has no basis upon which to suggest that it can, let alone does, provide TRANSACTION ISOLATION of any kind. Hint: every user is “isolated” simply because they hold a stale, offline version of the online data row. Note the difference between ISOLATED and “isolated” in this para.

Therefore:
> > START TRANSACTION, which has SQL compliance requirements, still does sweet Fanny Adams.

What is worse, all the academics, and all the developers on this hysterical MV-non-CC suite of programs that change with every version, are totally clueless re OLTP requirements; Transactions; SQL; and SQL/ACID Transactions. But they have hundreds of pages of academic “literature” that tells that them that they do, and that their hysterical ever-changing notions are “better”.

Again, in the context of this thread ...

> If I do:
>
> start transaction; [T0]
> -- Some stuff
> commit;
>
> select ...; -- (1)
>
> insert ...; -- (2)
>
> start transaction; [T3]
> -- Some stuff
> commit;
>
> don't (1) and (2) constitute each a transaction?

No.

This is more about simple Logic, than about ACID Transaction definitions.

This is the same insanity as in the other thread, where you proposed that “business transactions” are made up of multiple “database transaction”. No, there is only one type of Transaction in a SQL/ACID Platform. The problem is the same: you break the Atomicity (the [A] in ACID), and worse, you do not even realise it.

Here, you additionally fail to understand the Whole vs the Parts, which is also a common error that you make. You think that a Part is equal to (has the power and characteristics of) the Whole. You break the Atomicity that I have posted about many times.

If [T0} and [T3] are (a) Transactions, and (b) Atomic, how in heavens name can single verbs [1][2] also be (a) Transactions, and (b) Atomic ???

How can a single Part be the same as a constructed Whole (that contains Parts) ???

No. [1][2] are single SQL Verbs, they do not “constitute” a Transaction, let alone an SQL/ACID Transaction.

They are your “primitives”, as per the /Transaction/ thread, and yes, they should not be allowed. Easily prevented.

If and when a Transaction is constructed, sure, it contains single SQL verbs, and in that case only, the word /constituent/ can be used, because it is truly constituted. “Constituted” is not something that you can otherwise use as a label or property.

> On Monday, 5 July 2021 at 02:02:33 UTC+10, Nicola wrote:
>
> Syntactically, anything between "start transaction" and "commit".

Well, if you understand that, then you should understand that [1][2] are *NOT* SQL Transactions. They are *NOT* constituted by bracketing with START TRAN and COMMIT TRAN. You are contradicting yourself violently.

> Single commands are run as if enclosed between "start transaction"/"commit".

I would not put it that way, but sure, that is some implementation, an attempt at writing a bunch of programs that emulate some degree of SQL Transactions. If you focus on the “as if”, then you should understand that they are *NOT* Transactions, because “as if” and /actually/ are mutually exclusive processes.

> If I understand correctly, by "transaction" you mean what is executed by
> the whole stored procedure, which according to your sketched template
> consists of several "transactions" in the sense above.

Definitely not.

I do not have private definitions for anything in this science, the definitions have existed since 1965; 1984 for RDBMS; SQL-89 for SQL Compliance, and remain unchanged.

Can you not tell the difference between the occurrence of /Transaction/ in the type /Transaction Stored Proc/, which differentiates it from other types of stored procs, and the occurrence of /Transaction/ in START TRANSACTION ? In that case, you would not be able to appreciate the Transaction Stored Proc Template, and the value of its constituent Parts.

Are you getting confused with the naming <TransactionName>_tr ? Do you honestly think that because a stored proc is so named, everything inside it is a Transaction, which must mean that you deny the Transaction in it ???

No. The Transaction in the Transaction Stored Proc Template begins at the single START TRAN, and ends at the single COMMIT/ROLLBACK TRAN. The rest of the Transaction Stored Proc Template (ie. outside the START::COMMIT bracket) constitutes the Parts that are required for Online Transaction Processing. The Transaction Stored Proc Template gives the frame; the Form, that is required for ACID Transactions in an OLTP context. Which is beyond SQL/ACID, and must be executed by the app, which calls a method in the Database API, which is a Transaction Stored Proc.

I cannot see how the Transaction Stored Proc Template can be construed to be a Transaction, or even the contained Transaction.

I can offer, that it is the academic, insane /always in some “transaction” mode/ mindset, that screws up your thinking.

Please, in this thread, maintain the established terms and definitions.

Cheers
Derek