Most queries raise non-fatal `ValueError: Transaction is already committed`

[aderk]

Expected Behavior

Connection pool does not raise exceptions

Actual Behavior

Connection pool raises non-fatal exceptions for SELECT/INSERT transactions

Steps to Reproduce the Problem

from sqlalchemy import Column, Integer, MetaData, select, Text, Table, create_engine

project_id = 'foo'
instance_id = 'bar'
database = 'baz'

spanner_uri = f'spanner:///projects/{project_id}/instances/{instance_id}/databases/{database}'

# Create a table with one `Text`-type column
engine = create_engine(spanner_uri)
metadata = MetaData(bind=engine)
t = Table("TestTable", metadata, Column("test_column", Text, nullable=False))
metadata.create_all(engine)

with engine.begin() as conn:
 results = conn.execute(select([t.c.test_column])).fetchall()

print(results)

aderk@aderk ~/q/pyle (u/aderk/labdb-spanner)> python txn_bug.py 
Exception during reset or similar
Traceback (most recent call last):
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 697, in _finalize_fairy
 fairy._reset(pool)
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 893, in _reset
 pool._dialect.do_rollback(self)
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 773, in do_rollback
 dbapi_connection.rollback()
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/google/cloud/spanner_dbapi/connection.py", line 267, in rollback
 self._transaction.rollback()
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/google/cloud/spanner_v1/transaction.py", line 113, in rollback
 self._check_state()
 File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/google/cloud/spanner_v1/transaction.py", line 68, in _check_state
 raise ValueError("Transaction is already committed")
ValueError: Transaction is already committed
[]

Specifications

• Version: Python 3.8.8 (default, Apr 1 2021, 17:00:06)
  1c9be70
• Platform: Linux

[IlyaFaer]

There was an attempt to fix the error in the PR #68, but something seems to be left to do there.

[IlyaFaer]

@aderk, I can't reproduce the error yet.

What about google-cloud-spanner version? SQLAlchemy dialect is built on top of it, problem can be there. I'm using master/main branches in both repositories, and it works fine.

Still, I have a suspision. dbapi_connection arg accepted by the do_rollback() method can be of two types. Usually, we do something like this to avoid errors:
https://github.com/cloudspannerecosystem/python-spanner-sqlalchemy/blob/1c9be70f901ca016c357ced66fe258f01b694787/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py#L789-L792

But obviously it wasn't done in the do_rollback() method and it works fine only with proxies, while in your case it's most likely a Connection object:
https://github.com/cloudspannerecosystem/python-spanner-sqlalchemy/blob/1c9be70f901ca016c357ced66fe258f01b694787/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py#L796-L809

If you can check the type of the dbapi_connection arg passed into do_rollback() method on your machine to ensure my guess is right, that would be good. Otherwise you can wait for the fix.

[aderk]

Thanks Ilya for the quick response!

In case it still matters:

What version...?

google-cloud-spanner 3.7.0

I can confirm #106 fixes this issue on my machine! =D