Spanner mutation limit on deleting 1 row of parent table

Question

Insert and update operations count with the multiplicity of the number of columns they affect. For example, inserting a new record may count as five mutations, if values are inserted into five columns. Delete and delete range operations count as one mutation regardless of the number of columns affected. Deleting a row from a parent table that has the ON DELETE CASCADE annotation is also counted as one mutation regardless of the number of interleaved child rows present.

Today I'm trying to delete 1 row from a parent table whose the child (interleaved) table has ON DELETE CASCADE specified.

Example: DELETE FROM my_table WHERE some_primary_key='somevalue'

I'm getting error message saying I'm hitting mutation limit because the child table has more than 20k rows in this split. According to the documention above that should still count as 1 mutation no matter how many rows there are in the child table.

Note that the child table has a secondary index setup.

Is there an issue with this documentation, or am I missing something?

More as a side note: Have you tried to execute the same delete using a Mutation instead of a DML statement? (cloud.google.com/spanner/docs/…) The documentation seems to hint that the limit is counted this way when it is an operation on one Key or a KeyRange, while it does not specifically state that it also is counted this way when it is a DML statement. — Knut Olav Løite
Yes tried both of DML delete and API as well as partitioned update. As suggested in the answer bellow it's likely to be a side effect of the secondary index. — Soufiane Ghzal

Hailong Wen Hailong Wen · Accepted Answer · 2020-06-23T19:07:20

It seems that each subsequent index update is counted as a mutation. I did the following experiments to verify it:

Create a table

CREATE TABLE ParentTable (
    Pid INT64 NOT NULL,
    Data STRING(1024),
) PRIMARY KEY(Pid);

CREATE TABLE ChildTable (
    Pid INT64 NOT NULL,
    Cid INT64 NOT NULL,
    Data STRING(1024),
) PRIMARY KEY(Pid, Cid),
  INTERLEAVE IN PARENT ParentTable ON DELETE CASCADE;

Populate 1 parent row and 30000 child rows:

// Inserts 1 ParentTable rows
INSERT INTO ParentTable (Pid) (SELECT * FROM UNNEST([1]));

// Insert 30000 rows to ChildTable for pid=1
INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM 
(SELECT 0+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);

INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM 
(SELECT 10000+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);

INSERT INTO ChildTable (Pid, Cid) (SELECT 1, child.Cid FROM 
(SELECT 20000+G.g AS Cid FROM (SELECT E.e*10+F.f AS g FROM (SELECT C.c*10+D.d AS e FROM (SELECT A.a*10 + B.b AS c FROM (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS a) AS A, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS b) AS B) AS C, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) AS d) AS D) AS E, (SELECT * FROM UNNEST([0,1,2,3,4,5,6,7,8,9]) as f) AS F) AS G) AS Child);

Verify that the child table has 30000 rows with Pid=1:

// Verify counts
SELECT COUNT(*) FROM ChildTable WHERE Pid=1;

Try to delete it (succeeded)

DELETE FROM ParentTable WHERE Pid=1

Now repeat 1 to 3 again. This time we create secondary index on the child table

CREATE INDEX Cid_Increasing ON ChildTable (Cid) STORING (Data);

Try to delete the parent row again, this time it failed saying that

This DML statement exceeds the mutation limit for a single transaction (20000). To reduce the mutation count, try a transaction with fewer writes, or use fewer indexes. This can help because the mutation count for an operation is equal to the number of columns it affects. Reducing writes or indexes reduces the number of affected columns, bringing your mutation count under the limit. Alternatively, try a Partioned DML statement using the client libraries or gcloud command-line tool.

OK, try reducing the number of rows to 20000 and then delete. Failed again.

DELETE FROM ChildTable WHERE Cid > 19999; // deleted 10000 rows.
SELECT COUNT(*) FROM ChildTable WHERE Pid=1; // should be 20000 now.
DELETE FROM ParentTable WHERE Pid=1; // still failed.

Delete one more row in the child table. This time the parent deletion succeeded.

DELETE FROM ChildTable WHERE Cid > 19998;
SELECT COUNT(*) FROM ChildTable WHERE Pid=1; // should be 19999 now.
DELETE FROM ParentTable WHERE Pid=1; // succeeded

The last two experiments suggested that:

the parent row deletion, as well as its child rows deletion, are counted as only 1 mutation.
each subsequent index change is counted as 1 mutation.

Spanner mutation limit on deleting 1 row of parent table

1 Answers