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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq