On Nov 27, 2007, at 2:40 PM, imgrey wrote:
> >> default isolation mode settings usually dont need any changes. we >> dont have an official API for that yet, so you can apply it to all >> connections using a custom connect() function sent to create_engine, >> or you can try setting it individually as conn = >> session.connection(); >> conn.connection.<set isolation>. > > I've found that default postgres isolation level is `Read Committed > Isolation Level` : > http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html > but probably I need `Serializable Isolation Level` : """ The level > Serializable provides the strictest transaction isolation. This level > emulates serial transaction execution, as if transactions had been > executed one after another, serially, rather than concurrently.""" ? if you want to set this across the board, you can create your engine as so: import psycopg2 from psycopg2 import extensions def connect(): c = psycopg2.connect(user=... pass=...) c.set_isolation_level(extensions.ISOLATION_LEVEL_SERIALIZABLE) return c engine = create_engine('postgres://', creator=connect) we have a much nicer API for this kind of thing on the way. > >> if you are getting genuine, occasional concurrent modification >> errors, >> you can also look into using pessimistic locking, i.e. >> query.with_lockmode(), to pre-lock rows inside a transaction and thus >> prevent conflicts from occuring on the table in question. > > Could you please point on example of usage query.with_lockmode() ? > > By the way how is it working ? On changing table that is locked, SA > will raise an exception or queries will be placed in queue for > execution after transaction that locked table ? lockmode is based around the idea of "SELECT...FOR UPDATE": http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE so the general idea is explained there. from the SA perspective, you start a transaction, then use query.with_lockmode('update').filter(<whatever>).... to select rows. the SELECT which the query issues will include FOR UPDATE on the end, then other transactions will block when they try to modify that row (i believe this works with the default transaction isolation in PG as well). so its called "pessimistic" because the operation assumes there will be conflicts so it defensively locks. all of this locking takes place on the PG side of things. "optimistic" locking refers to when you dont think there will be any conflicts so you detect the conflict after its too late to not raise an error (the error that youve been getting). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
