Transaction ISOLATION LEVEL - have I missed something?

From: Pól Ua Laoínecháin <linehanp(at)tcd(dot)ie>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Transaction ISOLATION LEVEL - have I missed something?
Date: 2021-05-13 15:07:12
Message-ID: CAF4RT5RFCtbAnaHqRLomHYESwNcudd1XMu=3DjPwU=xPrkKDvw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all, not sure if this is for novice or general - however...

Fiddle here:

> https://dbfiddle.uk/?rdbms=postgres_12&fiddle=49de306c75e85ce4ba900719e2545c32

> SHOW TRANSACTION ISOLATION LEVEL;

Result:

> transaction_isolation
> read committed

So far, so good, then

> CREATE TABLE x (y INT, z VARCHAR (25));
> INSERT INTO x VALUES (1, 'One'), (2, 'Two'), (3, 'Three');

and now for a transaction:

> START TRANSACTION;

> SELECT CURRENT_TIMESTAMP(6);

> SELECT * FROM x;

> SELECT CURRENT_TIMESTAMP(6);

> COMMIT;

Result:

> current_timestamp 2021-05-13 15:51:11.194766+01

> y z
> 1 One
> 2 Two
> 3 Three

> current_timestamp 2021-05-13 15:51:11.194766+01

Now, my understanding of ISOLATION LEVEL = read-committed is that the
current_timestamp should change going through the transaction and that
it should not change when the level is serializable.

Am I wrong about this?

I did a similar experiment with MariaDB and none of the times are the same:

> https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=ad2d3b2f360c3dbe59fa00f623a3db6b

Have I misunderstood transactions and isolation levels...

URLs, references and pointers to same appreciated.

TIA and rgs,

Pól...

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2021-05-13 15:13:31 Re: Transaction ISOLATION LEVEL - have I missed something?
Previous Message Pavel Tide 2021-04-29 17:34:40 Re: suppress empty archive_command warning message