2

I'm actually using SQLAlchemy with MySQL and Pyro to make a server program. Many clients connect to this server to make requests. The programs only provides the information from the database MySQL and sometimes make some calculations.

Is it better to create a session for each client or to use the same session for every clients?

Sheena
  • 15,590
  • 14
  • 75
  • 113
nam
  • 3,542
  • 9
  • 46
  • 68
  • Sinh Choa Hoai Nam! Can you provide us with more details it is not going to be possible to give you a answer without additional information such as the usage of the server. – Namphibian Jan 15 '13 at 10:23
  • hello, xin chao, and for more details the programs only provides the information from the database MySQL and sometimes make some calculations – nam Jan 15 '13 at 10:30
  • The main question would be does the clients need to keep seperate state? Or can they share state variables? Do they need to keep state at all? You mention that there is calculations can the clients share the calculations? You need to look into what the state will maintain and then ask yourself can they share? – Namphibian Jan 15 '13 at 11:51
  • There is no state shared between the client, they are independant. But the problem is I don't need either create new session for each client, since once session is enough. – nam Jan 15 '13 at 12:47
  • You shouldn't share a session between clients because you would share the transaction state (what you should NEVER do). AFAIR Pyro is using a thread per connection, so a `scoped_session` would be exactly what you need: http://docs.sqlalchemy.org/en/latest/orm/session.html#contextual-thread-local-sessions – schlamar Jan 15 '13 at 14:53
  • BTW, it seems that you lack knowledge of how to design/implement multi threaded applications. Sharing a session between multiple threads would be a non-trivial challenge. – schlamar Jan 15 '13 at 14:56

2 Answers2

2

What you want is a scoped_session.

The benefits are (compared to a single shared session between clients):

  • No locking needed
  • Transactions supported
  • Connection pool to database (implicit done by SQLAlchemy)

How to use it

You just create the scoped_session:

Session = scoped_session(some_factory)

and access it in your Pyro methods:

class MyPyroObject():
    def remote_method(self):
         Session.query(MyModel).filter...

Behind the scenes

The code above guarantees that the Session is created and closed as needed. The session object is created as soon as you access it the first time in a thread and will be removed/closed after the thread is finished (ref). As each Pyro client connection has its own thread on the default setting (don't change it!), you will have one session per client.

schlamar
  • 9,238
  • 3
  • 38
  • 76
  • Thanks for the anwser. In your remote_method, if I get an object from the query, and then I want to access to the member of this object that needs a request to the database (lazy loading). I suppose to do that I need to attach this object to a session right? How you can do that in this case? For example creating a new Session and merge this object to the session? – nam Jan 18 '13 at 10:36
  • You have already a session and you will get objects attached to this session. Nothing to do at all :) – schlamar Jan 18 '13 at 10:47
  • You can do `session = Session()` in the remote method to get the actual session object, but that [doesn't make any difference](http://docs.sqlalchemy.org/en/latest/orm/session.html#implicit-method-access). – schlamar Jan 18 '13 at 10:49
  • Ok, then the session = Session() is an object, but the Session you created at first is actually a class. It's a little bit confusing. So to be clear, I will create the Session = scoped_session... when the server starts, and use the Session again and again for every request (and connection) of the client? – nam Jan 18 '13 at 10:56
  • Exactly. If you read [Implicit Method Access](http://docs.sqlalchemy.org/en/latest/orm/session.html#implicit-method-access) (already linked in my other comment, you might have missed it), then it shouldn't be confusing anymore :) – schlamar Jan 18 '13 at 11:01
-1

The best I can try is to create new Session in every client's request. I hope there is no penalty in the performance.

nam
  • 3,542
  • 9
  • 46
  • 68
  • Again, a [`scoped_session`](http://docs.sqlalchemy.org/en/latest/orm/session.html#contextual-thread-local-sessions) is exactly what you want. And it should be faster than sharing a global session, because in the latter case you have to guarantee a non-concurrent access on your session, which means locking it for other threads/clients. – schlamar Jan 17 '13 at 07:12
  • but what about creating new Session for every request? Is there a performance problem? – nam Jan 17 '13 at 08:55
  • Depends on how you define a "request" in context of Pyro (I assume a remote method call) and how many you have per connection. But if you have more than one request per connection it *could* be worse. The question is why do you want to have a session per request and not per connection? – schlamar Jan 17 '13 at 10:05
  • Because the session is owned by the server, I only need one connection: the server connection – nam Jan 17 '13 at 17:43
  • 1
    If you are still convinced of this you don't understand the basics and implications of multi threading and database programming. Actually, you don't want to share the session between multiple connections because this would require locking (performance loss) and you won't be able to use transactions correctly. – schlamar Jan 18 '13 at 07:02
  • And SQLAlchemy is providing a connection pool, so multiple sessions should be much more performant than a single one. – schlamar Jan 18 '13 at 07:08
  • I understand but do I need to close the session in the end of the request? Like I create a new scoped_session, doing the query, and finally close the session and return the result back ? – nam Jan 18 '13 at 09:51
  • I summarized everything in my answer, would have been to much for a comment. – schlamar Jan 18 '13 at 10:25