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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

Does this assessment satisfy you?

Nicola

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

----

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.

Yes, PoopAndPeeGross has {Rollbacks; deadlocks; and serialisation failures}, in one category of things that should not happen in “MVCC” systems, (a) but that do happen, frequently, AND (b) that do not happen in SQL Platforms such as Sybase; MS; DB2;
Informix.

----

Example 2 (assume SERIALIZABLE):
...

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?

I agree that your description is correct for the MV-non-CC+2PL side.

I reject your description for the SQL Platform such as Sybase side, because as explained above, and many times in other threads, you are simply [Straw Man] applying MV-non-CC+2PL mindset to the SQL Platform side that does not operate ala the MV-non-CC+
2PL anti-method. Like a prostitute who has syphilis, attempting to tell a virgin that she too has syphilis. This is a consistent pattern in our discussions.

So I have to give you SQL Platform operation, yet again. Please proceed with your planned benchmarks on Sybase vs PisteGres, so that you obtain some experience in the real world systems [not materialised fantasy] that we have had for forty years. Feel
free to read the manuals and the Sybase docs that I linked. Feel free to ask specific Sybase questions in the /Questions about Sybase/ thread. No discussion, answers only.

---------------------
-- Operation --
---------------------

First, we need to understand the operation of an SQL Platform.

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.

1. [T1] and [T2} do not do anything (other than submit SQL to the server). This is not a pedantic correction. It is important that you understand the vast difference between
__ MV-non-CC: a herd of programs competing with each other (each connection causes a new program instance on the “server” machine) wherein your terminology is correct
____ https://www.softwaregems.com.au/Documents/Article/Oracle%20Circus/Oracle%20vs%20Sybase.pdf
____ page 2 Process Non-Architecture LHS
____ replace [Oracle] with [PukeGross]

__ vs

__ SQL Platform: a single process on the server machine, with say 4 execution threads, that *serve* say 200 connections, wherein the server maintains 1 internal *context* per connection (plus 1 internal context per Worker). Nothing in the Sybase server
is affected by a connection. The server maintain all internal data structures itself, and makes its own scheduling decisions (*context switches*) based on all the activity at any given moment.
____ So [T1][T2] are Connections, not threads; not processes, as such, they can do nothing re server internal resources.
____ https://www.softwaregems.com.au/Documents/Article/Sybase%20Architecture/Sybase%20ASE%20Architecture.pdf
____ page 10 Threaded Execution, Task Context, per Connection (bottom right) ____ DNPS = default network packet size, usually 512 bytes
______ one incoming; one outgoing; and one being built
____ STLC = session tempdb log cache, usually 2K
______ tempdb is a real db for all temporary tables (#MyTable), that can be configured and optimised, same as a normal db
____ Stack size = usually 64K
____ SG = stack guard size, usually 8K
____ the LockChains are depicted below the Lock Manager function

There is no queue re locks (there is a high-performance method for scheduling, which for simplicity sake, can be perceived as a queue, but that is a separate thing).

There is a *lock-chain*. Locks are held on resources, not on Connections.
__ The MV-non-CC+PL notion of separate programs (1 per connection) holding locks, which are not on the resource but the connection, is stupid. Heavily indoctrinated stupid. Of course, that level of stupid can’t be helped, because it is based on the
schizophrenic notion that the resource is a stale offline version, that does not actually exist in reality at that moment, that will magically exist if and when it COMMITs or it rolled back.

2. If we are to resolve this, we need to be more precise and identify the *type* of locks (which is where the conflicts or not will be u(a) understood, and (b) resolved. Which is why I provide a *Resolution* table in my Sybase Lock Manager doc. Or else
read the Sybase manuals.

3. I have tried to explain that it is not locking, not number of locks, not even lock types, that are relevant, but lock duration. I appreciate that it is LEVELS of technical advancement beyond MV-non-CC+2PL, and quite foreign to that mindset. But it
must be asserted. Because it is a real world fact.
__ EoT means End of Transaction, a COMMIT

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

To maintain a viable exchange, here I am not asserting the spee/slowness issue, which would wipe out the problem, I am maintaining the academic example, for understanding.

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

Let’s say Row[Tom] is on Page[Px].
__ at Time[2]
____ Connection[T1] obtains a Shared-Page-Lock on Page[Px], until EoT
__ 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
____ Connection[T2] is in TaskStatus *sleep*, in LockStatus *lock-wait*

At time (4), T1's request for a write-lock is also denied, pending T2's

request (w/w conflict).

Definitely not. Thinking in terms of “w/w conflict” is primitive, idiotic [not you but MV-non-CC+2PL you are indoctrinated in], and has no bearing on a real Ordinary Lock Manager. Erase that filth from your mind, because it is fifty years behind
the technology, and try to understand a real Lock Manager. You will not be able to understand a real Lock Manager while holding onto the MV-non-CC+2Pl mindset.

We do not have “w/w conflicts”, or any similar or equivalent thing. We do not sing the Stonefreaker Mantra. Only knuckle-dragging imbeciles have such primitive things, to resolve the conflict they sing that they do not have, but as evidenced, they
actually have. And yes, the freeware world is full of them.

We wait on a resource, access to which is blocked.

For further detail, refer to the P&T Locking manual, or my Sybase Lock Manager doc, page 2, Resolution table:
____ https://www.softwaregems.com.au/Documents/Article/Sybase%20Lock%20Manager/Sybase%20Lock%20Manager.pdf

__ at Time[4]
____ Connection[T1] lock on Page[Px] is escalated [change of lock-type] to Update[Intent]-Page-Lock (succeeds), and thence to Exclusive-Page-Lock (succeeds)
____ Connection[T1] UPDATE, which requires Exclusive-Page-Lock on Page[Px], proceeds
__ at Time[5]
____ Connection[T2] COMMIT cannot occur, because the little fellow is asleep in *lock-wait*
__ at Time[6]
____ Connection[T1] COMMITS, and its locks are released
____ Connection[T2] lock is now at the head of the LockChain on Page[Px]
__ at Time[7]
____ the scheduler will find Connection[T2] is no longer in lock-wait, context-switch it in, and execute it in a thread
____ Connection[T2] Update[Intent]-Page-Lock on Page[Px] is escalated to Exclusive-Page-Lock (succeeds)
____ Connection[T2] UPDATE which requires Exclusive-Page-Lock on Page[Px] proceeds
____ Connection[T2] COMMIT proceeds, which releases it locks

Sybase records T1->T2 (T1 depends on T2 to

release a lock). The concurrency control system detects a cycle in the dependency graph,

We do not have a “concurrency control system” which is a MV-non-CC+2PL artefact. Straw Man again. We have a regular Lock Manager.

We do not have “dependency graph” or the like. That is only required for imbeciles that can only think in terms of single-threaded processing. Absolute droolers.

There is no “cycle”, with or without the “dependency graph” to determine a “cycle” from. We do have LockChains, that are already ordered per resource; determination of blocks (not “conflicts”) is pedestrian; determination of deadlocks is
pedestrian. Everything in the universe exists in a natural hierarchy, the LockChains are a perfect hierarchy. Determination is not an operation, it is already defined [exists] in the data structure.

In this case, the locks have been resolved. Both Connections[T1][T2] have completed without event.

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

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

--------------------------------------------------
-- Example Pathetic Operation -- --------------------------------------------------

1. Note that you have given a good example for an MV-non-CC+2PL scenario, not an SQL Platform with Ordinary Locking since 1984.
2. Note that it does deadlock.
3. Note that that is why we can say, categorically, that MV-non-CC+2PL program herds:
__ a. lock themselves silly, and
__ b. produce far more deadlocks (that they say cannot happen) than SQL Platforms with Ordinary Locking since 1984.
4. If you understand the abject hysteria of multiple offline stale versions of rows, you will understand that statement [3] more deeply.

----

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

Yes.

unless some mechanism were in place to prevent
that (and there is).

As detailed in my Transaction Sanity doc.

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.

Check Predicate Locks.

FYI
When Sybase detects a [true] deadlock (false deadlocks such as in PissGress cannot happen):
a. it retries 5 times (can be configured),
b. rolls back the connection that has the lower cpu usage
__ intending that the connection that has done more work and holds more locks proceeds to completion, and thus releases more locks for all
__ whereas rolling back the connection with more cpu usage would result in more locks being released immediately, but more work overall subsequently
c. alerts the client with Error 1205, so that it can re-submit the Transaction (execute sproc)

----

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”, it is one logical category: the effects that should not happen, but that do happen.
<<
Let’s call that category FalseDeadlocks, because even the [b] reported deadlocks are false, not caused by the app code, but internal.

2.

it's not possible for the two transactions to be waiting upon each
other

Yes, if both are reading only.
No, in the real world, they are not Transactions (SELECT does not require a Transaction)
Yes, in the fantasy of “MVCC”, it cannot happen.
No, in the materialised fantasy of MV-non-CC+2PL, the weird and contradicted realm of the asylum, it does happen.

3. “Kill”.
I hope you mean rolled back. If PoopGres kills a task due to internal incapacity and incontinence, or due to a true deadlock, that is stupid and wrong. It should roll the task back and allow it continued execution without interference.

----

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

I disagree, as per explanations above.

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.

Additionally ...

The notion of “interleaved operations” is plain stupid, self-contradictory. The Transaction is Atomic. When PissedGres breaks it up, such that interleaving is possible, it destroys the Atomicity that it is required to preserve. And then fails;
fails; fails in re-constituting the Atom that it should not have broken up. Therefore it has FalseDeadlocks (internal inability to resolve itself), in addition to true deadlocks (caused by app code). You have provided a great example in academic terms,
the links cited provide evidence.

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

The two are totally different in both operation and on resource management, separate to the fact that PukeGres is materialised fantasy and separate to the fact that it performs like an elephant in the Alps. The results are not the same, and they cannot
be expected to be the same. You are providing an academic scenario, and yes, we can discuss on that basis, but no, we cannot have resulting expectations on that basis. This is the same insanity as in the /Stored Proc for OLTP Transactions/, wherein you
cannot separate theoretical operation from implementation operation.

Think about this. No one in their right mind would expect the result in Sybase and the result in Oracle, to be the same. Sure, theoretically, they *SHOULD BE* the same. So what. It isn’t real. in the real world, no one in their right mind would
expect the same result from a race horse as they would from a blind elephant.

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

Ok, accepted.

So now you are flipping, yet again, and you accept this:

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

----

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.

False.

First, it happens often, far more than the heavy marketing admits, more precisely, than it declares “can never happen”.

Second, when the users chase the developers, the developers chase the DBAs, therefore the DBAs have to deal with it. Due to ignorance re the causative problem, they fiddle and fart around with the effects, changing the data structures that contain the
tables; changing the few parameters that can be changed

Third, the DBAs will impress upon the developers that rollbacks and deadlocks and serialisation failures are caused by code, that there are many low-level non-SQL ExtraPoopGress methods to reduce such occurrences [but create new deadlocks], and thus the
developers are forever changing code. This in additional to the normal forever-changing-code because PoopDePlopGres is forever changing with each new release, demanding code changes.

The horrendous and hilarious problem when perceived by those who use a SQL Platform, which is (a) SQL compliant, and (b) backward compatible, wherein we do not even think of changing code, and we do not have low-level primitives to mess with the internal
lock (Lock Manager or 2PL).

----

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

Your result answer is correct. But not for those reasons. I have explained in detail the real reasons.

----

5. Does this difference matter to the DBA? Probably yes: under the same workload, PostgreSQL might rollback

And false-deadlock.
And serialisation fail.
All of which is marketed as “not possible”, fraudulently.

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

Of course.

The more write-heavy the
concurrent workload, the more PostgreSQL is penalized

Not penalised (which implies an external authority), buts pig-poops itself, vomits and then eats its own vomit, because the entire problem is self-created, and they sing the Mantra loudly, while operating contra to it, in a feverish attempt to resolve
the unresolvable, and it finally expies, but produces a false message implyint that the non-SQL code has a problem.

compared to
a locking-based system. This seems what Uber has discovered the hard
way.

They experienced the fraud that was perpetrated on them, that the marketing is false; the Mantra is false, that the reality is PoopDePooGress poops itself, that OLTP Is simply not possible, even after low-level non-SQL code changes.

----

The more write-heavy the concurrent workload, the more PostgreSQL is penalized
compared to a locking-based system.

That is the most important thing that you have said today, perhaps all year. Congratulations.

Now you are *starting* to realise what I have stated from ten years ago, and as detailed in the last two months in several threads on c_d_t, and as summarised in my Transaction Sanity doc. Which you have been arguing against (up to now), stating the
opposite, that MV-non-CC systems are better suited to OLTP blah blah.

Evidenced fact is, contrary to the academic postulations and hundreds of academic papers (filth), PoopGres is not even remotely capable of OLTP.

Congratulations are in order, because you have now reached this realisation via an academic path. I await your paper that declares it. You will have to declare a set of definitions for Standard terms right after the abstract.

----

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.

Of course.

----

So, PostgreSQL might have an edge, if any, in prevalently read-only workflows, because reads do not acquire locks.

Academically, hypothetically, yes.
In reality, No.
Because a herd of 100 or 100 un-architected processes written by a bunch of ignorant rabble that believes in fantasy, that fight over one set of resources (no versions for read-only), perform 2 or 3 orders of magnitude slower than a single process that
is beautifully architected by PhD level engineers with hundreds of patents, and has forty years of maturity.

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

Yes.
At millisecond speeds.
5 millisecs plus the overhead of 0.1 millisecs is still 5 millisecs.

So, it's not clear to me who the
winner would be.

Well, you are edging slowly towards reality. Next task is your benchmark, wherein you will obtain hard, cold evidence. But if you stop singing hymns that are false, and you inspect the operations of actual Sybase (instead of projecting your Straw Man
MV-non-CC+2PL ideology onto it), and you study the Architecture docs I have given (as well as the manuals if you so desire), it is possible to form logical conclusions, such that you will not be paralysed with shock when you run the benchmarks.

Tony Andrews, a famous Oracle guru, tried the same nonsense with me, and we ran benchmarks. He never got over the results. Ten years on, he is still suffering from depression. Severe changes to the internal belief system, is not to be taken lightly.

How do you justify preferring Sybase (locking) over

[continued in next message]

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

Finally, the main point is this. Your academic exercise can only be contemplated because you have broken up the Atom, the Atomic Transaction. That is the limit of my explanations and entertaining your academic exercise. I will not discus it further.

----

Further, it would be silly to argue against it, especially to argue /from/ the manuals. If you push it, the explanation will break on precision, but the sense, the overall explanation of ESCALATION that I gave, will not change ... you will just feel
better because some things that were uncertain in your mind become certain in your mind, which has nothing to do with reality.

MC-non-CC systems have a stupid primitive 2PL, you need to learn about the operations of a real Ordinary Lock Manager, such as ESCALATE and PROMOTE (which you are doing, very slowly). But it should be from a proper course or from real world experience (
benchmark !!!), not from the manuals. It is not possible to learn a new product via the product manuals, you will form a skewed understanding. I am sure you know this. It is not possible to understand race conditions from an abstract notion, you need
a benchmark. I have done hundreds, therefore I make declarations from a rock-solid body of experience, which is why I produce docs that explain things that the manuals cannot.

I will not argue for or against the content in the manuals, but I will give you this guidance, if only because your reference it:
§1.5 Table
is Lock Sufficiency, horribly incomplete for our discussion, because it gives only the genii (three) of locks (eg. “shared”), not all 66 LockTypes; the species (eg. Shared-Intent; Shared-Page; Update-Intent). Most people would interpret a “shared�
� lock as the one that is held fleetingly for read operations. A Shared-Page-until-EoT is not at all the same.

§1.4 Table
is Lock Compatibility), again five genii, not species.

Further, while Lock Sufficiency & Lock Compatibility are explained, badly, Lock Escalation is not explained at all. Therefore, I produced something that the Sybase community needed, decades ago, the Sybase Lack Manager PDF. The entire diagram on page 2
is Lock Escalation.

No, it is not a full text explanation, yes, it is a basis for discussion (that has not changed in 20-something years). Sorry, all my docs are dense, as declared, they are memory tags, the final page of *all* lecture notes containing *all* lectured
points, to trigger the memory of the course in the reader’s mind. It cannot be readily understood by someone who is new to Sybase or a Lock Manager. I repeat, the 2PL academics know and love is filth, you have to get that out of your mind, in order
to even begin to understand a real Lock Manager, that the real world has had since 1984.

At least, this is my understanding from the compatibility table
at §1.5 in ASE's Locking and Concurrency Control guide.

If you really mean §1.4 Lock Compatibility (not §1.5), that applies to [T2} at Time[3].

----

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

The [old] “above” in the previous post does not count, as explained in the new “above” in this post.

For the rest. If you argue this, it boils down to the millisec or microsec difference between Time[3] and Time[4]. The sequence of UPDATE statements can be switched, obtaining different results, obviously, but in any case, it is good for abstract
understanding only. It does not portray the real world

I won’t discuss this further, it is an academic example for understanding, using hysterically stupid Parts of an Atom, while denying the Atomicity of the Atom. I especially won’t argue with a novice who reads manuals and obtains “certainty” from
such an act. Feel free to run 3 isql sessions and obtain hold, cold evidence, at snail speed, or better yet, a benchmark to understand race conditions, at normal human speed. Try many different sequences.

----

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.

Ok

----

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.

Yes it is. In both Examples, you provide evidence that PissGriss deadlocks or “deadlocks” and then rolls back, where Sybase does not. In the academic scope and discussion, it is certain. But I still would not make grand declarations about how that
would be realised in the real world, until I have a benchmark. The cited cases certainly provide evidence that PoopGress plops itself when it should not, rather than a specific instance of some abstract example or this abstract Example.

----

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.

1.
See, you are doing it again. You have this idiotic academic notion that you can have a set of Transactions that will be executing ala a mystical mythical mysterious “Schedule” that does not exist in Reality. And worse, second, that you can apply it
to Sybase and get one set of results, and then apply it to PukeGress and get the same result. Third, that if the results are different, woohoo there is an error. Take a breath. Have a second coffee. No such thing exists. All three levels are false.
Because the first is false. Now go and do something useful with your life, create something in the world, instead of fantasising about something is not in the world.

THERE IS NO SCHEDULE, ACCORDING TO WHICH YOU CAN ENTERTAIN SUCH EXPECTATIONS.

2. YOU ARE STILL NOT GETTING IT< YOU CANNOT BREAK UP THE ATOM.

The whole abstract exercise is based on Atomic Transactions whcih you have broken up into Parts. I entertained that and explained some, in order to assist your incremental understanding. In the normal case, of Atoms NOT being spilt up, your examples
simply do not exist. No more entertainment. Any further discussion must be real, any Transaction must be Atomic. Refer to my Transaction Sanity doc.

3. YOU ARE STILL NOT GETTING -- LOCK DURATION --.

This is why people in the real world run benchmarks. Run 100 isql sessions that have (a) no intentional deadlocks. Then (b) that say 10 of 100 have intentional deadlocks against the 90 that don’t. Then (c) 20 of 100 intentional deadlocks. You will
slowly realise that because each Transaction executed in millisecs, they virtually never block. Then run 200 isql sessions with (a)(b)(c). You might get a few deadlocks. And so on.

Now do the same on PlopPlopGross. Seconds and tenths instead of millisecs. Oooooo, we have many deadlocks. Not because of “different behaviour” but because the herd is pathetically slow.

It does not prove that PlopPlopGross produces “errors” where Sybase does not (that is “true” at the denial-of-reality, abstract level, only), it proves that you can’t compare a horse with a blind elephant; or an Architected geunine server with
a herd of stupid programs, you can’t run such a race and expect the same results. The expectation is stupid. It implies that a horse and a blind elephant are comparable.

Even though I know that PlopPlopGross is a mountain of pickled and curated pig poop, I will not be drawn into making a declaration such as you have concluded, because the basis of such conclusion is a stupid abstraction, not a real world determination.

----

That means that Sybase had to roll back
a transaction to prevent incorrect behaviour.

It is not “incorrect behaviour”, it is not an “error”, it is an event. A fleeting event. That may happen in one Sybase benchmark and not another Sybase benchmark.

The notion of a fixed “Schedule”, and second, an expectation of fixed results, is hysterically absurd. Ok, at snail speed, you can only try two or three competing isql sessions. When you get to operating at human speed, and perform some benchmarks,
you might notice Sybase is multi-threaded, there is no “schecdule” which implied single-threaded operation.

Of course, a brain-dead herd of programs, written by schizophrenics, who try desperately to materialise the collective fantasy of multiple offline stale versions, who have no concept of multi-threading, have to “serialise”, and for that they need a
single-threaded “schedule”. Both the “serialisation” and the “schedule” and the breaking up of Atoms that should not be broken up, are hysterically stupid concepts that exist only in asylum of academia. Do not impose that filth on us.

----

*and* if the [PG] server schedules those transactions *the same way*

How on earth are you going to get that to happen ??? It is 100% pure insanity. As an imagination, for academic discussion only, fine. When you imagine that imagination to be real, you cross the line into insanity.

We know (science; knowledge) that if an amino acid were bombarded with a trillion amino acids for 42 million years, there is a 1 in 10^170 chance that it will form into a protein. Imagining that it COULD happen is like believing in the tooth fairy,
stupid enough, but thinking that it ACTUALLY happened, that that amino acid DID evolve into a protein, and thence into a life form, and thence into a monkey, and thence into a rational spirit being, is hysterically insane. But it is the propaganda, on
every media channel, 24 hours by 7 days, every week, including Christmas, that you are indoctrinated into. So you feel quite comfortable; it is a familiar “thought” process, properly understood for what it is, indoctrination, imagining things that
do not exist, and then thinking that it actually exists, that it actually can happen.

----

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:

I don’t know why you try, it is already proved, academically, by you, in the first two examples.

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.

As an academic example, sure.

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.

In academia, they deny the Atom; they split the atom into fragments, and they live and breathe the fragments.

I agree with you that this is bad.

So please, in order to establish your presence in the real world, that academia has some truth to offer, do something about the mountain of false marketing re “MVCC”, the hilarious self-contradicting Mantra; the false statements in the manuals.
Tell the world that Stonefreaker is a disgusting drug addict, his entire “MVCC” notion, and everything built by his cultists, is false. As evidenced in all “MVCC” herds of programs, which are desperately trying to materialise insane fantasy.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

The project was one calendar month, which included tight scheduling of all resources from both SG and the customer; all scripting (we have a full set, just minor mods for local issues); all testing on test servers; proper allocation on the SAN; and the
execution of the rebuild on the Production server itself, in a single Easter weekend (four tranches; ten hour days; scripts left running overnight; check and minor fix-its the next morning).

These are extracts from the executive report (the full report is 26 pages; the appendices [Before vs After, one line per index] is 56 pages), I had to be political or “soft” in order to minimise damage to the on-site DBA’s credibility. For the
following, read DEADLOCKS:
_ application stability
___ (executive perception: causes at least the few lost transactions, but when the contention piles up (lock storm), rather than running around fixing each problem, the server has to be restarted, somethings that Sybase customer never have to do [all the
servers I have looked after run for at least a month, downtime for scheduled purposes only] )
_ data contention
___ (that is the way the staff perceive it)

__ https://www.softwaregems.com.au/Documents/Reference/Reference%20T%20A.pdf
__ https://www.softwaregems.com.au/Documents/Reference/Reference%20T%20B.pdf
__ https://www.softwaregems.com.au/Documents/Reference/Reference%20T%20C.pdf

a. The above gives enough detail for a full executive level report, with pretty charts (extracts only shown).

b. If you want to inspect the index level issues, let me know, I will give you the appendices. Given your love for argumentation, no doubt I will live to regret it.

c. For server rebuilds, I guarantee at least 10 times improvement in speed, but I actually deliver much more. Speed (Before vs After) is measured two ways: the standard server monitoring in Sybase; and 20 nominated queries, their worst queries.

__ For the nominated queries, all ran 10 to 100 times faster. There were a few that ran 1,000 times faster, but mention of that caused a few political problems for the DBA, so I did not state it in the executive report.

__ For monitoring server performance, which really is throughput, which was both an ongoing requirement during the project, and a qualitative metric to demonstrate the guarantee and approve payment, the raw data was the standard Sybase server monitoring,
which is massive. Our SG scripts process all that and produce a straight-forward report, such as this (for a different customer). I have given you this before, with notes on how to read it, with no response from you. Feel free to ask specific
questions:
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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.

The second over-arching issue is this business of redefined terms, which guarantees laboured communication with real world implementors, as evidenced, an maintains you inability to produce anything intended for it. In case it needs to be repeated, I don�
��t have private definitions, I conform to the absolute definitions.

Finally, the main point is this. Your academic exercise can only be contemplated because you have broken up the Atom, the Atomic Transaction. That is the limit of my explanations and entertaining your academic exercise. I will not discuss it further.

----

Further, it would be silly to argue against it, especially to argue /from/ the manuals. If you push it, the explanation will break on precision, but the sense, the overall explanation of ESCALATION that I gave, will not change ... you will just feel
better because some things that were uncertain in your mind become certain in your mind, which has nothing to do with reality.

MC-non-CC systems have a stupid primitive 2PL, you need to learn about the operations of a real Ordinary Lock Manager, such as ESCALATE and PROMOTE (which you are doing, very slowly). But it should be from a proper course or from real world experience (
benchmark !!!), not from the manuals. It is not possible to learn a new product via the product manuals, you will form a skewed understanding. I am sure you know this. It is not possible to understand race conditions from an abstract notion, you need
a benchmark. I have done hundreds, therefore I make declarations from a rock-solid body of experience, which is why I produce docs that explain things that the manuals cannot.

I will not argue for or against the content in the manuals, but I will give you this guidance, if only because your reference it:

§1.5 Table
is Lock Sufficiency, horribly incomplete for our discussion, because it gives only the genii (three) of locks (eg. “shared”), not all 66 LockTypes; the species (eg. Shared-Intent; Shared-Page; Update-Intent). Most people would interpret a “shared�
� lock as the one that is held fleetingly for read operations. A Shared-Page-until-EoT is not at all the same.

§1.4 Table
is Lock Compatibility), again five genii, not species.

Further, while Lock Sufficiency & Lock Compatibility are explained, badly, Lock Escalation is not explained at all. Therefore, I produced something that the Sybase community needed, decades ago, the Sybase Lack Manager PDF. The entire diagram on page 2
is Lock Escalation.

No, it is not a full text explanation, yes, it is a basis for discussion (that has not changed in 20-something years). Sorry, all my docs are dense, as declared, they are memory tags, the final page of *all* lecture notes containing *all* lectured
points, to trigger the memory of the course in the reader’s mind. It cannot be readily understood by someone who is new to Sybase or a Lock Manager. I repeat, the 2PL academics know and love is filth, you have to get that out of your mind, in order
to even begin to understand a real Lock Manager, that the real world has had since 1984.

At least, this is my understanding from the compatibility table
at §1.5 in ASE's Locking and Concurrency Control guide.

If you really mean §1.4 Lock Compatibility (not §1.5), that applies to [T2} at Time[3].

----

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

The [old] “above” in the previous post does not count, as explained in the new “above” in this post.

For the rest. If you argue this, it boils down to the millisec or microsec difference between Time[3] and Time[4]. The sequence of UPDATE statements can be switched, obtaining different results, obviously, but in any case, it is good for abstract
understanding only. It does not portray the real world

I won’t discuss this further, it is an academic example for understanding, using hysterically stupid Parts of an Atom, while denying the Atomicity of the Atom. I especially won’t argue with a novice who reads manuals and obtains “certainty” from
such an act. Feel free to run 3 isql sessions and obtain hold, cold evidence, at snail speed, or better yet, a benchmark to understand race conditions, at normal human speed. Try many different sequences.

