connection holds references to closed transactions forever? [PYFB78]

[firebird-automations]

Submitted by: Ehmmm (ehmmm.firebird)

On server I have a long running service which opens connection and then in endless loop starts transaction, gathers some data and closes transaction.
(More exactly the service has more threads, each of them has its own connection and endless loop with transactions.)
This service slowly consumes more and more memory.

Simple demo would look something like this:

con = fdb.connect(...)
while True:
cur = con.trans().cursor()
cur.execute(...)
cur.transaction.close()
sleep(...)

I think the problem is that con.trans() creates new Transaction object which is stored in con.transactions() and reference to this object is never released and that's why Python will never release memory of this Transaction even if it is closed for a long time.

One option is to close (and "forget") Connection. Then Python releases whole Connection with all transactions.

But my dirty solution of this issue is following:

...
tra = cur.transaction
tra.close()
for i in reversed(range(len(con._transactions))):
if con._transactions[i] == tra:
del con._transactions[i]
...

I'd like to hear your opinion.

[firebird-automations]

Commented by: Ehmmm (ehmmm.firebird)

typo

[firebird-automations]

Modified by: Ehmmm (ehmmm.firebird)

description: On server I have a long running service which opens connection and then in endless loop starts transaction, gathers some data and closes transaction.
(More exactly the service has more threads, each of them has its own connection and endless loop with transactions.)
This service slowly consumes more and more memory.

Simple demo would look something like this:

con = fdb.connect(...)
while True:
cur = con.trans().cursor()
cur.execute(...)
cur.transaction.close()
sleep(...)

I think the problem is that con.trans() creates new Transaction object which is stored in con.transactions() and reference to this object is never released and that's why Python will never release memory of this Transaction even if it is closed for a long time.

One option is to close (and "forget") Connection. Then Python releases whole Connection with all transactions.

But my dirty solution of this issue is following:

...
tra = cur.transaction
tra.close()
for i in reversed(range(len(con._transactions))):
if con._transactions[i] == tra:
del con._transaction[i]
...

I'd like to hear your opinion.

=>

On server I have a long running service which opens connection and then in endless loop starts transaction, gathers some data and closes transaction.
(More exactly the service has more threads, each of them has its own connection and endless loop with transactions.)
This service slowly consumes more and more memory.

Simple demo would look something like this:

con = fdb.connect(...)
while True:
cur = con.trans().cursor()
cur.execute(...)
cur.transaction.close()
sleep(...)

I think the problem is that con.trans() creates new Transaction object which is stored in con.transactions() and reference to this object is never released and that's why Python will never release memory of this Transaction even if it is closed for a long time.

One option is to close (and "forget") Connection. Then Python releases whole Connection with all transactions.

But my dirty solution of this issue is following:

...
tra = cur.transaction
tra.close()
for i in reversed(range(len(con._transactions))):
if con._transactions[i] == tra:
del con._transactions[i]
...

I'd like to hear your opinion.

[firebird-automations]

Commented by: @pcisar

Well, transaction object is not removed from list of transactions in Connection instance when transaction is closed by close(), so it's not garbage collected by Python. I will fix that in future version.

However, the reason why it caused trouble to you is that you used the anti-pattern:

1. one does not need to create new transaction object for each executed statement, but only when parallel transactions are truly needed. In fact, you don't need to create a transaction object at all in most cases (including your particular case), as Connection has one (default) transaction object already.
2. it's not necessary to close the transaction to start another one, just call begin() and commit()/rollback() as many times as you wish. Hence number of transaction objects is typically very low and they have the same lifetime as connection they belong to, i.e. they are closed (and disposed) together with connection.

BTW, you should always end transaction by calling commit() or rollback(), and not close(). While close() does commit() or rollback() which depends on default_action attribute (or context), it's a safety measure (as FDB has to end active transaction somehow on close()) to properly close the transaction/connection (for example in exception handler), but definitely not a good practice to use in normal code.

Your code should look like:

con = fdb.connect(...)
cur = con.cursor()
while True:
con.begin()
cur.execute(...)
con.commit()
sleep(...)

[firebird-automations]

Commented by: Ehmmm (ehmmm.firebird)

Thank you very much for your explanation.

I'll have to look better what Python cursors are really like.
(I thought it's something that exists only during the transaction and that after closing transaction and opening the new one I have to create new cursor.)

I also didn't know about the begin().
(I use commit() and rollback() quite often.)

Like you said the "anti-pattern" is the right word for me.

Thank you again.