Re: lock - feedback - Mailing list pgsql-odbc

From Merlin Moncure
Subject Re: lock - feedback
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3417DD593@Herge.rcsinc.local
Whole thread Raw
In response to lock - feedback  (Thomas Rokohl <rokohl@raygina.de>)
List pgsql-odbc
> >
>
> 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 ;-).

There is no 100% correct answer to this problem.  However, you are
thinking in terms of pessimistic locking (like most people do), so by
all means use it.

By 'lost update' problem, I assume you mean
user A reads
user B reads
user A writes
user B writes <-- overwrites user A's changes

I suggest you read up on locking strategies.  Pessimistic is the easiest
way to solve this problem but rarely the best way.  Worst problem here
is you are blocking on reads which (drastically) lowers concurrency and
forces your app to be prepared to handle read failures...this can be a
huge headache.

Pure optimistic locking (in example above) is obviously not suitable in
multi user applications if you care about your data.  However, you could
use timestamps + assertions to throw an error during an update if you
are out of synch with the server here.  The major difference here is
that you are making user aware of problem when it happens, not when your
app thinks it might happen.  This type of lock resolution can be made
via rules, triggers, or a specialized dynamic sql procedure to resolve
the record.

also, this is off-topic for odbc list :)

merlin


pgsql-odbc by date:

Previous
From: "Merlin Moncure"
Date:
Subject: Re: lock - feedback
Next
From: "Victor Rivero"
Date:
Subject: Re: Problem with psqlODBC on "Cache Size"