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