On Friday, 25 June 2021 at 23:58:38 UTC+10, daniel wrote:
My request
Finally, I accept that there are a number of views on matters such as MVCC, its advocates, and so on.
But it'd be great if the discussion remained clean and free of vitriol. If for no other reason than keeping it safe for viewing at work.
It was written to run in a database configured such that 'snapshot isolation' is disabled and 'read committed snapshot isolation' is disabled.
That is to say that this code has been written for use with a RDBMS that relies on a Lock Manager to guarantee isolation of transactions, not MVCC.
I've written such a stored procedure, and am hoping for feedback as to how closely it aligns with that description.
insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
The code I share below is designed to run on SQL Server. I've tested it on SQL Server 2016, but I've kept the example simple and it should run on most versions.
Finally, I accept that there are a number of views on matters such as MVCC, its advocates, and so on.
-- At this point we know there's a chance that we will
-- succeed in inserting a row.
-- Within a transaction:
-- 1. Re-execute the validation query, but this time acquire
-- Within a transaction:
-- 1. Re-execute the validation query, but this time acquire
-- an update lock while doing so
-- This KEY lock ensures that no other transactions running
-- This is due to the selection of an appropriate transaction
-- isolation level - in this case serializable
guaranteeing the Isolation in ACID.
-- Validation block
-- Transaction block
On Sunday, 27 June 2021 at 08:09:42 UTC+10, Derek Ignatius Asirvadem wrote:efficient, more “thinking ahead”. So for our purpose, understanding and writing code for an ACID RDBMS, do not concern yourself too much about the platform-specific type of lock, do concern yourself THAT it is locked and that it is some form of
On Friday, 25 June 2021 at 23:58:38 UTC+10, daniel wrote:
-- This KEY lock ensures that no other transactions runningWhether it is a Key lock; or Update Lock; or Intent Lock, is a platform-specific thing (other platforms will do the same thing, but have different locks, or different names for similar locks). In the Sybase & MS case, yes, a Key Lock is distinctly more
Generically, it is an Intent lock, that you have found, and after the insert it is an Exclusive lock. Platform-specifically, it means SQL Server is ready and waiting to insert another row with that or similar Key, hence Key lock.
Therefore, the best engagement is for Xero to engage us, and obtain the full complement in the usual way. As you may be aware, per Australian law, what we sell to customers, cannot be provided free to others (it would make the sale price fraudulent).
On Sunday, 27 June 2021 at 13:16:42 UTC+10, daniel wrote:attend club premises on a given day.
Actually, in this case, attendance is a shooter being present at a recognised sporting shooters club on a given date. NSW AU legislation and regulations for shooting clubs mandate a register of attendees be kept. This is merely a list of people who
that provides the CC part, continues to be used. But this connection has disabled its participation in the massive additional MV resources.That means the entire set of resources, that are required to host MV-non-CC (“snapshot isolation”) on the database, have bee ADDED. That is the massive resources to host the MV part, because the Ordinary Locking that pre-existed in the server,
I'm afraid that can't be helped for my part. Microsoft added snapshot isolation years ago, but it is an opt-in feature.
I can say that resource utilisation is markedly different when MVCC is turned on. Specifically, MVCC results in a marked uptick in TempDB activity.
Can we work on the premise that when MVCC is not enabled in SQL Server then there is no penalty imposed?
---what is being played out here, explained here.
Not just “guarantee isolation of transactions”, which is one requirement of ACID, but the server provides all four requirements for ACID. But but but ACID is not merely a server-side concept, there are requirements on the client side, which is
change). At worst it means complying with ACID partiallyand no OLTP. (Not complying with ACID deems the system outside this class of discussion).It does not consist of walking up to the db and inserting one row correctly. The whole system has to be ACID, ie. comply with ACID requirements. At best that means the Open Architecture/OLTP Standard. Unchanged from the 1960’s (truth does not
Agreed. I've looked at your document about Open Architecture, and accept the assertion that encapsulating all transactions within the database as stored procedures is the only way to guarantee that all constraints hold true at all times.
I agree that the procedure initially provided would not even be called if another procedure, perhaps named 'Attendance_Get_tr', indicated that the attendance on a given date had already been recorded.
---
Ok. You have done a good job of reading some of my answers, and figuring out what the sp template should be, at least for a start. The problem is, you have picked up fragments, not the whole (which is understandable if you understand my intro).
You said RDBMS. That means it has to be Relational. The “keys” are hopeless, non-Relational, but let’s overlook that.Hopefully the explanation above - that this is actually unrelated to 'Time and Attendance', but instead related to a requirement for sport shooting clubs to maintain a register of club attendees - changes your judgement concerning the key.
---
Make the following changes and re-submit:
1. Attendance is dependent on Person, and Person is dependent on organisation.
__ Create table Organisation, PK ( OrganisationId )
__ Create table Person, PK ( OrganisationId, PersonId )
Certainly. I'll share the additional DDL.
For ease of viewing, I've published all of the code below on GitHub too: https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
---
This personal project was actually designed in an effort to understand and incorporate your methodology. So you'll probably recognise a number of characteristics that you've touched on in various PDF documents shared on StackOverflow.
Now that I've shared these additional elements, does that change your feedback in any way?
---
create procedure dbo.Attendance_Get_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as
set nocount on;
set transaction isolation level read committed;
select OrganisationId, PersonId, AttendanceDate
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate;
---
create procedure dbo.Attendance_Add_tr
@OrganisationId int,
@PersonId int,
@AttendanceDate date
as
set nocount on;
------------------------------------------------------------
-- Validation block ------------------------------------------------------------
set transaction isolation level read committed;
select 1
from dbo.Person
where OrganisationId = @OrganisationId
and PersonId = @PersonId
)
begin
return 8; -- Person does not exist
end
if exists (
select 1
from dbo.Attendance
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
)
begin
return 7; -- Attendance on the given date has already been recorded.
end
------------------------------------------------------------
-- Execute block
------------------------------------------------------------
set transaction isolation level serializable;
begin transaction;
insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate
where not exists (
select 1
from dbo.Attendance
with (updlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
);
if @@ROWCOUNT <> 0
begin
commit;
end
else
begin
rollback;
end;
On Monday, 28 June 2021 at 11:26:56 UTC+10, Derek Ignatius Asirvadem wrote:
-- Transaction Processing --
-- Transaction • ACID Properties --
-- Transaction • ACID • Consistency -- -----------------------------------------------------
-- Transaction • ACID • Isolation -- -----------------------------------------------------
//The intermediate state of a transaction is invisible to [or isolated from] other transactions.
As a result, transactions that run concurrently appear to be serialized.//
----------
If you understand this, you will understand that there is only one level of depth in Transactions, either one Transaction or all Transactions in a system. The notion of “nested transactions” is hysterically absurd.
EXEC sp_addmessage 20003, '%1!: is a transaction, which is Atomic. It has been called from within an open transaction, which would render it a non-transaction. That is not allowed.', "us_english", FALSE, "REPLACE"Sybase Transact-SQL, the original, convert to MS Transact-SQL
The reason for this is, if there is a conflict with any other active Transaction, it will be blocked at the highest level in the tree, and NOT proceed further (eg. to the next statement). This is by design, desired. If you do not go AHA!, if you do notappreciate the brilliance of this, please contemplate, and then ask /specific/ questions.
EXEC sp_addmessage 20004, '%1!: A transaction has been opened by
On Tuesday, 29 June 2021 at 22:59:49 UTC+10, Daniel Loth rote:
Hi Derek,
I've posted some updated code here incorporating your feedback: https://gist.github.com/DanielLoth/a272a601e38c9c6f2c002956b08d534e
Microsoft recommend using the 'throw' operation instead of the older 'raiserror'. So I've incorporated the notion of an error, but it's in its own function.
Transaction chaining - implicit transactions - is also a little different in MSSQL. So I've incorporated it as a 'set option' clause.
---not appreciate the brilliance of this, please contemplate, and then ask /specific/ questions.
The reason for this is, if there is a conflict with any other active Transaction, it will be blocked at the highest level in the tree, and NOT proceed further (eg. to the next statement). This is by design, desired. If you do not go AHA!, if you do
It makes sense. It would remove the possibility of deadlock by ensuring that all locks are consistently acquired in the same order.
That is at the code level.
Also, even if for a moment there is contention (say, on dbo.Person in my example) it'll keep progressing immediately afterwards with a relatively good prospect of still succeeding.
Or if the circumstances have changed (e.g.: the transaction that blocked it actually removed the Person row), it'll quickly detect that the row is not there once unblocked, rollback, and then return control to the caller.
---
Question: What context are these two used in?
EXEC sp_addmessage 20004, '%1!: A transaction has been opened by the caller (but is declared NOT to be open).', "us_english", FALSE, "REPLACE"The other two made a lot of sense. But I'm not sure what you mean by 'declared to be'.
EXEC sp_addmessage 20005, '%1!: A transaction has NOT been opened by the caller (but is declared to be open).', "us_english", FALSE, "REPLACE"
Horrible. MS has always had a special way of doing things, which is always more work; more code that can break; more things to go wrong; more things to administer. Have they taken away “user defined” error messages ? What happened to good oldbackward-compatibility.
Yes. That is the only way to eliminate deadlocks. An overall Access Sequence, which has to be defined at the data modelling level. Here, it is a simple stepping through the hierarchy.
For ease of viewing, I've published all of the code below on GitHub too:
https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
__3 If exists Attendance[ OrganisationId, PersonId, AttendanceDate ] return 7 ____ HOLDLOCK
Nicola, note how that Batch Xact fits here. It needs to lock only the highest row in the hierarchy, and that will prevent any other OLTP
Xact from getting into the tree.
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
On 2021-06-27, Derek Ignatius Asirvadem wrote:
For ease of viewing, I've published all of the code below on GitHub too: >> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
Thanks. That's my attempt at porting to PostgreSQL:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
I see that you have continued your discussion on Github, but I haven't
kept up.
__3 If exists Attendance[ OrganisationId, PersonId, AttendanceDate ] return 7
____ HOLDLOCK
Why does this need holdlock, too? Locks on Organisation and Person have already been acquired at this point:
if exists (
select 1
from dbo.Attendance
with (holdlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
)
begin
rollback;
return 7; -- Attendance on the given date has already been recorded.
end
Certainly, with these new threads I better understand your criticisms.
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
On 2021-06-27, Derek Ignatius Asirvadem wrote:
For ease of viewing, I've published all of the code below on GitHub too: >> >> https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
Thanks. That's my attempt at porting to PostgreSQL:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
Great. I can see a lot a value in such a port, no argument, but the
purpose is unclear. If I knew the purpose, my responses would be more specific.
Why does this need holdlock, too? Locks on Organisation and Person have
already been acquired at this point:
if exists (
select 1
from dbo.Attendance
with (holdlock)
where OrganisationId = @OrganisationId
and PersonId = @PersonId
and AttendanceDate = @AttendanceDate
)
begin
rollback;
return 7; -- Attendance on the given date has already been recorded.
end
In chronological order.
- In Sybase at IL[3], it is an automatic HoldLock (hold page until end of Xact)
--- therefore if the Attendance row exists, the lock on the page is held until end of Xact
- MS/SQL was stolen from Sybase at V4.9.2, but MS have rewritten the
codeline several times, so while they are identical to a great degree, especially re the syntax, but at this level of detail they may not be.
So when discussing with Dan, to be clear, I indicated to him that we
want a HOLDLOCK in MS equivalent terms:. It is purely documentary.
That is for Organisation; Person; and Attendance, after the BEGIN
TRAN.
Put another way, in order to obtain a HOLDLOCK, one has to have
Isolation Level 3, which means one has to have a Transaction open.
And now, because you have ported the code, it is an indicator to you,
to do whatever is equivalent in your program suite.
__ https://help.sap.com/viewer/e0d4539d39c34f52ae9ef822c2060077/16.0.4.0/en-US/ab3e9a97bc2b10149c02ff1c52f92cb4.html
Certainly, with these new threads I better understand your criticisms.
It is not “criticism” [of MV-non-CC]. It is a tutorial to drag
academics and developers out of the MV-non-CC/PusGres mindset, into
the Transaction Processing that we have had since the 1960’s (pre-Relational) and the 1980’s (Relational platforms).
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
Discussion, not code.
Nicola, would you like differences between SQL and the PoopGres syntax discussed here, or on GitHub ?
----------
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
So that is not a Transaction. That states that the freaky thing is
always in a "transaction" mode of its own definition. This tells me
that nothing has changed since V10. Functions (oopsey, the now
sort-of stored procedure thingees) are "twunsackshunal". START
TRANSACTION still does Fanny Adams.
And now, security xor "transactional-ness" is a binary choice.
On Saturday, 3 July 2021 at 20:28:55 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
That's my attempt at porting to PostgreSQL:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
Great. I can see a lot a value in such a port, no argument, but the purpose is unclear. If I knew the purpose, my responses would be more specific.
For now, it's just to see to what extent PostgreSQL supports that kind
of coding. Stored procedures were added recently, so that is just my exploration of the feature.
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.
In fact, adding explicit locking might introduce the
possibility of deadlocks.
which in MVCC cannot
happen anyway.
Also,
one purpose of your template is to avoid deadlocks, which in MVCC cannot happen anyway.
It is not “criticism” [of MV-non-CC]. It is a tutorial to drag academics and developers out of the MV-non-CC/PusGres mindset, into
the Transaction Processing that we have had since the 1960’s (pre-Relational) and the 1980’s (Relational platforms).
First, the "inconvenient truth"—to put it as in some recent paper [0]—i.e., the issues you and Daniel mention about MVCC, is well known
also in academia.
issues
That is not a reason to abandon the technique, rather
a push towards further research to improve it or to find contexts in
which it might provide some advantage—which may not be the conventional disk-based storage model which you seem to assume.
Second, the only point I wanted to make when we first started talking
about MVCC is that it is possible to obtain serializable schedules with
(an extension of) that protocol, which you did (do) not want to accept.
Then, if you want to argue that in the wider "ACID/OLTP context" that
does not matter because the performance sucks, long-running transactions work with stale data, perfectly reasonable schedules can never happen
under MVCC, isolation is excessive, etc., then we have little to argue
about yet because I agree with you and Daniel on all those points.
[0] Rethink the Scan in MVCC Databases, SIGMMOD'21
On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
So that is not a Transaction. That states that the freaky thing is
always in a "transaction" mode of its own definition. This tells me
that nothing has changed since V10. Functions (oopsey, the now
sort-of stored procedure thingees) are "twunsackshunal". START
TRANSACTION still does Fanny Adams.
Can you elaborate on that? If I do:
start transaction;
-- Some stuff
commit;
select ...; -- (1)
insert ...; -- (2)
start transaction;
-- Some stuff
commit;
don't (1) and (2) constitute each a transaction?
On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
So that is not a Transaction. That states that the freaky thing is
always in a "transaction" mode of its own definition. This tells me
that nothing has changed since V10. Functions (oopsey, the now
sort-of stored procedure thingees) are "twunsackshunal". START
TRANSACTION still does Fanny Adams.
Can you elaborate on that? If I do:
Sure.
Before I do ...
start transaction;
-- Some stuff
commit;
select ...; -- (1)
insert ...; -- (2)
start transaction;
-- Some stuff
commit;
don't (1) and (2) constitute each a transaction?
What definition of "transaction" are you using ?
On Monday, 5 July 2021 at 02:02:33 UTC+10, Nicola wrote:
On 2021-07-04, Derek Ignatius Asirvadem wrote:
On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
So that is not a Transaction. That states that the freaky thing is
always in a "transaction" mode of its own definition. This tells me
that nothing has changed since V10. Functions (oopsey, the now
sort-of stored procedure thingees) are "twunsackshunal". START
TRANSACTION still does Fanny Adams.
Can you elaborate on that? If I do:
Sure.
Before I do ...
start transaction;
-- Some stuff
commit;
select ...; -- (1)
insert ...; -- (2)
start transaction;
-- Some stuff
commit;
don't (1) and (2) constitute each a transaction?
What definition of "transaction" are you using ?
A logical unit of processing that is executed atomically (either all the operations are carried out till the end or the database is not affected
in any way), does not violate the integrity constraints, does not
interfere with other transactions (in a way that produces results that
are incompatible with any serial execution of the same transactions),
and whose results are guaranteed to be persistent, unless the
transaction fails for some reason.
Syntactically, anything between "start transaction" and "commit". Single commands are run as if enclosed between "start transaction"/"commit".
If I understand correctly, by "transaction" you mean what is executed by
the whole stored procedure, which according to your sketched template consists of several "transactions" in the sense above.
On Monday, 5 July 2021 at 08:01:02 UTC+10, Derek Ignatius Asirvadem wrote:
the context has been:
__ database, specifically one that complies with Codd's /Relational Model/; __ SQL, specifically the Standard
__ ACID Transactions,
In this thread, it is ANSI/ISO/IEEEE SQL, and ACID only.
On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
And now, security xor "transactional-ness" is a binary choice.
I don't understand what this means.
-- «A SECURITY DEFINER procedure cannot execute transaction control statements
-- (for example, COMMIT and ROLLBACK […])»
118 security invoker -- “security definer” is not compatible with commit/rollback :(
On Monday, 5 July 2021 at 08:23:54 UTC+10, Derek Ignatius Asirvadem wrote:introduces confusion (which we, at least Dan and I, specifically reject), and it will sabotage the discussion.
On Monday, 5 July 2021 at 08:01:02 UTC+10, Derek Ignatius Asirvadem wrote:
the context has been:
__ database, specifically one that complies with Codd's /Relational Model/;
__ SQL, specifically the Standard
__ ACID Transactions,
In this thread, it is ANSI/ISO/IEEEE SQL, and ACID only.
Further, note that Daniel started this thread, and the context [further to the above] is SQL platforms using a Lock Manager (not MVCC), and how to construct Transactions therein, properly.
I am happy to entertain discussion about non-SQL fraudulently proposed as "SQL" to a degree, and non-Transaction in program suites that do not have Transactions, to a degree. But I will not stand for anyone imposing novel definitions, because that
Rather than answering this and the previous post in argumentative terms, re the low level issues, you may choose to rollback to the point where we were **NOT** confused, and move forward from that point.
have nothing to do with this thread, and should have been posted under the relevant thread:On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
Please identify which definition (established; within the context we are in; not personal) of "transaction" you are using. And please stick to it (do not switch to another definition). So that our conversation can progress.
On Saturday, 3 July 2021 at 06:55:38 UTC+10, Nicola wrote:
That's my attempt at porting to PostgreSQL:
https://gist.github.com/nv-uniud/dd0008c8684d9ff50fbc814466d09ff9
Nicola, note how that Batch Xact fits here. It needs to lock only the highest row in the hierarchy, and that will prevent any other OLTP
Xact from getting into the tree.
I have to review our previous discussion. I'll come back to you if
I need futher clarifications.
On Sunday, 4 July 2021 at 20:41:52 UTC+10, Derek Ignatius Asirvadem wrote:
Thus, in addition to
a. clarifying the purpose of your code and progressing it,
b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
__ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.
__ And of course [c] the solution.
On Monday, 5 July 2021 at 11:05:19 UTC+10, Derek Ignatius Asirvadem wrote:
---------------------------------------------
-- To Progress This Thread --
---------------------------------------------
On Sunday, 4 July 2021 at 20:41:52 UTC+10, Derek Ignatius Asirvadem wrote:
Thus, in addition to
a. clarifying the purpose of your code and progressing it,
b. please continue your digging into /what could go wrong/ in a Transaction Processing context.
__ Two holes, well-known to us of the Transaction Processing fraternity since 1965, remain.
__ And of course [c] the solution.
Now, for this thread, [a] is cancelled. Please place such discussions in the relevant thread.
Please keep [c] in mind, as the goal, and proceed with [b] in order to progress in that direction.
On Sunday, 4 July 2021 at 20:08:34 UTC+10, Nicola wrote:
On 2021-07-03, Derek Ignatius Asirvadem wrote:
-- From https://www.postgresql.org/docs/current/xproc.html:
-- A procedure can commit or roll back transactions during its execution (then
-- automatically beginning a new transaction),
So that is not a Transaction. That states that the freaky thing is
always in a "transaction" mode of its own definition. This tells me
that nothing has changed since V10. Functions (oopsey, the now
sort-of stored procedure thingees) are "twunsackshunal". START
TRANSACTION still does Fanny Adams.
Can you elaborate on that?
START TRANSACTION, which has SQL compliance requirements, still does sweet Fanny Adams.
If I do:
start transaction; [T0]
-- Some stuff
commit;
select ...; -- (1)
insert ...; -- (2)
start transaction; [T3]
-- Some stuff
commit;
don't (1) and (2) constitute each a transaction?
On Monday, 5 July 2021 at 02:02:33 UTC+10, Nicola wrote:
Syntactically, anything between "start transaction" and "commit".
Single commands are run as if enclosed between "start transaction"/"commit".
If I understand correctly, by "transaction" you mean what is executed by
the whole stored procedure, which according to your sketched template consists of several "transactions" in the sense above.
On Monday, 5 July 2021 at 22:00:03 UTC+10, Derek Ignatius Asirvadem wrote:
On Friday, 23 July 2021 at 22:36:02 UTC+10, Derek Ignatius Asirvadem wrote:
Two weeks and no activity. Come on, guys, this thing needs interaction. Proposal. Question. What if.
We know about Phantoms; Anomalies; Deadlocks.
We can dismiss "Serialisation Anomimimilies" as the particularly stupid way that people who think they have invented the wheel collide with the reality of the road.
What else could go wrong ?
What precisely does [D]urable mean ?
https://www.ibm.com/docs/en/cics-ts/5.4?topic=processing-acid-properties-transactions
Exclusions.
What precisely does [D]urable mean ?
Two down, two to go.
On Sunday, 25 July 2021 at 22:33:22 UTC+10, Derek Ignatius Asirvadem wrote:directly.
On Saturday, 24 July 2021 at 20:47:50 UTC+10, Daniel Loth wrote:
I read the PDF you put together years ago: http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Transaction%20TTM%20101122.pdf
Crikey. A bit of context is demanded.
In hindsight, that was my first crusade, I had made an awful lot of money and I wanted to give something back to my profession; I was responsible enough as a scientist, to correct errors; and I went to the source of those errors and addressed them
<<<<
On Saturday, 24 July 2021 at 20:47:50 UTC+10, Daniel Loth wrote:
Welcome back.
I’ll reply to things you've wrote across both of your emails.
---
What precisely does [D]urable mean ?It means that the outcome of the transaction has been successfully committed.
So it has been written to disk (and potentially one or more replica / follower nodes), and won't be lost should the system crash (or the power go out).
Two down, two to go.I know one is lost updates, though I see you've addressed it in your email.
I read the PDF you put together years ago: http://www.softwaregems.com.au/Documents/The%20Third%20Manifesto/Transaction%20TTM%20101122.pdf
Eleven years ago, I had just come out of pure high-end consulting (ignorance of the madness that was happening in the 95% of the market), and entered into helping people outside my ambit. I had not had the pleasure of reading the literature (no need
So did the example Transaction persist, or was it wiped out by a similar Transaction ?
- by your definition of [D]urable, the Transaction is fine, the Lost Update is unknown (happens all the time, and people are ignorant that it happens).
- by the IBM definition, the Transaction failed, it did not persist, it is not [D]urable. The Lost Update is known.
But how is it prevented ?
Question. What is the *BASIS* for the user having confidence that their Transaction will (should) work ?
How do the naïve developers who use an OLTP/SQL platform (especially if they have only MV-non-CC+application locking mindset) prevent Lost Updates ?
On Monday, 26 July 2021 at 22:10:33 UTC+10, Daniel Loth wrote:will not clobber it without being informed that my transaction has occurred in the period of time between them viewing the data and attempting to act upon it).
So did the example Transaction persist, or was it wiped out by a similar Transaction ?
- by your definition of [D]urable, the Transaction is fine, the Lost Update is unknown (happens all the time, and people are ignorant that it happens).
- by the IBM definition, the Transaction failed, it did not persist, it is not [D]urable. The Lost Update is known.
So you're talking about notions of physical durability (i.e., my data is written to non-volatile storage and will endure) -- versus -- logic durability (i.e., my data is written and other users of this system who might be operating on the same data
logic durability (i.e., my data is written and other users of this system who might be operating on the same data will not clobber it without being informed that my transaction has occurred in the period of time between them viewing the data andattempting to act upon it).
based concurrency control.But how is it prevented ?In Microsoft SQL Server the most robust way is using the ROWVERSION data type, which is just a very large monotonic integer that is automatically updated when modifying a record in a table with a column of type ROWVERSION. It's essentially timestamp
Of course, you needn't use ROWVERSION specifically. You could conceivably use some other very large value (a GUID / UUID for example) so long as you a) update it on all occasions where you're modifying the data it protects and b) always check it beforeproceeding with a data modification.
Thanks for the background re: the TTM discussion. I came across it while looking at other stuff, and it was actually quite eye-opening at the time. In fact that's how I came across the notion of timestamp based concurrency control.
Question. What is the *BASIS* for the user having confidence that their Transaction will (should) work ?In a well-designed system? The knowledge that the system won't allow them, or anyone else, to unknowingly overwrite someone else's changes.
In an abstract sense? Maybe they've had some degree of exposure to notions such as 'Durability' and, in laymen's terms, perceived it as something that will safeguard their modifications from data loss.
I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or “MVCC” notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of anyQuestion. What is the *BASIS* for the user having confidence that their Transaction will (should) work ?
---
How do the naïve developers who use an OLTP/SQL platform (especially if they have only MV-non-CC+application locking mindset) prevent Lost Updates ?The naive approach would be to acquire and hold locks for the duration of user activity right?
Though in my personal experience, I haven't seen this.
I have instead seen:coarse or too fine. Or alternatively again, there is confusion about which timestamps protect which data from lost updates.
1. No attempted lost update prevention at all. Last update wins.
2. Timestamp based concurrency control, but weakly implemented. For example, the notion exists but might not be used appropriately in all places in application code. Or alternatively, the breadth of data protected by a particular timestamp is too
We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. TransactionSanity/p4.
Was the Transaction Durable or not ?
Correct.
But that is not robust, it is a large overhead. A data-level TimeStamp (DATETIME datatype) is the most robust. We have had that since 1965, 1984 for RDBMS, forty years before MS implemented ROWVERSION.
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
Hi Derek,
Thanks for the response and continued discussion.
---Sanity/p4.
We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. Transaction
Was the Transaction Durable or not ?No. Neither the User 1 or User 2 transactions make use of the timestamp based concurrency control.
---
Correct.If you're using a clock-based timestamp such as DATETIME, instead of a server-wide monotonic integer (which is what the Microsoft ROWVERSION is), what do you do to handle the possibility of clock skew on the database server?
But that is not robust, it is a large overhead. A data-level TimeStamp (DATETIME datatype) is the most robust. We have had that since 1965, 1984 for RDBMS, forty years before MS implemented ROWVERSION.
Do you do anything, or do you just take your chances? I recognise that the situation is extraordinarily rare, and you may typically dismiss it on that basis.
---single fact.
I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or “MVCC” notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of any
So what you're getting at, especially with the mention of paper-based, is: It's inconceivable that you could inadvertently clobber someone else's change to that single piece of paper without being cognisant of that. After all, they've written on thepaper and put it back, and then you've picked it up and looked at it. Obviously being a single piece of paper, without duplicates, access to the piece of paper involves queuing up for it and waiting your turn.
On Tuesday, 27 July 2021 at 21:37:57 UTC+10, Nicola wrote:
I am back, too,
Derek, I have read your Transaction Sanity document. When academics
discuss "transactions", they do so in the context of what eventually
becomes a start transaction… commit block in implementations. For instance, they ask themselves: under what conditions are lost updates
(and other anomalies)
be avoided when:
-- Transaction 1
start transaction;
-- Perform some operations, including updates
commit;
-- Transaction 2
start transaction;
-- Perform some operations, including updates
commit;
are executed concurrently?
Admittedly, the examples that are usually
found in textbooks are quite misleading: the classical fund transfer is
used because the context is readily understood, but it should be taken
with a grain of salt, and certainly not as a way to suggest how a real banking transaction should be implemented.
It's naive to think that the
"naive solution" in your document is what a (good) teacher would suggest
as a practical way to solve the problem stated in that document.
To make
the point clearer (and simplifying things a bit): the transaction theory that we teach, and which you can find in (good) textbooks, is the theory
you would need to write a transaction manager for a DBMS (i.e., the
theory needed by Sybase developers),
Here is my take on science, as it has been 350BC to 1911. (Which is under attack since 1911, by Modernism.) It is a body of knowledge (certainty: the Latin word means knowledge not speculation). Of course, one can speculate BASED ON that body of
rather than the theory needed to
correctly implement the transactions for a banking system or any other application, which builds on top of the former and is seldom, if ever, discussed in textbooks.
Hence, I am eager to hear what you have to say
about it.
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
If you're using a clock-based timestamp such as DATETIME, instead of
a server-wide monotonic integer (which is what the Microsoft
ROWVERSION is), what do you do to handle the possibility of clock
skew on the database server?
What skew ??? Where is this skew ???
On Wednesday, 28 July 2021 at 17:55:29 UTC+10, Nicola wrote:
On 2021-07-27, Derek Ignatius Asirvadem Derek wrote:
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
If you're using a clock-based timestamp such as DATETIME, instead of
a server-wide monotonic integer (which is what the Microsoft
ROWVERSION is), what do you do to handle the possibility of clock
skew on the database server?
What skew ??? Where is this skew ???
How do you handle a situation in which the system's clock starts
reporting an inexact time?
Or, if you have two servers writing data into the database,
their clocks might be off, say, by a few seconds.
It may not happen frequently, but it does
happen (say, the NTP daemon crashes, or gets the wrong time for some reason).
Hell, I run several Sybase servers that host thousands of client connections, with just 12 chip threads. And I am definitely not the only one.
On Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:Sanity/p4.
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:
We need to understand it forwards. His Transaction completed and it persists ... and therefore he has confidence in the system. XOR his Transaction completed, but it did not persist ... and therefore he has no confidence in the system. Transaction
Yes, that is the back end, or developer’s understanding.Was the Transaction Durable or not ?No. Neither the User 1 or User 2 transactions make use of the timestamp based concurrency control.
What is in the user’s mind, how does he perceive it ?
single fact.I am provoking a thought process in your mind. Focus on *BASIS*. Here you have to get rid of any MV-non-CC or “MVCC” notions because they are bankrupt. Think in terms of Reality, an online shared database that contains a single version of any
paper and put it back, and then you've picked it up and looked at it. Obviously being a single piece of paper, without duplicates, access to the piece of paper involves queuing up for it and waiting your turn.So what you're getting at, especially with the mention of paper-based, is: It's inconceivable that you could inadvertently clobber someone else's change to that single piece of paper without being cognisant of that. After all, they've written on the
Yes. Excellent parallels drawn. Keep going.
- In the paper system, the person (first-up in the queue) would have be cognisant.
- think: a library card system, arranged in 200 little drawers for convenience of physical access by many people
- In the automated system, the program has to be cognisant.
- In the paper system, the person (first-up in the queue for a particular card) has an idea of its data currency based on something, so that when he returns (with the intended update), he would be cognisant that the card has changed.
- In the automated system, the program has to do that.
On Tuesday, 27 July 2021 at 21:37:57 UTC+10, Nicola wrote:
Hence, I am eager to hear what you have to say
about it.
Even got SQL Server and Sybase installed
... to verify your claims
:)
On Monday, 2 August 2021 at 12:19:13 UTC+10, Derek Ignatius Asirvadem wrote:
On Tuesday, 27 July 2021 at 21:37:57 UTC+10, Nicola wrote:
... to verify your claims
:)
Well, if you enumerate even one or two of those declaration (that you re-frame as "claims")
Concerning clock skew
Can we please get some progress here. We have not finished, and the
Template is not complete.
Recap for the exact position that we are at.
On Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:Yes, that is the back end, or developer’s understanding.
We need to understand it forwards. His Transaction completed andNo. Neither the User 1 or User 2 transactions make use of the
it persists ... and therefore he has confidence in the system. XOR
his Transaction completed, but it did not persist ... and
therefore he has no confidence in the system. Transaction
Sanity/p4.
Was the Transaction Durable or not ?
timestamp based concurrency control.
What is in the user’s mind, how does he perceive it ?
Pages 4 and 5 in the Transaction Sanity doc.
Derek in particular described his approach and in doing so expressed
two principles:
1. Only acquire exclusive (write) locks if the transaction will
potentially succeed.
2. Never acquire exclusive locks if embarking on the transaction is
futile (or put another way, don't start what you absolutely cannot
finish).
Consistent with those principles, the ideal was described as:
1. A validate block - Where we 'look before we leap' (to borrow that >expression). In this block we are reading only. We can use the READ
COMMITTED isolation level, thereby acquiring and releasing shared locks
in quick succession. No shared locks are held after the conclusion of
the statement (i.e., the select query in this case).
2. A transaction block - Where we check again and, if the conditions
are still right (i.e., other users might have changed the data), we can >proceed to do our work and then commit the transaction. In this block
we acquire write locks, and these locks are held for the remainder of
the transaction (until we commit or rollback).
On Monday, 2 August 2021 at 22:32:27 UTC+10, Daniel Loth wrote:is technically deprecated.
Concerning clock skew
Nicola's reply about clock skew covers what I was getting at. Specifically, a clock moving backwards or forwards. Moving backwards means there is potential for you to see the same datetime twice.
You don't see it happen often, but every now and then it does. Oftentimes things like authentication services stop working because they're heavily reliant on relatively precise time keeping.
This is why I personally lean towards the SQL Server rowversion type mentioned.
Rowversion is notably just a synonym for 'timestamp' as you've described Sybase 4.2 as having. Another Microsoft-ism. In fact, you can still use the 'timestamp' data type when writing the table DDL and that'll work fine, though the 'timestamp' keyword
I think the deprecation is because Microsoft's 'timestamp' is not 'timestamp' in the ISO standard sense. In SQL Server, a non-null rowversion is equivalent to binary(8). A nullable rowversion is equivalent to varbinary(8).
To make sense of the number expressed as binary(8), you can cast it to a bigint. At that point it becomes useful for concurrency control in much the same way that UpdatedDtm is.
---clobbered by a subsequent transaction.
Concerning durability and user perception
In the user's mind, you are right that their perception of the system is that it has not lived up to the guarantees of the ACID concept of durability. They won't recognise that it was written to disk for a fleeting moment before their changes were
They simply see the final outcome, which is that a subsequent user's actions have overwritten their own.
You also mentioned user training teaching users that they would expect to see their changes after performing a transaction, and teaching users that the durability property means that they can be confident that a reportedly committed transactionindicates that the changes made in their transaction will stick.
In the library card system, the basis for their confidence that they, and they alone, can update the physical card and return it to the drawer. And it's just one card, so their changes will be there when the next person takes it from the drawer.
And this is essentially what the lock management system does. It affords that mutually exclusive access to the library card.
Is that the basis you are asking for?
---changes during my 10 minute absence because what happened to be on my screen was the old, not-current, data when I clicked 'Save'.
Lost currency on page 5 in the Transaction Sanity document
I read this. Basically, the problem is that I load a screen with a number of fields on it. I grab a drink. I come back 10 minutes later, make a minor change, and click 'Save'. In doing so I've just wiped out the work of a colleague who made a lot of
---
The template being unfinished
Is it unfinished due to the lack of lost update protection? Or is there something else missing?
As far as I can see, it's already doing a good job in terms of contention management.
I've posted a new GitHub Gist here: https://gist.github.com/DanielLoth/0599c2475368083acc9032d34f0919e1
This revision of the code contains lost update protection using the UpdatedDtm column in the Person table.
On Tuesday, 3 August 2021 at 08:28:49 UTC+10, Nicola wrote:
On 2021-08-02, Derek Ignatius Asirvadem wrote:
Can we please get some progress here. We have not finished, and the Template is not complete.
Recap for the exact position that we are at.
I'd say that there are two Logical Units of Work (LUW—I think the termOn Wednesday, 28 July 2021 at 08:03:50 UTC+10, Derek Ignatius Asirvadem wrote:Pages 4 and 5 in the Transaction Sanity doc.
On Tuesday, 27 July 2021 at 20:59:24 UTC+10, Daniel wrote:Yes, that is the back end, or developer’s understanding.
We need to understand it forwards. His Transaction completed andNo. Neither the User 1 or User 2 transactions make use of the
it persists ... and therefore he has confidence in the system. XOR
his Transaction completed, but it did not persist ... and
therefore he has no confidence in the system. Transaction
Sanity/p4.
Was the Transaction Durable or not ?
timestamp based concurrency control.
What is in the user’s mind, how does he perceive it ?
was used in CICS), one by User 1 and one by User 2. When updating
a value, each user will assume that the value they have replaced is the
one they had previously read (if the system lets the update go through).
Each LUW consists of more than one database transactions. I refer to
each SELECT statement in that example as a (read-only, database) transaction.
Hence, some state must be maintained between database
transactions, to make each LUW meet the user's expectations. In this
case, the state is the pair (@CreditRatingCode,@BirthPlace), i.e., the information initially retrieved by each user. Then, both TweedleDumb and TweedleDumber should check that such values have not been changed since
the corresponding previous SELECT, and do so before updating the record.
Rather than checking the values directly, a SELECT could also read
a version number of the retrieved record and pass it to TweedleDumb
(resp., TweedleDumber), which would check that the version number still matches (by re-reading it), in which case it would update the record and increase the version number.
On Tuesday, 3 August 2021 at 08:28:49 UTC+10, Nicola wrote:
I refer to each SELECT statement in that example as a (read-only,
database) transaction.
No. That is not a Transaction. You are still holding onto an
incorrect notion that non-transactions are somehow Transactions.
One correction. LUW was the original CICS term, and it is still used
in IBM/DB2. But it means Transaction, not PoopDePoopGres
"transaction", not whatever you reframe it to mean. LUW means
a single database Transaction, controlled according to ACID.
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
On 2021-08-03, Derek Ignatius Asirvadem wrote:
On Tuesday, 3 August 2021 at 08:28:49 UTC+10, Nicola wrote:
I refer to each SELECT statement in that example as a (read-only,
database) transaction.
No. That is not a Transaction. You are still holding onto an
incorrect notion that non-transactions are somehow Transactions.
Ok, let me understand: what are the differences in behaviour between
select … ;
and
start transaction;
select … ;
commit;
select … ;means:
-- ISOLATION LEVEL 1/READ COMMITTEDSQL
start transaction;means:
select … ;
commit;
START TRANSQL
One correction. LUW was the original CICS term, and it is still used
in IBM/DB2. But it means Transaction, not PoopDePoopGres
"transaction", not whatever you reframe it to mean. LUW means
a single database Transaction, controlled according to ACID.
So, wrt to your Transaction Sanity example (latest revision),
So, wrt to your Transaction Sanity example (latest revision),
User 1 executes one LUW/Transaction, which starts at time T3.
And User 2 executes one LUW/Transaction starting at time T4.
Besides, the SELECTs
at T1 and T2, respectively, are not part of those Transactions. Is that right?
If you call the whole stored procedure a Transaction, how do you refer
to each BEGIN TRAN… COMMIT block?
If you call the whole stored procedure a Transaction, how do you refer
to each BEGIN TRAN… COMMIT block?
LABEL: -- must not be a reserved wordSQL
VALIDATE:SQL
If you call the whole stored procedure a Transaction, how do you refer
to each BEGIN TRAN… COMMIT block?
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
Ok, let me understand: what are the differences in behaviour between
select … ;
and
start transaction;
select … ;
commit;
Because you have not specified an ISOLATION LEVEL, and because SQL has default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
Therefore:
select … ;means:
-- ISOLATION LEVEL 1/READ COMMITTEDSQL
-- Locks will be held for the duration of each Read operation (page or row) -- Phantoms and result set Anomalies may occur
Note that that means the SELECT is not in a Transaction.
start transaction;means:
select … ;
commit;
START TRANSQL
-- ISOLATION LEVEL 3/SERIALIZABLE
-- Locks will be held until end of Transaction, the COMMIT/ROLLBACK TRAN
-- Phantoms and result set Anomalies will not occur
On Wednesday, 4 August 2021 at 10:15:03 UTC+10, Derek Ignatius Asirvadem wrote:
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
So, wrt to your Transaction Sanity example (latest revision),I will assume page 7, because that is the solution I gave, Optimistic Locking.
User 1 executes one LUW/Transaction, which starts at time T3.
And User 2 executes one LUW/Transaction starting at time T4.
Yes.
Besides, the SELECTs
at T1 and T2, respectively, are not part of those Transactions. Is that right?
Yes.
As per the legend at the bottom of page 4.
Green is resident and executed in the client app.
Blue is resident and executed in the server, the Transaction stored proc. The delay between [T1] and [T3] for User 1, and between [T2] and [T4] for User 2, cannot be controlled due to being user interaction.
On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:
On 2021-08-04, Derek Ignatius Asirvadem wrote:
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
Ok, let me understand: what are the differences in behaviour between
select … ;
and
start transaction;
select … ;
commit;
Because you have not specified an ISOLATION LEVEL, and because SQL has default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
Therefore:
select … ;means:
-- ISOLATION LEVEL 1/READ COMMITTEDSQL
-- Locks will be held for the duration of each Read operation (page or row)
-- Phantoms and result set Anomalies may occur
Note that that means the SELECT is not in a Transaction.
Ok. For understading: let us assume that locks are row-based.
Consider
this instance:
Person
Name Age
--------
John 34
Bob 34
And this query:
select P1.Name, P2.Name
from Person P1, Person P2
where P1.Name <> P2.Name
and P1.Age = P2.Age;
Assume that the query is executed using a nested-loop join.
Besides,
suppose that an update is concurrently performed:
update Person set Age = 35 where Name = 'John';
Is it correct that in this situation the query can produce at least
three possible results?
In various benchmarks, on a genuine Relational database, using various table structures, many people have tried to produce contention, in this sort of way. It never succeeds, the effort is futile.
1. If the update is performed after the select, then the result is:
John, Bob
Bob, John
2. If the update is performed before the select, the result is empty.
3. But the update can happen *during* the select, in which case the
result may be:
John, Bob
This happens when at the first iteration of the outer loop (John, 34)
is locked (so the update is blocked), then the inner loop scans the
table and outputs (John,34, Bob,34), and only after that the update
takes place (in the meantime, the lock on (John,34) will have been released). Hence, at the second iteration of the outer loop, the
inner scan will not find anything else to join with (Bob,34).
Is this right?
Are locks under these circumstances actually just
latches?
You are correct that in a system such as PostgreSQL there is nothing
like that: the select would see a snapshot of the table at time it is executed, and would not see any changes by concurrent commands.
«PostgreSQL actually treats every SQL statement as being executed within
a transaction.
If you do not issue a BEGIN command, then each individual
statement has an implicit BEGIN and (if successful) COMMIT wrapped
around it.»
On Wednesday, 4 August 2021 at 22:05:01 UTC+10, Derek Ignatius Asirvadem wrote:eliminated those concerns via a server in 1984, and which has no o/s files. No.
On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:
Basically, in your post you are telling me that your mickey mouse system does not do what it should not do. Ok.
And then you are trying to say that the low-level concerns that 10,000 mickey mouse developers have, in their herd of hundreds of programs that have no architecture and handle miserable o/s files, should be of concern to 200 PhD level engineers who
This is just one of the great differences between freeware (including Orable) and commercial SQL Platforms. You get what you pay for.fantasies.
The problem is, consistently, the insane insist that the sane should have their insane problems. No, they are not real to us. They are real only in the land where people expand vast amounts of energy to make their fantasies real, physicalising their
Because you have not specified an ISOLATION LEVEL, and because SQL has
default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:
On 2021-08-04, Derek Ignatius Asirvadem wrote:
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
Ok, let me understand: what are the differences in behaviour between
select … ;
and
start transaction;
select … ;
commit;
Because you have not specified an ISOLATION LEVEL, and because SQL has
default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
Therefore:
select … ;means:
-- ISOLATION LEVEL 1/READ COMMITTEDSQL
-- Locks will be held for the duration of each Read operation (page or row)
-- Phantoms and result set Anomalies may occur
Note that that means the SELECT is not in a Transaction.
Ok. For understading
Exercise 18.11 (and its solution) from Silberschatz's "Database System
Concepts" seems relevant:
https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf
No.
They are stuck in the usual academic mindset, the examples are
particularly about 2PL, which is an MV-non-CC artefact, not relevant
to, or used in SQL OLTP Platforms, because we do not have 2PL. They
do not understand or reference the "1PL" Lock Manager we have in the
real world.
We definitely do not "re-execute" a Transaction, the Validate block is
not a [paraphrased] "first execution without holding locks". The
notion of re-executing a Transaction [note the compute-intensive !] is stupid. The Validate block is a separate formal article.
On Thursday, 5 August 2021 at 00:46:30 UTC+10, Nicola wrote:
On 2021-08-04, Derek Ignatius Asirvadem wrote:Btw, why does the transaction isolation level even matter? A single
Because you have not specified an ISOLATION LEVEL, and because SQL has >> > default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
SELECT statement (not enclosed in BEGIN TRAN… COMMIT) is not
a transaction.
A single
SELECT statement (not enclosed in BEGIN TRAN… COMMIT) is not
a transaction.
Btw, why does the transaction isolation level even matter?
On Thursday, 5 August 2021 at 07:40:10 UTC+10, Nicola wrote:
Exercise 18.11 (and its solution) from Silberschatz's "Database System
Concepts" seems relevant:
https://www.db-book.com/db7/Practice-Exercises/PDF-practice-solu-dir/18.pdf
No.
They are stuck in the usual academic mindset, the examples are particularly about 2PL, which is an MV-non-CC artefact, not relevant
to, or used in SQL OLTP Platforms, because we do not have 2PL. They
do not understand or reference the "1PL" Lock Manager we have in the
real world.
We definitely do not "re-execute" a Transaction, the Validate block isWhat the exercise describes is a concurrency control protocol within the DBMS. So, it's the system that "first execute[s] the transaction without acquiring any locks…" and then "rerun[s] the transaction using 2PL". That's transparent for the user.
not a [paraphrased] "first execution without holding locks". The
notion of re-executing a Transaction [note the compute-intensive !] is stupid. The Validate block is a separate formal article.
On Thursday, 5 August 2021 at 00:34:46 UTC+10, Nicola wrote:
On 2021-08-04, Derek Ignatius Asirvadem wrote:I'll try to rephrase my question, which basically asks if you have statement-level consistency of SELECT queries at READ COMMITTED in
On Wednesday, 4 August 2021 at 20:13:54 UTC+10, Nicola wrote:
On 2021-08-04, Derek Ignatius Asirvadem wrote:
On Wednesday, 4 August 2021 at 06:05:09 UTC+10, Nicola wrote:
Ok, let me understand: what are the differences in behaviour between >> >>
select … ;
and
start transaction;
select … ;
commit;
Because you have not specified an ISOLATION LEVEL, and because SQL has >> > default ISOLATION LEVELS for each different context, before you
started your example, the ISOLATION LEVEL is 1/READ COMMITTED.
Therefore:
select … ;means:
-- ISOLATION LEVEL 1/READ COMMITTEDSQL
-- Locks will be held for the duration of each Read operation (page or row)
-- Phantoms and result set Anomalies may occur
Note that that means the SELECT is not in a Transaction.
Ok. For understading
Sybase.
A SELECT statement, which runs at READ COMMITTED, holds (let's say,
page) locks for the duration of each read operation, and not until the
end of the statement. Assume that the SELECT statement is a complex
query accessing many records over a non-negligible time-span (seconds),
and that it is run concurrently with several (serializable) update operations on the same data.
(1) Is it possible (in Sybase) that the SELECT query returns an
incorrect result, where by "incorrect" I mean a result that is
impossible to obtain when the query is run in a serial context, that is,
in absence of any other concurrent operation?
(2) If the answer to (1) is negative, where does such a correctness guarantee come from? If, on the contrary, the answer to (1) is
affirmative, can you please describe a situation in which that happens?
I'd say that the answer to (1) is affirmative. For instance, during
a (block) nested-loop join a certain number of pages must be read into memory buffers in the outer loop. Later on, it may be necessary to evict such pages from the memory buffers to make room to another block of
pages. Later on, it may be necessary to read the evicted pages again in
the inner loop (think of join of a table with itself). What prevents concurrent transactions to modify those pages in between the two reads?
On Thursday, 5 August 2021 at 11:50:32 UTC+10, Derek Ignatius Asirvadem wrote:
On Thursday, 5 August 2021 at 00:34:46 UTC+10, Nicola wrote:
This thread is about /Stored procedure structure in RDBMS using Lock Manager for transaction isolation/, and the real world of high end implementations. I don't have the inclination to argue speculation against speculation, endlessly.
Ok, let me understand: what are the differences in behaviour between
Ok. For understading
I'll try to rephrase my question, which basically asks if you have statement-level consistency of SELECT queries at READ COMMITTED in
Sybase.
I have already answered this in detail in this thread. Please read.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 399 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 97:56:09 |
| Calls: | 8,363 |
| Calls today: | 2 |
| Files: | 13,162 |
| Messages: | 5,897,714 |