SQLAlchemy in multithreaded application - Lock wait timeout exceeded

Question

I work on a data import application that updates a database. Data source is a bottleneck in my case therefore the data comes in chunks in multiple threads:

engine = create_engine(f"mysql+mysqldb://{DB_USER}:{DB_PASS}@{DB_HOST}/{DB_DB}")
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)

def build_network():
...
    for node_id in nodes:
        thread = threading.Thread(
            target=_build_page_nodes, args=(node_id, traversing_nodes, update),
            name="Thread-" + node_id)
        thread.start()
...

def _build_page_nodes(node_id, traversing_nodes, update):
    session = Session()
...
    logger.debug('Committing transaction')
    session.commit()
    Session.remove()
    logger.debug('Done')

First thread executes fine. Next one however causes a problem. Here is a log of execution:

2021-05-05 09:52:02,846 INFO    MainThread      build_network   1 of 109015. 0 tasks are running
2021-05-05 09:52:04,811 DEBUG   Thread-10000060 build_network   Getting children for 10000060
2021-05-05 09:52:04,812 DEBUG   Thread-10000060 build_network   Committing transaction
2021-05-05 09:52:04,814 DEBUG   Thread-10000060 build_network   Done
2021-05-05 09:52:04,850 INFO    MainThread      build_network   2 of 109015. 0 tasks are running
2021-05-05 09:52:06,609 DEBUG   Thread-10001280 build_network   Getting children for 10001280
2021-05-05 09:52:06,610 DEBUG   Thread-10001280 build_network   Committing transaction
Exception in thread Thread-10001280:
Traceback (most recent call last):
  File "/var/www/order/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    self.dialect.do_execute(
  File "/var/www/order/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/var/www/order/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/var/www/order/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/var/www/order/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

The above exception was the direct cause of the following exception:

# Skipped the stacktrace
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')
[SQL: UPDATE network_nodes SET built_tree=%s WHERE network_nodes.id = %s]
[parameters: (1, '10001280')]

As I can see first thread is executed without problems. So I assumed it leaves some connection active. But as you can see I close and remove the session upon thread completion.

No other clients are using this database at the moment. Also when the application was single-threaded it ran ok. So is there something wrong with the way I finish the thread?

user_200160 · Answer

It turned out the problem was not with threads themselves but the SQLAlchemy session. Before starting spawning the threads I've gathered list of nodes to work on. After I've got the list of nodes I didn't remove the scoped session with Session.remove(). This caused the transaction be remain running. With running select * from information_schema.innodb_trx; I could see that.

I still don't know why first update of the record was successful and only second caused the problem. But since I could fix it I'm ok.

SQLAlchemy in multithreaded application - Lock wait timeout exceeded

1 Answers