Aug-08-2023, 02:36 PM
I have a python program that is using SQLAlchemy to work with an SQLite3 database.
One function I have will read a counter from a table from a specific row, increment the counter, and write/commit the updated value back to the table.
My issue is that the counter is not incrementing consistently. There are multiple instances of my Python program running, so I am thinking that multiple instances are reading the table and incrementing at the same time, basically corrupting the counter.
My table is simple. Column "chassis" holds a device name, and column "counter" holds the number of active connections on that device.
How can I write this so that only one instance at a time can update the counter?
This is the function I am using to increment the connection counter:
One function I have will read a counter from a table from a specific row, increment the counter, and write/commit the updated value back to the table.
My issue is that the counter is not incrementing consistently. There are multiple instances of my Python program running, so I am thinking that multiple instances are reading the table and incrementing at the same time, basically corrupting the counter.
My table is simple. Column "chassis" holds a device name, and column "counter" holds the number of active connections on that device.
How can I write this so that only one instance at a time can update the counter?
This is the function I am using to increment the connection counter:
def _add_new_connection(self): """ Ensure we don't have too many connections on a device """ hostname = self.get_host_name() loop = 300 # How many seconds to wait for an opening to be free for an agent. retry_time = 10 # How many seconds between tries while loop > 0: loop -= retry_time with self.__get_db() as db: # Find an entry for this chassis record = db.query(models.Agents).filter(models.Agents.chassis == hostname).first() # If no entry was found, we will make one if not record: record = models.Agents() record.chassis = hostname record.counter = 1 db.add(record) db.commit() break # Max of 3 connections elif record.counter < 3: record.counter = record.counter + 1 db.add(record) db.commit() break time.sleep(retry_time) print(f"{hostname} has {record.counter} connections and is waiting", flush=True) else: # Loop timed out return False print(f"{hostname} has {record.counter} connections and is connected", flush=True) return True