The following thread is relevant to this one. It provides a discussion in an ACID Transaction context, which MVCC does not have, and cannot do (MVCC is Anti-ACID; MVCC is Anti-Transaction).
__ https://groups.google.com/g/comp.databases.theory/c/LSoYp9wrv0M

Cheers
Derek

Nicola

In the /Stored Proc for OLTP Transactions/ thread ...

> On Saturday, 3 July 2021 at 20:28:55 UTC+10, Nicola wrote:
>
> 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.

> in MVCC [deadlocks] cannot happen anyway

Separate to the "serialisation failures", which are the rough equivalent to deadlocks in a SQL Platform (with a Lock Manager), if deadlocks cannot happen, what is this, that people are experiencing problems with in PusGross, that they are calling deadlocks ?
__ https://dba.stackexchange.com/q/281846/64114
__ https://dba.stackexchange.com/q/151813/64114

Cheers
Derek

Derek,
long post this time. Please skim through it entirely before commenting.

On 2021-08-23, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
> Nicola
>
> In the /Stored Proc for OLTP Transactions/ thread ...
>
>> On Saturday, 3 July 2021 at 20:28:55 UTC+10, Nicola wrote:
>>
>> 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.
>
>> in MVCC [deadlocks] cannot happen anyway
>
> Separate to the "serialisation failures", which are the rough
> equivalent to deadlocks in a SQL Platform (with a Lock Manager), if
> deadlocks cannot happen, what is this, that people are experiencing
> problems with in PusGross, that they are calling deadlocks ?
> __ https://dba.stackexchange.com/q/281846/64114
> __ https://dba.stackexchange.com/q/151813/64114

Ok, I stand corrected, in two ways. First:

>and the duration of the conflict would be short

Strictly speaking, that is inaccurate, because it does not mention that
in case of write-write conflicts, one of the two transactions must be
rolled back.

Second, yes, deadlocks are possible, but they only involve write
operations (read statements do not acquire locks).

Example 1 (assume SERIALIZABLE):

create table account (name varchar(5) primary key, balance int not null);
insert into account(name,balance) values ('Tom', 40), ('Sam', 50);

Wall time| T1 | T2
---------|-------------------------------|------------------------------
(0) | begin transaction; |
| |
(1) | | begin transaction;
| |
(2) | update account |
| set balance = balance - 10 |
| where name = 'Sam'; |
(3) | | update account
| | set balance = balance + 20
| | where name = 'Tom';
(4) | update account |
| set balance = balance - 10 |
| where name = 'Tom'; |
| -- T1 is put on hold |
(5) | | update account
| | set balance = balance + 20
| | where name = 'Sam';
-- ERROR: deadlock detected

With row-level locking, or if the two records were on different pages,
Sybase would behave the same. But when read statements are involved,
things may run differently.

> if deadlocks cannot happen, what is this, that people are experiencing

Ok, they are deadlocks, as above. Could also be "serialization errors",
discussed below.

Example 2 (assume SERIALIZABLE):

Wall time T1 T2
---------|-------------------------------|------------------------------
(0) | begin transaction; |
| |
(1) | | begin transaction;
| |
(2) | select * |
| from account |
| where name = 'Tom'; |
(3) | | update account
| | set balance = balance + 20
| | where name = 'Tom';
(4) | update account |
| set balance = balance - 10 |
| where name = 'Tom'; |
| |
(5) | | commit;
| |
(6) | commit; |

In Sybase: at time (1), T1 locks the page containing the record, so that
at time (3) T2's request for a write lock is put into a queue and T2 is
put to wait. Sybase records T2->T1 (T2 depends on T1 to release a lock).
At time (4), T1's request for a write-lock is also denied, pending T2's
request (w/w conflict). Sybase records T1->T2 (T1 depends on T2 to
release a lock). The concurrency control system detects a cycle in the
dependency graph, and rolls back a transaction, which happens to be T1.
T2 can now commit. Note that without the select statement at time (2)
there would be no deadlock: the update at (4) would only be delayed
until T2 commits (or rolls back).

Do you agree with the above description of events?

In PostgreSQL: each transaction operates on its own private snapshot of
the database (that incurs a overhead, which I am not discussing for
simplicity). In the sequence above, that would result in a lost update
(namely, T2's update), unless some mechanism were in place to prevent
that (and there is). AFAICT, PostgreSQL implements a "first-updater
wins" rule: if a transaction attempts an update on an item that has been
updated by a concurrent transaction, if the latter has already
committed, the former immediately aborts; if the latter is still active,
the former is put on hold. This rule may be implemented with locks or in
other ways. I think that PostgreSQL uses a mechanism different from
locks for that, but that is not relevant in this context.

Anyway, in Example 2 it is not correct to talk about deadlocks in
PostgreSQL, because the read statements do not acquire any locks, so
it's not possible for the two transactions to be waiting upon each
other. The update at time (3) proceeds normally (such update is local to
T2). At time (4), though, T1 must be put on hold, because, according to
the rule above, another transaction has concurrently updated the same
record and is stil active. When T2 commits, PostgreSQL realises that T1
cannot sensibly continue (because that would result in a lost update),
and kills it. Now, T2 can commit.

Some remarks (recall that I am assuming SERIALIZABLE):

1. The outcome, in this specific example, is the same (Tom's balance
becomes 60), but the underlying mechanisms to achieve that outcome are
profoundly different. In general, the outcome will be different as
well (read further).

2. Can there be a concurrent interleaving of operations that leads to
a deadlock in Sybase, but the same concurrent interleaving does not
yield a deadlock or a serialization error in PostgreSQL? No, because
a concurrent interleaving of operations leading to a deadlock is an
incorrect interleaving. So, if Sybase "encounters a deadlock
situation", then PostgreSQL must also produce an error in that same
situation, otherwise it would output an incorrect result. The
difference is that PostgreSQL may output a "serialization error",
rather than a deadlock error.

> Separate to the "serialisation failures", which are the rough
> equivalent to deadlocks in a SQL Platform (with a Lock Manager)

Ok, accepted.

3. Does the difference matter to the DBA? Probably not: what matters in
practice is that a transaction was rolled back and must be retried.

4. Is there a concurrent interleaving of operations that makes PostgreSQL
return a "serializable error", but the same interleaving succeeds in
Sybase? Yes, because MVCC reduces the number of acceptable
interleaving of operations. For instance, remove the select statement
from T1 (but keep the same timing for the other operations): then in
Sybase both T1 and T2 will commit, but PostgreSQL will still kill T1
as soon as T2 commits (as per the above rule).

5. Does this difference matter to the DBA? Probably yes: under the same
workload, PostgreSQL might rollback more transactions. According to
the posts you cite, that appears to be the case (note that I am not
discussing other important details, such as updating indexes, or the
overhead of versions—I am keeping it simple). The more write-heavy the
concurrent workload, the more PostgreSQL is penalized compared to
a locking-based system. This seems what Uber has discovered the hard
way.

6. I am perfectly aware that you would not write T1 the way I have done,
and that you would avoid deadlocks. That's not the point of this post.
T1 is written that way for explanatory purposes only.

So, PostgreSQL might have an edge, if any, in prevalently read-only
workflows, because reads do not acquire locks. But you would perform
reads in Sybase at READ COMMITTED, where locks are held for a very short
time (it's still an overhead, though). So, it's not clear to me who the
winner would be. How do you justify preferring Sybase (locking) over
PostgreSQL (MVCC) in a context of almost exclusively read-only
transactions? Note that if transactions are read-only, versions are not
created.

Nicola

> On Tuesday, 24 August 2021 at 03:24:58 UTC+10, Nicola wrote:
> > On 2021-08-23, Derek Ignatius Asirvadem wrote:
> > > On Saturday, 3 July 2021 at 20:28:55 UTC+10, Nicola wrote:

> long post this time. Please skim through it entirely before commenting.

Sure. With the same implied intent, I will give my responses in a ordered sequence, different from yours, in order to make sense, and avoid jumping back-and-forth. That does not imply that your order of points is incorrect.

I will give short responses where it is appropriate to maintain flow, with full explanations at some chosen point. Hopefully that will allow you to make short responses as you see fit.

> > In the /Stored Proc for OLTP Transactions/ thread ...
> >
> >> 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.
> >
> >> in MVCC [deadlocks] cannot happen anyway
> >
> > Separate to the "serialisation failures", which are the rough
> > equivalent to deadlocks in a SQL Platform (with a Lock Manager), if
> > deadlocks cannot happen, what is this, that people are experiencing
> > problems with in PusGross, that they are calling deadlocks ?
> > __ https://dba.stackexchange.com/q/281846/64114
> > __ https://dba.stackexchange.com/q/151813/64114
>
> Ok, I stand corrected, in two ways. First:
>
> >and the duration of the conflict would be short
>
> Strictly speaking, that is inaccurate, because it does not mention that
> in case of write-write conflicts, one of the two transactions must be
> rolled back.

Accepted.
But there is more. SQL Platforms do not rollback in many situations that PoopDePooGres (and Oracle) poops itself and rolls back.

> Second, yes, deadlocks are possible, but they only involve write
> operations (read statements do not acquire locks).

Accepted.
But the qualifier is irrelevant (as you have implied, whatever one or the other does is internal, and therefore irrelevant, but the result of submitting the same SQL [non-SQL for PoopGres] should be; is expected to be, the same).

Until we get to your summary at the end, wherein the internal operation become relevant.

> 6. I am perfectly aware that you would not write T1 the way I have done,
> and that you would avoid deadlocks. That's not the point of this post.
> T1 is written that way for explanatory purposes only.

Yes, of course. Accepted.

> (note that I am not
> discussing other important details, such as updating indexes, or the
> overhead of versions—I am keeping it simple)

Yes, of course. Accepted.

> In PostgreSQL: each transaction operates on its own private snapshot of
> the database (that incurs a overhead, which I am not discussing for
> simplicity).

Yes, of course. Accepted.

> > citations ...
> examples you cite ...
> Example 1 ...
> Example 2 ...

Ok. But note this exchange is academic, not real, because on the Sybase side the operations happen at millisec speed, and are rarely even detected, let alone observed (via sp_lock or sp_who)
__ vs
on the PoopDeExtrude side the operations happen at 1/10 second speed, and often, such that it is detected, and observed, and cited, as per links.

> Example 1 ...
> With row-level locking, or if the two records were on different pages,
> Sybase would behave the same.

Come on, mate. As evidenced, you know nothing about SQL Platforms, let alone Sybase. You are not in a position to say what an SQL Platform, or what Sybase does, or does not do.

>>>>
I have already explained, several times, that this is a Straw Man that prevents understanding of an Ordinary Lock Manager.

It is in my Transaction Sanity doc, which you have accepted (“How could I dispute facts?”).

Now you have regressed.

I am not saying you are dishonest, I am saying you are indoctrinated, and propose such dishonest proposals unconsciously. Therefore you must *consciously* choose to erase the filth; the fantasy; the Straw Man, and be *consciously* willing to learn an altogether new thing, in the real world: an Ordinary Lock Manager in an SQL Platform from 1984.
<<<<

All you have done is, apply stupid MV-non-CC+2PL mindset onto the Sybase side, where there is no MV-non-CC; no 2PL. Short answer: no, SQL Platforms such as Sybase definitely do not do that. Explanations later.

> But when read statements are involved,
> things may run differently.
>
> > if deadlocks cannot happen, what is this, that people are experiencing
>
> Ok, they are deadlocks, as above. Could also be "serialization errors",
> discussed below.

1. I think we agree: in PoopGres, where it is reported as (a) rollbacks, or (b) “deadlocks”, or (c) “serialisation failures”, it is one logical category: the effects that should not happen, but that do happen.

Extensively, and commonly.

2. As distinct from a true deadlock, which is the result of poor SQL [or non-SQL] coding *ONLY*, not a result of internal server resource conflicts or incompetent coding in the “server”.

3. In SQL Platforms such as Sybase, [1] does not, cannot, happen. [2] happens only when the SQL code is sub-standard (the central theme in the /Stored proc for OLTP/ thread).

4. In MV-non-CC+2PL systems, [1] happens, and happens quite often. You have provided details of an abstract case, the links cited provide details of real cases. There are thousands of such reports.

Further, I am stating categorically, that it is no use writing Standard OLTP code in such primitive systems, because ACID is not possible, Transactions are not properly supported (a weird form is *always* in operation), and Standard Transactions are based on ACID. (Again, the central theme in the /Stored proc for OLTP/ thread), and as per detailed resolution provided in my Transaction Sanity doc.)

Users in such degenerate systems are reduced to writing fart concerts involving low-level primitives to secure locking, proving in each instance that “MVCC” and the devil-worshipping Mantra is false, engaging the 2PL, and thus interfering with teh locking, and thus legs wide open to new [true] deadlocks.

> Do you agree with the above description of events?

Definitely not. You are proposing the Straw Man, that Sybase operates as MV-non-CC+2PL does. It does not. And the results are drastically different..

----
Now for the detail.

----
> Example 1 (assume SERIALIZABLE):
> ...
>
> With row-level locking, or if the two records were on different pages,
> Sybase would behave the same.

Generally, yes. With the following caveats:

1. As explained above, the example is academic, not real.
2. As an academic example, yes, it is a [true] deadlock.
3. In real life, the two UPDATES bracketed by BEGIN-COMMIT TRAN in Connection[T1] are Atomic, in the same contiguous code block, and thus will execute at Time[0].
4. There is no “interleaving” of Atomic operations.
__ (There are such violent contradictions [breaking up the Atom, and then re-constituting the fragments; and then agonising over the inability to do so] in MV-non-CC+2PL systems. Those who are not schizophrenic do not break up the Atom in the first place.)
5. Connection[T1] COMMITs at Time[0]
6. Connection[T2] BEGINs; performs two UPDATES; and COMMITs at Time[1]

You are not getting it.

>>>>
Sorry, I cannot give you the whole lecture from our course, you have to try and understand this from my textual descriptions. Note that many senior SQL Platforms consultants do not understand this, it is a deep understanding of OLTP, confirmed every time, in scores of contention resolution assignments. I am privileged because I knew this from the 1970’s when I was a DBA using Cincom/TOTAL NDBMS, and then a software engineer for Cincom. Competing head-on with IBM/IMS; DEC/RDb; Cullinane; Britton-Lee; etc.

Yes, of course, number of locks; types of locks; maturity of the Lock Manager (good; bad; multi-level; escalation; promotion; etc), are all relevant, but nowhere near as relevant as ...
---------------------------------
-- LOCK DURATION --
---------------------------------

I offer getting rid of deadlocks [of that nature, due to lock duration as opposed to poor code], without changing either the database or the app code, as a fixed price service. If you are interested, I can provide proof. But I suspect you won’t read it: you don’t read the links that I provide.
<<<<

So, Connection[T1] is in and out in millisecs, long before Connection[T2] submits its SQL, which also executes in millisecs.

But yes, in pathetic herds of programs taht execute non-SQL, where the operations execute in tenths of seconds or seconds, your scenario can, and does, happen. Frequently.

Re Example [1]. In sum, there is no comparison, and we do not obtain the same result: conditions that {deadlock; rollback; serialisation fail} in MV-non-CC+2PL systems as you describe, do happen in such systems, but simply do not happen in Sybase, for the real world reasons I have given.

Derek,
I run commands at human speed, because I can't understand full-speed
execution if I cannot understand snail-speed execution. I am planning
a full-speed benchmark, but bear with me for now.

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

>> Example 2 (assume SERIALIZABLE):

> 2. As an academic example, yes, it is a [true] deadlock.

Ok. More on that below.

> 3. In real life, the two UPDATES bracketed by BEGIN-COMMIT TRAN in
> Connection[T1] are Atomic, in the same contiguous code block, and thus
> will execute at Time[0]. 4. There is no “interleaving” of Atomic
> operations.

> The notion of “interleaved operations” is plain stupid,
> self-contradictory. The Transaction is Atomic.

> Sybase has no “interleaved operations”, it preserves the Atom. Sybase
> has no internal deadlocks, all deadlocks are true (coding in the app).

> You are not getting it.

I am starting to. In general, how is an Atomic contiguous code block
defined? Asked the other way round, what does break a contiguous code
block, allowing interleaving? Surely, BEGIN and COMMIT. Anything else
in between?

> as opposed to poor code], without changing either the database or the
> app code, as a fixed price service. If you are interested, I can
> provide proof.

Yes, please. I do read your links. I am not always understand their
dense content at once.

> -------------------------------------------
> -- Example 2 Sybase Side --
> -------------------------------------------

> Let’s say Row[Tom] is on Page[Px].
> __ at Time[2]
> ____ Connection[T1] obtains a Shared-Page-Lock on Page[Px], until EoT

Yes.

> __ at Time[3]
> ____ Connection[T2] obtains an Update[Intent]-Page-Lock on Page[Px]
> (which does not escalate to Exclusive), and the lock is chained onto
> Page[Px], behind Connection[T1]’s lock

Ok.

> ____ Connection[T2] is in TaskStatus *sleep*, in LockStatus *lock-wait*

So far, so good.

> __ at Time[4]
> ____ Connection[T1] lock on Page[Px] is escalated [change of
> lock-type] to Update[Intent]-Page-Lock (succeeds),

Ok.

> and thence to Exclusive-Page-Lock (succeeds)

With all due respect, are you sure? T2 already has an Update lock on
that page. T1 cannot acquire an Exclusive lock *on the same page*,
right? At least, this is my understanding from the compatibility table
at §1.5 in ASE's Locking and Concurrency Control guide.

>> [At time [4], Sybase] rolls back a transaction, which happens to be
>> T1. T2 can now commit.

> No. As explained in detail above. There is no deadlock. Both
> Connections[T1][T2] succeed.

Really? See above.

>> Anyway, in Example 2 it is not correct to talk about deadlocks in
>> PostgreSQL, because the read statements do not acquire any locks, so
>> it's not possible for the two transactions to be waiting upon each
>> other. The update at time (3) proceeds normally (such update is local to
>> T2). At time (4), though, T1 must be put on hold, because, according to
>> the rule above, another transaction has concurrently updated the same
>> record and is stil active. When T2 commits, PostgreSQL realises that T1
>> cannot sensibly continue (because that would result in a lost update),
>> and kills it. Now, T2 can commit.
>
> 1. Repeating:
>>>
> I think we agree: in PoopGres, where it is reported as (a) rollbacks,
> or (b) “deadlocks”, or (c) “serialisation failures”,

Only (b) and (c). (a) is a consequence of (a) or (b), not a separate
error.

> it is one logical category: the effects that should not happen, but
> that do happen.

Well, yes.

> Let’s call that category FalseDeadlocks, because even the [b] reported
> deadlocks are false, not caused by the app code, but internal.

It may be that PostgreSQL deadlocks in situations in which Sybase does
not. Not sure that Example 2 is such a case: waiting for your reply.

> 3. “Kill”.
> I hope you mean rolled back.

Yes.

>> 2. Can there be a concurrent interleaving of operations that leads to
>> a deadlock in Sybase, but the same concurrent interleaving does not
>> yield a deadlock or a serialization error in PostgreSQL? No, because
>> a concurrent interleaving of operations leading to a deadlock is an
>> incorrect interleaving. So, if Sybase "encounters a deadlock
>> situation", then PostgreSQL must also produce an error in that same
>> situation, otherwise it would output an incorrect result. The
>> difference is that PostgreSQL may output a "serialization error",
>> rather than a deadlock error.
>
> Definitely not. As explained above, so I will not repeat.

I don't think that what you have said (whatever Sybase does) contradicts
my point. My point starts from the assumption that clients concurrently
submit a set of transactions *and* Sybase returns 1205 to one of them
(say, badly coded applications). That means that Sybase had to roll back
a transaction to prevent incorrect behaviour.

Now, if the clients submit the same transactions to a PostgreSQL server,
*and* if the server schedules those transactions *the same way* as
Sybase did, then PostgreSQL *must* return an error to one of them.

So, the point is: PostgreSQL cannot make all the transactions commit
where Sybase has rolled back one, because Sybase did that to prevent an
incorrect execution. PostgreSQL would have a bug if it did not do the
same.

The vice versa, however, is not true (that was my other remark). There
are cases in which PostgreSQL rolls back some transaction, but Sybase,
under the same conditions, is able to commit all of them. This is one
such situation:

Example 3

Wall time | T1 | T2
----------|-------------------|------------------
(0) | begin |
(1) | | begin
(2) | | update Tom's data
(3) | update Tom's data |
(4) | | commit
(5) | commit |

While Sybase makes T1 wait until T2 commits and then commits T1,
PostgreSQL rolls back T1 as soon as T2 commits.

I agree with you that this is bad.

Nicola

Nicola

> On Wednesday, 25 August 2021 at 02:36:37 UTC+10, Nicola wrote:
>
> Derek,
>
> I run commands at human speed, because I can't understand full-speed
> execution if I cannot understand snail-speed execution.

I don’t accept that from a teaching professor in a real world university.

Thanks for the admission. That is precisely one of the well-known problems with academia, it comes up regularly in our discussions, and it is a serious impediment to understanding ANYTHING in the real world. In my book, I have an entire chapter to deal with it. The chapter title is *Atomicity*, the problem is:

------------------------------
-- Denial of Atom --
------------------------------

I can’t give you chapter and verse, just some direction. Of course, I have repeated this many times, so I will try different words. The problem is rife, in the indoctrination system that passes for “education”, across all subject areas. Philosophically the problem is defined as *The Whole and the Parts*. I can’t give you the philosophical lecture, but you must understand that this was defined and resolved by Aristotle in 350BC. When you break it or deny it, you are operating at an intellectual level that is more primitive than the ancient Greeks. The indoctrination system that you teach, teaches young minds to deny reality, and to fabricate a fantasy, hence I assert that you teach schizophrenia (the alternate clinical term for schizophrenia is fragmentation).

The thing that exists in the real world is an Atom, the thing that people whose brains have not been scrambled by indoctrination, apprehend, is the Atom. First, you deny the Atom (the Whole). Therefore, second, you only perceive fragments (the Parts). Third, you are therefore self-condemned to deal with only the fragments (Parts). Totally clueless as to the Atom that makes sense of the fragments.

I see a cat, you see four legs; whiskers; and a tail. I see an Invoice, you see an InvoiceId; 10 item lines; and an address. Sheila sees a car, you see the engine and worry about whether it is four-stroke or two-stroke. Sheila can drive the car without understanding anything about internal combustion engines, you can’t drive, you can’t leave your desk, because you are figuring out how a four-stroke engine does not explode because the strokes obviously contradict each other.

What is worse, you have the cheek to declare that no one can drive without a full understanding of an internal combustion engine.

In our recent exchange in this thread, we are dealing with two instances of Atomicity, that work perfectly in the real world, particularly in SQL Platforms, that (a) you are in denial of, and thus (b) you obsess about the fragments, (c) finding “problems” that exist only in the concocted abstraction of the asylum, and thus (d) you cannot comprehend how the real world simply does not have those “problems”

If you are going to succeed in crossing the chasm between the isolated collective ideology that has been academia in this field, as evidenced, for fifty years, this century, you will have to start breaking your denial of the real world, and how un-indoctrinated human beings think, how we have been thinking from 350BC to 1911 (the implementation of Modernism in science, and thus its destruction. Because you are heavily indoctrinated in insanity; denial of reality; anti-science, you have to consciously choose sanity; reality; science.

The two instances are:
_ ACID Transactions = The Transaction is Atomic
___ As detailed in the other thread, Atomic in spirit, in all deployments
___ DO NOT BREAK IT UP
___ There is no “interleaving* of parts
___ There is no re-constitution of Parts afterward (it would not be the original Atom)
___ If you don’t break it up in the first place, you don’t have to re-constitute it, in the second place, and have the problems of re-constitution in the third place.
___ Yes, I understand that this insane thinking is indoctrinated; inculcated into your mind, into “MVCC”, PoopGres, starting with the StoneFrealker and his hysterically self-contradictory Mantra, it just maintains the insanity

_ Contemplation of Transactions (eg. deadlocks)
___ Ditto

I appreciate that that is your starting point, and therefore I have to entertain it to some degree, in order to get you to see what lies on the other side, across the chasm. But that is an academic exercise only, that does not validate your thinking, the exercise is abstract, it does not occur in the real world. When I teach the course, I do not allow idiotic abstract argumentation, I allow real world concepts only, but here, I have to allow the idiotic concepts to some degree.

No, you do not have to work at snail speed, because that is fiddling and farting with the Parts while denying the Whole. You are sitting there in a position in which you cannot drive the car that Sheila can, thinking yourself superior. No, we don’t need to hear why you are superior, because it is utterly false, any reasoon for being unable to drive is utterly false.

> I am planning
> a full-speed benchmark, but bear with me for now.

Yeah, sure. But please stop this academic speculation about things you do not know, and get to a real world implementation as soon as you can. That, and only that is what I agreed to.

> On 2021-08-24, Derek Ignatius Asirvadem wrote:
>
> >> Example 2 (assume SERIALIZABLE):
> > 2. As an academic example, yes, it is a [true] deadlock.
> Ok. More on that below.
> > 3. In real life, the two UPDATES bracketed by BEGIN-COMMIT TRAN in
> > Connection[T1] are Atomic, in the same contiguous code block, and thus
> > will execute at Time[0]. 4. There is no “interleaving” of Atomic
> > operations.
> > The notion of “interleaved operations” is plain stupid,
> > self-contradictory. The Transaction is Atomic.
> > Sybase has no “interleaved operations”, it preserves the Atom. Sybase
> > has no internal deadlocks, all deadlocks are true (coding in the app).
> > You are not getting it.
> I am starting to. In general, how is an Atomic contiguous code block
> defined? Asked the other way round, what does break a contiguous code
> block, allowing interleaving? Surely, BEGIN and COMMIT. Anything else
> in between?
> > as opposed to poor code], without changing either the database or the
> > app code, as a fixed price service. If you are interested, I can
> > provide proof.
> Yes, please. I do read your links. I am not always understand their
> dense content at once.
> > -------------------------------------------
> > -- Example 2 Sybase Side --
> > -------------------------------------------
> > Let’s say Row[Tom] is on Page[Px].
> > __ at Time[2]
> > ____ Connection[T1] obtains a Shared-Page-Lock on Page[Px], until EoT
> Yes.
> > __ at Time[3]
> > ____ Connection[T2] obtains an Update[Intent]-Page-Lock on Page[Px]
> > (which does not escalate to Exclusive), and the lock is chained onto
> > Page[Px], behind Connection[T1]’s lock
>
> Ok.
>
> > ____ Connection[T2] is in TaskStatus *sleep*, in LockStatus *lock-wait*
> So far, so good.
> > __ at Time[4]
> > ____ Connection[T1] lock on Page[Px] is escalated [change of
> > lock-type] to Update[Intent]-Page-Lock (succeeds),
>
> Ok.
>
> > and thence to Exclusive-Page-Lock (succeeds)
>
> With all due respect, are you sure? T2 already has an Update lock on
> that page. T1 cannot acquire an Exclusive lock *on the same page*,
> right? At least, this is my understanding from the compatibility table
> at §1.5 in ASE's Locking and Concurrency Control guide.

Re real world operation under stress, of course I am sure. Normally, I run the course only with access to a server, so that participants can try it immediately. Trying to imagine these things, to understand, without any hard evidence, is known to be impossible. It is *race conditions*, and you have no clue how to apprehend it (you need a benchmark that stresses the issues). Aside from a benchmark, operating at snail speed, I recommend you do the same, run two instances of isql for [T1][T2], and a third to watch, via sp_who & sp_lock.

Re the academic exercise, which is an abstraction, no, I cannot be sure, and I couldn’t care less. You can take that sequence, or reverse the sequence, and dream about the tooth fairy as much as you like. Just do not declare that that imagined sequence, of broken up Parts of an Atom that should not be broken into Parts, is a “problem” in the real world, do not make any declarations, because they will be schizophrenic; idiotic.

I entertained the academic exercise (abstract; non-real) to some degree, for the purpose of assisting your understanding only, but this is going beyond it, because you are implying real world consequences to your non-real; abstract notion. You cannot build the world from abstract notions, the concept is insane, anti-science. The purpose of science is to observe the real world and to DETERMINE the truth, not to PRODUCE the truth, from non-real abstract notions, which will be nothing but hysterical fiction because it is isolated from reality. You need to experiment with reality and determine the truth.

Likewise, precision in this academic example is not possible, it is for understanding only. So in that sense, no, I am not sure because it can be argued academically, either way, until the cows come home.

Nicola

> On Wednesday, 25 August 2021 at 02:36:37 UTC+10, Nicola wrote:
> > On 2021-08-24, Derek Ignatius Asirvadem wrote:
> >
> > 3. In real life, the two UPDATES bracketed by BEGIN-COMMIT TRAN in
> > Connection[T1] are Atomic, in the same contiguous code block, and thus
> > will execute at Time[0].
> >
> > 4. There is no “interleaving” of Atomic operations.
> >
> > The notion of “interleaved operations” is plain stupid,
> > self-contradictory. The Transaction is Atomic.
> > Sybase has no “interleaved operations”, it preserves the Atom. Sybase
> > has no internal deadlocks, all deadlocks are true (coding in the app).
> > You are not getting it.
>
> I am starting to. In general, how is an Atomic contiguous code block
> defined? Asked the other way round, what does break a contiguous code
> block, allowing interleaving? Surely, BEGIN and COMMIT. Anything else
> in between?

I cannot accept that a teaching professor does not understand the established term CS /contiguous/. This is one of those basic things that one has to apprehend and deal with in the first few months of an undergrad CS course, when writing the first file-system or C-ISAM program.

Second, this is the same problem as you had when you could not tell the difference between a Transaction (everything between a BEGIN-COMMIT pair) and a Transaction stored proc that contains it.

Third, we yet again crash into the overarching problem, that the freaky academics have private definitions of established industry terms, or worse, re-definitions, such that you can commit scientific fraud.

1. The Oxford English Dictionary, because humans do not use a word in a particular context that is outside the original English meaning (except for the asylum, of course):
contiguous |kənˈtɪgjʊəs|
adjective
• sharing a common border; touching: the Southern Ocean is contiguous with the Atlantic.
• next or together in sequence. five hundred contiguous dictionary entries.

2. In CS, /contiguous/ is used to describe PHYSICALLY adjacent; touching; sequential, memory address spaces (accessed in blocks nowadays, not bytes), or disk blocks (read/write is block mode, not character mode).

3. When applied to code segments, it means exactly the same thing: that the code is PHYSICALLY continuous; touching; sequential.

Coders understand that when using a real language such as SQL (more, later), due to IF-THEN-ELSE structs and GOTOs, and to keep (eg) the error handling in one contiguous code block, the code may not be 100% contiguous, which is understandable, but the whole code between BEGIN and COMMIT/ROLLBACK must be [imperfectly] contiguous.

THIS GIVES THE TRANSACTION //CODE// ATOMICITY, THE [A] IN ACID

Example 1
Transaction Sanity doc, pages 3; 4; 7. Appreciating that it is pseudo-code, not code. Each blue rectangle is a contiguous code block.

Example 2
The latest version of the OLTP Transaction Template. Here it is real SQL code.
__ https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Visit_Add_tr%20DA.sql
a. the entire content is contiguous
b. the content between BEGIN and COMMIT is contiguous

