Обсуждение: WARNING: there is already a transaction in progress

Поиск
Список
Период
Сортировка

WARNING: there is already a transaction in progress

От
Matevž Mrak
Дата:
Dear support,

I am using these software with your psqlodbc driver:

unixODBC: 2.3.2.
ODBC psqlodbc driver: http://www.postgresql.org/ftp/odbc/versions/src/ (latest) - psqlodbc-09.03.0300.tar.gz
OSX version: 10.9.3
UCS: UCS2
Eclipse: Eclipse Standard/SDK - Kepler Service Release - Build id: 20140224-0627
Python: 2.7.6
mxBase version: egenix-mx-base-3.2.6-py2.7_ucs2-macosx-10.4-fat-prebuilt
mxODBC version: egenix-mxodbc-3.1.2.macosx-10.4-fat-py2.7_ucs2.prebuilt

-----------------

I am getting error "WARNING: there is already a transaction in progress" when trying to execute command "BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;'"

We have written mutex which is working on other systems to unlock and lock database table using pgsql-odbc driver. Is that possible that transaction is ran automatically by driver or when "cur = self.conn.cursor()" is executed ? Has anyone else experienced that issue?

-----------------

Python code:

                def lock(self):

            """

            Engages the lock

            @return: None

            """

            log.info("mutex lock %d" % self.locked)

            if not self.locked:

                cur = self.conn.cursor()

                log.info("mutex start tran")

                self.locked = True

                try:

                    cur.execute('BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;' % self.table)

                except:

                    log.info("mutex tran failed")

                    log.exception()

                    raise

                else:

                    log.info("mutex tran started")

                cur.close()

                del cur


        def unlock(self):

            """

            Releases the lock

            @return: None

            """

            log.info("mutex unlock %d" % self.locked)

            if self.locked:

                try:

                    cur = self.conn.cursor()

                except:

                    log.exception()

                    raise

                log.info("mutex end trans")

                self.locked = False

                cur.execute("END TRANSACTION")

                log.info("mutex trans ended")

                cur.close()

                del cur


 
Python error: 

InterfaceError: ('25001', -1, 'WARNING: there is already a transaction in progress', 8489)

 - File "/Users/user/ana/ana/srv/orm/driver.py", line 100, in execute

