python, threads and pgsqlodbc problems

[Michal Vitecek <fuf(at)mageo(dot)cz>]

 hello everyone,

 recently one of my projects which uses threads extensively started to
 freeze. after some digging i found out that the freezes are caused by
 2+ threads doing UPDATE to the same row in the same table:

   thread 1                    |                    thread 2
   ----------------------------+----------------------------
   BEGIN TRANSACTION           | BEGIN TRANSACTION
   ----------------------------+----------------------------
   UPDATE tableA SET           |
   columnA = 1 WHERE           |
   columnB = 2                 |
   ----------------------------+----------------------------
                               | UPDATE tableA SET
                               | columnA = 1 WHERE
                               | colunmB = 2
   ----------------------------+----------------------------
   COMMIT TRANSACTION (*)      | COMMIT TRANSACTION (*)

 (*) never reached

 the simplest code that exhibits the problem is:

---CUT HERE---
#!/usr/bin/python
import threading
import time
import mx.ODBC.unixODBC as ODBC

class Worker(threading.Thread):
    def __init__(self, sleepBeforeUpdateTime, sleepAfterUpdateTime):
        threading.Thread.__init__(self)
        self.sleepBeforeUpdateTime = sleepBeforeUpdateTime
        self.sleepAfterUpdateTime = sleepAfterUpdateTime

    def run(self):
        d = ODBC.connect(DSN, UID, PWD)
        c = d.cursor()
        c.execute("BEGIN TRANSACTION")
        time.sleep(self.sleepBeforeUpdateTime)
        c.execute("UPDATE tableA SET columnA = 1 WHERE columnB = 2")
        time.sleep(self.sleepAfterUpdateTime)
        c.execute("COMMIT TRANSACTION")
        print "never reached"    # this line is NEVER reached
        d.close()

workers = (
    Worker(0.0, 1.0),            # sleep after issuing UPDATE
    Worker(0.4, 0.0),            # sleep a while before issuing UPDATE
)
for worker in workers:
    worker.start()

time.sleep(5)
print "done"
---CUT HERE---

 i'm using python 2.4.4., mxODBC 2.0.7, unixODBC 2.2.12, psqlodbc
 08.02.0400 and postgresql 8.2.4.
 
 when two processes are doing the same all is working correctly. by
 tracing the process i've found out that the second UPDATE is issued but
 the call to SOCK_get_id() in connection.c doesn't return because the
 database waits for the 1st UPDATE to be either committed or rolled
 back. but since the 1st worker is blocked by the 2nd one it's never
 given chance to issue COMMIT and thus there's no way to recover from
 the situation.

 is there any easy way to remedy the problem?
 
 p.s.: changing the project to use processes instead of threads is a way
 to go, but if there was a simpler solution, i'd be happy to take that
 path.


        thanks a lot,
                    Michal
-- 
		fuf		(fuf(at)mageo(dot)cz)