From: Mario Splivalo <mario.splivalo@mobart.hr>
To: pgsql-sql@postgresql.org
Subject: Changing the transaction isolation level within the stored procedure?
Date: Wed, 25 Jan 2006 15:12:28 +0100
Message-ID: <1138198348.8828.32.camel@localhost.localdomain> (raw)
Is it possible to change the transaction level within the procedure?
I'm using Postgres 8.1.2 default isolation level. But, I would like one
stored procedure to execute as in serializable isolation level. I have
created my stored procedure like this:
CREATE OR REPLACE FUNCTION set_message_status("varchar", int4)
RETURNS void AS
$BODY$
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE messages SET message_status = $2 WHERE message_id = $1 AND
message_status < $2;
INSERT INTO _update_log VALUES (now()::timestamp, $1, $2);$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
But I'm getting error:
octopussy2=# select * from
set_message_status('b4c15204-123f-4cba-ad09-d423630c999d', 90);
ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT: SQL function "set_message_status" statement 1
I get the same error if I write my stored procedure in plpgsql language.
What I want to achive goes like this:
I have a client code (java/jdbc) that inserts some messages to my
database, and then process them.
Basicaly, it goes like this:
One thread (thread A) does this:
1. java got the message via http (whatever)
2. java does: begin;
3. java does: select * from create_message(...)
4. java does some checking
5. java does: select * from set_message_status(...)
6. java does some more checing
7. java does commit; (under rare circumstances java does rollback).
Another thread (thread B) does this:
1. java got the update_status_request via http (whatever)
2. java does: begin;
3. java does: select * from set_message_status(...)
4. java does: commit;
As I've said, I'm using 'read commited', the default isolation level.
Now, sometimes it happens that steps 4 or 6 take more time, and thread B
steps are executed before steps in thread A have finished. So I would
like the UPDATE in set_message_status to 'hold', until the transaction
that previoulsy called the set_message_status have commited or rolled
back.
Is there a way to do so withing the postgres, or I need to do 'SET
TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both
thread A and thread B?
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@mobart.hr
Subject: Re: Changing the transaction isolation level within the stored procedure?
In-Reply-To: <1138198348.8828.32.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox