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 plpgsqlyou 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.