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(at)egenix(dot)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" <fuf(at)mageo(dot)cz>
To: <pgsql-odbc(at)postgresql(dot)org>
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(at)mageo(dot)cz)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly