On Mon, Oct 4, 2010 at 7:30 PM, Dragonfyre13 <[email protected]> wrote:
> I was having a discussion with my DBA on why the DB he tuned for
> another project ran so much better than the one tuned for general use
> (I was previously on the dedicated server, now on the general use
> server). He said the configuration was the same, with one change.
> Transaction isolation level was set to 0 (in mysql it's "read
> uncommitted")
>
> He said that most DALs, especially JDBC allow for this to be set for
> many databases on a session basis, so I can have my cake (the bigger
> generally tuned server) and eat it too (transaction isolation set to
> 0). Here's how to set it for mysql for example:
> http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
>
> I'm wondering if there's a way in web2py that I can do this. I kind of
> expected if it were there it would be in the connection string, but I
> don't see anything there for it. Is there support for this, or do I go
> back to my DBA to convince him to set the transaction isolation to 0
> on the general server as well?
You can set this directly to database executing raw SQL, ie, in PostgreSQL:
db._execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
If you can access your connection driver, maybe it has some extension
to do it (ie. with psycopg2):
self._connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
Anyway, setting isolation level to READ_UNCOMMITED may be a bad idea
(it would break ACID, as you can read data not yet commited, what
about rollbacks?)
Regards,
Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com
