Transaction aborted while reading table #301
Comments
Joselguti
added
priority: p3
product-auto-label
bot
added
the
api: spanner
|
@Joselguti, Python Spanner client, on top of which the SQLAlchemy dialect is built, has a system of auto-retry of aborted transactions (see for details: https://github.com/googleapis/python-spanner-sqlalchemy#automatic-transactions-retry). Thus, what's happening in your case is a too long transaction and some kind of a data change in parallel. The solution for this sort of problems is always the same - reduce the size of the transaction. |
|
@IlyaFaer Thank you, A follow up question, I have noticed that in general it takes a long time (in comparasion to other DBs) to insert data to spanner and looking at the logs/insight page at spanner both insert and read are around kB order in speed. (Usually read is bigger than insert) I am utilizing the examples in this library to insert data into Spanner, keeping it around 40.000 mutations per cycle when said inserted dataframes are bigger. Is there another way or something I am doing wrong that can be done in order to boost insert speed? Thank you. |
|
@Joselguti, hm-m. Let's say sqlalchemy-spanner is not intended for such huge operations. Pure Python Spanner client, which is used by sqlalchemy-spanner dialect under the hood, has a mechanism of mutations, which gives significant speed boost: https://cloud.google.com/spanner/docs/modify-mutation-api But SQLAlchemy doesn't provide a way to patch python-spanner batches mechanism to SQLAlchemy. Still, I think there is a workaround. Actually, when you're getting a connection with sqlalchemy-spanner, it has a reference to the original db_api connection, which has a method So, preliminary, I see two workarounds to try: with engine.begin() as connection:
connection.connection.executemany(sql, values)Or even deeper, to the very basic Spanner API: with engine.begin() as connection:
with connection.connection._database.batch() as batch:
batch.insert(
table="Singers",
columns=("SingerId", "FirstName", "LastName"),
values=[
(1, "Marc", "Richards"),
(2, "Catalina", "Smith"),
(3, "Alice", "Trentor"),
(4, "Lea", "Martin"),
(5, "David", "Lomond"),
],
)This approach uses the mutations mechanism I mentioned at the top of this comment. Here is the code of the I see you have uneasy cases, so I'd recommend to study this class, because django-spanner is just a wrap around. We override things that SQLAlchemy allows us to override. It has limitations at some items, but you still can go to the very basic functionality and use standard Spanner functions for complex cases. |
|
Hello, I have been working around some of the issues we have been talking in this post, everything was working as intended however there is a new situation I'm having trouble to deal with. Currently I'm generating several conections and data uploads (to different tables in spanners), to achieve this I am using Python threading tool, however when I have around 3-4 or more process uploading at the same time I sometimes get the following error: I don't understand why it says that the Databsase schema has changed, the relations I have between thread - connection - data - upload are 1 to 1, meaning I have no parallel uploads towards the same table in spanner. To respect the limit given by Spanner of 40.000 mutations I am using the following code: |
Hello,
I am trying to read data from a spanner table that currently has 134.964 rows and 288 columns.
I have been using the example posted in the Github under "Read" and modying the query to read less rows between each cycle.
The first issue is that it takes a good couple minutes (Probably 3+ to execute said transactions) and the second is that they keep failing.
Here is the error:
The text was updated successfully, but these errors were encountered: