Re: python, threads and pgsqlodbc problems - Mailing list pgsql-odbc

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" <fuf@mageo.cz>
To: <pgsql-odbc@postgresql.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@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