SEO

#1 (**permalink**) 04-16-2008, 01:28 AM

> > Have you looked at contrib\userlock? With it, you can simulate
> > pessimistic locks with a non-blocking result code.
> >
> > Merlin
> >
> >
> >
> contrib/userlock? where i can find something about this? in the manual
i

http://developer.postgresql.org/cvsw...l/contrib/user
lock/README.user_locks?rev=1.3;content-type=text%2Fplain

> can't find something ?!?!
> mhm what is with the "lost update" problem, can i solve it with it?!?!

probably. However, in many cases proper use of transactions is more
appropriate. This will be even easier to do when we get proper
assertions.

> i can't visualize how it can works?
select user_write_lock(oid) from my_table where value = x;
returns 1 on success, 0 on failure.

just be careful...
select user_write_lock(oid) from my_table where value = x order by y
limit 1;
can acquire more locks than you might think since the table has to be
materialized to do the order.

better to write:

select user_write_lock(oid) from
(
select oid, * from my_table where value = x order by y limit 1;
)

also, don't use oid

. In my applications, I make a domain type called
'cuid' which pulls nextval() from a public sequence. Put that into your
tables and lock on it. Just watch for dump/restore.

Merlin

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2 (**permalink**) 04-16-2008, 01:28 AM

Merlin Moncure wrote:
> select user_write_lock(oid) from my_table where value = x;
> returns 1 on success, 0 on failure.
>
> just be careful...
> select user_write_lock(oid) from my_table where value = x order by y
> limit 1;
> can acquire more locks than you might think since the table has to be
> materialized to do the order.
>
> better to write:
>
> select user_write_lock(oid) from
> (
> select oid, * from my_table where value = x order by y limit 1;
> )
>
> also, don't use oid

. In my applications, I make a domain type called
> 'cuid' which pulls nextval() from a public sequence. Put that into your
> tables and lock on it. Just watch for dump/restore.
>
> Merlin
>
>
>

ok i understand, thanks. that work's.

but i'm a little bit confused. this problem is, from my point of view, a
highly frequently appearing problem.
on all places where it can be that two users edit the same record, the
"lost update" problem or the "waiting" problem ;-) can be appeared.
and this is not rare i think.
your solution is good and works surely
<http://dict.leo.org/se?lp=ende&p=lURE.&search=surely> fine( i will test
it ), but it is also unhandy.
my opinion is that this problem should be solved by the database and not
by the user, so i think it is a good point for a wish list ;-).

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: lock - feedback