spanner: consider implementing long-running aka auto-refreshing Transaction

[odeke-em]

I understand that Cloud Spanner transactions are meant to be used for a short while and committed or rolled back and referring to the authoritative advisory at https://cloud.google.com/spanner/docs/reference/rest/v1/TransactionOptions#idle-transactions
which says

A transaction is considered idle if it has no outstanding reads or SQL queries and has not started a read or SQL query within the last 10 seconds. Idle transactions can be aborted by Cloud Spanner so that they don't hold on to locks indefinitely. In that case, the commit will fail with error ABORTED.

If this behavior is undesirable, periodically executing a simple SQL query in the transaction (e.g., SELECT 1) prevents the transaction from becoming idle.

and for my purposes I need a transaction that'll potentially be held for long by a Python DBAPI v2 Cursor for an arbitrary period, so definitely it needs a refresh every 9 seconds to send SELECT 1=1 I have prototyped a Transaction at https://gist.github.com/odeke-em/a17aa49854aeae1d83ffc14715f52d79

In the midst of concurrency and usage with other threads, this becomes ridiculous to deal with because at times both might want to use any of the Transaction methods so the re-entrant locking being used with "shared memory".

However, this is so much work to use this library ontop of other errors that I feel like the barrier for entry could be reduced perhaps by an option implemented by this library so that I just have to do

txn = sess.transaction(auto_refresh=True)

or better yet every Transaction should be able to auto-refresh

[odeke-em]

A better prototype is at https://gist.github.com/odeke-em/89719e9e820a5aed1ba8cbaace0708a5

[larkee]

Thanks for filing this issue!

Unfortunately, this feature will not be added to the client as we do not want to encourage people to keep transactions open for the reasons mentioned in the documentation. If someone needs to do this and understands the consequences of keeping a transaction open over a long period of time, then using an approach similar to yours in your own implementation is fine.