On Thursday, 17 June 2021 at 05:45:09 UTC+10, Nicola wrote:No, that is not correct.
On 2021-06-16, Derek Ignatius Asirvadem wrote:
Suppose that, for some reason, one needs to update one HotelChain,
changing the value of HotelChain from ABC to H-ABC. If I understand
correctly, you would start by (optimistically) locking the record with
HotelChain = ABC.
Yes.
And leave it locked for the duration, it will be the last deleted.
(optimistically)
Because I know that you guys do not understand Locking, let alone Optimistic Locking, note this this is not an act within that [generic, theoretical] framework. This is a data-level lock (if you have row locking, and a row locking command, you do not have ACID *and* you
have broken a cardinal rule of OLTP).
Let me see whether we are on the same tune. While you are performing the batch transaction we are talking about, another transaction may also
attempt to update the record with HotelChain = ABC (and specifically
update the HotelChain field). Is it correct that this second transaction will be allowed to perform the update, and that first (batch
transaction) will detect at the next commit that the "top" row has been overwritten, and rollback (at least rollback the running ACID
transaction)?
Because that is my understanding of "optimistic". But the rest of your
post, (and the remark above) make me doubt that we agree on this
meaning.
1. Lock top-most <Table>[ <OldKey> ]
You need some column that indicates a data-level lock. Standard columns include CreatedDtm; UpdatedDtm; etc, that can be used for this purpose [eg. set CreatedDtm to TODAY+1 or other fixed value [it is temporal, yes] ). You may have IsDeleted to support SoftDeletes. LetrCOs use the easy one, and say you have Soft Deletes. For clarity, you need a condition [ IsLocked ], and you implement that as indicated in one of the above columns, if not, you have a column [ IsLocked ].
another transaction may also attempt to(Which row is meant by rCLrecordrCY is not clear, but it doesnrCOt matter for the answer: all rows from top to bottom that belong to [ ABC ] are locked.)
(a) update the record with HotelChain = ABC
(b) (and specifically update the HotelChain field)
On Thursday, 17 June 2021 at 08:36:12 UTC+10, Derek Ignatius Asirvadem wrote:--------------------------
In this case, that means:Mistake, sorry, that should be:
-- UPDATE HotelChain.IsDeleted[ ABC ] = 1
---- WHERE HotelChain.IsDeleted[ ABC ] = 0
or
-- UPDATE HotelChain.IsLocked[ ABC ] = 1
---- WHERE HotelChain.IsLocked[ ABC ] = 0
...
It also prevents:
-- UPDATE Hotel.NumStar[]
or
-- UPDATE HotelRoomType.Description[]
Yes, you do *not* have a correct understanding of Optimistic Locking, none of the academics do, they have only the Stonebraker MVCC Kool Aid, and they have been drinking it and circle-jerking while chanting the *Mantra* for decades. As I have tried to explain, Optimistic Locking is completely different to the Straw Man that academics erect as rCLoptimistic lockingrCY. This point (your question) has nothing to do with Optimistic Locking.The term Optimistic Locking and the method (Standard) that it defines, is IBM [360] CICS/TCP from the early 1960rCOs. That is science. That is truth that does not change.
It has to do with how to construct a Batch Transaction (MonthEnd Procedure at a bank or Move OldKey to NewKey).But of course, we canrCOt help touching OLTP structures, because it is an OLTP environment, and that is the over-arching requirement.
On Thursday, 17 June 2021 at 08:36:12 UTC+10, Derek Ignatius Asirvadem wrote:--------------------------
In this case, that means:Mistake, sorry, that should be:
-- UPDATE HotelChain.IsDeleted[ ABC ] = 1
---- WHERE HotelChain.IsDeleted[ ABC ] = 0
or
-- UPDATE HotelChain.IsLocked[ ABC ] = 1
---- WHERE HotelChain.IsLocked[ ABC ] = 0
...
It also prevents:
-- UPDATE Hotel.NumStar[]
or
-- UPDATE HotelRoomType.Description[]
Yes, you do *not* have a correct understanding of Optimistic Locking, none of the academics do, they have only the Stonebraker MVCC Kool Aid, and they have been drinking it and circle-jerking while chanting the *Mantra* for decades. As I have tried to explain, Optimistic Locking is completely different to the Straw Man that academics erect as rCLoptimistic lockingrCY. This point (your question) has nothing to do with Optimistic Locking.The term Optimistic Locking and the method (Standard) that it defines, is IBM [360] CICS/TCP from the early 1960rCOs. That is science. That is truth that does not change.
It has to do with how to construct a Batch Transaction (MonthEnd Procedure at a bank or Move OldKey to NewKey).But of course, we canrCOt help touching OLTP structures, because it is an OLTP environment, and that is the over-arching requirement.
No, that is not correct.
You need some column that indicates a data-level lock. Standard
columns include CreatedDtm; UpdatedDtm; etc, that can be used for this purpose [eg. set CreatedDtm to TODAY+1 or other fixed value [it is
temporal, yes] ). You may have IsDeleted to support SoftDeletes.
LetrCOs use the easy one, and say you have Soft Deletes. For clarity,
you need a condition [ IsLocked ], and you implement that as indicated
in one of the above columns, if not, you have a column [ IsLocked ].
The fact that there is such a column, and that contention is
understood and consciously minimised, *is* part of Optimistic Locking,
part of the OLTP Standard.
But this is not an ACID Transaction, it is a Batch Transaction. Of
course, we use the column that is there for OLTP purposes, for the now
Batch purpose.
In this case, that means:
-- UPDATE HotelChain.IsDeleted[ ABC ] = 1
---- WHERE HotelChain.IsDeleted[ ABC ] = 0
or
-- UPDATE HotelChain.IsLocked[ ABC ] = 1
---- WHERE HotelChain.IsLocked[ ABC ] = 0
That prevents both [a] and [b] in this:
another transaction may also attempt to
(a) update the record with HotelChain = ABC
(b) (and specifically update the HotelChain field)
It also prevents any Transaction containing:
-- UPDATE Hotel.NumStar[ ABC ]
or
-- UPDATE HotelRoomType.Description[ ABC ]
or
-- INSERT <AnyTableInHierarchy>[ ABC ]
or
-- DELETE <AnyTableInHierarchy>[ ABC ]
Separate point. That rCLanother transactionrCY sounds suspiciously like another instance of the same transaction, using the same key. That
doesnrCOt happen in the real world because the person who administers
the first instance of changing ABC is one and the same person who
administers the second instance of changing ABC. This sort of
insanity is /also/ prevented by proper GUI design (a Tree with no
circular references).
On Thursday, 17 June 2021 at 19:27:16 UTC+10, Nicola wrote:The /Today+1/ is for temporal tables, wherein a current SELECT does WHERE UpdatedDtm = MAX( UpdatedDtm) less-than-or-equal to today. It is just the standard method or construct to grab only the current row (exclude historic rows). (Whereas a temporal SELECT would grab the particular historic row.) I expect everyone who implements a temporal table [using CoddrCOs temporal definition, not the massive TwiddleDee & TwidleDumb monstrosity] to know that.
On 2021-06-16, Derek Ignatius Asirvadem wrote:As expected.
No, that is not correct.
You need some column that indicates a data-level lock. Standard
columns include CreatedDtm; UpdatedDtm; etc, that can be used for this purpose [eg. set CreatedDtm to TODAY+1 or other fixed value [it is temporal, yes] ). You may have IsDeleted to support SoftDeletes.
LetrCOs use the easy one, and say you have Soft Deletes. For clarity,
you need a condition [ IsLocked ], and you implement that as indicated
in one of the above columns, if not, you have a column [ IsLocked ].
The fact that there is such a column, and that contention isThat starts to sound vaguely familiar.
understood and consciously minimised, *is* part of Optimistic Locking, part of the OLTP Standard.
But this is not an ACID Transaction, it is a Batch Transaction. Of
course, we use the column that is there for OLTP purposes, for the now Batch purpose.
In this case, that means:
-- UPDATE HotelChain.IsDeleted[ ABC ] = 1
---- WHERE HotelChain.IsDeleted[ ABC ] = 0
or
-- UPDATE HotelChain.IsLocked[ ABC ] = 1
---- WHERE HotelChain.IsLocked[ ABC ] = 0
That prevents both [a] and [b] in this:
another transaction may also attempt to
(a) update the record with HotelChain = ABC
(b) (and specifically update the HotelChain field)
Is it because each transaction checks UpdatedDtm and finds that it is
equal to TODAY+1, hence it immediately aborts?
Yes.It also prevents any Transaction containing:
-- UPDATE Hotel.NumStar[ ABC ]
or
-- UPDATE HotelRoomType.Description[ ABC ]
or
-- INSERT <AnyTableInHierarchy>[ ABC ]
or
-- DELETE <AnyTableInHierarchy>[ ABC ]
Such transactions perform the same check, right? And they abort
immediately, I assume, when they find that the top row has been marked
as deleted by the transaction that set UpdatedDtm. Is that correct?
Absolutely. I did not say rCLit cannot happenrCY. It is prevented from happening, by the Transaction standard. That was an explanation, that it is actually prevented at a higher level (in any proper GUI), before the Transaction is called. Therefore rCL/also/rCL.Separate point. That rCLanother transactionrCY sounds suspiciously like another instance of the same transaction, using the same key. That doesnrCOt happen in the real world because the person who administers
the first instance of changing ABC is one and the same person who administers the second instance of changing ABC. This sort of
insanity is /also/ prevented by proper GUI design (a Tree with no
circular references).
It may not happen under normal conditions. But I'd be very worried if
"it cannot happen" relies on correct (human or machine) behaviour.
A system should be resilient to improper and malicious behaviour as
well.
The /Today+1/ is for temporal tables, wherein a current SELECT does
WHERE UpdatedDtm = MAX( UpdatedDtm) less-than-or-equal to today. It
is just the standard method or construct to grab only the current row (exclude historic rows). (Whereas a temporal SELECT would grab the particular historic row.) I expect everyone who implements a temporal
table [using CoddrCOs temporal definition, not the massive TwiddleDee
& TwidleDumb monstrosity] to know that.
The /Today+1/ just makes the affected row (and the hierarchy below
it) invisible to other users, a form of ISOLATION. Heck, we are on
a planet named ACID, we are performing an [I]solation. DonrCOt let that confuse you. If the column is there, use it.
If IsDeleted is there, use it.
Else implement IsLocked, and use it.
Since God carved onto the OLTP stone tablet:
__ THOU SHALT NOT ATTEMPT ANYTHING THAT IS IMPOSSIBLE
yes, every Transaction (both ACID and this one) validates everything
it will attempt, before attempting it, before the BEGIN TRAN. In our
case, HotelChain is first. Because it is caught in the VALIDATE
block, before the BEGIN TRAN, there is no rCLabortrCY or ROLLBACK TRAN.
The corollary to that Commandment is of course:
__ Thous Shalt Not Begin Anything That Will Fail, That Thou Canst Not Complete.
What you guys call rCL2 phaserCY is anti-ACID, it starts a transaction and then fiddles and farts while rambling, meaning time spans between
requests. As per the wiki cesspool article, in the rCLSS2PLrCY that we
have, there is only one rCLphaserCY.
But we donrCOt start that rCLphaserCY until the previous rCLphaserCY of VALIDATE
completes successfully. And we have no time spans in-between
operations.
On Saturday, 19 June 2021 at 17:20:51 UTC+10, Derek Ignatius Asirvadem wrote: Read locksThe previous post is a bit long, and that item was addressed in a few places. In case it isn't clear:
On Saturday, 19 June 2021 at 17:52:18 UTC+10, Derek Ignatius Asirvadem wrote:Sorry, stuffed up yet again. It has been at least 12 years since the last time I explained this.
On Saturday, 19 June 2021 at 17:20:51 UTC+10, Derek Ignatius Asirvadem wrote:
No. This:Read locks__ Read locks outside a Transaction are transient, held for the duration of the read operation
__________________
1. VALIDATE Block
Code:
rCo Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__rCo RETURN on any failure.
rCo Check NOT_EXISTS all rows that must not exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__rCo RETURN on any failure.
rCo Transaction NOT yet started
rCo All locking is transient and very short duration
rCo TransactionLog is not touched
rCo Fall into ...
__________________
2. EXECUTE Block
* BEGIN TRAN
rCo TransactionLog activity commenced for this xact
rCo Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__rCo ROLLBACK & RETURN on any failure.
__rCo the only relevant locking starts here
__rCo if blocked by another xact, you are blocked here (NOT after the first verb below)
__rCo the longest wait is for the longest xact in the system that touches the page
So here we code each SELECT as:
__ IF EXISTS (
____ SELECT ...
______ FROM Hotel
______ WHERE ...
______ { FOR UPDATE | HOLDLOCK } -- platform-specific syntax, rCLbeyondrCY SQL
____ )
__rCo <-[2.1]
rCo Check NOT_EXISTS all rows that must not exist, in order for the
intended rows (INSERT/UPDATE/DELETE) to succeed.
__rCo ROLLBACK & RETURN on any failure.
rCo (INSERT/UPDATE/DELETE)
__rCo ROLLBACK & RETURN on any failure.
rCo COMMIT TRAN
rCo TransactionLog is released.
___________________
3. CLEAN-UP Block
rCo All error handling
rCo ROLLBACK TRAN (if structured, the only ROLLBACK)
rCo <-[3.1]
rCo RETURN (if structured, the only RETURN)
______
Client
There is ordinary interplay between the Client and the Server. The
client may now be a GUI or a webpage or middleware in an app server.
The VALIDATE block [1] is repeated in the client.
how do you ensure that
nothing happens between the instant VALIDATE completes and the instant
BEGIN TRAN is executed?
That duration between instants is:
a. not relevant [assuming you understand the above], and
b. microseconds, because it is between two consecutive steps inside
a contiguous stored proc, which is running as object + plan code
(compiled and a full resource plan established), and
* BEGIN TRAN
rCo TransactionLog activity commenced for this xact
rCo Read all rows that must exist, in order for the intended rows (INSERT/UPDATE/DELETE) to succeed.
__rCo ROLLBACK & RETURN on any failure.
c. harmless because no resources are held.
Batch Transaction
Add:
2.1 Add the data-level lock
___ BEGIN TRAN
___ UPDATE HotelChainIsDeleted[ @HotelChain ] = 1
___ COMMIT TRAN
3.1 Remove the data-level lock (in our example this ois not required, because it is the last row deleted).
___ BEGIN TRAN
___ UPDATE HotelChainIsDeleted[ @HotelChain ] = 0
___ COMMIT TRAN
You may consider the BEGIN-COMMIT TRAN superfluous. No, They are
demanded by the standard. Further, they are anchors for code
investigation, and on the server vis-a-vis monitoring metrics.
1. What you are describing is built on top of the standard locking
mechanism provided by the DBMS.
If you mean the normal ACID Transaction in the full OLTP Context, no.
It is aware of, and implicitly uses the locking in the server
(whatever that is).
If you mean the Batch Transaction, yes. I said so at the start. That
is why I call it a data-level lock, it has to be done in data, we have
no direct access to the locks, and we never do idiotic things such as:
__ LOCK TABLE
OLAP ReportsWhy do you worry about locks in OLAP reports ?
Who cares.
Read locks are extremely short, and transient (held for the duration
of the read operation, not the Transaction). We canrCOt control Read
locks, even implicitly
But still, that is not Optimistic Locking. And in this post, there is nothing about Optimistic Locking.
| Sysop: | Amessyroom |
|---|---|
| Location: | Fayetteville, NC |
| Users: | 65 |
| Nodes: | 6 (0 / 6) |
| Uptime: | 03:56:27 |
| Calls: | 862 |
| Files: | 1,311 |
| D/L today: |
812 files (9,653M bytes) |
| Messages: | 264,528 |