Multithreading for queries in SQL Database

Suppose I have a database, for example, for messenger. My app is collecting messages and stores them in my database. It doesn’t matter what type of database it is (i want to know how it is usually embodied and does my database support it, and are there other databases that do if it doesn’t), but in my case it is PostgreSQL. I’m also using Python psychopg2 adapter. What I want actually is managing all the queries so that they don’t interrupt each other.

Let’s make clear what I want. Suppose we have two threads, two clients apparently, that are running in different threads, and those are loading messages from the database. Client 1 is running:

SELECT * FROM messages WHERE user_id = client1_id;

To select all messages, that he’s written, from the database, and then tries to fetch data, but instead, what it becomes, I guess, are all the messages from Client2. What happens now, is this:

# in thread 1:
   
  cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client1_id)

# in thread 2
   
  cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client2_id)   << this is the last SELECT

# then again in thread 1

  cursor.fetchall()                                                          << fetching data from the last select

# and then in thread 2
  
  cursor.fetchall()

What * I think is going to happen*, is Client1 will retrieve data from the last select, in this case, it is data from Client2, and that is the question I’m trying to find an answer to.

---

QUESTION

How can I read and write data from my database in different threads, so that every client runs his queries separately from other clients?

---

As it was mentioned here, multiple connections aren’t this good solution for me, and I think there’s no sense in buffering all queries and executing them as a sequence, in a row, when you have, for example, 10000 users. So, what kind of architecture should I use for this case? Thanks in advance.

Answer

Basically when you open a connection in any, it is a good approach to close it and then create a new connection. In the above code you are executing two queries and fetching after the second thread. Now you will get result for on the second thread. The right approach is, create a connection, fetch the result and close the connection on your operation is completed and then open a new connection as alike perform the operation and close it. This approach is very good to follow in any programming language. Below is the sample code,

# in thread 1:

   cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client1_id)

   cursor.fetchall()   
   con.close()   

# in thread 2

   cursor.execute('SELECT * FROM messages WHERE user_id = %s;', client2_id)   

   cursor.fetchall()
   con.close()