CODEWITHSUNDEEP
pythonsqlalchemydatabase-connectionflask-sqlalchemy
Amit Tomar
Amit Tomar

Reputation: 4858

Connection per request , issue with concurrent query execution

Following is how my SQLAlchemy based Database accessing class is structured in general. There is flask wrapper APIs, which uses these.

I am getting quite a few exceptions of several kinds with this structure. Specifically :

A webservice 'X' uses the function fooBar. If this service gets more than one call concurrently, I get the following exceptions :

File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1631, in commit raise exc.InvalidRequestError("This transaction is inactive") InvalidRequestError: This transaction is inactive

I also get the following randomly in between :

ProgrammingError: (pyodbc.ProgrammingError) ('24000', '[24000] [Microsoft][SQL Server Native Client 11.0]Invalid cursor state (0) (SQLFetch)')

Not to mention several deadlocks and rollbacks happening all over.

I get a sense that there is perhaps something wrong in the way I am creating connection. It looks like same connection is getting used across all requests.

Would appreciate if someone could direct what is wrong with this code structure and any other general comments about it are also welcomed.

class DBHandler:

 def init(self):        
    try:        
        self.engine = create_engine(SQLALCHEMY_DATABASE_URI, connect_args={'connect_timeout': 10000}, echo=True)

        self.connection = self.engine.connect() 
    except:
        print 'Not connecting to AWS Database'

 def foo(self, skuID):
    try:
        transaction = self.connection.begin()           

        query = "Update Master_SKU SET SKU_Stage = '3' WHERE SKU_ID = ?"            
        results = self.connection.execute(query,skuID)
        transaction.commit()

        return True
    except:
        transaction.rollback()
        return None

 def fooBar(self):
    try:
        transaction = self.connection.begin()           

        query = "SELECT * FROM Master_SKU"          
        results = self.connection.execute(query)
        transaction.commit()

        return True
    except:
        transaction.rollback()
        return None

Edit :

Alternate approach :

Does this makes sense ?

def fooBar(self):
    try:
        tempConnection = self.engine.connect()
        transaction = tempConnection.begin()

        query = "SELECT * FROM Master_SKU"

        rows = tempConnection.execute(query).fetchall()                      
        transaction.commit()
        return True
    except:
        transaction.rollback()
        return False

Upvotes: 1

Views: 727

Answers (1)

Siegmeyer
Siegmeyer

Reputation: 4512

You're opening connection too soon (self.connection = self.engine.connect()). At this point you've opened a connection and you're not doing anything with it. Probably what is happening under the hood is that database opens a connection for you and creates a lock. Until you've closed it, you cannot perform other transactions (e.g. UPDATE). This happens a lot, for example, in PostgreSQL. Try this idiom:

connection = self.engine.connect()
# ... perform work
connection.close()

Also, see related issue: python sqlalchemy + postgresql program freezes.

Upvotes: 1

Related Questions