[0m[31m14:58:53.862 0.0.0.0 {unknown} - Query to execute: BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; - File "/Users/user/ana/ana/srv/orm/driver.py", line 101, in execute




SQL ODBC warning in log:

Statement = 0x100a0fa00
SQL = [BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE;][length = 64]
[ODBC][20715][1405083533.616254][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS_WITH_INFO]
DIAG [25001] WARNING: there is already a transaction in progress



(BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; is called three times one afteranother without unlock)


--
Lep pozdrav,
Matevž Mrak

Re: WARNING: there is already a transaction in progress

От
Matevž Mrak
Дата:
            with conn.db_mutex("ANA-autoinc-" + key, "Sys_sequencer"):
                cur = conn.cursor()
                drv = conn.driver
                _table_name = drv.table_name("Sys_sequencer")
                _seq_id = drv.field_name("seq_id")
                _seq_counter = drv.field_name("seq_counter")
                val = cur.execute_single(drv.create_select(_seq_counter, _table_name, {_seq_id: "'%s'" % key})) or 0
                val = int(val) + 1
                cur.execute(drv.upsert(_table_name, {_seq_counter: str(val), _seq_id: "'%s'" % (key,)}, {_seq_id: "'%s'" % (key,)}))
                #val = cur.execute_single(drv.create_select(_seq_counter, _table_name, {_seq_id: "'%s'" % key})) or 0


############################################################# called from there

 Something went wrong with python code (here it is once again):
        def lock(self):
            """
            Engages the lock
            @return: None
            """
            log.info("mutex lock %d" % self.locked)
            if not self.locked:
                cur = self.conn.cursor()
                log.info("mutex start tran")
                self.locked = True
                try:
                    cur.execute('BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;' % self.table)
                except:
                    log.info("mutex tran failed")
                    log.exception()
                    raise
                else:
                    log.info("mutex tran started")
                cur.close()
                del cur


        def unlock(self):
            """
            Releases the lock
            @return: None
            """
            log.info("mutex unlock %d" % self.locked)
            if self.locked:
                try:
                    cur = self.conn.cursor()
                except:
                    log.exception()
                    raise
                log.info("mutex end trans")
                self.locked = False
                cur.execute("END TRANSACTION")
                log.info("mutex trans ended")
                cur.close()
                del cur



2014-07-11 15:38 GMT+02:00 Matevž Mrak <matevz.mr@gmail.com>:
Dear support,

I am using these software with your psqlodbc driver:

unixODBC: 2.3.2.
ODBC psqlodbc driver: http://www.postgresql.org/ftp/odbc/versions/src/ (latest) - psqlodbc-09.03.0300.tar.gz
OSX version: 10.9.3
UCS: UCS2
Eclipse: Eclipse Standard/SDK - Kepler Service Release - Build id: 20140224-0627
Python: 2.7.6
mxBase version: egenix-mx-base-3.2.6-py2.7_ucs2-macosx-10.4-fat-prebuilt
mxODBC version: egenix-mxodbc-3.1.2.macosx-10.4-fat-py2.7_ucs2.prebuilt

-----------------

I am getting error "WARNING: there is already a transaction in progress" when trying to execute command "BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;'"

We have written mutex which is working on other systems to unlock and lock database table using pgsql-odbc driver. Is that possible that transaction is ran automatically by driver or when "cur = self.conn.cursor()" is executed ? Has anyone else experienced that issue?

-----------------

Python code:

                def lock(self):

            """

            Engages the lock

            @return: None

            """

            log.info("mutex lock %d" % self.locked)

            if not self.locked:

                cur = self.conn.cursor()

                log.info("mutex start tran")

                self.locked = True

                try:

                    cur.execute('BEGIN TRANSACTION; LOCK TABLE "%s" IN EXCLUSIVE MODE;' % self.table)

                except:

                    log.info("mutex tran failed")

                    log.exception()

                    raise

                else:

                    log.info("mutex tran started")

                cur.close()

                del cur


        def unlock(self):

            """

            Releases the lock

            @return: None

            """

            log.info("mutex unlock %d" % self.locked)

            if self.locked:

                try:

                    cur = self.conn.cursor()

                except:

                    log.exception()

                    raise

                log.info("mutex end trans")

                self.locked = False

                cur.execute("END TRANSACTION")

                log.info("mutex trans ended")

                cur.close()

                del cur


 
Python error: 

InterfaceError: ('25001', -1, 'WARNING: there is already a transaction in progress', 8489)

 - File "/Users/user/ana/ana/srv/orm/driver.py", line 100, in execute

[0m[31m14:58:53.862 0.0.0.0 {unknown} - Query to execute: BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; - File "/Users/user/ana/ana/srv/orm/driver.py", line 101, in execute




SQL ODBC warning in log:

Statement = 0x100a0fa00
SQL = [BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE;][length = 64]
[ODBC][20715][1405083533.616254][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS_WITH_INFO]
DIAG [25001] WARNING: there is already a transaction in progress



(BEGIN TRANSACTION; LOCK TABLE "Sys_sequencer" IN EXCLUSIVE MODE; is called three times one afteranother without unlock)


--
Lep pozdrav,
Matevž Mrak



--
Lep pozdrav,
Matevž Mrak

Re: WARNING: there is already a transaction in progress

От
David G Johnston
Дата:
matevzm wrote
> I am getting error *"WARNING: there is already a transaction in progress"*
> when trying to execute command "*BEGIN TRANSACTION; LOCK TABLE "%s" IN
> EXCLUSIVE MODE;'"*
>
> We have written mutex which is working on other systems to unlock and lock
> database table using pgsql-odbc driver. Is that possible that transaction
> is ran automatically by driver or when "*cur *=* self.conn.cursor*()" is
> executed ? Has anyone else experienced that

This is exactly what is happening.  You are being warned (not a failure)
that you are issuing BEGIN after the driver has issued BEGIN. The ODBC
driver provides the user the option to auto commit or not but fundamentally
in ODBC you are always in a transaction.  The lack of warnings by other
drivers is the probable reason you haven't noticed your redundancy until
now.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/WARNING-there-is-already-a-transaction-in-progress-tp5811280p5811288.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.