But note, that is a simple Template. In the real world implementation, as any developer would know, there would NOT be several
__ RAISERROR/ROLLBACK/RETURN
all over the shore, there would be a single block
__ ERROR_HANDLE:
which handles all errors and does a single
__ RAISERROR xyz
__ RETURN q
which allows the multiple RAISERROR/ROLLBACK/RETURNs in the code to be replaced with
__ GOTO ERROR_HANDLE

Hence the code in the stored proc, and particularly in the BEGIN-COMMIT/ROLLBACK sequence, is still said to be contiguous.

4. While SQL is a real language (a) as defined by Codd, (b) as defined by ANSI/IEEE/IEC/ISO, and (c) implemented as such [with additions to make it a full; complete; powerful language], in Sybase; DB2; MS; Informix, the pathetic crap that is provided by freeware and Oracle, is not a real language; not SQL, by any stretch of the imagination (ok, the asylum thinks it is, in denial of actual knowledge of SQL).
- Stored procs were introduced only in the last release, which mean only a pathetic fraction
- Constraints are implemented as internal triggers
- it is not SQL compliant at all (which binds the code to the freeware)
- process distribution passed off as “parallelism”
- masses of non-SQL and pretend-SQL, which keep changing with every major release (you guys love re-writes)
- the list is endless

----
> Asked the other way round, what does break a contiguous code
> block, allowing interleaving?

Nothing.

As detailed in this and other threads, in order to allow “interleaving”, one has to break up the Atom. Which renders the code; the Transaction NON-ATOMIC, which fails the [A]tomicity in ACID, and thus fails ACID.

Yet again, because it breaks up the Atom, into fragments, in order to allow “interleaving”, and it does “interleave”, in order to make the snail move like a tortoise, PoopGress simply does not support ACID. It is too stupid for a human being to even contemplate. And then you have the stupefying “schedule”, for single-threaded processing. And then the totally insane “serialisation conflicts” and the wringing of hands and the gnashing of teeth that goes with it, straight to hell. You guys teach this as “computer science”, as a “database” course. Totally and utterly insane. Which is why, yet again, as evidenced, you teach schizophrenia as “education”.

Humans do not break up the Atom. Humans do not re-define ACID or Atomicity, in order to get fragments of the broken-up Atom (pig poop) to “qualify” as “atomic”, and then lose control of their bowels when they try to re-constitute the fragments. Humans in 2021 understand Multi-Treading, we have had it since 1976, in the preceding form (Re-Entrant code) since 1965. But the intellectual level of you guys is still stuck in knuckle-dragging single-thread mode. Dare I say it, ok, because in order to understand multi-thread, you have to first understand single-thread. Hopefully you will obtain the goal of understanding Multi-Thread this century.

----
> > -- LOCK DURATION --
> > I offer getting rid of deadlocks [of that nature, due to lock duration,
> > as opposed to poor code], without changing either the database or the
> > app code, as a fixed price service. If you are interested, I can
> > provide proof.
>
> Yes, please. I do read your links.

SG was level 3 support for this customer, a large Australian Insurance company. They had many systems, both 3rd party apps and home-grown, mostly using one or more Sybase databases on a single server machine (Production). The core system (that all other systems used to some degree) was a 3rd party document handling app and database. The supplier had gone out of business, but it was really good in some ways, certainly ahead of its time when purchased, thus they retained it, and keep building systems around it. We supply support for unsupported (old) releases of Sybase. The core database was on a Sybase release that had been end-of-lifed ten years prior. We do not recommend moving to a later or current release, and consequently having to deal with a mountain of new problems. The app & db worked on a EOL release, we don’t fix something that is not broken, and we maintained that. The main problem in the 3rd party app and database was lock contention, including deadlocks, which slowly increased over time.

The on-site DBA (several over the decades), were quite alright in handling day-to-day problems, but hopeless in planning and executing any projects that would [obviously] alleviate the ongoing problems. We had offered to fix all problems without them having to change their app code, years before, but it was only when the number of deadlocks became untenable (interfered with work badly; staff queueing up at document readers; etc) that they said, ok, ok, just do it.

They had no idea that lock duration, not number of locks or lock types, was the empirical cause. And I was not about to teach a stressed DBA what he did not understand. The project was a straight server rebuild, all data structures, done properly, and allowing for a few years of growth. In case it is not obvious, that act causes (a) the data structures, and (b) the data therein, to be contiguous. The result is, hey presto, minimised lock duration, and therefore, elimination of lock contention caused by lock duration, total elimination of deadlocks.

//
Of course, the deadlocks are written in the app code, and that could not be touched (3rd party app; supplier out of business; no source code). They remain in their little underground caves, into which I have banished them, to show their miserable faces, some day, if and when lock duration increases, due to the data structures getting fragmented, and thus non-contiguous.

Hence I built the data structs to handle two years of growth without losing speed. That gave them enough time to replace the app & db completely, with a modern document handling system. Otherwise, just re-run the scripts.
//

When we examined the server for the purpose of quotation, we identified other various errors, that should sensibly be done together in a server rebuild. Eg. in order to maintain speed in the existing data structs, they had archived a bunch of data into a separate database, via VIEWS that had to be changed with each archive increment. That is a common fix-it that ignorant DBAs perform, it is ridiculous, there is no problem at all for a qualified DBA to maintain speed on a massive table. I reversed all those errors, and gave them single tables that were blindingly fast.

Nicola

> On Wednesday, 25 August 2021 at 02:36:37 UTC+10, Nicola wrote:
>
> Derek,
>
> I run commands at human speed, because I can't understand full-speed
> execution if I cannot understand snail-speed execution.

I don’t accept that from a teaching professor in a real world university.

Thanks for the admission. That is precisely one of the well-known problems with academia, it comes up regularly in our discussions, and it is a serious impediment to understanding ANYTHING in the real world. In my book, I have an entire chapter to deal with it. The chapter title is *Atomicity*, the problem is:

------------------------------
-- Denial of Atom --
------------------------------

I can’t give you chapter and verse, just some direction. Of course, I have repeated this many times, so I will try different words. The problem is rife, in the indoctrination system that passes for “education”, across all subject areas. Philosophically the problem is defined as *The Whole and the Parts*. I can’t give you the philosophical lecture, but you must understand that this was defined and resolved by Aristotle in 350BC. When you break it or deny it, you are operating at an intellectual level that is more primitive than the ancient Greeks. The indoctrination system that you teach, teaches young minds to deny reality, and to fabricate a fantasy, hence I assert that you teach schizophrenia (the alternate clinical term for schizophrenia is fragmentation).

The thing that exists in the real world is an Atom, the thing that people whose brains have not been scrambled by indoctrination, apprehend, is the Atom. First, you deny the Atom (the Whole). Therefore, second, you only perceive fragments (the Parts). Third, you are therefore self-condemned to deal with only the fragments (Parts). Totally clueless as to the Atom that makes sense of the fragments.

I see a cat, you see four legs; whiskers; and a swishing tail. I see an Invoice, you see an InvoiceId; 10 item lines; and an address. Sheila sees a car, you see the engine and worry about whether it is four-stroke or two-stroke. I see a Relational Key, you see only the components, and worse, you need 17 “NFs” to grapple with them. You look at a coccyx and perceive a vestigial tail because that validates you heavolution fantasy, I see a carefully designed structure that supports all the muscles attached to the hips and legs, permitting the only animal that can sit.

Sheila can drive the car without understanding anything about internal combustion engines, you can’t drive, you can’t even leave your desk, because you are figuring out how a four-stroke engine does not explode when the strokes obviously contradict each other. What is worse, you have the cheek to declare that no one can drive without a full understanding of an internal combustion engine.

In our recent exchange in this thread, we are dealing with two instances of Atomicity, that work perfectly in the real world, particularly in SQL Platforms, that (a) you are in denial of, and thus (b) you obsess about the fragments, (c) finding “problems” that exist only in the concocted abstraction of the asylum, and thus (d) you cannot comprehend how the real world simply does not have those “problems”.

You are seeing the moon in the river, and you are asking me to fish it out.

If you are going to succeed in this welcome crossing of the chasm between the isolated collective ideology that has been academia in this field, as evidenced, for fifty years, this century, you will have to start breaking your denial of the real world, and learn about how un-indoctrinated human beings think, how we have been thinking from 350BC to 1911 (the implementation of Modernism in science, and thus its destruction).

Because you are heavily indoctrinated in insanity; denial of reality; anti-science, you have to consciously choose sanity; reality; science. Starting at the foundation, the Four Laws of Thought. You have the God-given Power to think whatever you like, via Free Will, but you do not have the right to do so. Licence comes from conforming to the absolute, objective truth, which is the only Right. Right Thinking, is the foundation of Science.

In case it is not obvious, I am all for the exercise of the intellect, which is abstraction, if it is at least one level of abstraction away from reality. Whereas you guys don’t not have that constraint, and thus fantasy.

The two instances are:
_ ACID Transactions = The Transaction is Atomic
___ As detailed in the other thread, Atomic in spirit, in all deployments: server code (execution of submitted SQL, ensuring ACID; client- or middle-layer-side SQL; Transaction SQL code)
___ DO NOT BREAK IT UP
___ There is no “interleaving* of Parts
___ There is no re-constitution of Parts afterward (it would not be the original Atom)
___ If you didn’t break it up in the first place, you wouldn’t have to re-constitute it, in the second place, and suffer the problems of re-constitution in the third place.
___ Yes, I understand that this insane thinking is indoctrinated; inculcated into your mind, into “MVCC”, PoopGres, starting with the StoneFreaker and his hysterically self-contradictory Mantra, which just maintains the insanity of singing one thing while performing the opposite.

_ Contemplation of Transactions (eg. deadlocks)
___ Ditto, on all points

I appreciate that that is your starting point, and therefore I have to entertain it to some degree, in order to get you to see what lies on the other side, across the chasm. But that is an academic exercise only, that does not validate your thinking, the exercise is abstract, it does not occur in the real world. When I teach the course, I do not allow idiotic abstract argumentation, I allow real world concepts only, but here, I have to allow the idiotic concepts to some degree.

No, you do not have to work at snail speed, because that is fiddling and farting with the Parts while denying the Whole. You are sitting there in a position in which you cannot drive the car that Sheila can, thinking yourself superior. No, we don’t need to hear why you are superior, because it is utterly false, any “reason” for being unable to drive is utterly false.

> I am planning
> a full-speed benchmark, but bear with me for now.

Yeah, sure. But please stop this academic speculation about things you do not know, and get to a real world implementation as soon as you can. That, and only that is what I agreed to.

> On 2021-08-24, Derek Ignatius Asirvadem wrote:
>
> >> Example 2 (assume SERIALIZABLE):
> > 2. As an academic example, yes, it is a [true] deadlock.
> Ok. More on that below.
> > 3. In real life, the two UPDATES bracketed by BEGIN-COMMIT TRAN in
> > Connection[T1] are Atomic, in the same contiguous code block, and thus
> > will execute at Time[0]. 4. There is no “interleaving” of Atomic
> > operations.
> > The notion of “interleaved operations” is plain stupid,
> > self-contradictory. The Transaction is Atomic.
> > Sybase has no “interleaved operations”, it preserves the Atom. Sybase
> > has no internal deadlocks, all deadlocks are true (coding in the app).
> > You are not getting it.
> I am starting to. In general, how is an Atomic contiguous code block
> defined? Asked the other way round, what does break a contiguous code
> block, allowing interleaving? Surely, BEGIN and COMMIT. Anything else
> in between?
> > as opposed to poor code], without changing either the database or the
> > app code, as a fixed price service. If you are interested, I can
> > provide proof.
> Yes, please. I do read your links. I am not always understand their
> dense content at once.
> > -------------------------------------------
> > -- Example 2 Sybase Side --
> > -------------------------------------------
> > Let’s say Row[Tom] is on Page[Px].
> > __ at Time[2]
> > ____ Connection[T1] obtains a Shared-Page-Lock on Page[Px], until EoT
> Yes.
> > __ at Time[3]
> > ____ Connection[T2] obtains an Update[Intent]-Page-Lock on Page[Px]
> > (which does not escalate to Exclusive), and the lock is chained onto
> > Page[Px], behind Connection[T1]’s lock
>
> Ok.
>
> > ____ Connection[T2] is in TaskStatus *sleep*, in LockStatus *lock-wait*
> So far, so good.
> > __ at Time[4]
> > ____ Connection[T1] lock on Page[Px] is escalated [change of
> > lock-type] to Update[Intent]-Page-Lock (succeeds),
>
> Ok.
>
> > and thence to Exclusive-Page-Lock (succeeds)
>
> With all due respect, are you sure? T2 already has an Update lock on
> that page. T1 cannot acquire an Exclusive lock *on the same page*,
> right? At least, this is my understanding from the compatibility table
> at §1.5 in ASE's Locking and Concurrency Control guide.

Re real world operation under stress, of course I am sure. Normally, I run the course only with access to a server, so that participants can try it immediately. Trying to imagine these things, to understand, without any hard evidence, is known to be impossible. It is *race conditions*, and you have no clue how to apprehend it (you need a benchmark that stresses the issues). Aside from a benchmark, operating at snail speed, I recommend you do the same, run two instances of isql for [T1][T2], and a third to watch, via sp_who & sp_lock.

On 2021-08-26, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
> As a real world possibility, it is the usual insanity; nonsense,
> because you are splitting the Atom (the Whole) (everything between
> each BEGIN-COMMIT pair), and then fiddling and farting with the Parts,
> like the other famous shell game you guys play to “find” the Key
> because you have denied the extant Key. In the real world, Atoms
> remain Atoms, not fragments. Even on the PeePeeGres side, as long as
> the code is contiguous, as opposed to being artificially spread out on
> a piece of paper, [T2] will execute after [T1], there will not be
> a rollback.

So, I have run some tests (btw, thanks for the tips from the Sybase
questions thread!). One of them is based of one of my previous examples,
which I summarize here for clarity (full code at the end of this
message):

T1: select Tom's balance; update Tom's balance.
T2: update Tom's balance.

In Sybase:

- when the statements of T1 and T2 are submitted to the server
individually, but within a begin.. commit block, then I can obtain
a deadlock. I think that this is to be expected. It's like the "snail
speed" manual test.

- Interestingly, when T1 and T2 are implemented as stored procedures, no
deadlocks occur, even when the number of concurrent threads is very
high (I have tried with up to 1000 threads), no matter whether I open
two connections overall or one connection per thread (although the
free version has a limit on the number of connections, so I could use
<100 threads in this case). So, it appears that no context switch ever
happens during the execution of T1 (resp., T2). Is that a correct
interpretation?

- I have added an artificial delay in T1 between select and update.
Still, no deadlocks, although I get several 5s timeouts. Is that
because a transaction is rolled back if it's waiting for too long?

In PostgreSQL:

- you cannot set the isolation level within a stored procedure; that
must be done before calling the stored procedure.

- Even with a couple of threads, a serialization failure can be obtained:

ERROR: could not serialize access due to concurrent update

PostgreSQL's behaviour is consistent with what was previously discussed,
so I don't have anything else to add, except that

> Even on the PeePeeGres side, as long as
> the code is contiguous, as opposed to being artificially spread out on
> a piece of paper, [T2] will execute after [T1], there will not be
> a rollback.

I find that not to be the case. If you know how I could run T1 and T2
concurrently without errors, please let me know.

Nicola

####### RUBY SCRIPT FOR ASE ##########################################
# Requirements:
# gem install sequel
# gem install tiny_tds
require 'sequel'
require 'tiny_tds'

opts = {
adapter: 'tinytds',
login_timeout: 5,
timeout: 100000,
tds_version: '50', # 42 or 50
host: 'localhost',
port: 5000,
database: 'scratch',
username: 'sa',
password: ''
}

