How do 'transactions' in Planetscale work?

[markski1]

Hello, I have written a simple mysql connection pool I use in a few Python things. For standard mysql/mariadb databases this works fine, but when I try to use it with PlanetScale, the reused connections seem to generate the following error:

target: <dbname>.-.replica: vttablet: rpc error: code = Aborted desc = transaction <transaction>: ended at <time> (exceeded timeout: 20s)

This is confusing, because in this particular case I am only SELECTing from this database, so I'm not sure why there's an open transaction. Could be that I'm misunderstanding something about what a transaction is supposed to mean, but as far as I was aware I thought that was only for things that modify the database.

Either way, what should I do with a connection before pooling it to 'close' this transaction and avoid this error? Ideally the connection should be freed to be grabbed by whatever requests a connection from the pool next. If it helps, I am using mysql-connector-python

[mscoutermarsh]

Can you share more about the queries you are running? My guess is the code is accidentally opening a transaction and never committing it.

The only difference you should be experiencing is we have a default transaction timeout set to 20 seconds.

[markski1]

There are only 2 queries being ran, and both are "SELECT * FROM table WHERE column = value" type queries. It's possible that I'm misunderstanding the library and something is starting a transaction, and it only presents in PlanetScale in practice due to the lower timeout. I'll be looking deeper into that then, thanks

[mscoutermarsh]

Ah, must be something with the libary then. I'd be surprised if we haven't heard of this before. I'm sharing to see if anyone else has run into this.

[mscoutermarsh]

Heard back from some coworkers, we have seen this before with that library. Couple things to check.

1. Is it possible any of your select queries are taking longer than 20s? Normally the query limit is 900s, but if a select is performed within a transaction, then it will be limited to 20s.
2. We've had another developer say that setting autocommit to true, solved the problem for them. This will commit any open transaction after a query is run. The default for the python connector is false. https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

[markski1]

They're very quick queries, sub 100ms. I will try autocommit and update here, thanks.

[markski1]

Sorry forgot this question was open. Autocommit indeed solved my problem, many thanks.