Re: Changing the transaction isolation level within - Mailing list pgsql-sql

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