From Newsgroup: comp.databases.theory

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;

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCLthinking aheadrCY. 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCo ACID Properties --
-- Transaction rCo ACID rCo Consistency -- -----------------------------------------------------
-- Transaction rCo ACID rCo 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 rCLnested transactionsrCY 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 rCLuser definedrCY 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCLuser definedrCY 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 1960rCOs Record Filing System that the pig poop eaters keep marketing and teaching as rCLrelationalrCY, and their forced circular references, the rCLdatabaserCY 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 rCLcleverrCY GUI. Some developers scream like stuck pigs when they hear that implicit transactions are banned, others have seizures when the myopic rCLpersistencerCY 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 rCLhardeningrCY 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCLuser definedrCY 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 rCLhardeningrCY 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 DanrCOs 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 canrCOt do that here, I am answering questions only. So it is gearing you up in Transactions & OLTP, the hard way.
It is not rCLcriticismrCY [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 1960rCOs (pre-Relational) and the 1980rCOs (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 rCLheldrCY 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, letrCOs all sing the Stonefreak mantra, because it feels good.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 insertrCoand 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 rCLcriticismrCY [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 1960rCOs (pre-Relational) and the 1980rCOs (Relational platforms).

First, the "inconvenient truth"rCoto put it as in some recent paper
[0]rCoi.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 advantagerCowhich 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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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:

-- -2A SECURITY DEFINER procedure cannot execute transaction control statements
-- (for example, COMMIT and ROLLBACK [rCa])-+

and this:

118 security invoker -- rCLsecurity definerrCY is not compatible with commit/rollback :(

Since the rCLdevelopment teamrCY 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 rCLsecurityrCY implementation breaks the "transactional-ness" implementation, and the "transactional-ness" implementation breaks the rCLsecurityrCY implementation, thus it is a binary choice.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2