Cannot delete shard mapping using Azure SQL Elastic Scale

Question

We have an entity in our system called an "identity program." That is also our sharding boundary, every identity program is stored in its own shard, so the identifier of the shard is the identifier of the identity program.

We are in the process of implementing the ability to physically delete an identity program. As part of that process we want to clean up the shard map. To do so I've written the following:

var shardKey = new Guid("E03F1DC0-5CA9-45AE-B6EC-0C90529C0062");

var connectionString = @"shard-catalog-connection-string";
var shardMapManager = ShardMapManagerFactory.GetSqlShardMapManager(connectionString, ShardMapManagerLoadPolicy.Lazy);
var shardMap = shardMapManager.GetListShardMap<Guid>("IdentityProgramIdListShardMap");

if (shardMap.TryGetMappingForKey(shardKey, out PointMapping<Guid> mapping))
{
    if (mapping.Status == MappingStatus.Online)
    {
        shardMap.MarkMappingOffline(mapping);
    }

    shardMap.DeleteMapping(mapping);
}

The problem is that when it hits the DeleteMapping call it gets an exception:

ShardManagementException: Mapping referencing shard '[shard-connection-string]' in the shard map 'IdentityProgramIdListShardMap' does not exist. Error occurred while executing stored procedure '__ShardManagement.spBulkOperationShardMappingsGlobalBegin' for operation 'RemovePointMapping'. This can occur if another concurrent user has already removed the mapping.

But the mapping hasn't been removed, because right after that I execute:

mappings = shardMap.GetMappings();
foreach(var mapping in mappings)
{
    Console.WriteLine(mapping.Value);
}

And I can see that the shardmap entry is still there, and is marked Offline.

If I remove the call to MarkMappingOffline I get an exception stating that the shard mapping can't be removed because it is online.

So I seem to have a catch-22. If I mark it offline it thinks the shard mapping is gone and won't let me delete it. If I don't mark it as offline it tells me it has to be offline.

score 7 · Accepted Answer · answered Oct 17 '17 at 00:20

You must always operate on the current version of the mapping, so you code should be

if (shardMap.TryGetMappingForKey(shardKey, out PointMapping<Guid> mapping))
{
    if (mapping.Status == MappingStatus.Online)
    {
        // `mapping =` on next line is needed
        mapping = shardMap.MarkMappingOffline(mapping);
    }

    shardMap.DeleteMapping(mapping);
}

To explain a little further: elastic db tools uses an optimistic concurrency model where each operation checks that you operating on the latest version of each object. The error message is saying that there was a "concurrent" modification to the mapping, i.e. somebody else concurrently did MarkMappingOffline after you got the mapping but before you did DeleteMapping. Actually that "other person" was yourself, but because you didn't delete the latest version of the mapping, elastic db tools was not aware that it was you. :)

Thanks for the explanation. I had actually figured this out myself shortly after I posted the question which is why I didn't get back to it to mark the answer. I (mistakenly) assumed that `shardMap.MarkMappingOffline(mapping);` would simply modify the passed-in `mapping` rather than returning a new/modified instance. It's all working now. — Craig W., Nov 07 '17 at 16:23

Cannot delete shard mapping using Azure SQL Elastic Scale

1 Answers1