Hi Gareth, Right, 'SELECT 1' issued at the beginning of every transaction is a pessimistic check to detect disconnects early. oslo.db will create a new DB connection (as well as invalidate all the existing connections to the same DB in the pool) and retry the transaction once [1]
ROLLBACK you are referring to is issued on returning of a connection to the pool. This is a SQLAlchemy configurable feature [2] . The reasoning behind this is that all connections are in transactional mode by default (there is always an ongoing transaction, you just need to do COMMITs) and they are pooled: if we don't issue a ROLLBACK here, it's possible that someone will return a connection to the pool not ending the transaction properly, which can possibly lead to deadlocks (DB rows remain locked) and stale data reads, when the very same DB connection is checked out from the pool again and used by someone else. As long as you finish all your transactions with either COMMIT or ROLLBACK before returning a connection to the pool, these forced ROLLBACKs must be cheap, as the RDBMS doesn't have to maintain some state bound to this transaction (as it's just begun and you ended the previous transaction on this connection). Still, it protects you from the cases, when something went wrong and you forgot to end the transaction. Thanks, Roman [1] https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/engines.py#L53-L82 [2] http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.Pool.params.reset_on_return On Wed, Sep 16, 2015 at 12:13 PM, Gareth <academicgar...@gmail.com> wrote: > Hi DB experts, > > I'm using mysql now and have general log like: > > 1397 Query SELECT 1 > > 1397 Query SELECT xxxxxxxx > > 1397 Query UPDATE xxxxxxxx > > 1397 Query COMMIT > > 1397 Query ROLLBACK > > I found there always is 'SELECT 1' before real queries and 'COMMIT' > and 'ROLLBACK' after. I know 'SELECT 1' is the lowest cost for check > db's availability and 'COMMIT' is for persistence. But why is a > 'ROLLBACK' here? Is this 'ROLLBACK' the behaviour of oslo.db or > sqlchemy? > > > > -- > Gareth > > Cloud Computing, OpenStack, Distributed Storage, Fitness, Basketball > OpenStack contributor, kun_huang@freenode > My promise: if you find any spelling or grammar mistakes in my email > from Mar 1 2013, notify me > and I'll donate $1 or ￥1 to an open organization you specify. > > __________________________________________________________________________ > OpenStack Development Mailing List (not for usage questions) > Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev __________________________________________________________________________ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
