Обсуждение: Bug #722: SELECT FOR UPDATE bug

Pablo Aviles
27/07/2002 10:58


        To:     Tom Lane <tgl@sss.pgh.pa.us>
        cc:=20
        Subject:        Re: [BUGS] Bug #722: SELECT FOR UPDATE bug

=20
 Hi,
we are use Postgresql 7.3.
The case in questions is: we have many clients (pc's) of postgresql. Every=
=20
client try to block an employee to display at user. The user make change=20
in the employee, but nobody can take the same employee at same time.=20
Before changes, the next step is update the record (Record blocked)

But, when I use=20
>   SELECT * FROM employees=20
>   WHERE status =3D 'A'=20
>   LIMIT 1=20
>   FOR UPDATE;=20
to catch the employee, only the first user select a record. Other users=20
can`t select any employee until the first user release the record. The=20
first conclusion here, is that Postgresql block all employee table.
When we try to use the LOCK sentence, the result don=B4t have change.

regards,=20
Pablo Avil=E9s Cisneros
Technology, ITS=20
Software Development Area
Technology, ITS=20
San Jos=E9, Costa Rica
email: paviles@its.co.cr
=20
=20




Tom Lane <tgl@sss.pgh.pa.us>
27/07/2002 09:58

=20
        To:     paviles@its.co.cr, pgsql-bugs@postgresql.org
        cc:=20
        Subject:        Re: [BUGS] Bug #722: SELECT FOR UPDATE bug


pgsql-bugs@postgresql.org writes:
>   SELECT * FROM employees=20
>   WHERE status =3D 'A'=20
>   LIMIT 1=20
>   FOR UPDATE;=20

> The problem, in the previous sentence, is block every record on
> employees, although we want one record, we want limit 1.

I do not believe that.  Could you give a more complete example of
your problem?

It would also help if you'd state which Postgres version you're using.

                                                 regards, tom lane

On Fri, 26 Jul 2002 paviles@its.co.cr wrote:

> Pablo Aviles
> 27/07/2002 10:58
>
>
>         To:     Tom Lane <tgl@sss.pgh.pa.us>
>         cc:
>         Subject:        Re: [BUGS] Bug #722: SELECT FOR UPDATE bug
>
>
>  Hi,
> we are use Postgresql 7.3.
> The case in questions is: we have many clients (pc's) of postgresql. Every
> client try to block an employee to display at user. The user make change
> in the employee, but nobody can take the same employee at same time.
> Before changes, the next step is update the record (Record blocked)
>
> But, when I use
> >   SELECT * FROM employees
> >   WHERE status = 'A'
> >   LIMIT 1
> >   FOR UPDATE;
> to catch the employee, only the first user select a record. Other users
> can`t select any employee until the first user release the record. The

If you use that same query in two transactions of course it's going to
block, they're almost certainly going to try to get the same record and
the second'll block since the first already has the lock.

When I do the following:
sszabo=# create table employee(name text, status char(1));
CREATE TABLE
sszabo=# insert into employee('a', 'a');
ERROR:  parser: parse error at or near "'a'"
sszabo=# insert into employee values('a', 'a');
INSERT 8567431 1
sszabo=# insert into employee values('b', 'a');
INSERT 8567432 1
sszabo=# insert into employee values('c', 'b');
INSERT 8567433 1

Transaction 1:
begin;
sszabo=# select * from employee where status='a' for update limit 1;
 name | status
------+--------
 a    | a
(1 row)

Transaction 2:
sszabo=# begin;
BEGIN
sszabo=# select * from employee where status='a' and name='b' for update
limit 1;
 name | status
------+--------
 b    | a
(1 row)

Whereas running the same query in both causes the second to block until
the first finishes.  If I update the first transaction's row in that
transaction so that it doesn't match, the second query doesn't return
anything, which might be a bug however (I'm not sure what should
happen in that case)