AbortedException retry strategy

[kurisumakise2011]

I'm always frustrated when facing with the following errors:

ABORTED: com.google.api.gax.rpc.AbortedException: io.grpc.StatusRuntimeException: ABORTED: Transaction was aborted. It was wounded by a higher priority transaction due to conflict on keys in range [[9a657b0e-93ea-41eb-b19b-53fe73a4eb78], [9a657b0e-93ea-41eb-b19b-53fe73a4eb78]), column created_timestamp in table user_entity.
retry_delay {
  nanos: 171129581
}

And other similar.
It always fails on select statements generated by Hibernate ORM while concurrent execution (it's close to real situation, when we have many requests, for instance 100 per second.

So the message is not pretty clarified, but I guessed that it was discarded due to conflicts between transaction, because of all spanner databases have SERIALIZABLE isolation level only.

But the main issue is after aborting it's going to be retried by client mechanism, but it also checks hashes after each retry for two results sets. it does retry impossible due to concurrent modification exception. I want to leave a little change for this transaction to be retried with another strategy that will be chosen via URL properties, without comparing of hashes.

cloudspanner:/projects/test-project-123/instances/test-instance/databases/test-database?retryStrategy=FORCE_RETRY

cloudspanner:/projects/test-project-123/instances/test-instance/databases/test-database?retryStrategy=CHECK_SUM_RESULT - by default, can be omitted. The compatibility is saved.

And then in it will be something like that

13:33:07,796 FINE  [com.google.cloud.spanner.connection.ForceRetryStrategy] (connection-executor-67) About to retry SQL transaction
[select userentity0_.ID as ID1_73_, userentity0_.CREATED_TIMESTAMP as CREATED_2_73_, userentity0_.EMAIL as EMAIL3_73_, userentity0_.EMAIL_CONSTRAINT as EMAIL_CO4_73_, userentity0_.EMAIL_VERIFIED as EMAIL_VE5_73_, userentity0_.ENABLED as ENABLED6_73_, userentity0_.FEDERATION_LINK as FEDERATI7_73_, userentity0_.FIRST_NAME as FIRST_NA8_73_, userentity0_.LAST_NAME as LAST_NAM9_73_, userentity0_.NOT_BEFORE as NOT_BEF10_73_, userentity0_.REALM_ID as REALM_I11_73_, userentity0_.SERVICE_ACCOUNT_CLIENT_LINK as SERVICE12_73_, userentity0_.USERNAME as USERNAM13_73_ from USER_ENTITY userentity0_ where userentity0_.EMAIL=@p1 and userentity0_.REALM_ID=@p2]
After getting a message: [ABORTED: com.google.api.gax.rpc.AbortedException: io.grpc.StatusRuntimeException: ABORTED: Transaction was aborted. It was wounded by a higher priority transaction due to conflict on keys in range [[9a657b0e-93ea-41eb-b19b-53fe73a4eb78], [9a657b0e-93ea-41eb-b19b-53fe73a4eb78]), column created_timestamp in table user_entity.
retry_delay {
  nanos: 171129581
}
]

Maybe there is another solution, but I haven't found it yet.

Spanner is mutlicluster database. It does spanner more preferable than self-distributed and self-maintained database like MySQL or PostgreSQL. But retry is not allowed for some transactions, and and this complicates the task.

I know that, the option retryAbortsInternally, and I would handle this instead of PR, if I didn't use ORM.

ORM is more convenient in lot of applications than integrating via pure JDBC directly.

I made a PR, I knew that firstly, I was supposed to open issue, discuss and other things and I agreed that my suggestion can be discarded, but maybe this PR as a POC will be interesting.

Thanks for reading.

[olavloite]

Hi @kurisumakise2011 and thank you for the report and the PR.

The retry strategy that the JDBC driver by default uses is, as you noted, one where the entire transaction is retried and all update statements and queries are re-executed and the results of those statements and queries are compared to the results of the original transaction. This is done to ensure that the application will not make a decision based on stale data.

Consider the following (simplified) example:

1. Application A starts a transaction.
2. Application A executes the query SELECT Amount FROM BankAccount WHERE AccountHolder=1. This query returns 100 to Application A.
3. Application B starts a transaction.
4. Application B executes the statement UPDATE BankAccount SET Amount=0 WHERE AccountHolder=1
5. Application B commits.
6. Application A wants to execute the statement UPDATE BankAccount SET Amount=Amount-50 WHERE AccountHolder=1.
7. The transaction of application A is aborted and the JDBC driver starts a retry. This retry will fail because the query in step 2 now returns a different value than during the original transaction.

I realize that there are better ways to solve the specific use case above, it is purely intended to illustrate why the retry strategy using a ChecksumResultSet is the default for the JDBC driver. The FORCE_RETRY strategy would allow the above situation to proceed, causing the amount in the bank account to become negative.

I wonder whether this situation is something that you would want to allow to happen silently. Do you know which data was changed in your specific case that causes the retry to fail? Is it always (or mostly) a specific column that is not used by the application to determine which code path to follow?

It might be better to allow a slightly more sophisticated retry strategy where certain columns are allowed to return differing values in the original transaction and the retry transaction, without causing the retry to fail.

[kurisumakise2011]

Dear, @olavloite
Thank you for the quite quick response.

Your example a good self-explained about why developer decided to use such approach of verifying hashes.
It guarantees consistency of data and it's certainly important thing.

Force retry may lead to re update row, and it's not correct that in one transaction, we encounters with a kind of non-repeatable reads problem.

I'm facing with Aborted Transaction in a little different case.
What is going on:

I have a set of parallel transactions - set A, and parallel transactions - set B

Each a from A has to do the same queries, the same for b from B

first a -> second b - it's application flow

a of A belongs to Q (queries set)
b of B belongs to Q or U (updates set)

a has batch of operations as: (select_0 -> select_1 ... -> select_k), where k is (natural number U 0)
b has batch of operations as: (select_0 -> insert_1 -> insert_2 -> insert_3)

some a transactions aborted by b transaction.

The message: Transaction was aborted. It was wounded by a higher priority transaction due to conflict on keys in range [[be051f8a-8667-4ace-b2e0-262c264465e0], [be051f8a-8667-4ace-b2e0-262c264465e0]), column add_token_role in table identity_provider.
(cannot figure out why is there mentioned add_token_role column), be051f8a-8667-4ace-b2e0-262c264465e0 - the row with such key has been inserted.

So it looks like a_k is doing selections, while b is doing selection\insertion, and then returns AbortedException (I totally don't know why, might be because of a row was inserted)

Then a_k is going to be retried by JDBC, but unfortunately it fails later, because of a new row appeared ?

I would to suggest retrying only and only such a_k transactions with ignoring hashes on demand (by passed strategy param)

If some a_k transactions contains modification statement of course such transaction consider as conflicted, and will be aborted.

So maybe you help me to figure out, what is happening to the application and why a_k is always aborted?

Thank you.

p.s I had posted this comment via another account, then changed it to current)
I don't want to confuse anyone) Just I'm pumping my main account as contributor)

[olavloite]

Then a_k is going to be retried by JDBC, but unfortunately it fails later, because of a new row appeared ?

Yes, that is most probably the reason that the JDBC driver will not retry the transaction.

So maybe you help me to figure out, what is happening to the application and why a_k is always aborted?

The most probable reason that transaction a is aborted, is that it first did a SELECT statement that would have returned the row that transaction b inserted before b committed. Consider the following simplified example:

1. Transaction A: SELECT * FROM identity_provider WHERE id=1. This returns 0 rows.
2. Transaction B: INSERT INTO identity_provider (id, ...) VALUES (1, ...).
3. Transaction B: COMMIT.
4. Transaction A: SELECT * FROM identity_provider WHERE id=1. This should now return 1 row as transaction B committed, but at the same time it should return 0 rows as the transaction should be serializable. The transaction could therefore be aborted by Cloud Spanner.

I'm not 100% sure I completely understand the application flow in this case. Are transactions a and b somehow both part of the same application flow? In that case I would suggest:

1. Combine them into 1 transaction, OR
2. If possible, make transaction a a read-only transaction. A read-only transaction will never be aborted by Cloud Spanner.

I would to suggest retrying only and only such a_k transactions with ignoring hashes on demand (by passed strategy param)

I understand that. My main concern is that it might not be clear to all users what the consequences of such a setting might be.

[kurisumakise2011]

Hello, @olavloite thanks for the explanation.

I got stuck in spanner logic could you please have a look at my example?

I have simplified two application transactions as much as can

The first transaction.

select 
    ID, CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, EMAIL_VERIFIED, ENABLED, FEDERATION_LINK,
    FIRST_NAME, LAST_NAME, NOT_BEFORE, REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, USERNAME
from USER_ENTITY
where USERNAME='5a62787b-c3d5-4d05-a824-d6b2a58bd68e' and REALM_ID='test'


select 
    ID, CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, EMAIL_VERIFIED,
    ENABLED, FEDERATION_LINK, FIRST_NAME, LAST_NAME, NOT_BEFORE,
    REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, USERNAME
from USER_ENTITY
where EMAIL='add24514-897e-4075-aa15-6b632b6bf...' and REALM_ID='test'


insert into USER_ENTITY 
(CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, 
EMAIL_VERIFIED, ENABLED, FEDERATION_LINK, FIRST_NAME,
LAST_NAME, NOT_BEFORE, REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, 
USERNAME, ID)
values 
(1593100429637, 'add24514-897e-4075-aa15-6b632b6bf...', 'ae5aa4bd-2b9e-4883-86a4-39bf008fc317', 
false, false, NULL, NULL, 
NULL, 0, 'test', NULL, 
'5a62787b-c3d5-4d05-a824-d6b2a58bd68e', '64a86f74-4e2c-4962-9dc3-b3c16832c05e')

The second

select 
    ID, CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, EMAIL_VERIFIED, ENABLED, FEDERATION_LINK,
    FIRST_NAME, LAST_NAME, NOT_BEFORE, REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, USERNAME
from USER_ENTITY
where USERNAME='599161a5-6b9a-4505-b7ed-756bf9a96c46' and REALM_ID='test'


select 
    ID, CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, EMAIL_VERIFIED,
    ENABLED, FEDERATION_LINK, FIRST_NAME, LAST_NAME, NOT_BEFORE,
    REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, USERNAME
from USER_ENTITY
where EMAIL='a82b8fc2-a342-402c-a50a-5e576eabb...' and REALM_ID='test'


insert into USER_ENTITY 
(CREATED_TIMESTAMP, EMAIL, EMAIL_CONSTRAINT, 
EMAIL_VERIFIED, ENABLED, FEDERATION_LINK, FIRST_NAME,
LAST_NAME, NOT_BEFORE, REALM_ID, SERVICE_ACCOUNT_CLIENT_LINK, 
USERNAME, ID)
values 
(1593100429664, 'a82b8fc2-a342-402c-a50a-5e576eabb...', '50336ed4-2c7b-4e46-8f7f-25fad9b2fa58', 
false, false, NULL, NULL, 
NULL, 0, 'test', NULL, 
'599161a5-6b9a-4505-b7ed-756bf9a96c46', 'e239a2ed-5d3a-4f4e-9ed5-9d7698567dfa')

Why I got

Transaction was aborted. It was wounded by a higher priority transaction due to conflict on keys in range [[e239a2ed-5d3a-4f4e-9ed5-9d7698567dfa], [e239a2ed-5d3a-4f4e-9ed5-9d7698567dfa]), column created_timestamp in table user_entity.

How might be there any conflict on keys and furthermore on column created_timestamp

I have been looking for any conflicts in selection or something else. But two selection were executed with unique values in where clause.

So for me it looks like, spanner doesn't allow changing the same table while another transaction does

Updated.

I just really want to make a solution which will be clear to all users and save compatibility

[olavloite]

Why I got

Transaction was aborted. It was wounded by a higher priority transaction due to conflict on keys in range [[e239a2ed-5d3a-4f4e-9ed5-9d7698567dfa], [e239a2ed-5d3a-4f4e-9ed5-9d7698567dfa]), column created_timestamp in table user_entity.

How might be there any conflict on keys and furthermore on column created_timestamp

I don't know why this specific abort occurred. But in general, you should always structure your code in such a way that you take into account that any read/write transaction may be aborted by Cloud Spanner. It could also be caused by factors that you are not able to control from the client side, like for example a temporary network error.

The above transactions do however look like transactions that the JDBC driver should be able to retry automatically. Did the retry by the JDBC driver fail in this case as well?

[kurisumakise2011]

The above transactions do however look like transactions that the JDBC driver should be able to retry automatically. Did the retry by the JDBC driver fail in this case as well?

Correct. These transactions are able to retry.

But if there is additional select (like select id from client where realm_id = 'test') it may fail retry, you know why, checksums are not equal.

Actually ORM generates some selections and it's almost a real case when client table was changed (during running transactions and the one of two is failed), and when client does retry it will be aborted due to different hashes.

So it's why I've decided to make a pull request.

[olavloite]

But if there is additional select (like select id from client where realm_id = 'test') it may fail retry, you know why, checksums are not equal.

Yes, I understand that. My point is that if a transaction executes select id from client where realm_id = 'test', it should do so because it needs to make a decision based on all the results from that query. That also means that a transaction should not be allowed to retry if that query returns a different result during the retry. If it does not need all the results in that query to make its decision, it should narrow the where clause to only return the records that are actually needed.

My main concern is that not being strict in this retry logic is that it could lead to errors that would seem very illogical and hard to debug. Consider the following transaction in pseudo-code loosely based on your example:

BEGIN TRANSACTION;

SELECT COUNT(*) as C
FROM USER_ENTITY
WHERE email='foo@bar.com';

IF C = 0 {
  INSERT INTO USER_ENTITY (email, ...) VALUES ('foo@bar.com', ...);
}

COMMIT;

Considering the fact that Cloud Spanner transactions are serializable, anyone will rightly expect that this transaction will never cause the USER_ENTITY table to contain multiple rows with the same email value. However, if we during a transaction retry would ignore any difference in the SELECT query between the original transaction and the retry attempt, the table will occasionally receive duplicate email records. These kind of subtle errors can be incredibly hard to track down. Anyone looking at this code will for example come to the conclusion that this part of the application cannot be what is causing the duplicate email records.

I do know that it can be hard to limit a query to exactly the results you need at that moment, especially when there's an ORM in between your application and the database. That's why I asked earlier if the checksum mismatch was always related to a specific column. If your tables contain one or more columns that change often, and that are not really important to the application logic but are automatically included by the ORM in all queries, then I would rather look into limiting the checksum check for that kind of columns, than allowing it to be turned off all together.

[kurisumakise2011]

I understood. For now, I think limiting the checksum it would be pretty enough. Excluding all primary keys and unique values is prohibited, because it leads to a certain fault.

I presume that your example won't work in real application, and spanner is supposed to throw kind of ViolationPrimaryKey or UniqueIndex exception. But it's very clear for me, why force retry looks as workaround and will be incomprehensible.

According to it. May I make some POC with limiting columns in the checksum comparison in the same pull request which I opened?

[olavloite]

I presume that your example won't work in real application, and spanner is supposed to throw kind of ViolationPrimaryKey or UniqueIndex exception. But it's very clear for me, why force retry looks as workaround and will be incomprehensible.

Correct, if there is a unique constraint on the email column in the above example, the retry of the transaction would fail with an ALREADY_EXISTS error.

May I make some POC with limiting columns in the checksum comparison in the same pull request which I opened?

Certainly, but bear in mind that the change must be something that would be safe and understandable to include for all users.

[kurisumakise2011]

Hello @olavloite it's again me.
I have got back to this issue recently.

I thought for some time, and didn't find the perfect solution right now.
Your concern is clear, and actually java client may delegate retrying to the using application. But already written applications cannot be changed throughout for using spanner, it's really sad.

So, what I came up with.

All listed suggestions are on demand via specific property, and won't break compatibility.

1) Checking checksum only for unique columns. (have to select and cache data from information_schema)

2) Checking checksum only for existing rows before aborting (but it's problem to figure out what is the primary key of row, for that I have to make the sql query to information_schema.

Why I suggested that? Because things may occur when an app selects all data from the table, then transaction is being aborted, and an app will come across that, a new row has been inserted into the table. Eventually, an app got ConcurrentModification, but I cannot say, that is really concurrent modification.
But the most problem is that, table might be changed completely (3 rows were inserted and 3 rows were removed). So-so, not very good idea to me.

And I think more appropriate option

3) To make some metadata properties, which column or tables can be excluded from checksum checking there.

So, Unfortunately I could not handle those exception in my own way on the application side (it's because there is a ORM layer between spanner). It's why I'm suggesting you some weird PR. I was confused, when I didn't found any similar issues on the internet (only a few questions on stackoverflow and one on github).

Actually spanner is working very well, I suspect at any rate, that some time the issue will appear (for example two clients try adding new different models in the same time, meanwhile the third client is adding another model into table, that is being selected in the others transactions).

May I suggest to make ExceptionHandler interface (for spanner client), where users can write their own behaviour for handling any exception? Instead of changing retry policy.

Thanks you for replying and answers.

[kurisumakise2011]

Hello @olavloite , I know I'm pretty annoying you with my questions(
I apologies.
I think that understand a the cause of aborted transaction, and would like to know your opinion if you don't mind.

So the cause might be false-positives

Tx1

SELECT *
FROM user_entity
WHERE username='user_1' AND realm_id='test'

SELECT *
FROM user_entity
WHERE **email**='email_1' AND realm_id='test'

INSERT INTO user_entity (created_timestamp, email, email_constraint, realm_id, username, id, email_verified, enabled, not_before)
VALUES (1593100429637, 'email_1', 'email_1_constraint', 'test', 'user_1', '1', true, true, 0)

Tx2

SELECT *
FROM user_entity
WHERE username='user_2' AND realm_id='test'

SELECT *
FROM user_entity
WHERE **email**='email_2' AND realm_id='test'

INSERT INTO user_entity (created_timestamp, email, email_constraint, realm_id, username, id, email_verified, enabled, not_before)
VALUES (1593100429640, 'email_2', 'email_2_constraint', 'test', 'user_2', '2', true, true, 0)

These transactions are wrong.

But if I change email to email_constraint it will work

Tx1

SELECT *
FROM user_entity
WHERE username='user_1' AND realm_id='test'


SELECT *
FROM user_entity
WHERE **email_constraint**='email_1_constraint' AND realm_id='test'


INSERT INTO user_entity (created_timestamp, email, email_constraint, realm_id, username, id, email_verified, enabled, not_before)
VALUES (1593100429637, 'email_1', 'email_1_constraint', 'test', 'user_1', '1', true, true, 0)

Tx2

SELECT *
FROM user_entity
WHERE username='user_2' AND realm_id='test'


SELECT *
FROM user_entity
WHERE **email_constraint**='email_2_constraint' AND realm_id='test'


INSERT INTO user_entity (created_timestamp, email, email_constraint, realm_id, username, id, email_verified, enabled, not_before)
VALUES (1593100429640, 'email_2', 'email_2_constraint', 'test', 'user_2', '2', true, true, 0)

Because my indexes:

(realm_id, username)
(realm_id, email_constraint)
(email)

So what do you think ?
Could be known 'false-positive' issue.

I'm really would like to read you thoughts.

Thank you, and I'm sorry for a lot of text.