----

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.

Ok

----

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.

Yes it is. In both Examples, you provide evidence that PissGriss deadlocks or “deadlocks” and then rolls back, where Sybase does not. In the academic scope and discussion, it is certain. But I still would not make grand declarations about how that
would be realised in the real world, until I have a benchmark. The cited cases certainly provide evidence that PoopGress plops itself when it should not, rather than a specific instance of some abstract example or this abstract Example.

----

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.

1.
See, you are doing it again. You have this idiotic academic notion that you can have a set of Transactions that will be executing ala a mystical mythical mysterious “Schedule” that does not exist in Reality. And worse, second, that you can apply it
to Sybase and get one set of results, and then apply it to PukeGress and get the same result. Third, that if the results are different, woohoo there is an error. Take a breath. Have a second coffee. No such thing exists. All three levels are false.
Because the first is false. Now go and do something useful with your life, create something in the world, instead of fantasising about something is NOT in the world.

THERE IS NO SCHEDULE, ACCORDING TO WHICH YOU CAN ENTERTAIN SUCH EXPECTATIONS.

2. YOU ARE STILL NOT GETTING THE -- ATOM -- YOU CANNOT BREAK UP THE ATOM.

The whole abstract exercise is based on Atomic Transactions which you have broken up into Parts. I entertained that and explained some, in order to assist your incremental understanding. In the normal case, of Atoms NOT being split up, your examples
simply do not exist. No more entertainment. Any further discussion must be real, any Transaction must be Atomic. Refer to my Transaction Sanity doc.

3. YOU ARE STILL NOT GETTING -- LOCK DURATION --

This is why people in the real world run benchmarks. Run 100 isql sessions that have:
a. no intentional deadlocks.
b. Then say 10 of 100 have intentional deadlocks against the 90 that don’t. c. Then 20 of 100 intentional deadlocks.
__ You will slowly realise that because each Transaction executed in millisecs, they virtually never block. d. Then run 200 isql sessions with (a)(b)(c). You might get a few deadlocks. And so on.

Now do the same on PlopPlopGross. Seconds and tenths instead of millisecs. Oooooo, we have many deadlocks. Not because of “different behaviour” but because the herd is pathetically slow.

It does not prove that PlopPlopGross produces “errors” where Sybase does not (that is “true” at the denial-of-reality, abstract level, only), it proves that you can’t compare a horse with a blind elephant; or an Architected genuine server with
a herd of stupid programs, you can’t run such a race and expect the same results. The expectation is stupid. It implies that a horse and a blind elephant are comparable.

Even though I know that PlopPlopGross is a mountain of pickled and curated pig poop, I will not be drawn into making a declaration such as you have concluded, because the basis of such conclusion is a stupid abstraction, not a real world determination.

----

That means that Sybase had to roll back
a transaction to prevent incorrect behaviour.

