Thread: Changing the transaction isolation level within the stored procedure?

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

Hi, Mario,

Mario Splivalo wrote:
> Is it possible to change the transaction level within the procedure?

No, currently not, the PostgreSQL "stored procedures" really are "stored
functions" that are called inside a query, and thus cannot contain inner
transactions.

> 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:
[...]
> 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).

So you even want to change the transaction serialization level within a
running transaction? I'm sorry, this will not work, and I cannot think
of a sane way to make it work.

It is locically not possible to raise the isolation level when the
transaction was started with a lower level and thus may already have
irreversibly violated the constraits that the higher level wants to
guarantee.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

> 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?
>

you need to set the transaction level after the begin and before every
other statement... after the begin you have a select that invoke your
function so that set is not the first statement...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Wed, 2006-01-25 at 11:46 -0500, Jaime Casanova wrote:

> you need to set the transaction level after the begin and before every
> other statement... after the begin you have a select that invoke your
> function so that set is not the first statement...

But I can't do that inside of a function, right?
Mari

On Wed, 2006-01-25 at 15:54 +0100, Markus Schaber wrote:
> Hi, Mario,
> 
> Mario Splivalo wrote:
> > Is it possible to change the transaction level within the procedure?
> 
> No, currently not, the PostgreSQL "stored procedures" really are "stored
> functions" that are called inside a query, and thus cannot contain inner
> transactions.

Is above true for the newly introduced stored procedures? (Above, when
mentioning 'stored procedures' I was actualy reffering to 'functions').

> > 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:
> [...]
> > 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).
> 
> So you even want to change the transaction serialization level within a
> running transaction? I'm sorry, this will not work, and I cannot think
> of a sane way to make it work.

I have some ideas, I just needed confirmation it can't be done this way.
Thank you! :)

> It is locically not possible to raise the isolation level when the
> transaction was started with a lower level and thus may already have
> irreversibly violated the constraits that the higher level wants to
> guarantee.

Yes, a thread will need to start a transaction, I'm just affraid that
create_message could lead me to deadlocks.

Thank you for your responses.
Mario

Hi, Mario,

Mario Splivalo wrote:

>>you need to set the transaction level after the begin and before every
>>other statement... after the begin you have a select that invoke your
>>function so that set is not the first statement...
> 
> But I can't do that inside of a function, right?

Right, as you need a SELECT to actually execute your function, so the
transaction commands inside the function are invoced _after_ the first
SELECT began execution.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Hi, Mario,

My explanation is a little longer, as I think I must at least basically
explain some of the fundamentals of database synchronization.

Mario Splivalo wrote:

>>>Is it possible to change the transaction level within the procedure?
>>No, currently not, the PostgreSQL "stored procedures" really are "stored
>>functions" that are called inside a query, and thus cannot contain inner
>>transactions.
> Is above true for the newly introduced stored procedures? (Above, when
> mentioning 'stored procedures' I was actualy reffering to 'functions').

I have to admit that I don't know what "newly introduced stored
procedures" you're talking about? Is this an 8.2 feature?

>>So you even want to change the transaction serialization level within a
>>running transaction? I'm sorry, this will not work, and I cannot think
>>of a sane way to make it work.
> I have some ideas, I just needed confirmation it can't be done this way.
> Thank you! :)
>>It is locically not possible to raise the isolation level when the
>>transaction was started with a lower level and thus may already have
>>irreversibly violated the constraits that the higher level wants to
>>guarantee.
> Yes, a thread will need to start a transaction, I'm just affraid that
> create_message could lead me to deadlocks.

Don't misinterpret transaction isolation as locking.

PostgreSQL (and e. G. Oracle) use a MVCC system for transactions, that
doesn't need exclusive locks.

Read-only transactions can never collide, and writing transactions only
when using transaction isolation "serializable" and manipulating the
same data rows. Some of the colliding transactions will be aborted to
resolve the conflicts, and the others can commit fine.

AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
the only way to introduce deadlocks is to issue LOCK commands to take
locks manually. And for this rare case, PostgreSQL contains a deadlock
detection routine that will abort one of the insulting transactions, and
the others can proceed.

I suggest you to read "Chapter 12. Concurrency Control" from the
PostgreSLQ docs.

Its easy: if you need "read committed" guarantees, then run the entire
transaction as "read committed". If you need "serializable", then run
the entire transaction as "serializable". If you need real serialization
and synchronization of external programs, use LOCK (or take a deep
breath, redesign your application and use e. G. LISTEN/NOTIFY. Most
times, the usage of LOCK is a good indicator of misdesign.)

I just re-read your original posting. You want to make thread B wait
until thread A has committed. This will not be possible with the ACID
levels. Even when using "serializable" for both threads. If thread B
issues SELECT after thread A committed, then all works fine. If thread B
issues SELECT before thread A commits, it sees the database in the state
it was before thread A started its transaction (so even create_message
has not been called). It cannot know whether thread A will COMMIT or
ROLLBACK.

Transaction isolation is about consistency guarantees, not for true
serialization. The reason for this is that databases with high load will
need to allow paralellism.

So for your case, threas A should issue "NOTIFY" before COMMIT, and then
thread B should use LISTEN and then wait for the notification before
beginning its transaction. Be shure to read the paragraph about how
"NOTIFY interacts with SQL transactions" in the NOTIFY documentation.

I don't know the exact sematics of set_message_status and your checks,
but it may be another solution to split thread A into two transactions
by committing after step 3, and another BEGIN after step 4.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

On Thu, Jan 26, 2006 at 10:42:54AM +0100, Markus Schaber wrote:

> AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
> the only way to introduce deadlocks is to issue LOCK commands to take
> locks manually. And for this rare case, PostgreSQL contains a deadlock
> detection routine that will abort one of the insulting transactions, and
> the others can proceed.

You can too.  Consider this:

t1                                        t2

BEGIN;                                    BEGIN;
UPDATE table1 SET col1=                   UPDATE table2 SET col1=    col1+5;                                  (SELECT
col3FROM
 
DELETE FROM table2 WHERE                          table3);col1 = col1+6;                    UPDATE table1 SET col1 =
                                col1 +5;
 
COMMIT;                                   COMMIT;

Suppose these are concurrent.  The problem here is that each
transaction need something in the other transaction either to
complete or rollback before the work can proceed.  So one of them has
to lose.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.     --Dennis Ritchie

Hi, Andrew,

Andrew Sullivan wrote:

>>AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
>>the only way to introduce deadlocks is to issue LOCK commands to take
>>locks manually. And for this rare case, PostgreSQL contains a deadlock
>>detection routine that will abort one of the insulting transactions, and
>>the others can proceed.
> 
> You can too.  Consider this:
> 
> t1                                        t2
> 
> BEGIN;                                    BEGIN;
> UPDATE table1 SET col1=                   UPDATE table2 SET col1=
>      col1+5;                                  (SELECT col3 FROM
> DELETE FROM table2 WHERE                          table3);
>     col1 = col1+6;                    UPDATE table1 SET col1 =
>                                          col1 +5;
> COMMIT;                                   COMMIT;

Hmm, are you shure that this is correct? The delete will always delete 0
rows.

http://www.postgresql.org/docs/8.0/static/transaction-iso.html#XACT-SERIALIZABLE
contains a nice example in '12.2.2.1. Serializable Isolation versus True
Serializability' that you should probably read.

> Suppose these are concurrent.  The problem here is that each
> transaction need something in the other transaction either to
> complete or rollback before the work can proceed.  So one of them has
> to lose.

Despite the fact that I don't see such a collision in your example:

Depending on the transaction isolation level and exact timings,
colliding queries may lead to different results or even one transaction
aborted, but there is no deadlock under MVCC.

Not needing such locks is the whole point in using MVCC at all.

Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

On Thu, 26 Jan 2006, Markus Schaber wrote:


> AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
> the only way to introduce deadlocks is to issue LOCK commands to take
> locks manually. And for this rare case, PostgreSQL contains a deadlock
> detection routine that will abort one of the insulting transactions, and
> the others can proceed.

That's not true.  See all the complaints about pre-8.1 foreign keys and
the row locks taken out by FOR UPDATE as an example.

A simpler example than the one given before (with potential timing) is:
create table t1 (a int);
create table t2 (a int);
insert into t1 values(1);
insert into t2 values(1);

T1: begin;
T2: begin;
T1: update t1 set a=3;
T2: update t2 set a=3;
T1: update t2 set a=2;
T2: update t1 set a=2;

On Thu, Jan 26, 2006 at 01:51:27PM +0100, Markus Schaber wrote:
> Hmm, are you shure that this is correct? The delete will always delete 0
> rows.

Quite, and no it won't.  The contrived example is actually a
simplification of a case one of our developers implemented.  The
conflict is on the updates.  Two concurrent transactions likely
wouldn't be enough to cause it on a fast system, but multiple ones
for sure will.

The problem is that the updates have to wait for one another to
complete in order to know what result they can use, but then the
_other_ contention on the other table causes them to have to wait for
one another there.  I don't think anybody would have gone to the
trouble of putting in deadlock detection if the only way to deadlock
was to trip over yourself with manual locking: presumably, if you're
issuing locks by hand, you either know what you're doing or get what
you deserve.

> Depending on the transaction isolation level and exact timings,
> colliding queries may lead to different results or even one transaction
> aborted, but there is no deadlock under MVCC.
> 
> Not needing such locks is the whole point in using MVCC at all.

I think you don't have a clear idea of what locks are necessary for
updates.  Write operations on a row must block other write operations
on the same row.  If more than one transaction needs the same kinds
of locks on two different tables, but attempts to get those locks in
the opposite order, you are all but guaranteed a deadlock.  MVCC
helps, but it can't avoid locking the same data when that data is
being updated.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.                --Brad Holland

