From Newsgroup: comp.databases.theory

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 insertrCoand the duration of the conflict would be short. Also, one purpose of your template is to avoid deadlocks, which in MVCC cannot happen anyway. In fact, adding explicit locking might introduce the possibility of deadlocks.
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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

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 rCLinterleavingrCY of Atomic operations.

The notion of rCLinterleaved operationsrCY is plain stupid, self-contradictory. The Transaction is Atomic.

Sybase has no rCLinterleaved operationsrCY, 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 --
-------------------------------------------

LetrCOs 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]rCOs 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 -o1.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) rCLdeadlocksrCY, or (c) rCLserialisation failuresrCY,

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.

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

From Newsgroup: comp.databases.theory

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

From Newsgroup: comp.databases.theory

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

From Newsgroup: comp.databases.theory

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

From Newsgroup: comp.databases.theory

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
benchmarksrCoyet): my first attempt has been pretty naive.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2