DB = Sequel.connect opts

begin
puts ("Dropping data...")
DB.run("drop table Account")
DB.run("drop procedure t1")
DB.run("drop procedure t2")
rescue
end

puts "Populating db..."

DB.run("create table Account (
name char(5) primary key,
balance integer)
")

DB.run("insert into Account(name, balance) values ('Tom', 40)")

DB.run("create procedure t1 as
set transaction isolation level serializable
select balance
from Account
where name = 'Tom'

-- waitfor delay '00:00:00.5'

update Account
set balance = balance - 10
where name = 'Tom'")

DB.run("create procedure t2 as
set transaction isolation level serializable
update Account
set balance = balance + 20
where name = 'Tom'")

t1 = [] # List of threads running t1
t2 = [] # List of threads running t2
N = 100 # Number of concurrent threads for each procedure
numerr = 0

stime = Time.now
puts "Started At #{stime}"

db1 = Sequel.connect opts
db2 = Sequel.connect opts
# db1 = []
# db2 = []
N.times do |i|
t1[i] = Thread.new {
begin
# db1[i] = Sequel.connect opts
# db1[i].run("t1")
db1.run("t1")
rescue Exception => e
numerr += 1
puts "FAIL: #{e.message}"
end
}

t2[i] = Thread.new {
begin
# db2[i] = Sequel.connect opts
# db2[i].run("t2")
db2.run("t2")
rescue Exception => e
numerr += 1
puts "FAIL: #{e.message}"
end
}
end

# Wait for all the threads to complete
N.times do |i|
t1[i].join
t2[i].join
end

etime = Time.now
puts "End at #{etime}"
puts "Elapsed time: #{(1000 * (etime - stime)).round()}ms"
puts "Number of errors: #{numerr}"
puts "Account table:"
DB.fetch('select balance from Account') { |r| puts r }
######################################################################
# $ ruby test-ase.rb
# Dropping data...
# Populating db...
# Started At 2021-08-29 15:05:43 +0200
# End at 2021-08-29 15:05:43 +0200
# Elapsed time: 178ms
# Number of errors: 0
# Account table:
# {:balance=>1040}

####### RUBY SCRIPT FOR POSTGRESQL ###################################
# Requirements:
# gem install sequel
# gem install pg
require 'sequel'

DB = Sequel.postgres('scratch', user: 'nicola', password: '', host: 'localhost')

puts ("Dropping data...")
DB.run("drop table if exists Account")

puts "Populating db..."

DB.run("create table Account (
name char(5) primary key,
balance integer)
")

DB.run("insert into Account(name, balance) values ('Tom', 40)")

DB.run("create or replace procedure t1() language sql as $$
select balance
from Account
where name = 'Tom'
for update;

update Account
set balance = balance - 10
where name = 'Tom';
$$")

DB.run("create or replace procedure t2() language sql as $$
update Account
set balance = balance + 20
where name = 'Tom';
$$")

t1 = [] # List of threads running t1
t2 = [] # List of threads running t2
N = 1 # Number of concurrent threads for each procedure
numerr = 0

stime = Time.now
puts "Started At #{stime}"

db1 = Sequel.postgres('scratch', user: 'nicola', password: '', host: 'localhost')
db2 = Sequel.postgres('scratch', user: 'nicola', password: '', host: 'localhost')

N.times do |i|
t1[i] = Thread.new {
begin
# db1.transaction(isolation: :serializable) do
db1.run("set transaction isolation level serializable; call t1()")
# end
rescue Exception => e
numerr += 1
puts "FAIL: #{e.message}"
end
}

t2[i] = Thread.new {
begin
# db2.transaction(isolation: :serializable) do
db2.run("set transaction isolation level serializable; call t2()")
# end
rescue Exception => e
numerr += 1
puts "FAIL: #{e.message}"
end
}
end

# Wait for all the threads to complete
N.times do |i|
t1[i].join
t2[i].join
end

etime = Time.now
puts "End at #{etime}"
puts "Elapsed time: #{(1000 * (etime - stime)).round()}ms"
puts "Number of errors: #{numerr}"
puts "Account table:"
DB.fetch('select balance from Account') { |r| puts r }
######################################################################
# $ ruby test-postgresql.rb
# Dropping data...
# Populating db...
# Started At 2021-08-29 15:04:55 +0200
# FAIL: PG::TRSerializationFailure: ERROR: could not serialize access due to concurrent update
# CONTEXT: SQL function "t2" statement 1
# End at 2021-08-29 15:04:55 +0200
# Elapsed time: 13ms
# Number of errors: 1
# Account table:
# {:balance=>30}

On 2021-08-29, Nicola <nicola@nohost.org> wrote:
> - Interestingly, when T1 and T2 are implemented as stored procedures,
> no deadlocks occur

Wait. I haven't started a transaction. After adding begin transaction..
commit to each stored procedure, I do get deadlocks. That makes more
sense to me.

Nicola

Nicola

> On Sunday, 29 August 2021 at 23:11:23 UTC+10, Nicola wrote:
> On 2021-08-26, Derek Ignatius Asirvadem wrote:
> >
> > As a real world possibility, it is the usual insanity; nonsense,
> > because you are splitting the Atom (the Whole) (everything between
> > each BEGIN-COMMIT pair), and then fiddling and farting with the Parts,
> > like the other famous shell game you guys play to “find” the Key
> > because you have denied the extant Key. In the real world, Atoms
> > remain Atoms, not fragments. Even on the PeePeeGres side, as long as
> > the code is contiguous, as opposed to being artificially spread out on
> > a piece of paper, [T2] will execute after [T1], there will not be
> > a rollback.
>
> (btw, thanks for the tips from the Sybase
> questions thread!).

You are welcome.

> So, I have run some tests

Good work.

Given that the test is early stage (not mature), and the detail in your post, I think it is best to respond with notes, rather than responding to each item that you raise. Of course, you have an intent, to prove/disprove something, but it may be a bit too early to form conclusions ... what you have is just enough “proof” to validate your pre-existing belief, rather than a proper stand-alone proof. Mostly, I am qualifying the test, so that it can progress to maturity, and it gives the proof you seek.

1. This is not a benchmark, but a stress test, which is fine.

2. Ruby is an additional layer, and it uses ODBC, which is a slow connection. For a benchmark, that will not be good enough, it is better to use ISQL directly (and eliminate ODBC): everything in your code can be done just as easily with ISQL.

3. On the ASE side, we have to use ASE terminology. They are not threads, they are connections [to the server]. Threads are only in the server, completely internal. Real threads on the real CPU. Whatever this is set to:
__ sp_configure “max online engines”

I appreciate that Ruby has “threads”, I don’t know if that means it executes:
a. 1,000 concurrent instances of the code (meaning client-side “threads”, 1,000 instances of ISQL)
__ or
b. 1,000 iterations of the code on 1 instance of ISQL

4. I don’t know how you can run 1,000 client-side “threads” with the free version which has limited connections.

What is the limit ?

5 ----
> One of them is based of one of my previous examples,
> which I summarize here for clarity (full code at the end of this
> message):
> T1: select Tom's balance; update Tom's balance.
> T2: update Tom's balance.
>
> In Sybase:
> - when the statements of T1 and T2 are submitted to the server
> individually, but within a begin.. commit block, then I can obtain
> a deadlock. I think that this is to be expected.

To be clear,
that means manually, in isql or equivalent, for the purpose of learning and understanding, as advised, and NOT simulating the real world (where there is no appreciable execution time between the BEGIN-COMMIT pair [because there is not a tiny nano-robot to halt execution in the middle of a Transaction (which is not-real) until we finish counting our toes] ).

But excellent learning, nonetheless.

6 ----
The code doesn’t have BEGIN-COMMIT. I expect there are several versions of the code, and the version you attached simply does not have it, but the code you tried in this particular does have it.

The thing to be careful about is
__ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
holds locks until end-of-transaction ... but when there is no BEGIN-COMMIT, there is no Transaction to define that point, therefore when there is no BEGIN-COMMIT locks are held until end-of-statement.

You can check this via (Building Blocks manual):
__ SELECT @@TRANCOUNT

You also have to ensure that chained transactions (aka autocommit) is OFF (session level):
__ SET CHAINED OFF

> It's like the "snail
> speed" manual test.

Exactly.

My declarations proved.

7 ----
> - Interestingly, when T1 and T2 are implemented as stored procedures, no
> deadlocks occur, even when the number of concurrent threads is very
> high (I have tried with up to 1000 threads),

Exactly.

My declarations re ASE and real-world operation are proved.

7.a Is that with/without BEGIN-COMMIT ?
7.b Is that with/without the WAITFOR 0.5 secs ?
___ Scratch that, answered later, it is without.
7.c Is ASE running in Process or Thread mode ?

To be clear
that means NOT manually, NOT for the purpose of learning and understanding, but simulating the real world (where there is no appreciable execution time between the BEGIN-COMMIT pair [because there is not a tiny nano-robot to halt execution until we finish counting our toes] ). That is without the WAITFOR.

Sure, withe the WAITFOR is worth testing, IFF your intent is to simulate the user interaction, in which case the WAITFOR must not be in the Transaction (between BEGIN & COMMIT), but between Transactions.

8. From a third connection, to watch while giving connections[T1}[T2] start/stop commands:
__ sp_who
__ sp_lock

9 ----
> no matter whether I open
> two connections overall or one connection per thread (although the
> free version has a limit on the number of connections, so I could use
> <100 threads in this case).

Sorry, I cannot understand that.

10 ----
> So, it appears that no context switch ever
> happens during the execution of T1 (resp., T2). Is that a correct
> interpretation?

Context Switches happen at four specific levels, which do you mean ?

a. Machine/Linux - use vmstat

b. ASE is designed as a genuine server, that means it expects to be the only app on the machine. For each o/s it is [default level] somewhat bound to the o/s, and further, there are sp_configure parms that bind it to the o/s even more. And it runs Threads, machine Threads. The first thing to understand is, when it is idle, it does not release the Thread (that would cause 2 x machine context switch, which is expensive). It waits for a [configurable] time, expecting an event such as completed I/O or Lock Release. When it does release a Thread, it is called a VOLUNTARY YEILD.

c. ASE/Task. For each task (connection; session, spid), it maintains a full internal Stack (executable code); etc. When it schedules a task out, and another task in, yes, it context switches. Look at this monitor report, second §, Kernel: Context Switch has 16 species (important info for performance & tuning) (15 Task species plus server Voluntary Yield):
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

Same as machine context switches, task context switches are expensive, to be tuned down (we want tasks to use the entire timeslice). Without looking too hard, just looking at the shape (width) of the numerics, one can see that the network is slow; that the Log and Locks have been tuned.

d. The fourth level is not a genuine context switch, but what developers often call a “context switch”. They have a perspective from their code only, looking at the sever, instead of reality, which is the reverse. But we have to entertain their silliness, instead of arguing each and every time. By this they mean anything that causes their code to context switch out. Of course, it is a subset of [c], it simply means they exclude server internals that they do not understand.

I suspect you are in [d]. Please note the above, which you must learn and understand over time.

In attempting to answer your question, I have to assert [c]. First, how many ASE Engine Threads do you have ? I expect way more than 2. Therefore both [T1][T2] will not be context switching at all, in the [d] sense. In reality, both [T1][T2] will be context switching all the time, because it is an ASE Thread that runs each task, if at all, and those species would be the set [c] minus set [d].

Bottom line
If I understand the intent of your question correctly, connection [T1] is context switching as much as [T2}, and the context switches do not matter.

Relevance
In terms of relevance, you will be context switching only for server internal reasons, eg. Cache Miss (not in this case because the one page is in cache) or Lock Release or Log Write (which is militated by how well you have tuned the Log), and ASE simply being /idle/.
__ Given the tiny load, /idle/ would be the largest metric.
__ For your stress test, on a default-config server, Lock Waits would be the only metric that is of concern.
__ For the future (benchmarks), you must configure all ASE resources better, particularly the Log, and then the other Context Switch species will become relevant, as they are tuned and can be reduced.

11 ----
For any benchmark, for any stress test, wherein you need to know what the server did/did not do, as per expectations, and to ensure that nothing unexpected [that would skew the test] did/did not happen, you need a server monitor report.

a. There are great 3rd party graphical tools, but they are expensive.

b. The heavy-duty SAP/Sybase Monitor Server. Recommended for serious heavy-duty benchmarks only (eg. TPC). It runs as an OpenServer, mapping onto ASE Shared Memory. So it is extremely powerful, with zero overhead. Licence fee, not recommended. Identified for understanding only.

c. There is a modern ASE set of tables, but they have to be set up, and SQL/scripts have to be written. And they have overhead, which defeats the purpose of a benchmark. So the monitoring activity has to be sp_configured carefully, for the particular purpose. Too much work. Because they are problematic, SG has a full set of scripts for all this, which eliminates the problems. But I never use them.

> On Monday, 30 August 2021 at 17:39:27 UTC+10, Nicola wrote:
> > On 2021-08-29, Nicola wrote:
> > - Interestingly, when T1 and T2 are implemented as stored procedures,
> > no deadlocks occur
> Wait. I haven't started a transaction.

Yes, that was not clear. Refer my questions in my response.

> After adding begin transaction..
> commit to each stored procedure, I do get deadlocks. That makes more
> sense to me.

1. Per my comments, re the lock duration, yes, that makes more sense.
2. From a Transaction perspective, no, it doesn't make sense to me.
__ stored proc [T2] is not holding anything that [T1] holds, so a deadlock is not possible.
3. Therefore (debugging this remotely, with little detail provided), it would be that you do have several concurrent [T1] connections that are deadlocking it other, nothing to do with [T2].

Best to read my response and provide more detail (summary) for each test.

Cheers
Derek

> On Monday, 30 August 2021 at 17:39:27 UTC+10, Nicola wrote:
> > On 2021-08-29, Nicola wrote:
> > - Interestingly, when T1 and T2 are implemented as stored procedures,
> > no deadlocks occur
> Wait. I haven't started a transaction.

Yes, that was not clear. Refer my questions in my response.

> After adding begin transaction..
> commit to each stored procedure, I do get deadlocks. That makes more
> sense to me.

1. Per my comments, re the lock duration, yes, that makes more sense.
2. But from a Transaction perspective, no, it doesn't make sense to me.
__ stored proc [T1] is not holding anything that [T2] holds, so a deadlock between them is not possible.
3. Therefore (debugging this remotely, with the little detail provided), it would be that you do have several concurrent [T1] connections that are deadlocking each other, nothing to do with [T2].

Best to read my response and provide more detail (summary) for each test.

Cheers
Derek

On 2021-08-30, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:
>> On Monday, 30 August 2021 at 17:39:27 UTC+10, Nicola wrote:
>> > On 2021-08-29, Nicola wrote:
>> > - Interestingly, when T1 and T2 are implemented as stored procedures,
>> > no deadlocks occur
>> Wait. I haven't started a transaction.
>
> Yes, that was not clear. Refer my questions in my response.
>
>> After adding begin transaction..
>> commit to each stored procedure, I do get deadlocks. That makes more
>> sense to me.
>
> 1. Per my comments, re the lock duration, yes, that makes more sense.
> 2. But from a Transaction perspective, no, it doesn't make sense to me.
> __ stored proc [T1] is not holding anything that [T2] holds, so a deadlock between them is not possible.
> 3. Therefore (debugging this remotely, with the little detail
> provided), it would be that you do have several concurrent [T1]
> connections that are deadlocking each other, nothing to do with [T2].

Correct. Deadlocks still happen if I remove T2.

> Best to read my response and provide more detail (summary) for each test.

Sure, and I'll design a better experiment (these are not
benchmarks—yet): my first attempt has been pretty naive.

Nicola