It is not “incorrect behaviour”, it is not an “error”, it is an event. A fleeting event. That may happen in one Sybase benchmark and not another Sybase benchmark.

The notion of a fixed “Schedule”, and second, an expectation of fixed results, is hysterically absurd. Ok, at snail speed, you can only try two or three competing isql sessions. When you get to operating at human speed, and perform some benchmarks,
you might notice Sybase is multi-threaded, there is no “schedule” which implied single-threaded operation.

Of course, a brain-dead herd of programs, written by schizophrenics, who try desperately to materialise the collective fantasy of multiple offline stale versions, who have no concept of multi-threading, have to “serialise”, and for that they need a
single-threaded “schedule”. Both the “serialisation” and the “schedule” and the breaking up of Atoms that should not be broken up, are hysterically stupid concepts that exist only in asylum of academia.

Do not impose that filth on us.

----

*and* if the [PG] server schedules those transactions *the same way*

How on earth are you going to get that to happen ??? It is 100% pure insanity. As an imagination, for academic discussion only, fine. When you imagine that imagination to be real, you cross the line into insanity.

We know (science; knowledge) that if a bunch of amino acid were bombarded with a trillion amino acids for 42 million years (setting aside the obvious fact that that first bunch of amino acids would be dead, in order to prevent ruin of the story), there
is a
__ 1 in 10^170
chance that it will form into a protein. Imagining that it COULD happen is like believing in the tooth fairy, stupid enough, but thinking that it ACTUALLY happened, that that amino acid DID evolve into a protein, and thence into a life form, and thence
into a monkey, and thence into a rational spirit being, is hysterically insane. But it is the propaganda, on every media channel, 24 hours by 7 days, every week, including Christmas, that you are indoctrinated into. So you feel quite comfortable; it is
a familiar “thought” process, properly understood for what it is, indoctrination, imagining things that do not exist, and then thinking that it actually exists, that it actually can happen.

