[sqlalchemy] rollbacks on select
Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Regards Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] rollbacks on select
Sent from my iPhone On Aug 23, 2010, at 8:29 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Because we're using a connection pool, putting the connection back into the pool without a rollback means you throw existing transactional locks and state into the pool as well, holding them open indefinitely and generally causing problems for subsequent usages of those pooled connections. However, we get a complaint about every 6 months from a mysql myisam user, who uses many ad-hoc connection checkouts (which in itself is a little unusual) and who would rather not have it (as myisam has no transactional integrity anyway). For those cases, we tell them to add rollback_on_return=False to their create_engine to turn the behavior off. But also maybe consider why you have a high volume of checkins, rather then working in some kind of transaction-per-logical-operation scheme (like a web request). Regards Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] rollbacks on select
On Mon, Aug 23, 2010 at 5:48 PM, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 23, 2010, at 8:29 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Because we're using a connection pool, putting the connection back into the pool without a rollback means you throw existing transactional locks and state into the pool as well, holding them open indefinitely and generally causing problems for subsequent usages of those pooled connections. However, we get a complaint about every 6 months from a mysql myisam user, who uses many ad-hoc connection checkouts (which in itself is a little unusual) and who would rather not have it (as myisam has no transactional integrity anyway). For those cases, we tell them to add rollback_on_return=False to their create_engine to turn the behavior off. But also maybe consider why you have a high volume of checkins, rather then working in some kind of transaction-per-logical-operation scheme (like a web request). Thanks Michael ! -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] rollbacks on select
On Mon, Aug 23, 2010 at 5:53 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: On Mon, Aug 23, 2010 at 5:48 PM, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 23, 2010, at 8:29 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Because we're using a connection pool, putting the connection back into the pool without a rollback means you throw existing transactional locks and state into the pool as well, holding them open indefinitely and generally causing problems for subsequent usages of those pooled connections. However, we get a complaint about every 6 months from a mysql myisam user, who uses many ad-hoc connection checkouts (which in itself is a little unusual) and who would rather not have it (as myisam has no transactional integrity anyway). For those cases, we tell them to add rollback_on_return=False to their create_engine to turn the behavior off. But also maybe consider why you have a high volume of checkins, rather then working in some kind of transaction-per-logical-operation scheme (like a web request). Thanks Michael ! Just for the record for others, the option is reset_on_return Cheers Tarek -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
