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@mageo.cz)
