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
