Re: Retrieving last InsertedID : INSERT... RETURNING safe ?

Heikki

On 20-Feb-08, at 8:14 AM, Heikki Linnakangas wrote:

> Dave Cramer wrote:
>> On 20-Feb-08, at 7:19 AM, Paul Tomblin wrote:
>>> Dave Cramer wrote:
>>>>> Well, that other solution is dangerous in case multiple inserts
>>>>> to that table are done concurrently; a quite common usage pattern
>>>>> with java web applications handling multiple HTTP requests with
>>>>> concurrent java threads..
>>>>>
>>>> No it is not dangerous. It is the right way to do it. There is
>>>> absolutely no danger in using currval in this manner.
>>>
>>> Unless you have autocommit on.
>>>
>> I was going to say there are absolutely no situations where this is
>> not true, however in your case autocommit or not it doesn't matter.
>> You have a single connection for the entire application and
>> asynchronous events using that connection. Autocommit or not it
>> will not work with currval.
>> In your case you must use nextval before doing the insert.
>
> Now you lost me. By asynchronous events, do you mean NOTIFY/LISTEN?
> What exactly is the scenario you're talking about?
>
> One problematic scenario for nextval+currval is an INSERT trigger
> that calls nextval() behind your back, but you can fool any method
> with a trigger if you really want to.
>
As far as I can tall Paul has inherited an application which uses a
single connection for all database operations, and is a swing app
which has callbacks which do the following

Callback code

grab the global connection object
create a statement
do something
close statement

in this scenario, since currval has connection scope if two callbacks
are called at the same time, only one will have the right answer .

Paul am I correct in my assumptions above ?

Dave
>