ValueError: `staleness` option can't be changed while a transaction is in progress

[odino]

Environment details

• OS: Linux uname -a 5.11.0-46-generic #51~20.04.1-Ubuntu
• Language runtime version: Python 3.8.10
• Package versions:

google-cloud-spanner        3.12.1              
sqlalchemy-spanner          1.0.0 
grpc-google-iam-v1          0.12.3              
grpcio                      1.42.0              
grpcio-status               1.42.0              
grpcio-tools                1.38.0 
google-api-client           3.14.159265359.post1
google-api-core             2.3.0               
google-api-python-client    2.0.2               
google-auth                 1.28.0              
google-auth-httplib2        0.1.0               
google-cloud-core           2.2.1               
googleapis-common-protos    1.53.0              
grpc-google-iam-v1          0.12.3

Steps to reproduce

Running this script:

from sqlalchemy import create_engine

engine = create_engine(
    "spanner:///projects/X/instances/Y/databases/Z"
)

print(engine.execute("SELECT 1").scalar())

ends up running the query, but also throwing an error -- output from the CLI:

Exception during reset or similar
Traceback (most recent call last):
  File "/home/alex/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 671, in _finalize_fairy
    fairy._reset(pool)
  File "/home/alex/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 847, in _reset
    pool.dispatch.reset(self, self._connection_record)
  File "/home/alex/.local/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 259, in __call__
    fn(*args, **kw)
  File "/home/alex/.local/lib/python3.8/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 51, in reset_connection
    dbapi_conn.connection.staleness = None
  File "/home/alex/.local/lib/python3.8/site-packages/google/cloud/spanner_dbapi/connection.py", line 203, in staleness
    raise ValueError(
ValueError: `staleness` option can't be changed while a transaction is in progress. Commit or rollback the current transaction and try again.
1

Now, if I use:

with engine.begin() as connection:
    print(sql(connection, "SELECT 1 + 1").scalar())

it's all good. Any idea?

[IlyaFaer]

@odino, oh, I guess I know what's happening. By default, you're in SERIALIZABLE mode, which means you should commit your operations manually, or use a context manager, like you did:

with engine.begin() as connection:
    connection.execute(...)

Otherwise you're returning a connection back to connections pool, but there is a transaction in progress on the connection. As staleness drop is happening after operation execution, you see the results of the operation, but it fails later.

[odino]

@IlyaFaer thanks for your pointer -- I do assume that is the problem. Though I see you're working on a PR, as it would be convenient not having to commit / roll it back manually eg. when you're executing just some random selects. I actually did not realize everything ran in the context of a tx here.

[odino]

BTW one thing to note, when I switch to AUTOCOMMIT I still see some funky warning:

/home/alex/.local/lib/python3.8/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py:947: UserWarning: This method is non-operational in autocommit mode
  dbapi_connection.rollback()

[vi3k6i5]

The issue is resolved now, will do a release with the fix and update here.

[vi3k6i5]

The fix is now released with the latest version.

[kazshinohara]

When v1.1.0 will be available at PyPI ?
https://pypi.org/project/sqlalchemy-spanner/#history

[larkee]

Hi @kazshinohara,

It looks like the release failed which is why it is not available on PyPI. Unfortunately, the issue for the failure was closed by a bot before we saw it so we weren't aware of this until now. I'm looking into this issue now and will let you know when it has been successfully published to PyPI.

Apologies for the inconvenience!

[kazshinohara]

@larkee Thanks !! I'm planning to talk about this ORM at an user community in Japan next month, hopefully the release wil be landed soon.