Open Stack

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 <academicgareth at 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 at 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-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev