Thread: concurrency in stored procedures

Hi,

using constraints on tables I was able to remove some race conditions,
because the unique index prevents the same data to be inserted twice
into the table.

But I still didn't fix all the race conditions, because in some
functions I have to modify more than one table or I just have read and
write data in the same table. So, what is the best way to handle
concurrency in stored procedures?

I read that using locks isn't good because it may lead to deadlocks, so
I was thinking about transactions, but I wan't able to find a good example.

What would you to in order to be sure that one function or a part of it
is atomically executed?

I also read that postgresql is able to detect deadlocks and can try to
solve them. How does this happen in a stored procedure and how can a
procedure know that it was aborted because of the deadlock?

Thank you

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

Ottavio Campana wrote:
> What would you to in order to be sure that one function or a part of it
> is atomically executed?

would it be correct something like? or how would you write this?

create or replace function my_function () returs integer as
$$
declare
  ...
  status boolean;
  ...
begin
  ...
  loop
    begin
      set transaction isolation level serializable;
      ...
      do_something();
      ...
      status := true;
    exception serialization_failure
      status := false;
    end;

    if status then exit;
  end loop;
  ...
  return 0;
end
$$ language plpgsql

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

On 3/23/07, Ottavio Campana <ottavio@campana.vi.it> wrote:
> Ottavio Campana wrote:
> > What would you to in order to be sure that one function or a part of it
> > is atomically executed?
>
> would it be correct something like? or how would you write this?
>
> create or replace function my_function () returs integer as
> $$
> declare
>   ...
>   status boolean;
>   ...
> begin
>   ...
>   loop
>     begin
>       set transaction isolation level serializable;
>       ...
>       do_something();
>       ...
>       status := true;
>     exception serialization_failure
>       status := false;
>     end;
>
>     if status then exit;
>   end loop;
>   ...
>   return 0;
> end
> $$ language plpgsql

you can also use advisory locks if you want to implement 'critical
section' inside a plpgsql function.

see:
http://merlinmoncure.blogspot.com/2006/12/advisory-locks-part-2.html

merlin

Merlin Moncure wrote:
> On 3/23/07, Ottavio Campana <ottavio@campana.vi.it> wrote:
>> Ottavio Campana wrote:
>> > What would you to in order to be sure that one function or a part of it
>> > is atomically executed?
>>
>> would it be correct something like? or how would you write this?
>>
>> create or replace function my_function () returs integer as
>> $$
>> declare
>>   ...
>>   status boolean;
>>   ...
>> begin
>>   ...
>>   loop
>>     begin
>>       set transaction isolation level serializable;
>>       ...
>>       do_something();
>>       ...
>>       status := true;
>>     exception serialization_failure
>>       status := false;
>>     end;
>>
>>     if status then exit;
>>   end loop;
>>   ...
>>   return 0;
>> end
>> $$ language plpgsql
>
> you can also use advisory locks if you want to implement 'critical
> section' inside a plpgsql function.

am I wrong or are advisory locks available only in 8.2?

Anyway, apart from a couple of errors in the code i wrote (an in isn't
closed and exception handling is not correctly written), can I be sure
that the code in the sub-block works an a snapshot of the db?

Thank you

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.

Ottavio Campana wrote:
> Anyway, apart from a couple of errors in the code i wrote (an in isn't
> closed and exception handling is not correctly written), can I be sure
> that the code in the sub-block works an a snapshot of the db?

It seems not to work, I put it into my application and I got

Error Type: ProgrammingError
Error Value: ERROR: SET TRANSACTION ISOLATION LEVEL must be called
before any query CONTEXT: SQL statement "set transaction isolation level
serializable" PL/pgSQL function "test_function" line 31 at SQL statement
select test_function ( 11, 'tizio', '', ' ', '', ' ', '', ' ', ' ', ' ',
'', '', ' ', ' ', '0.0', ' ', '', 1, 1, 0.0, 1 ) as risultato;

So I think I'll have to use locking....

--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.