Using SQLAlchemy, prevent SQLite3 table update by multiple program instances

[Calab]

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:

    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

[Larz60+]

can you use autoincrement?
see https://www.sqlite.org/autoinc.html

[Pubfonts]

# Max of 3 connections
elif record.counter < 3:
record.counter = record.counter + 1

db.add(record)
db.commit()

[Calab]

I found the bug causing my issue. It wasn't with the code I shared. It was a fault in how I was cleaning up the connections when I was done with them.

When my code was finished with a connection, it would make a call to agent.disconnect(), which decremented the connection counter. Later, Python would delete the agent object and part of that process was also to call agent.disconnect(). This resulted in my counter going down by two instead of one when I was finished with the connection.

I now ensure that I have a connection before I do a disconnect.