----

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:

I don’t know why you try, it is already proved, academically, by you, in the first two examples.

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.

As an academic example, sure.

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.

In academia, they deny the Atom; they split the atom into fragments, and they live and breathe the fragments.

I agree with you that this is bad.

So please, in order to establish your presence in the real world, that academia has some knowledge to offer, that is beneficial, do something about the mountain of false marketing re “MVCC”, the hilarious self-contradicting Mantra; the false
statements in the manuals. Tell the world that StoneBroken is a disgusting drug addict, his entire “MVCC” notion, and everything built by his cultists, is false. As evidenced in all “MVCC” herds of programs, which are desperately trying to
materialise insane fantasy.

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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}

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

d. The tried and tested, decades-old method, that we have had from Day One, beloved by all the old Sybase hands: sp_sysmon. Simple, character based, zero overhead, can be used for any kind of monitoring: here a benchmark; there some lock problem
diagnosis; etc. For Production servers, I obtain one per hour (so that the counters do not overflow, every 30 mins if they do).

Before the trial, to clear the counters, run:
__ sp_sysmon begin_sample
After the trial run:
__ sp_sysmon end_sample
and collect the report. That will give the activity in monitor stats, for the duration. It looks like this:
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/SEQUOIA_120806_sysmon_09

//
The YNHH monitor reports that I have linked in the past, and here again below, are from their Production server, ie. 24 sp_sysmon [above] reports per day. Which are then processed by our **Sysmon Processor** scripts, to produce any kind of comparison or
trend. It is massively useful for problem diagnosis, and it produces .csv for erection of pretty charts in Numbers/Excel.

