[openstack-dev] [oslo.db][sqlalchemy] rollback after commit

[Gareth]

Hi DB experts,

I'm using mysql now and have general log like:

1397 Query SELECT 1

1397 Query SELECT 

1397 Query UPDATE 

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

Re: [openstack-dev] [oslo.db][sqlalchemy] rollback after commit

[Roman Podoliaka]

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  wrote:
> Hi DB experts,
>
> I'm using mysql now and have general log like:
>
> 1397 Query SELECT 1
>
> 1397 Query SELECT 
>
> 1397 Query UPDATE 
>
> 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

Re: [openstack-dev] [oslo.db][sqlalchemy] rollback after commit

[Mike Bayer]

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  wrote:

Hi DB experts,

I'm using mysql now and have general log like:

1397 Query SELECT 1

1397 Query SELECT 

1397 Query UPDATE 

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



__
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