Issue: question: Aborted transaction is not being rolled back and connection closed after conflict handling
Author: madMathematician971
State: closed
Created: 2020-08-28 13:19:45

Hello there,

I am facing an issue that has been talked over a few times already, but none of your suggestions helped.

I am trying to handle insert_many conflict and do some logic afterwards. My API is hosted on AWS Lambda.

DB init: `self.__db = pw.PostgresqlDatabase(**DB_CREDENTIALS, autocommit=True, autorollback=True)`

logic:

`    def __bulk_create(self):

        with self.__db.atomic():
            try:
                query = Website.insert_many(self.data).returning(*self.__RETURNING)
                query.execute()

                self.__response = list(query)

            except IntegrityError as err:
                self.__db.rollback()
                self.__response = [{'error_message': f'DB conflict has occurred: {err}. Please check the field'}]
                self.conflict = True`


when I run this function for the first time after updating Lambda it handles the exception just fine, but when I try to run any other query on the db - it fails with "errorMessage": "current transaction is aborted, commands ignored until end of transaction block\n",


moving try\except outside the transaction does not help either. Tried using explicit transactions - no luck either. 

db.close() does not help.

In PGADMIN for this connection I have the following after first run of the function:

State: idle in transaction (aborted)


Any help would be appreciated

* using peewee==3.13.3  and Python 3.8.0

I cannot replicate this and I believe it is peculiar to aws lambda. Also I'm not sure why you have autocommit/autorollback enabled -- autocommit is deprecated (no-op) and displays a warning. If you have auto-rollback enabled then you don't need to roll-back in the except block (although doing so won't hurt anything, it's unnecessary). If  you're catching the IntegrityError then the try/except properly goes outside the transaction.

That being said, I've tried to replicate your script, as well as some things you didn't cover, and everything works fine for me:

```python

db = PostgresqlDatabase('peewee_test', autocommit=True, autorollback=True)

class Reg(Model):
    key = TextField(unique=True)
    class Meta:
        database = db

db.connect()
db.create_tables([Reg])

with db.atomic():
    Reg.create(key='k1')

try:
    with db.atomic():
        Reg.create(key='k1')
except IntegrityError:
    print('received integrity error as expected')

# try/except inside tx block
with db.atomic():
    try:
        Reg.create(key='k1')
    except IntegrityError:
        print('received 2nd integrity error as expected')

# Explicit rollback.
with db.atomic() as tx:
    Reg.create(key='k2')
    tx.rollback()

# Implicit commit.
with db.atomic() as tx:
    Reg.create(key='k3')

# Should print k1, k3
for row in Reg:
    print(row.key)

db.drop_tables([Reg])
```

Output:

```
received integrity error as expected
received 2nd integrity error as expected
k1
k3   
```

Solved the issue.

If anyone comes across such behavior on AWS Lambda and **using OOP** - **just delete your class instances that initiated the connection to the database** after you've handled the IntegtityError raised by peewee. For some reason if a class instance is not deleted explicitly by calling `del instance_name` - the connection will be held open and blocking you from establishing new ones untill a complete update of Lambda code. The session with an aborted transaction will get dropped and database will be no longer locked for new connections/transactions.

NOT A LIBRARY ISSUE