Standard <acronym>SQL</acronym> Transaction Isolation

On Fri, Ap= r 24, 2015 at 9:57 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 4/17/15 7:36 PM, David G. Johnston wro= te:
> diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
> index f88b16e..5002138 100644
> --- a/doc/src/sgml/mvcc.sgml
> +++ b/doc/src/sgml/mvcc.sgml
> @@ -100,6 +100,14 @@
>=C2=A0 =C2=A0 =C2=A0 phenomena caused by interactions?)
>=C2=A0 =C2=A0 =C2=A0</para>
>
> +=C2=A0 <para>
> +=C2=A0 =C2=A0The concepts covered in this section are
> +=C2=A0 =C2=A0presented without examples of the behaviors described.= =C2=A0 The internet,
> +=C2=A0 =C2=A0including and espcially the <productname>PostgreSQ= L</productname> Wiki, is
> +=C2=A0 =C2=A0an excellent resource to learn more about circumstances = under which these
> +=C2=A0 =C2=A0data phenomena occur, and what the results look like whe= n they do.
> +=C2=A0 </para>
> +

I don't think our documentation should go out of its way to say, "= our
documentation is bad, look elsewhere".=C2=A0 If we think examples are<= br> necessary, then we should add some.=C2=A0 Otherwise, it's implied that<= br> improvement is always possible.

=E2= =80=8BI'm not - I am explicitly listing the assumptions the documentati= on makes regarding reader experience (and ease of documenting) - and pointi= ng out were the reader can go if their experience is lacking in those areas= .=E2=80=8B =C2=A0It seems unproductive to move all of the SSI content on ou= r Wiki into the documentation and so, lacking such, we should point out whe= re else content can be found.

=C2=A0

>=C2=A0 =C2=A0 =C2=A0<para>
>=C2=A0 =C2=A0 =C2=A0 The phenomena which are prohibited at various leve= ls are:
>
> @@ -150,12 +158,12 @@
>=C2=A0 =C2=A0 =C2=A0 <indexterm>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0<primary>transaction isolation level&l= t;/primary>
>=C2=A0 =C2=A0 =C2=A0 </indexterm>
> -=C2=A0 =C2=A0 The four transaction isolation levels and the correspon= ding
> -=C2=A0 =C2=A0 behaviors are described in <xref linkend=3D"mvc= c-isolevel-table">.
> +=C2=A0 =C2=A0 The four SQL transaction isolation levels, and their co= rresponding
> +=C2=A0 =C2=A0 behaviors, are described in <xref linkend=3D"mv= cc-isolevel-table">.
>=C2=A0 =C2=A0 =C2=A0</para>

I don't think this change is good.

I think it reads cleaner but not so much as to argue it.

=C2=A0

>
>=C2=A0 =C2=A0 =C2=A0 <table tocentry=3D"1" id=3D"mvcc= -isolevel-table">
> -=C2=A0 =C2=A0 =C2=A0<title>Standard <acronym>SQL</acro= nym> Transaction Isolation Levels</title>
> +=C2=A0 =C2=A0 =C2=A0<title><acronym>SQL</acronym> S= tandard Transaction Isolation Levels</title>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0<tgroup cols=3D"4">
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 <thead>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<row>

Why this change?

=E2=80=8BThe new ta= ble reads "PostgreSQL ..." and the corresponding noun is the &quo= t;SQL Standard".=C2=A0 Writing "Standard SQL" can be read as= implying the existence of "Non-Standard SQL..." which is not cor= rect.=C2=A0 Just saying "SQL..." seems to be too generic - though= after reading the conclusion and pondering that "SQL Standard" c= ould also imply "SQL Non-Standard..." I'm not so sure whether= just saying SQL wouldn't be best. =C2=A0"Here are the four words = that can be used with SET TRANSACTION ISOLATION LEVEL..." - and then s= how/describe the minimum required non-behaviors and the non-behaviors as im= plemented in PostgreSQL.

=E2=80=8BMaybe p= ossessive would work "PostgreSQL's ..." and "SQL Standar= d's..."=E2=80=8B

> @@ -256,6 +264,89 @@
>=C2=A0 =C2=A0 =C2=A0</para>
>
>=C2=A0 =C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 The three <productname>PostgreSQL</productname= > transaction isolation levels, and their corresponding
> +=C2=A0 =C2=A0 behaviors, are described in <xref linkend=3D"mv= cc-pgsql-isolevel-table">.
> +=C2=A0 =C2=A0</para>

This isn't really correct.=C2=A0 The PostgreSQL isolation levels were described in the paragraph above.=C2=A0 The table is really just a summary = of
the previous explanation.

=E2=80=8B"[...], are summarized in <xref...>" ?

<= /div>

=E2=80=8B

=C2=A0

> +=C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 As the table makes clear there is no difference in the = potential phenomena
> +=C2=A0 =C2=A0 at the REPEATABLE READ and SERIALIZABLE transaction iso= lation levels; but
> +=C2=A0 =C2=A0 the phenomena listed only pertain to the data seen by t= he transaction.

Please adapt the existing spelling and capitalization.

=E2=80=8BOK

=E2=80=8B

=C2=A0

> +=C2=A0 =C2=A0 The difference is that REPEATABLE READ will only serial= -fail

This term "serial-fail" would need further explanation.

=E2=80=8BI will probably stick= with the more verbose "serialization failure"...does that requir= e explaining here, or a xref?
=E2=80=8B=C2=A0

> +=C2=A0 =C2=A0 if two transactions attempt to modify the same record w= hile SERIALIZABLE will
> +=C2=A0 =C2=A0 also serial-fail if one transaction modifies a record t= hat another transaction
> +=C2=A0 =C2=A0 has only read.
> +=C2=A0 =C2=A0</para>

I don't think this new table adds clarity.=C2=A0 Users should generally= have
their applications use the appropriate standard isolation level.
=E2=80=8B

=E2=80=8B=E2=80= =8BThen why not sure write the entire section relative to the standard and = point out the differences between the standard and our implementation on th= e command definition page in the compatibility section?

=E2=80=8Bhttp://www.postgresql.org/docs/devel/static/sql-set-t= ransaction.html
=E2=80=8B=C2=A0
Adding
another table that says, some of these are not actually different,
following by text that says they are different in other ways, just
repeats the point that was made earlier and will be explained in more
detail in the following subsections.

The real difference, in my mind, is that the SQL standard defines four
levels in terms of three criteria, but PostgreSQL really has four
criteria and only three different levels implemented.=C2=A0 It might be wor= th
visualizing that somehow.

=E2=80=8BW= ell, I would at least add "Read uncommitted" to the PostgreSQL ta= ble and have it setup the same as "Read committed".=C2=A0 We do i= mplement all four - by name.

And, as to the pri= or point, visualizing the differences seems best accomplished in a compatib= ility section and likely will just confuse the issue here - if indeed the e= xpectation is that users will define their requirements relative to the sta= ndard and not relative to our implementation.

= =E2=80=8BOtherwise, a summary table describing our implementation seems lik= e a self-evident need.=C2=A0 We are already going to great lengths to descr= ibe everything in the table anyway and we already are using a table to desc= ribe the standard's definitions.=E2=80=8B =C2=A0Placing said table here= seems easiest and if summarizing what is already present in the text someh= ow makes the section more confusing I posit that it must already be confusi= ng without the table.=C2=A0 At least this way the confusing stuff is summar= ized and is readily available for lookup by those who know what they are lo= oking for.
=E2=80=8B
For clarity - what is the 4= th criteria that you are thinking of?=C2=A0 More specifically, how would yo= u name it so that it could be a table column?

Two sep= arate patches here:

1) =E2=80=8Bpointing out that addi= tional information is available on the wiki and the internet

2) summarizing the PostgreSQL implementation into a table similar to= that already present for the Standard

#2 can be imple= mented in the MVCC section or a more extensive patch can also update the SQ= L command SET TRANSACTION section - which will mean someone feels strongly = enough that the status quo is better than updating MVCC while waiting for s= omeone to write the more invasive patch.

=E2=80=8BDavid J.=E2=80=8B

--001a11c318de37a6d405147c2fb1--