python, threads and pgsqlodbc problems

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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: python, threads and pgsqlodbc problems

Hi.

Sorry, very late reaction....

I tried it. Then, one has noticed mxODBC being a commercial license then...
However, it installed by the reason said that there is an evaluation license.
I installed it from the following site.
http://www.egenix.com/products/python/mxODBC/

First..

iinet% py_thread.py
/usr/local/lib/python2.4/site-packages
Traceback (most recent call last):
File "py_thread.py", line 9, in ?
import mx.ODBC.iODBC as ODBC
File "mx/ODBC/iODBC/__init__.py", line 8, in ?
ImportError: initialization of module mxODBC failed
(mx.ODBC.iODBC.LicenseError:mx.ODBC.license could not be loaded; please visit the
http://www.egenix.com/ web-site to obtain a license file or write to licenses@egenix.com for
information.)

Uga...

I registered and got the evaluation license.

License was received.

> unzip -x licenses.zip
> cp 9753-6907-2465-9987-3790-6672/* /usr/local/lib/python2.4/site-packages/mx/ODBC

Second...

inet% py_thread.py
/usr/local/lib/python2.4/site-packages
never reached
never reached
done

My environment:
I changed your sample unixODBC into iODBC.
--
psqlODBC Version 08.02.0430.
FreeBSD 6.0-RELEASE
libiodbc-3.52.4

Therefore, I think that a problem does not exist now.

Regards,
Hiroshi Saito

----- Original Message -----
From: "Michal Vitecek"
To:
Sent: Friday, September 07, 2007 6:27 PM
Subject: [ODBC] python, threads and pgsqlodbc problems


> 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly