Thread: Can I use row-level locks to sequence READ COMMITTED transactions?

Can I use row-level locks to sequence READ COMMITTED transactions?

From

Matthew Woodcraft

Date:

22 July 2002, 11:40:35

If I use a READ COMMITTED transaction, the documentation says that a
query in my transaction may see changes which were committed by other
transactions after my transaction started.

My question is, is it guaranteed that a SELECT in my transaction will
see changes previously committed by other transactions, or is it only a
possibility?

By 'previously committed', I mean 'committed by a transaction which held
a row-level lock which my transaction has since obtained'.



For example, if I run the following transaction many times
simultaneously,

    BEGIN;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT 1 FROM walls WHERE wall_id = 3 FOR UPDATE;

    INSERT INTO bottles (
            wall_id,
            number_seen)
        VALUES (
            3,
            (SELECT COUNT(*) FROM bottles WHERE wall = 3)
        );
    COMMIT;

can I end up with two rows in the 'bottles' table with the same value
for 'seen'? Or do I have a guarantee that after one instance of the
transaction has released the 'SELECT FOR UPDATE' lock, any other
instance will see the data that it has committed?


-M-

Re: Can I use row-level locks to sequence READ COMMITTED transactions?

From

Tom Lane

Date:

22 July 2002, 19:35:17

Matthew Woodcraft <mattheww@chiark.greenend.org.uk> writes:
> If I use a READ COMMITTED transaction, the documentation says that a
> query in my transaction may see changes which were committed by other
> transactions after my transaction started.
> My question is, is it guaranteed that a SELECT in my transaction will
> see changes previously committed by other transactions, or is it only a
> possibility?
> By 'previously committed', I mean 'committed by a transaction which held
> a row-level lock which my transaction has since obtained'.

Yes, that will work.  The SELECT sees rows that were committed at the
instant it starts, which will be after the other xact completes if you
use a FOR UPDATE lock.

            regards, tom lane

Re: Can I use row-level locks to sequence READ COMMITTED transactions?

From

"Craig Sturman"

Date:

22 July 2002, 19:48:53

Actually, I was having problems with restoring a pg dump too although it may
be tied to my date settings.  I changed my date format to non-US and I
periodically get an email containing the following:

From: Cron Daemon [mailto:root@hades.emedscentral]
Sent: July 22, 2002 4:00 AM
To: postgres@hades.emedscentral
Subject: Cron <postgres@hades> [ -x
/usr/lib/postgresql/bin/do.maintenance ] &&
/usr/lib/postgresql/bin/do.maintenance -a


NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date

When I try to restore a dumped database, this error comes back up along with
the following user error:

craig@hades:~$ psql polaris < /home/craig/enigma.dump
NOTICE:  Conflicting settings for date
\connect: FATAL 1:  IDENT authentication failed for user "postgres"

craig@hades:~$

Anyone know where else I should change my date format and whether this could
be causing the ident problem?  Both the database and dump file are owned by
the user craig.  This is all under PostgreSQL 7.2.1 on a Debian woody
system.

Thanks,

Craig S.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: July 22, 2002 4:31 PM
To: Matthew Woodcraft
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can I use row-level locks to sequence READ
COMMITTED transactions?


Matthew Woodcraft <mattheww@chiark.greenend.org.uk> writes:
> If I use a READ COMMITTED transaction, the documentation says that a
> query in my transaction may see changes which were committed by other
> transactions after my transaction started.
> My question is, is it guaranteed that a SELECT in my transaction will
> see changes previously committed by other transactions, or is it only a
> possibility?
> By 'previously committed', I mean 'committed by a transaction which held
> a row-level lock which my transaction has since obtained'.

Yes, that will work.  The SELECT sees rows that were committed at the
instant it starts, which will be after the other xact completes if you
use a FOR UPDATE lock.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Can I use row-level locks to sequence READ COMMITTED transactions?

From

"Craig Sturman"

Date:

22 July 2002, 19:55:09

Oops.  Forgot to mention that I changed the date format in
/etc/postgresql/postgresql.conf but no where else.  Sorry for the omission.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Craig Sturman
Sent: July 22, 2002 4:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can I use row-level locks to sequence READ
COMMITTED transactions?


Actually, I was having problems with restoring a pg dump too although it may
be tied to my date settings.  I changed my date format to non-US and I
periodically get an email containing the following:

From: Cron Daemon [mailto:root@hades.emedscentral]
Sent: July 22, 2002 4:00 AM
To: postgres@hades.emedscentral
Subject: Cron <postgres@hades> [ -x
/usr/lib/postgresql/bin/do.maintenance ] &&
/usr/lib/postgresql/bin/do.maintenance -a


NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date
NOTICE:  Conflicting settings for date

When I try to restore a dumped database, this error comes back up along with
the following user error:

craig@hades:~$ psql polaris < /home/craig/enigma.dump
NOTICE:  Conflicting settings for date
\connect: FATAL 1:  IDENT authentication failed for user "postgres"

craig@hades:~$

Anyone know where else I should change my date format and whether this could
be causing the ident problem?  Both the database and dump file are owned by
the user craig.  This is all under PostgreSQL 7.2.1 on a Debian woody
system.

Thanks,

Craig S.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: July 22, 2002 4:31 PM
To: Matthew Woodcraft
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can I use row-level locks to sequence READ
COMMITTED transactions?


Matthew Woodcraft <mattheww@chiark.greenend.org.uk> writes:
> If I use a READ COMMITTED transaction, the documentation says that a
> query in my transaction may see changes which were committed by other
> transactions after my transaction started.
> My question is, is it guaranteed that a SELECT in my transaction will
> see changes previously committed by other transactions, or is it only a
> possibility?
> By 'previously committed', I mean 'committed by a transaction which held
> a row-level lock which my transaction has since obtained'.

Yes, that will work.  The SELECT sees rows that were committed at the
instant it starts, which will be after the other xact completes if you
use a FOR UPDATE lock.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster