Re: Patch for gdl2 to handle LIMIT PostgreSQL keyword in PostgreSQLAdaptor.

[David Ayers]

Hello Thierry,

Am Montag, den 16.03.2009, 20:09 +0100 schrieb Thierry DELHAISE:

> Two daemons (based on gdl2) access the same database/table/schema and
> those two daemons use a "shared" table (like a queue). Those two use
> Pessimistic locking.
> 
> Those start their jobs by requesting some rows to update (FOR UPDATE)
> with some criterias. But based on criterias, the select statement
> execute on table can return more than one row and since FOR UPDATE is
> used, the result is that all rows are locked. Since this is done in a
> transaction, imagine one update failed, and you've to rollback all
> records. So, the currently used/admit technic is to fetch 1 by 1 and
> so lock FOR UPDATE only one row (this is where the "limit 1" is used.
> This was what I wanted to do with gdl2 when I submit this patch. So
> concurrent daemon continue to work and lock only one row at a time.
> Sometimes, one daemon will be blocked until the other unlock the row.
> But here , it is a fonctionnal problem (not the gdl2 one) : the select
> algorythm use/share by all the daemon have to be well design to fit
> this feature and so rely on "application implementation", not gdl2. So
> here LIMIT keyword is used with FOR UPDATE and need to be manage by
> database server, not by gdl2.

Thanks for the explanation.

I this situation, I'd switch the fetch specification back to optimistic
locking by default and configure the EOEditingContext to
setLocksObjectsBeforeFirstModification: YES and lock each object
separately.  You can also use lockObject: explicitly if you like.

Once you saveChanges or invalidateAllObjects the lock should be
released.

Now that is the theory... We'd still need to write a test case to see if
this works as expected.

Cheers,
David

PS: I'm assuming that you are using EOEditingContext... if not you can
probably do the locking at EOAccess level also... let me know if you
need some advice there.