On Sep 19, 2006, at 8:19 AM, Wolfgang Keller wrote: > > I forgot to mention that what I have in mind are not several > instances of > SQLAlchemy, but a single instance which would be part of something > like an > "application server". Then all client requests would be processed > by one and > the same instance of SQLAlchemy, running on the server side. > > In such an environment, where SQLAlchemy should "know" at runtime > which > objects are modified by other clients, it should be possible to > automatically > keep everything in sync, no?
right, but your server probably uses multiple threads and/or child processes, and the SA session is only intended to be used in one thread at a time, so concurrency issues can still come up, and one thread/process can change the database while another session is not exactly in sync with that new data. > >> without going back to the database to refetch the data, or by >> pessimistically locking everything (which is not recommended). > > Why not recommended? In some cases, consistency is more important than > performance... it depends on the needs of the specific application, but pessimistic locking is usually overkill, complicated, and can lead to deadlocking conditions. it should be used sparingly and only where its demonstrable that its needed. > > My uneducated guess would be that without pessimistic locking, you > would get > lots of transaction failures in a concurrent environment. And that > there > would be a lot of handwork to cleanup behind. :-( not really! although again it depends on the needs of the application. going with two assumptions that are the overwhelmingly common case, a. the session is short-lived, meaning it only lasts within the context of a request, as opposed to staying open with the same objects for several seconds/minutes, and b. the application involves users logging in and working on areas of data that are specific to their login (meaning, user A typically works with rows in set X, user B typically works with rows in set Y, and the intersection of X and Y is very small or none), optimistic collisions will be extremely rare. if only A or B is the case, collisions are still pretty rare as well. long running sessions (with corresponding long-running transactions) are not recommended anyway. Hibernate has this to say on the subject (http://www.hibernate.org/hib_docs/v3/reference/en/html/ transactions.html#transactions-basics-apptx): "The session-per-request pattern is not the only useful concept you can use to design units of work. Many business processes require a whole series of interactions with the user interleaved with database accesses...<snip>..A first naive implementation might keep the Session and database transaction open during user think time, with locks held in the database to prevent concurrent modification, and to guarantee isolation and atomicity. This is of course an anti-pattern, since lock contention would not allow the application to scale with the number of concurrent users." check out the discussion on that page for further detail over how SA seeks to handle it. > But, between "knowing something about the theoretical > basis" (what's a lock, > what does rollback mean), and being able to actually implement an > entire > transaction manager "by hand" "from scratch", there's a H-U-G-E > difference > imho. Something like five years of CS studies and at least as much > of actual > practical experience I guess. well theres a third option to those methods which is to use known design patterns. While I came up with a lot of ideas for SA on my own, I didnt have any great new ideas for how to handle sessions; so SA tries to rely on what is already known. the original unit of work model I got from a design patterns book (Fowler's); and in version 0.2 i totally changed the API around with regards to Sessions to mimic the behavior of hibernate much more closely, since it was already a widely used pattern that has withstood a lot of scrutiny. ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users