2

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.

Improve this question
0

2 Answers 2

Reset to default
2

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()
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Wow, that could be exactly what i want, thanks. But is it good approach to open and close connection every time? Does it take reasonably more time to proceed? Consider i connect to my database via localhost (my database and my are connected to the same network).
Yes, its good approach to open and close every time. Because, when you don't close the connections and open a new connection, then multiple connections would be open and database gets locked, since it cannot accept connections in parallel as you are using localhost. It is the best approach to open and close. If you feel this answer is reliable, please mark it as correct answer, so that others may also be helpful
The main reason why i am bothering is - does it take much time to open and close connection?
No it will not take much time. It takes same time as you are opening multiple connections.
2

For modern databases each query will be run in it's separate "space". That means those two queries may return different result sets.

The wikipedia page Multiversion concurrency control is a good read.

Also, remember that when retrieve data from a database you only get a copy of the data; you don't control it in your app. The moment you retrieve that data from the database it may already be stale since some other thread or app may have modified it already.

Also, I would recommend that in a highly multi-threaded app you should consider the "Version Number Pattern" to detect and control many users updating the same data. Otherwise a badly designed app may end up corrupting the data unintentionally.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.