cloud-spanner-emulator: Cannot insert multiple rows in one transaction with a commit timestamp column

I have a table with a TIMESTAMP column with OPTIONS (allow_commit_timestamp=true). If I try to execute two INSERT statements on this table with a PENDING_COMMIT_TIMESTAMP() value for the commit timestamp column, the second statement will fail with a INVALID_ARGUMENT: Column LastModified cannot be accessed because it, or its associated index, has a pending CommitTimestamp error message.

Example code that reproduces the problem:

  public static void main(String[] args) throws Exception {
    try (Spanner spanner = SpannerOptions.newBuilder().setProjectId("test-project")
        .build().getService()) {
      try {
        spanner.getInstanceAdminClient().createInstance(
            InstanceInfo.newBuilder(InstanceId.of("test-project", "test-instance"))
            .setDisplayName("test")
            .setNodeCount(1)
            .setInstanceConfigId(InstanceConfigId.of("test-project", "emulator-config"))
            .build()
            ).get();
      } catch (ExecutionException ee) {
        SpannerException e = (SpannerException) ee.getCause();
        if (e.getErrorCode() != ErrorCode.ALREADY_EXISTS) {
          throw e;
        }
      }
      try {
        spanner.getDatabaseAdminClient().createDatabase("test-instance", "test-db", Arrays.asList(
               "CREATE TABLE Singers (\n"
            + "  SingerId     INT64 NOT NULL,\n"
            + "  FirstName    STRING(200),\n"
            + "  LastName     STRING(200) NOT NULL,\n"
            + "  LastModified TIMESTAMP OPTIONS (allow_commit_timestamp=true),\n"
            + ") PRIMARY KEY (SingerId)")).get();
      } catch (ExecutionException ee) {
        SpannerException e = (SpannerException) ee.getCause();
        if (e.getErrorCode() != ErrorCode.ALREADY_EXISTS) {
          throw e;
        }
      }
      final Random rnd = new Random();
      DatabaseClient client =
          spanner.getDatabaseClient(DatabaseId.of("test-project", "test-instance", "test-db"));
      client.readWriteTransaction().run(new TransactionCallable<Void>() {
        @Override
        public Void run(TransactionContext transaction) throws Exception {
          transaction.executeUpdate(Statement.newBuilder(
              "INSERT INTO Singers (SingerId, FirstName, LastName, LastModified) VALUES (@id, @first, @last, PENDING_COMMIT_TIMESTAMP())")
              .bind("id").to(rnd.nextLong()).bind("first").to("Pete").bind("last").to("Harrisson")
              .build());
          transaction.executeUpdate(Statement.newBuilder(
              "INSERT INTO Singers (SingerId, FirstName, LastName, LastModified) VALUES (@id, @first, @last, PENDING_COMMIT_TIMESTAMP())")
              .bind("id").to(rnd.nextLong()).bind("first").to("Zeke").bind("last").to("Allison")
              .build());
          return null;
        }
      });
  }
}

The same behavior is also observed if the two statements are sent as one BatchDML request.

The same operation does work on a real Spanner instance.

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Comments: 16 (2 by maintainers)

Most upvoted comments

@Vizerai Any updates? We are running into this as well

Not sure who I need to tag here from Google team (@arunmoezhi ?), but I can open a PR (even though you’re not accepting contributions directly) with the same commit separately if you guys are interested in incorporating (or in getting an inspiration of) the change into the future release

@kberezin-nshl Thanks for the suggested fix. I’ll relay it to the people working on this part of the emulator. One of the reasons that we cannot accept contributions directly, is that the code here on GitHub is just an export from an internal repository. But the commit itself should be a good source of inspiration.

Thanks for pointing this out. We are looking into the issue.

Not sure who I need to tag here from Google team (@arunmoezhi ?), but I can open a PR (even though you’re not accepting contributions directly) with the same commit separately if you guys are interested in incorporating (or in getting an inspiration of) the change into the future release

Okay, I’ve figured that out.

The root cause of the issue is the fact that internal reads made by ZetaSQL engine during DML query evaluation used the same ReadWriteTransaction::Read method which throws when there is an attempt to read a column with pending commit timestamp.

I came up with a fix for this in our fork here: commit. It may not cover all the possible cases but it now allows execution of multiple DML queries (inserts/updates) with pending commit timestamp within the same transaction.

If folks here are interested, we have also built the emulator with this fix (and some others), here are the release notes with a docker container (linux/amd64 only) link in Docker Hub: https://github.com/nutshelllabs/cloud-spanner-emulator/releases/tag/v1.5.14.5