Re: Procedure - how to lock some records in table

On 13 Sie, 23:01, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 13.08.2008 18:27, joel garry wrote:
>
> > On Aug 13, 7:49 am, PiotreK <piotr.kna..._at_gmail.com> wrote:
> >> On 13 Sie, 15:41, "rogergorden@....gmail.com" <rogergor..._at_gmail.com>
> >> wrote:
> >> Again in some performance test by grinder the same group sometimes is
> >> taken by two different users... I don't have a clue why it is like
> >> this...
>
> > Please read the concepts manual about when a transaction begins.  You
> > have two different users taking the group because the two users each
> > have a read consistent view of the database as it exists when their
> > transaction starts, which says the group is available.  If you select
> > for update, the second one will not be able to update because he will
> > be locked out.  Your code needs to handle that.
>
> Another remark: I don't see the need for all this dynamic SQL.  Also, it
> might be that the whole thing can be solved with one (a single UPDATE)
> or two (SELECT ... FOR UPDATE and then UPDATE) SQL statements doing set
> operations.
>
> Btw, I spotted a missing "THEN" - so that code seems to not even be
> syntactically correct.
>
> Kind regards
>
>         robert

The whole code is simplified version and not tested - the 'INTO' part is also missing from some of the EXECUTE IMMEDIATE statements. Dynamic language is also needed (that's right - you can't see it on this example...). I will try to do my best and I will write the effects tomorrow. I think proper use of SELECT ... FOR UPDATE will succeed.

Thanks for instructions,
PiotreK Received on Wed Aug 13 2008 - 17:07:54 CDT