This one is a Before/After report of a long-advised SAN reconfiguration, that finally happened, comparing two daily figures (the last column is Delta). Noteworthy again, the problem was **Lock Duration**, not number of locks, not types of locks, not the
app code (which is 3rd Health System, that cannot be changed or affected). Look at the § Lock Manager.
__ https://www.softwaregems.com.au/Documents/Article/Sysmon%20Processor/Sysmon%20Processor%20Eg%20Date%20Delta.pdf
//

12 ----

- I have added an artificial delay in T1 between select and update.

As explained above, if you are simulating a delay due to user interaction, the WAITFOR must be after the COMMIT, before the next BEGIN TRAN.

Therefore, in this stress test, you are testing a different thing, a simulated LockWait.

It is not a true test (ie. good enough to publish), but it is fair enough to prove/disprove the specific thing you question.

Still, no deadlocks,

No surprise. As I expected. My declarations re ASE and real-world operation are proved.

13 ----

although I get several 5s timeouts. Is that
because a transaction is rolled back if it's waiting for too long?

a. Rollback
No. A Rollback causes an Error 1205 to be RAISED in the client, and you will be well and truly notified. Ruby will throw an exception of some sort.

b. Deadlock
ASE retries automatically 5 times [configurable] (not seconds) before declaring “deadlock”, so the client-side code does not have to. These are not deadlocks or potential deadlocks, these are LockWaits.

c. LockWait
Yes, of course you have LockWaits, because you coded it. ASE [default] Lock Wait Period is infinite [configurable].

//
Infinite is a basic OLTP design article: eg. you can diagnose locking problems; find and kill the errant spid; correct bad code; etc. Proved as such for forty years. Best to leave it at infinite, do not change it. If you do, it will mess up your
understanding of these issues, and complicate everything.
//

d. TimeOut
There is no timeout in ASE or the client. The timeout must be in Ruby, common in that kind of layer. From the evidence, the timeout period is 5 secs. //Which is not the same as your WAITFOR 0.5 secs.//

e. I don’t know if you will understand this, but I will try. Here again, you might notice, the relevance of Loch Duration. Compare with the previous test (no artificial WAITFOR). Even in such a simple stress test, it is a simple but classic proof
that Lock duration causes this kind of error, that when lock duration is zero, there is no limit to the number of concurrent Transactions.

14 ----

In PostgreSQL:

- you cannot set the isolation level within a stored procedure;

a. That is anti-SQL.
b. That is anti-ACID, you cannot write Transaction “stored procs”.
c. That proves what I said, the “sql” in PG is not implemented as a language.

that
must be done before calling the stored procedure.

From another “language” context.
Same sort of problem, as in older versions, wherein “functions” were “transactional”.

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

ERROR: could not serialize access due to concurrent update

No surprise, I predicted that, and did so long ago, from the only study of its “MVCC” and “ACID” implementation.

The surprise is that it cacks itself at just 2 threads.

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

Same here. Good stress test, even at default and with Ruby, it exposed what you wanted to expose.
__ Sybase blows the doors of PlopPlopGres.
__ MickeyMouseGres cannot perform its much-marketed “MVCC”, the Mantra is false, even with MV-non-CC+2PL and a mild load, it cacks itself.

15 ----

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.

a. I don’t sleep with sows, so I can’t help you there, sorry. That is why I don’t make low-level declarations about PG, but I give you links (eg. SO/PG/deadlock) and ask you to confirm.

b. Your conclusion is good. My prediction is incorrect. I expected PG to be better than that.

c. But more than your conclusion. What this has proved to me is, the PG “stored proc” speed is equivalent to ASE manual “snail” speed.

d. If you appreciate that the ASE stored proc test (i) proved what I stated about ASE, (ii) that LOCK DURATION is the issue, and (iii) even with 100 (1,000 ?) “threads”, there are no collisions
__ vs
the PG “stored proc” (assumed to be compiled and Query Optimised to some degree) operating in MV-non-CC+2PL: it (iv) performs incorrectly, and (v) like a snail on the same machine.

16 ----
If you run this stress test again, run some more of your different academic tests, which will provide more learning.

-- 17 --
When you run any set of tests, for each test that you describe, please include the report block at the end:

######################################################################
# $ 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}

Please add whether it is:
__ BEGIN-COMMIT
__ Artificial WAITFOR and period
__ row count or number of iterations (I appreciate in this instance, Balance $1040 is proof)

-- 18 --
In formal benchmarks, and for any test that these may be relevant:
__ sp_sysmon report
__ vmstat report
__ iostats (if disk i/o is being benchmarked)
__ sp_configure (number of threads; etc)
__ machine resources
__ O/S setting that are relevant

Cheers
Derek

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)