Andrus,
thanks! When you say "As PK generator operates at
the JDBC level you can do SELECT FOR UPDATE manually or even use an updateable result set." does that mean I can't do this using Cayenne? I tried using SQLTemplate() but it still possible to update NEXT_ACCOUNT_ID from another connection between the SELECT FOR UPDATE and UPDATE. Can you provide and example?
J.R.
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: Sunday, February 13, 2005 6:05 PM
To: cayenne-use..bjectstyle.org
Subject: Re: One Transaction for select and update
I guess what you are asking for is pessimistic locking (aka SELECT FOR
UPDATE). I am not sure that just wrapping both statements in a single
DB transaction (which would've been trivial to do with Spring or J2EE
external transactions) would solve it.
If it is for PK generation purposes, it shouldn't be too hard to create
a PK generator to use your custom schema. As PK generator operates at
the JDBC level you can do SELECT FOR UPDATE manually or even use an
updateable result set.
Andrus
On Feb 13, 2005, at 12:18 AM, JR Ruggentaler wrote:
> I have a TABLE with one record. I am trying to use it like the primary
> key table (auto_pk_support) is used in Cayenne. I want to get the
> current value (NEXT_ACCOUNT_ID) and increment it by one in a single
> transaction. The code looks like this.
>
> SelectQuery query = new SelectQuery(AccountSequence.class);
> AccountSequence accSequence = (AccountSequence) list.get(0);
> Long nextAccountId = accSequence.getNextAccountId();
> long value = nextAccountId.longValue();
> nextAccountId = new Long(++value);
> accSequence.setNextAccountId(nextAccountId);
> ctxt.commitChanges();
> List list = ctxt.performQuery(query);
>
> How do I do the select and update in one transaction?
>
> Here is the log output from the above code:
>
> - --- transaction started.
> - SELECT t0.NEXT_ACCOUNT_ID, t0.ACCOUNT_SEQUENCE_ID FROM
> ACCOUNT_SEQUENCE t0 - prepared in 20 ms.
> - === returned 1 row. - took 50 ms.
> - +++ transaction committed.
> - --- transaction started.
> - --- will run 1 query.
> - UPDATE ACCOUNT_SEQUENCE SET NEXT_ACCOUNT_ID = ? WHERE
> ACCOUNT_SEQUENCE_ID = ?
> - [bind: 1234100020003003, 200]
> - === updated 1 row.
> - +++ transaction committed.
>
This archive was generated by hypermail 2.0.0 : Sun Feb 13 2005 - 22:58:57 EST