Transactions, locking and retries

[matthewjumpsoffbuildings]

I am trying to set up some logic to implement a scalable totals counter that is updated when rows are inserted into another table.

As per the documentation, I am using DB::transaction(), but I have a few questions.

What I want to happen within the transaction is the following

• A row is inserted into a different table
• Then, a random integer is used as an id to attempt to get a row from the totals table
• If a row for that id exists, the count column is incremented and then that row is updated
• If a row for that id does not exist, a new row is inserted with that id and a fresh count value of 1
• If another request is accessing the same row, so that row is locked, minimal retries are attempted, and instead a new row is inserted with a fresh count value of 1

The parts I am unclear on are:

• Within DB::transaction() how do I detect lock contention when 2 concurrent requests are accessing the same row on totals
• How do I ensure that lock contention doesnt cause deadlock, but instead the later request simply gives up attempting to access the row in question very quickly, and activates the logic to handle inserting a new row in the previous point

[taka-oyama]

Within DB::transaction() how do I detect lock contention when 2 concurrent requests are accessing the same row on totals

I 'm assuming you're referring to a feature like MySQL/PgSQL's NOWAIT option. Spanner doesn't have an equivalent feature (at least not to my knowledge).

Within DB::transaction() how do I detect lock contention when 2 concurrent requests are accessing the same row on totals

The request that opened the transaction later will just wait for the totals to unlock, write to it, attempt to commit, fail (because the value changed), then retry by running the transaction callback again. Spanner will attempt this 11 times. You can change the number of attempts by setting it as the second argument for DB::transaction().

How do I ensure that lock contention doesnt cause deadlock, but instead the later request simply gives up attempting to access the row in question very quickly, and activates the logic to handle inserting a new row in the previous point

If both requests are locking the rows in the same order, I don't think it will cause any deadlocks. It'll just cause alot of waiting if you write to it too often.

[matthewjumpsoffbuildings]

The request that opened the transaction later will just wait for the totals to unlock, write to it, fail, then retry by running the transaction callback again. Spanner will attempt this 11 times. You can change the number of attempts by setting it as the second argument for DB::transaction().

If I set the number of attempts to 1, and then the later transaction fails on the first attempt due to the row being locked

• How long will that take? Eg how long will the later transaction take, roughly, to fail its first and only attempt?
• How do I catch that failure? Do I put DB::transaction() in a try/catch block? Or is there some other approach to catching the failure of the later transaction to commit?

[taka-oyama]

How long will that take? Eg how long will the later transaction take, roughly, to fail its first and only attempt?

Transactions usually time out if you leave it open for more than 10 seconds or so. My memory is a bit fuzzy on this so you should test this yourself.

Do I put DB::transaction() in a try/catch block?

Yes.

[matthewjumpsoffbuildings]

It appears from the docs here:

https://cloud.google.com/spanner/docs/custom-timeout-and-retry#node.js

That there is a way to control things like retry delays and RPC timeouts

Is there a way to pass these options to the Spanner PHP client?

Also with the try/catch block for transactions aborted due to locked rows and failed number of attempts, is there a particular type of Throwable I should be catching for that specific case, allowing other types of Throwable to pass to a more general catch block? Or should I be checking for a particular error code or property on the Throwable object in the catch block?

[taka-oyama]

Is there a way to pass these options to the Spanner PHP client?

No. One only one I can find is requestTimeout which is not what you want.

That there is a way to control things like retry delays and RPC timeouts

There is no way to do it internally but you can add retry delays in userland.

Also with the try/catch block for transactions aborted due to locked rows and failed number of attempts, is there a particular type of Throwable I should be catching for that specific case, allowing other types of Throwable to pass to a more general catch block? Or should I be checking for a particular error code or property on the Throwable object in the catch block?

The transaction callback block retries all AbortedExceptions but if you just want to retry locked rows, you should just catch the one you're concerned with.

[matthewjumpsoffbuildings]

Right so would a rough approximation of what I want be something like

try {
  DB::transaction(function($conn) {
    $conn->insert("INSERT new row into other table");

    $id = random_int(1,100);
    $existing = $conn->select("SELECT count FROM totals WHERE id = $id");

    if(count($existing) {
      $count = $existing[0]->count + 1;
      $conn->update("UPDATE totals set count = $count WHERE id = $id");
    } else {
      $conn->insert("INSERT new row into totals");
    }
  }, 1);  // only do 1 attempt
} catch (AbortedException $e) {
  DB::transaction(function($conn) {
    $conn->insert("INSERT new row into other table");
    $conn->insert("INSERT new row into totals table");
  );
} catch (Throwable $e) {
  // handle other errors
}

[taka-oyama]

AbortedException is not limited to Transaction locks so you should narrow down the exception you're catching.

Other than that, the code looks good.

[matthewjumpsoffbuildings]

What exception should I be catching?

[matthewjumpsoffbuildings]

Oh and I feel like an example like this might be good to add to the docs somewhere? Just to make clearer how to handle retries and locks etc?

[taka-oyama]

What exception should I be catching?

Don't remember the exact error.

Oh and I feel like an example like this might be good to add to the docs somewhere? Just to make clearer how to handle retries and locks etc?

PR is always welcome.

[taka-oyama]

Closing due to no activity.