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
> PostgreSQL (MVCC) in a context of almost exclusively read-only
> transactions? Note that if transactions are read-only, versions are not
> created.
First, we do not have Transactions for read-only operations, thus you do have an overhead that we don’t have. I would not suggest that that is equal to the “added” locking we have for read-only operations, because it is minuscule in comparison.
Second, whatever you do produce as a result set is false wrt SQL compliance, whereas ours is true. A brain-dead series of offline stale uncommitted (you have no real implementation of COMMIT) versions of rows is not comparable to a series of true committed online rows at READ COMMITTED. Sure, a new version may not be *created*, but the version taken and used is false, just as when a version *IS* created.
Third, and most important, the problem is in your question. It is stated with a limited scope, that is yours, not mine. I do not justify Sybase over AsylumGres “in a context of almost exclusively read-only [operations]”.
I justify Sybase over AsylumGres for
- OLTP only;
- AND for mixed OLTP+OLAP,
- AND for OLAP only.
Because I prefer reality to materialised fantasy, in all contexts that can be related to the provision of an SQL platform, an SQL and ACID compliant architected server. Refer to the Architecture vs Non-Architecture performance links above.
> Does this assessment satisfy you?
Yes, it is the best I have received from an academic. No fuss; no nonsense; no idiotic argumentation; no dishonest Straw Men (that which is due to indoctrination is excused); no splitting hairs. You are engaging your lineage back to 1492. You are starting to heave (the harsh but non-issuing action that precedes vomiting) at Modernism. Progressively leaving the asylum, in the direction of science.
Cheers
Derek