Possible concurrency issues with SQL approach

[wqweto]

Generating next timestamp after executing query might miss time interval needed to process the query itself.

Consider this code snippet:

    # For given list of topics, get all the messages from the SQLite db using subscriber's timestamp
    for topic in self.topics:
        self.cursor.execute("SELECT message, timestamp from mps_messages WHERE topic=:topic and timestamp>:timestamp", {"topic": topic, "timestamp": self.timestamp})
        data = self.cursor.fetchall()
        for each_record in data:
            self.messages.put_nowait(each_record[0])
    # Update the timestamp
    self.timestamp = datetime.now()

Probably a more resilient approach would be to generate next timestamp before processing the query and put it as upper bound on timestamp too.

    # For given list of topics, get all the messages from the SQLite db using subscriber's timestamp
    new_timestamp = datetime.now()
    for topic in self.topics:
        self.cursor.execute("SELECT message, timestamp from mps_messages WHERE topic=:topic and timestamp>:timestamp and timestamp<=:new_timestamp", {"topic": topic, "timestamp": self.timestamp, "new_timestamp": new_timestamp})
        data = self.cursor.fetchall()
        for each_record in data:
            self.messages.put_nowait(each_record[0])
    # Update the timestamp
    self.timestamp = new_timestamp

(air code)