Not release connection from connection pool

[spree1010]

Hi,

After get connection from connection pool, application run below code to make two database insert in a transaction.
The problem is when sql_2 execute fails, the database connection is released.
Would you please help suggest where is wrong.

with conn:
with conn.cursor() as cur:
sql_1= "INSERT INTO T1 (V1, V2) values(%s, %s)"
cur.execute(sql_1, ('Value1', 'Value2'))
sql_2= "INSERT INTO T2 (V3, V4) values(%s, %s)"
cur.execute(sql_2, ('Value3', 'Value4'))
conn.commit()

[jkklee]

1. make two database insert in a transaction:

you didn't use conn.begin() before the insert sql.

if you want to make a transaction, you should use begin and commit explicit.

try:
    conn=pool.get_connection()
    conn.begin()
    #do something
    conn.commit()
    conn.close()
except Exception as e:
    conn.rollback()