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

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