Re: WARNING: there is already a transaction in progress

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(dot)mr(at)gmail(dot)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*
>
> *(at)return:** None*
>
> * """*
>
> * log.info <http://log.info>*(*"**mutex** lock %d"* %*
> self.locked*)
>
> if not* self.locked:*
>
> * cur *=* self.conn.cursor*()
>
> * log.info <http://log.info>*(*"**mutex** start **tran**"*)
>
> * self.locked *= True
>
> try*:*
>
> * cur.execute*(*'BEGIN TRANSACTION; LOCK TABLE "%s" IN
> EXCLUSIVE MODE;'* %* self.table*)
>
> except*:*
>
> * log.info <http://log.info>*(*"**mutex* *tran**
> failed"*)
>
> * log.exception*()
>
> raise
>
> else*:*
>
> * log.info <http://log.info>*(*"**mutex* *tran**
> started"*)
>
> * cur.close*()
>
> del* cur*
>
>
> def *unlock*(*self*)*:*
>
> *"""*
>
> * Releases the lock*
>
> *(at)return:** None*
>
> * """*
>
> * log.info <http://log.info>*(*"**mutex** unlock %d"* %*
> self.locked*)
>
> if* self.locked:*
>
> try*:*
>
> * cur *=* self.conn.cursor*()
>
> except*:*
>
> * log.exception*()
>
> raise
>
> * log.info <http://log.info>*(*"**mutex** end **trans**"*)
>
> * self.locked *= False
>
> * cur.execute*(*"END TRANSACTION"*)
>
> * log.info <http://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 log: *https://dl.dropboxusercontent.com/u/17952943/sql.log
>
> *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