← Back to DevBytes
Developer Tutorial August 13, 2026 7 min read

Troubleshooting Spanner: Common Issues and Solutions

Introduction to Troubleshooting Spanner

Google Cloud Spanner is a globally distributed, strongly consistent relational database designed for high availability and horizontal scalability. While Spanner handles many complexities of distributed databases automatically, developers still encounter issues related to performance, schema design, transaction management, and connectivity. This tutorial covers the most common Spanner issues and provides practical solutions to diagnose and resolve them.

Why Troubleshooting Spanner Matters

Spanner's unique architecture—combining the scalability of NoSQL with the semantics of a relational database—means that traditional database troubleshooting techniques don't always apply. Understanding Spanner-specific failure modes helps you maintain low latency, avoid costly anti-patterns, and ensure your application remains reliable at scale. A single poorly designed query or schema choice can cause cascading performance problems across your entire instance.

Issue 1: High Latency on Queries

High latency is one of the most frequently reported Spanner issues. It often stems from full table scans, missing secondary indexes, or hot keys. Spanner's query optimizer is powerful, but it cannot compensate for fundamentally inefficient query patterns.

Diagnosing Latency with Query Plans

Use the Cloud Console's Query Insights or the gcloud spanner databases execute-sql command with the --query-mode=PLAN flag to inspect query execution plans. Look for Scan operators that show Full scan rather than Index scan.

# Execute a query and view its plan
gcloud spanner databases execute-sql my-database \
    --instance=my-instance \
    --sql="SELECT * FROM Orders WHERE customer_id = 123" \
    --query-mode=PROFILE

Solution: Add Secondary Indexes

If your query filters on a non-key column, create a secondary index to avoid full table scans:

CREATE INDEX OrdersByCustomerId ON Orders(customer_id);

For queries that also need to retrieve non-indexed columns, consider a storing index to avoid a join back to the base table:

CREATE INDEX OrdersByCustomerIdWithStatus ON Orders(customer_id) STORING (status, total);

Solution: Use Query Hints When Needed

Sometimes the optimizer picks a suboptimal plan. You can force index usage with a query hint:

SELECT *
FROM Orders@{FORCE_INDEX=OrdersByCustomerId}
WHERE customer_id = 123;

Issue 2: Hotspotting on Primary Keys

Hotspotting occurs when a disproportionate amount of read or write traffic targets a single split. This is common when primary keys are monotonically increasing (like timestamps or auto-incrementing IDs), because Spanner distributes data by key ranges and new keys all land in the same split.

Detecting Hotspotting

Monitor the Cloud Spanner metrics dashboard for:

Solution: Use Hash-Sharded Keys

Prepend a hash or shard key to distribute writes evenly across splits:

-- Create a table with a hash-prefixed primary key
CREATE TABLE Orders (
    shard_id INT64 NOT NULL,
    order_id STRING(36) NOT NULL,
    customer_id INT64 NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total NUMERIC NOT NULL,
) PRIMARY KEY (shard_id, order_id);

When inserting, compute the shard by hashing a natural key:

import hashlib

def generate_shard_id(order_id: str, num_shards: int = 10) -> int:
    h = hashlib.sha256(order_id.encode()).hexdigest()
    return int(h, 16) % num_shards

order_id = "550e8400-e29b-41d4-a716-446655440000"
shard_id = generate_shard_id(order_id)
print(f"shard_id={shard_id}, order_id={order_id}")

Solution: Use Bit-Reversed Sequences

If you need sequential-looking IDs but want to avoid hotspotting, use a bit-reversed sequence. This spreads consecutive values across the key space:

def bit_reverse(x: int, bits: int = 64) -> int:
    result = 0
    for i in range(bits):
        if x & (1 << i):
            result |= 1 << (bits - 1 - i)
    return result

next_seq = 1001
reversed_id = bit_reverse(next_seq)
print(f"Original: {next_seq}, Reversed: {reversed_id}")

Issue 3: Transaction Aborts and Deadlocks

Spanner uses two-phase commit and optimistic concurrency control. Long-running transactions or transactions that touch many rows are more likely to be aborted due to write-write conflicts or lock contention. When a transaction aborts, Spanner returns an ABORTED error, which the client library typically retries automatically—but only if you structure your code correctly.

Correct Transaction Retry Pattern

Always use the client library's transaction runner, which handles retries internally. Never catch AbortedError and retry manually with a custom loop unless you understand the backoff semantics.

from google.cloud import spanner

client = spanner.Client()
instance = client.instance("my-instance")
database = instance.database("my-database")

def transfer_funds(from_account, to_account, amount):
    def txn(transaction):
        # Read both rows with a shared lock
        rows = transaction.execute_sql(
            "SELECT balance FROM Accounts WHERE account_id IN (@from, @to)",
            params={"from": from_account, "to": to_account},
            param_types={"from": spanner.param_types.INT64, "to": spanner.param_types.INT64},
        ).rows
        balances = {row[0]: row[1] for row in rows}

        if balances.get(from_account, 0) < amount:
            raise ValueError("Insufficient funds")

        transaction.execute_update(
            "UPDATE Accounts SET balance = balance - @amount WHERE account_id = @from",
            params={"amount": amount, "from": from_account},
            param_types={
                "amount": spanner.param_types.INT64,
                "from": spanner.param_types.INT64,
            },
        )
        transaction.execute_update(
            "UPDATE Accounts SET balance = balance + @amount WHERE account_id = @to",
            params={"amount": amount, "to": to_account},
            param_types={
                "amount": spanner.param_types.INT64,
                "to": spanner.param_types.INT64,
            },
        )

    # The library handles ABORTED retries automatically
    database.run_in_transaction(txn)

transfer_funds(101, 202, 500)

Reducing Abort Rates

Issue 4: Connection and Authentication Errors

Spanner client libraries use gRPC under the hood. Common connection errors include UNAVAILABLE, DEADLINE_EXCEEDED, and PERMISSION_DENIED. These often relate to network configuration, IAM roles, or quota limits.

Common Error Causes

Solution: Configure Retry and Timeout Settings

from google.cloud import spanner
from google.api_core import retry

# Custom retry policy for transient errors
custom_retry = retry.Retry(
    initial=1.0,
    maximum=60.0,
    multiplier=2.0,
    deadline=300.0,
    predicate=retry.if_exception_type(
        ConnectionError,
        TimeoutError,
    ),
)

client = spanner.Client(
    project="my-project",
    client_options={"api_endpoint": "staging-wrenchworks.sandbox.googleapis.com:443"},
)

# Increase gRPC timeout for long-running queries
database = instance.database(
    "my-database",
    pool=spanner.database.BurstyPool(labels={"checkout-pool": "v1"}),
)

Solution: Verify IAM Permissions

# Grant database user role to a service account
gcloud spanner databases add-iam-policy-binding my-database \
    --instance=my-instance \
    --member="serviceAccount:app@my-project.iam.gserviceaccount.com" \
    --role="roles/spanner.databaseUser"

# Verify the binding
gcloud spanner databases get-iam-policy my-database \
    --instance=my-instance

Issue 5: Schema Migration Failures

Spanner supports online schema changes, but certain operations are long-running and can fail if they conflict with concurrent DDL statements or if the instance is under heavy load.

Solution: Batch DDL Statements and Monitor Progress

# Submit multiple DDL statements as a batch
gcloud spanner databases ddl update my-database \
    --instance=my-instance \
    --ddl='CREATE INDEX OrdersByStatus ON Orders(status);' \
    --ddl='CREATE TABLE AuditLog (log_id STRING(36) NOT NULL, event STRING(MAX)) PRIMARY KEY(log_id);'

# Check progress of long-running operations
gcloud spanner operations list --instance=my-instance

Best Practices for Schema Changes

Issue 6: Out of Memory and Client-Side Resource Leaks

Streaming large result sets without proper resource management can cause memory exhaustion on the client. Always consume or close result streams, and use connection pooling appropriately.

Solution: Stream Results with Pagination

def stream_large_table(database, batch_size=10000):
    def fetch_batch(txn, offset):
        results = txn.execute_sql(
            "SELECT order_id, total FROM Orders ORDER BY order_id LIMIT @limit OFFSET @offset",
            params={"limit": batch_size, "offset": offset},
            param_types={
                "limit": spanner.param_types.INT64,
                "offset": spanner.param_types.INT64,
            },
        )
        return list(results)

    offset = 0
    while True:
        batch = database.run_in_transaction(fetch_batch, offset)
        if not batch:
            break
        for row in batch:
            yield row
        offset += batch_size

for order in stream_large_table(database):
    process_order(order)

Best Practices Summary

Conclusion

Troubleshooting Cloud Spanner effectively requires understanding its distributed architecture and the specific patterns that lead to performance problems. By addressing hotspotting through thoughtful key design, optimizing queries with proper indexing, structuring transactions to minimize aborts, and configuring robust retry logic, you can keep your Spanner-backed applications fast and reliable. The key is to combine proactive monitoring with a solid grasp of these common failure modes—so that when issues arise, you can diagnose and resolve them quickly without disrupting your users.

— Ad —

Google AdSense will appear here after approval

← Back to all articles