Обсуждение: Concurrency problem

Hi,
I have a problem with concurrency,
where ordinary selects render me

Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
/home/site/PHP/db_func.php on line 301

Code of get_rights:GRANT SELECT ON c.users TOadm,w3;
                                                                          SELECT rights INTO result               FROM
c.users              WHERE usr_id=uid;
 
       REVOKE ALL ON c.users FROM w3,adm;       RETURN result;

What function is in error at a specific point in time appears to 
be random. There are no "update" SQL commands issued between two loads
(actually two meny selections in sequence, the first not being served
completely). Still the db complain about "updates". I use triggers only
at "create" and in some instances "update", not when doing "select".
Tables are interconnected by foreign keys, sometimes mutliple.

Is there anybody out there with similar experiences? 
Someone who might point in some directions in terms of 
documentation of *how* to design and use a database to 
avoid concurrency problems?

Any thoughts on this are greatly appreciated.

/Dag

Dag Gullberg <dag.gullberg@telia.com> writes:
> Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> /home/site/PHP/db_func.php on line 301

> Code of get_rights:
>     GRANT SELECT ON c.users TO
>     adm,w3;
                            
 
>         SELECT rights INTO result
>                 FROM c.users
>                 WHERE usr_id=uid;

>         REVOKE ALL ON c.users FROM w3,adm;
>         RETURN result;

Do you have a bunch of clients doing this in parallel?  If so, the
problem is probably coming from two instances of the function trying to
update the catalog entry for c.users at the same time.  The approach
is fundamentally broken anyhow, because there is nothing stopping
client 2 from revoking the rights in between client 1 doing his GRANT
and his SELECT.

The right way to do what you seem to want (allow only this function to
access the table) is not to flap the rights settings back and forth
like that.  Grant SELECT rights to a specific userid and make the
function be owned by that userid and be labeled SECURITY DEFINER.
        regards, tom lane

Hi Tom,

Yes, I have several clients connecting to the db, using the same
username, doing the same things, pretty much.
Please educate me: 
When a table is accessed, is there an entry that is updated in
pg_catalog.pg_tables (or somewhere else) in such a fashion that the MVCC
cannot handle it and that consecutive selects from other clients will
cause concurrency errors? Are these entries not "locked" while accessed?

What's more in your response is very clear and it is amazing how easy it
is to slip into singel user reasoning, if not careful,  when writing
code for a multi user environment. Its a blunder, pure and simple.  I
stand corrected (and wiser). Thanks! 

/Dag

(apologies for sending this to you private address first)

sön 2004-10-03 klockan 19.15 skrev Tom Lane:
> Dag Gullberg <dag.gullberg@telia.com> writes:
> > Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> > CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> > /home/site/PHP/db_func.php on line 301
> 
> > Code of get_rights:
> >     GRANT SELECT ON c.users TO
> >     adm,w3;
                              
 
> >         SELECT rights INTO result
> >                 FROM c.users
> >                 WHERE usr_id=uid;
> 
> >         REVOKE ALL ON c.users FROM w3,adm;
> >         RETURN result;
> 
> Do you have a bunch of clients doing this in parallel?  If so, the
> problem is probably coming from two instances of the function trying to
> update the catalog entry for c.users at the same time.  The approach
> is fundamentally broken anyhow, because there is nothing stopping
> client 2 from revoking the rights in between client 1 doing his GRANT
> and his SELECT.
> 
> The right way to do what you seem to want (allow only this function to
> access the table) is not to flap the rights settings back and forth
> like that.  Grant SELECT rights to a specific userid and make the
> function be owned by that userid and be labeled SECURITY DEFINER.

>     Yes, I have several clients connecting to the db, using
> the same username, doing the same things, pretty much.
>
>     Please educate me:
> When a table is accessed, is there an entry that is updated
> in pg_catalog.pg_tables (or somewhere else) in such a fashion
> that the MVCC cannot handle it and that consecutive selects
> from other clients will cause concurrency errors? Are these
> entries not "locked" while accessed?

No, but the GRANT statement is translated into doing an UPDATE on a
system catalog. The concurrency error only happens when you do UPDATEs,
you can do any number of parallell SELECTs. If you wrote the SQL
directly you would use SELECT FOR UPDATE, but with GRANT I don't think
there is a way to make it use that semantic.

And no, without using FOR UPDATE, rows are not locked when they are read
in MVCC. That's what's so great about it :-)

//Magnus