Open Stack

On 9/16/15 10:16 AM, Roman Podoliaka wrote:
> 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.
So I'll note that the reason this behavior is configurable is because 
specifically some MySQL users complained that these empty ROLLBACKs are 
still expensive; these are users that were using non-transactional 
MyISAM schemas, though, it may have been an older version of MySQL, and 
I don't have access to current details on this issue.

There are ways we could tailor oslo.db to reduce these ROLLBACK calls; 
we'd turn it off in the connection pool and then use oslo.db-level event 
handlers to run the rollback conditionally, based on the observed state 
of the connection.

However I'd like to see benching first that illustrates these ROLLBACKs 
are in fact prohibitively expensive.


>
> 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
> __________________________________________________________________________
> 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