On Thu, 2006-01-26 at 10:42 +0100, Markus Schaber wrote:

> >>>Is it possible to change the transaction level within the procedure?
> >>No, currently not, the PostgreSQL "stored procedures" really are "stored
> >>functions" that are called inside a query, and thus cannot contain inner
> >>transactions.
> > Is above true for the newly introduced stored procedures? (Above, when
> > mentioning 'stored procedures' I was actualy reffering to 'functions').
> 
> I have to admit that I don't know what "newly introduced stored
> procedures" you're talking about? Is this an 8.2 feature?

And I have to cry 'Why am I not Dumbo, so I could cover myslef with me
ears'. I was reffering to the IN/OUT parametar ability for the
functions, and confused them with 'stored procedures' in other RDBMSes.


> PostgreSQL (and e. G. Oracle) use a MVCC system for transactions, that
> doesn't need exclusive locks.

Has nothing to do with a thread, but, even with MVCC you sometimes need
locking. One case is tracking stock by FIFO metod. There, you need to
serialize transactions which take items from the stock, so they're
ordered.

> I suggest you to read "Chapter 12. Concurrency Control" from the
> PostgreSLQ docs.
> 
> Its easy: if you need "read committed" guarantees, then run the entire
> transaction as "read committed". If you need "serializable", then run
> the entire transaction as "serializable". If you need real serialization
> and synchronization of external programs, use LOCK (or take a deep
> breath, redesign your application and use e. G. LISTEN/NOTIFY. Most
> times, the usage of LOCK is a good indicator of misdesign.)

But, I need to control those from the client, right? For some action
that I'd like database to perform I created functions as an interface to
the application. I'd like for database to take care about transactions
and everything. So, there is one 'external' function that client
application calls, and dozen of 'internal' functions that should be
never called by the application (those reside in shema not visible by
the client application role - so the app developers can't easily get to
them).

> 
> I just re-read your original posting. You want to make thread B wait
> until thread A has committed. This will not be possible with the ACID
> levels. Even when using "serializable" for both threads. If thread B
> issues SELECT after thread A committed, then all works fine. If thread B
> issues SELECT before thread A commits, it sees the database in the state
> it was before thread A started its transaction (so even create_message
> has not been called). It cannot know whether thread A will COMMIT or
> ROLLBACK.

Well, what happens now is that thread A calls set_message_status, and
sets the status for the message to, let's say, 20. Then the thread
continues to work, and if everything goes well, does COMMIT at the end.
But, sometimes, if the calculations after the set_message_status take
longer (actually if the SMS gateway goes bezerk, the reconnection
timeout causes thread A to execute a bit longer) thread B starts doing
something for that message, and it needs to set the message status to
90. So it calls set_message_status, and commits. But, when thread A
commits, the status is back to 20. So I tought I'd do a rowlock while
doing an UPDATE on table messages (that's what set_message_status does)
so that thread B needs to wait untill thread A commits or rollbacks. If
it commits, thread B woud do the update, if it rollback, thread B would
return '0 rows updated' (because if thread A issues rollback at the end,
the create_message is also rolled back, so there is nothing to update
for thread B.). I tought serialization woud help here.

Part of my problem goes from working with MSSQL for too long :) There
you can have nested transactions, and you can have different isolation
levels for them.

But, apparently, there was an error in process design. Thread B is fired
up from within the calculations in thread A. But, thread B should be
started only and only if thread A commits. 

> 
> Transaction isolation is about consistency guarantees, not for true
> serialization. The reason for this is that databases with high load will
> need to allow paralellism.
> 
> So for your case, threas A should issue "NOTIFY" before COMMIT, and then
> thread B should use LISTEN and then wait for the notification before
> beginning its transaction. Be shure to read the paragraph about how
> "NOTIFY interacts with SQL transactions" in the NOTIFY documentation.

I've been playing with that, but performance drops significantly with
those notifications. And, it would be a hack. We redesigned the process
so it makes sense :)

> 
> I don't know the exact sematics of set_message_status and your checks,
> but it may be another solution to split thread A into two transactions
> by committing after step 3, and another BEGIN after step 4.

Yes, that was a guideline :) Thank you for your effort!
Mario
-- 
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."

Hi, Andreq,

Andrew Sullivan wrote:

> I think you don't have a clear idea of what locks are necessary for
> updates.  Write operations on a row must block other write operations
> on the same row.  If more than one transaction needs the same kinds
> of locks on two different tables, but attempts to get those locks in
> the opposite order, you are all but guaranteed a deadlock.  MVCC
> helps, but it can't avoid locking the same data when that data is
> being updated.

You're right, I was mislead from my memory.

Sorry for the confusion I brought to this issue.

Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org