Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
For such cases, duck Modeling /duck provides a mechanism that broadcasts changes made by one editing context to all others upon committing. Maybe they could share code with SQLAlchemy? if someone wants to write an extension, most easily to sessioncontext, that just synchronizes data between open sessions, thats not a big deal. But it really is somewhat of a reinvention of the database itself. the Session only represents the set of objects youre working on *right now*. if i wrote a function that loaded a row from a database, and then i was going to operate on some of those values, would i want those values to magically change while i was in the middle of my operation ? Err, no. However I do think that you should be notified that they have been changed by some third-party and then have the possibility to update your values if you want to. Personally, I agree with SGI that: GUI tools should not mislead the user; they should display the current state of the system, even when changes to the system originate from outside of the tools themselves. (Citation from http://oss.sgi.com/projects/fam/). So imho the GUI of a database application should always reflect the current state of the data in the database. Without polling, of course. Not like e.g. this §$%@! Windows Explorer... I would not agree that SA is a GUI, But it's being used for GUI applications (among others) in my case. and i wouldnt agree that a GUI should always reflect the current state of the database. My potential end-users won't agree with you on that. :- if i open up a document and im editing the document, and i havent yet pressed save, the GUI does not represent the current state of the database. Yes, and that's bad imho. :- The application should notify you as soon as possible that someone has modified the data behind your back. Everything else will lead to unwanted results. Unfortunately there seems to be no standard mechanism to register a client with the database itself for notification of any updates to records. Even the PostgreSQL-specific listen and notify commands require polling to retrieve the notification events as far as I understand from the documentation. I was wrong here. PostgreSQL notifications don't require polling the database server. It's just the libpq client library itself that requires polling a function to retrieve the notifications. But an example for Psycopg2 shows how to solve this: tap the socket where the notifications arrive and poll the function only when something has arrived. because nobody uses stuff like that. really, its not the end of the world to have to deal with normal data synchronization issues. No it isn't. But I have to deal with them, I can't ignore them. And I have to deal with them in the way that my potential end-users need, so polling every minute or hour is out of question. And I have to deal with them in an efficient way, so polling the database every second is out of question. I wonder whether PL/Python support in PostgreSQL would allow to implement something like such an asynchronous notification system... its a solution in search of a problem. ;) Well, err, no. :- Not in my case. I am precisely searching for a solution for this kind of problem. Because I know from the end users (I'm closer to being an end-user myself than to being a developer) that they wouldn't accept to see something on screen that may already be wrong at the moment when they see it. And they wouldn't accept either to have to press a refresh button every few seconds to see what is going on. As a sidenote: We _did_ encounter precisely this issue in a past project (I was on the end-user side in this one), and because the developers were stuck on using web services just because it was considered as modern, this issue (among lots of others) couldn't be solved in a way that would not have been ridiculous. In the end the project didn't produce anything that was actually useful for the end-users. And it was precisely the experience with this project (wasting quite a few years and millions of EUR) what made me look for possibilities to implement such applications myself. And with Python and a solid framework it could be possible even for a non-computer scientist to implement typical database applications himself. come up with the application first In my case, just as _one_ example among many others: See a sort of dashboard on screen about the state of the ongoing work in a workshop. Work orders, ressource booking, asset tracking etc. This will basically be the desktop background on the screen of the workshop manager. Another example would be the use of a shared database for data exchange between applications. In the environment where I work, most applications are in fact database applications, so exchanging data between them via a shared database is natural. But you wouldn't want to
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
On Sep 21, 2006, at 10:51 AM, Wolfgang Keller wrote: I was wrong here. PostgreSQL notifications don't require polling the database server. It's just the libpq client library itself that requires polling a function to retrieve the notifications. But an example for Psycopg2 shows how to solve this: tap the socket where the notifications arrive and poll the function only when something has arrived. if there is some psycopg2 method of receiving change notifications from the database, then it would not be too hard to write an extension to SessionContext that simply tracks all open sessions, receives the notifications, and sends appropriate expire() calls to the affected objects contained within those sessions. however this will mean that if you are in the process of doing some setting/ getting on one of those objects, your operation will need to be interrupted in some way, or some flag need be set so that the Session raises a concurrency exception when the flush() method is called. *or* - if you really really want all concurrent threads to totally share the same state at all times, you can have them all use the same Session, if you synchronize operations to that session since its not a threadsafe object by default. - 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
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. For such cases, duck Modeling /duck provides a mechanism that broadcasts changes made by one editing context to all others upon committing. Maybe they could share code with SQLAlchemy? Personally, I agree with SGI that: GUI tools should not mislead the user; they should display the current state of the system, even when changes to the system originate from outside of the tools themselves. (Citation from http://oss.sgi.com/projects/fam/). So imho the GUI of a database application should always reflect the current state of the data in the database. Without polling, of course. Not like e.g. this §$%@! Windows Explorer... Unfortunately there seems to be no standard mechanism to register a client with the database itself for notification of any updates to records. Even the PostgreSQL-specific listen and notify commands require polling to retrieve the notification events as far as I understand from the documentation. I wonder whether PL/Python support in PostgreSQL would allow to implement something like such an asynchronous notification system... 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. In case of the naive implementation mentioned, e.g. if someone opens a record for modification and then leaves the dialogbox open for the rest of the day, because (s)he gets distracted and forgets about it... 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): Thanks for the link. 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. And a fourth (imho the most Pythonic) option: Don't re-invent your own nuts and bolts, use well implemented, tested and documented modules provided by third parties. :-) Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove .nospam to reply. - 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
Hello, as far as basic transactional sanity, its reasonable; we imitate the constructs used by Hibernate. in a concurrent environment, 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. its not possible to insure that objects in memory are always exactly in sync with the database, 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? 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... 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. :-( If you always use your Session objects within a SessionTransaction, then youll have ACID behavior as good as that which you get from working with the database directly. That's fine, thanks. :-) note that SQLAlchemy is not a framework and does not insure any particular programming pattern, and is also a bit more open ended than Hibernate. so while its easier than not to write sane code using SQLAlchemy, its not a substitute for knowing the basics of how transactions work. No problem for me, I love learning how things work in principle. 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. Sincerely, Wolfgang Keler -- My email-address is correct. Do NOT remove .nospam to reply. - 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
On Mon, 18 Sep 2006 20:09:48 +0200 Wolfgang Keller [EMAIL PROTECTED] wrote: Just because I don't want to have to take care of all this transaction management myself, as this is complete rocket-science to me... I know not of ACID, but... rocket science this is not. trans = session.create_transaction() DoStuff(session) session.flush() # Oh noes we've written to the database! DoMoreStuff(session) if AllIsWell(): trans.commit() else: trans.rollback() If you've ever used save points in a video game, then you've used transaction management. It's like a little save point you can carry around, which disappears harmlessly when you restore your old save, or when you save your game. It's supported in the database across the phone line, so you don't have to have sqlalchemy keep all the pending stuff in memory, which is wasteful plus you can't query it. Instead you write everything to the database, and if something goes wrong you tell the database to rollback to the beginning of your transaction and it'll delete everything you've done since create_transaction(). There's two levels of safety: first sqlalchemy keeps the stuff in memory until you flush(), then the database keeps tabs on the stuff you write to it, until you commit(). Normally sqlalchemy does both levels with every flush(), but if you create a transaction now you have control of when the database rolls back or commits. - 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
Re: [Sqlalchemy-users] How ACID are SQLAlchemy transactions?
as far as basic transactional sanity, its reasonable; we imitate the constructs used by Hibernate. in a concurrent environment, its not possible to insure that objects in memory are always exactly in sync with the database, without going back to the database to refetch the data, or by pessimistically locking everything (which is not recommended). however, the Session/Query objects now support all the same methods that hibernate uses to insure consistency, which include optimistic concurrency control via checking the affected rowcount of operations using an optional versioning column, and pessimistic concurrency control via the lockmode keyword parameter recently added to the Query object (which corresponds to the usage of SELECT..FOR UPDATE and related constructs...this newer feature still needs documentation; however for most needs, optimistic concurrency control is effective). If you always use your Session objects within a SessionTransaction, then youll have ACID behavior as good as that which you get from working with the database directly. note that SQLAlchemy is not a framework and does not insure any particular programming pattern, and is also a bit more open ended than Hibernate. so while its easier than not to write sane code using SQLAlchemy, its not a substitute for knowing the basics of how transactions work. On Sep 18, 2006, at 2:09 PM, Wolfgang Keller wrote: Hello, as a lazy Python scripting dilettant who's looking for a persistence framework for database applications, I was wondering whether SQLAlchemy does transparently ensure that all transactions will always be as ACID as the underlying database (PostgreSQL in my case) allows. Among others, whether it reliably ensures that e.g. the objects in memory and the records in the database are always consistent themselves and in sync with each other, especially with multiple concurrent clients in a client-server application. Just because I don't want to have to take care of all this transaction management myself, as this is complete rocket-science to me... TIA, Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove .nospam to reply. -- --- 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users - 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.phpp=sourceforgeCID=DEVDEV ___ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
