On Dec 18, 2009, at 8:45 PM, Kevin Ar18 wrote: > it really is that - simultaneous transactions are isolated from one another, > and their results are only made visible to other transactions after they're > committed. as far as locking, you generally choose between an "optimistic" > and a "pessimistic" approach: > > Thanks. > Ok, so I found an article that talks about the different locking types for > databases: > * lock everything > * don't lock but check if the data changed on you and fix it if it did > * don't care -- only use for single db connection > > Now, where does SQLAlchemy fit into all of this? Also, I notice PostgreSQL > had 2 different locking strategies you can use and the info I am looking at > now suggests I am going to have do some complex database handling to check > for data integrity. Of course, the whole point of SQLAlchemy is to abstract > away from such low level database interaction... is it not? > > Since I'm guessing everyone who uses a db must deal with this problem that > much mean there is some information I am missing or something I don't > understand that everyone else out there just knows about. :)
The method that works 95% of the time is, just establish a connection to the database, start a transaction, do some work that stays within the thread, commit the transaction. Then throw all loaded state in that thread away, and reload it all on the next transaction. Do this in as many concurrent threads or processes as you like. In typical cases, you don't have to worry about taking specific steps towards locking - PG does a very good job of isolating concurrent sessions out of the box. Its only if you expect to have many concurrent threads/processes all contending for the same row in a table, or if you have to insert a row which other threads/processes might be trying to insert at exactly the same time that you have to consider different outcomes. And even then, you still don't have to really "do" anything except have proper constraints on your tables - PG will ensure that invalid data doesn't enter the DB. But the "losers" in a contention battle will get an exception. So if you have a problem with threads/processes losing the battle for contention, and you'd like to be more graceful about it, then you can start strategizing around it. But its not something I worry about too much until it becomes a problem. A tool like SQLAlchemy doesn't have too much to do with any of this, except to not get in one's way when attempting to use transaction isolation. It similarly doesn't really "do" anything regarding concurrency or isolation - PG does an incredible job at that on its own. You get a Session, it will open a connection the first time you use it, and thats it, until you rollback or commit. Modern versions ensure that once a brand new transaction is started, any data thats currently in the session from a previous transaction is considered to be invalid, and will be loaded again once it is accessed - this since once your transaction is complete, other transactions can now get a hold of it and change it further. I'd also advise going through the session docs carefully for a full overview http://www.sqlalchemy.org/docs/05/session.html . You also might be interested to read Hibernate's document at http://docs.jboss.org/hibernate/stable/core/reference/en/html/transactions.html, since the SQLA session was largely based off that of Hibernate. The general idea there is the same as that of SQLAlchemy, though some specifics are different, but they do reiterate a lot of the same concepts. > > Hotmail: Trusted email with powerful SPAM protection. Sign up now. > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
