Unexpected behaviour from transaction with isolation level snapshot table stability

[KS85]

Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.7.33374 Firebird 3.0"

ServerMode: Super

Operating System: Windows 10 10.0.19042

I'm experimenting with transactions and found that the documentation of the isolation level 'snapshot table stability' doesn't match the behaviour I experienced. The documentation states that:

If any other transaction has an uncommitted change of data pending in any database table before a transaction with the SNAPSHOT TABLE STABILITY isolation level is started, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an exception.

To test this, I started two isql instances and connected to the same database. Then I started a default transaction (read write wait isolation level stability) and began to update rows of a table and kept the transaction running, i.e. no commit, no rollback.

In the other isql instance I started a snapshot table stability transaction like this:

set transaction snapshot table stability;

select current_transaction from rdb$database;

The transaction immediately started, even though another transaction has pending changes, which contradicts the documentation? 

I experienced the following behaviour of a transaction with snapshot table stability isolation level:

Once a snapshot table stability transaction T has read at least one row of a table, any other transaction trying to update this table in the meantime will block/wait/timeout. If  a database table hasn't been read by T yet,  other transactions can still update, insert, delete data in these non-read tables. If T however tries to read this changed tables it will block/wait until the changes have been committed or rolled back. Why does it need to block in this case? T is not able/intended to see committed changes since it started. After the other transaction committed/rolled back, T continues with the old data anyway.

Do I misunderstand here something or is the documentation misleading/wrong?

[KS85]

The default transaction had the isolation level snapshot (not stability)

[Dimitry Sibiryakov]

27.07.2021 15:35, 'KS85' via firebird-support wrote:
> set transaction snapshot table stability;
> select current_transaction from rdb$database;
>
> The transaction immediately started, even though another transaction has pending changes,
> which contradicts the documentation?
>
> I experienced the following behaviour of a transaction with snapshot table stability
> isolation level:
>
> Once a snapshot table stability transaction T has read at least one row of a table, any
> other transaction trying to update this table in the meantime will block/wait/timeout. If
> a database table hasn't been read by T yet,  other transactions can still update, insert,
> delete data in these non-read tables. If T however tries to read this changed tables it
> will block/wait until the changes have been committed or rolled back. Why does it need to
> block in this case? T is not able/intended to see committed changes since it started.
> After the other transaction committed/rolled back, T continues with the old data anyway.
>
> Do I misunderstand here something or is the documentation misleading/wrong?

rdb$database in your case is "this table" from documentation. I cannot believe that
other transactions have pending changes in it.
"Table stability" transaction locks tables when trying to read from them as written in
the documentation. Your transaction doesn't read any table so it doesn't lock anything and
doesn't wait for anything.

--
WBR, SD.

[KS85]

Example:

In transaction A (Snapshot Isolation Level):

1) select * from stocks;

name     stock_count

=================

amazon   10

microsoft 20

2) select * from stock_sums;

name       stock_sum

=================

report01 100

3) Now lets make a change

insert into stocks values('oracle', 30)

4) select * from stocks;

name     stock_count

=================

amazon    10

microsoft 20

oracle       30

5) We neither commit, nor do we rollback. There are obviously pending changes in this table.

Now we start a new transaction with isolation level Snapshot Table Stability in another isql instance, which should throw an exception because there is still a running transaction A which has pending uncommitted changes (as stated in the paragraph of the documentation [see my original post])

In other isql instance:

1) set transaction snapshot table stability; <-- succeeds even though A is still running and has pending changes (documentation says this should throw exception)

2) select * from stocks;    <-- this blocks until A commits or rolls back (why?)

[Vlad Khorsun]

On Tuesday, 27 July 2021 at 20:28:45 UTC+3 KS85 wrote:

Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.7.33374 Firebird 3.0"

ServerMode: Super

Operating System: Windows 10 10.0.19042

I'm experimenting with transactions and found that the documentation of the isolation level 'snapshot table stability' doesn't match the behaviour I experienced. The documentation states that:

If any other transaction has an uncommitted change of data pending in any database table before a transaction with the SNAPSHOT TABLE STABILITY isolation level is started, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an exception.

This is not correct statement. It is about RESERVING clause and means not just "ANY table" but "ANY explicitly RESERVED by starting transaction" table.

Since you use no RESERVING clause, this statement is not applicable to your case.

Regards,

Vlad

[KS85]

Ok, thank you Vlad. Should I inform the documentation team to write this statement more explicit, like you did? I think for newcomers like me its a bit misleading otherwise.

By the way: I found this article to be very helpful:  the database experts Doc/Transaction options explained (ibexpert.net)

Regards,

KS

[Vlad Khorsun]

On Friday, 30 July 2021 at 14:05:30 UTC+3 KS85 wrote:

Ok, thank you Vlad. Should I inform the documentation team to write this statement more explicit, like you did? I think for newcomers like me its a bit misleading otherwise.

Yes, please

 

By the way: I found this article to be very helpful:  the database experts Doc/Transaction options explained (ibexpert.net)

Claudio was one of the Firebird developers and wrote a lot of good and useful arcticles.

Regards,

Vlad

[Mark Rotteveel]

On 30-07-2021 13:05, 'KS85' via firebird-support wrote:
> Ok, thank you Vlad. Should I inform the documentation team to write this
> statement more explicit, like you did? I think for newcomers like me its
> a bit misleading otherwise.

I fixed the wording. Though to be honest, this behaviour seems more
related to the RESERVING clause than to the SNAPSHOT TABLE STABILITY
isolation level, so I'm not sure it even belongs in that section. I
might rewrite it further at a later point.

Mark
--
Mark Rotteveel

[KS85]

Thank you Mark

Regards

KS