Lock statistics

Spanner provides lock statistics that enable you to identify the row key and table column(s) that were the main sources of transaction lock conflicts in your database during a particular time period. You can retrieve these statistics from the SPANNER_SYS.LOCK_STATS* system tables using SQL statements.

Availability

SPANNER_SYS data is available only through SQL interfaces; for example:

• A database's Spanner Studio page in the Google Cloud console

• The gcloud spanner databases execute-sql command

• The Lock insights dashboard

• The executeQuery API

Other single read methods that Spanner provides don't support SPANNER_SYS.

Lock statistics by row key

The following tables track the row key with the highest wait time:

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE: Row keys with the highest lock wait times during 1 minute intervals.

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE: Row keys with the highest lock wait times during 10 minute intervals.

• SPANNER_SYS.LOCK_STATS_TOP_HOUR: Row keys with the highest lock wait times during 1 hour intervals

These tables have the following properties:

• Each table contains data for non-overlapping time intervals of the length the table name specifies.

• Intervals are based on clock times. 1 minute intervals end on the minute, 10 minute intervals end every 10 minutes starting on the hour, and 1 hour intervals end on the hour. After each interval, Spanner collects data from all servers and then makes the data available in the SPANNER_SYS tables shortly thereafter.

  For example, at 11:59:30 AM, the most recent intervals available to SQL queries are:

  • 1 minute: 11:58:00–11:58:59 AM
  • 10 minute: 11:40:00–11:49:59 AM
  • 1 hour: 10:00:00–10:59:59 AM

• Spanner groups the statistics by starting row key range.

• Each row contains statistics for total lock wait time of a particular starting row key range that Spanner captures statistics for during the specified interval.

• If Spanner is unable to store information about every row key range for lock waits during the interval, the system prioritizes row key range with the highest lock wait time during the specified interval.

• All columns in the tables are nullable.

Table schema

Lock modes

Spanner operations acquire locks when the operations are part of a read-write transaction. Read-only transactions don't acquire locks. Spanner uses different lock modes to maximize the number of transactions that have access to a particular data cell at a given time. Different locks have different characteristics. For example, some locks can be shared among multiple transactions, while others can't.

A lock conflict can occur when you attempt to acquire one of the following lock modes in a transaction.

• ReaderShared Lock - A lock which allows other reads to still access the data until your transaction is ready to commit. This shared lock is acquired when a read-write transaction reads data.

• WriterShared Lock - This lock is acquired when a read-write transaction tries to commit a write.

• Exclusive Lock - an exclusive lock is acquired when a read-write transaction, which has already acquired a ReaderShared lock, tries to write data after the completion of read. An exclusive lock is an upgrade from a ReaderShared lock. An exclusive lock is a special case of a transaction holding both the ReaderShared lock and the WriterShared lock at the same time. No other transaction can acquire any lock on the same cell.

• WriterSharedTimestamp Lock - a special type of WriterShared lock which is acquired when inserting new rows into a table that has a commit timestamp as part of the primary key. This type of lock prevents transaction participants from creating the exact same row and, therefore, conflicting with each other. Spanner updates the key of the inserted row to match the commit timestamp of the transaction that performed the insert.

For more information on transaction types and the kinds of locks that are available, see Transactions.

Lock mode conflicts

The following table shows the possible conflicts between different lock modes.

WriterSharedTimestamp locks are only used when inserting new rows with a timestamp as part of its primary key. WriterShared and Exclusive locks are used when writing to existing cells or inserting new rows without timestamps. As a result, WriterSharedTimestamp can't conflict with other types of locks, and those scenarios are shown as Not applicable in the preceding table.

The only exception is ReaderShared, which can be applied to non-existing rows and, therefore, could potentially conflict with WriterSharedTimestamp. For example, a full table scan locks the whole table even for rows that haven't been created, so it's possible for ReaderShared to conflict with WriterSharedTimestamp.

What is a row range start key?

The ROW_RANGE_START_KEY column identifies the composite primary key, or starting primary key of a row range, that has lock conflicts. The following schema is used to illustrate an example.

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  TrackId      INT64 NOT NULL,
  SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Users (
  UserId     INT64 NOT NULL,
  LastAccess TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  ...
) PRIMARY KEY (UserId, LastAccess);

As the following table of row key and row key ranges shows, a range is represented with a plus, '+', sign in the key. The key in those cases represents the starting key of a key range in which a lock conflict occurred.

Aggregate statistics

SPANNER_SYS also contains tables to store aggregate data for lock statistics captured by Spanner in a specific time period:

• SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Aggregate statistics for all lock waits during 1 minute intervals.

• SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Aggregate statistics for all lock waits during 10 minute intervals.

• SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Aggregate statistics for all lock waits during 1 hour intervals.

Aggregate statistics tables have the following properties:

• Each table contains data for non-overlapping time intervals of the length the table name specifies.

• Intervals are based on clock times. 1 minute intervals end on the minute, 10 minute intervals end every 10 minutes starting on the hour, and 1 hour intervals end on the hour.

  For example, at 11:59:30 AM, the most recent intervals available to SQL queries on aggregate lock statistics are:

  • 1 minute: 11:58:00–11:58:59 AM
  • 10 minute: 11:40:00–11:49:59 AM
  • 1 hour: 10:00:00–10:59:59 AM

• Each row contains statistics for all lock waits on the database during the specified interval, aggregated together. There is only one row per time interval.

• The statistics captured in the SPANNER_SYS.LOCK_STATS_TOTAL_* tables include lock waits that Spanner did not capture in the SPANNER_SYS.LOCK_STATS_TOP_* tables.

• Some columns in these tables are exposed as metrics in Cloud Monitoring. The exposed metrics are:

  • Lock wait time

  For more information, see Spanner metrics.

Table schema

Example queries

The following is an example of a SQL statement that you can use to retrieve lock statistics. You can run these SQL statements using the client libraries, the gcloud spanner, or the Google Cloud console.

List the lock statistics for the previous 1-minute interval

The following query returns the lock wait information for each row key with a lock conflict, including the fraction of total lock conflicts, during the most recent 1-minute time interval.

The CAST() function converts the row_range_start_key BYTES field to a STRING.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_minute t, spanner_sys.lock_stats_top_minute s
WHERE t.interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.lock_stats_total_minute)
AND s.interval_end = t.interval_end
ORDER BY s.lock_wait_seconds DESC;

Query output

¹ _exists is an internal field that is used to check whether a certain row exists or not.

Data retention

At a minimum, Spanner keeps data for each table for the following time periods:

• SPANNER_SYS.LOCK_STATS_TOP_MINUTE and SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Intervals covering the previous 6 hours.

• SPANNER_SYS.LOCK_STATS_TOP_10MINUTE and SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Intervals covering the previous 4 days.

• SPANNER_SYS.LOCK_STATS_TOP_HOUR and SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Intervals covering the previous 30 days.

Troubleshoot lock conflicts in your database using lock statistics

You can use SQL or the Lock insights dashboard to view lock conflicts in your database.

The following topics show how you can investigate such lock conflicts using SQL code.

Select a time period to investigate

You examine the Latency metrics for your Spanner database and discover a time period when your app is experiencing high latency and CPU usage. For example, the issue started occurring around 10:50 PM on November 12th, 2020.

Determine whether transaction commit latency increased along with the lock wait time during the selected period

Locks are acquired by transactions so, if lock conflicts cause long wait times, we should be able to see the increase in the transaction commit latency along with the increase in the lock wait time.

Having selected a time period to start our investigation, we'll join the transaction statistics TXN_STATS_TOTAL_10MINUTE with lock statistics LOCK_STATS_TOTAL_10MINUTE around that time to help us understand if the increase of the average commit latency is contributed to by the increase of the lock waiting time.

SELECT t.interval_end, t.avg_commit_latency_seconds, l.total_lock_wait_seconds
FROM spanner_sys.txn_stats_total_10minute t
LEFT JOIN spanner_sys.lock_stats_total_10minute l
ON t.interval_end = l.interval_end
WHERE
  t.interval_end >= "2020-11-12T21:50:00Z"
  AND t.interval_end <= "2020-11-12T23:50:00Z"
ORDER BY interval_end;

Take the following data as an example of the results we get back from our query.

This preceding results show a dramatic increase in avg_commit_latency_seconds and total_lock_wait_seconds during the same time period from 2020-11-12 22:40:00 to 2020-11-12 22:50:00, and dropped after that. One thing to note is that the avg_commit_latency_seconds is the average time spent for only the commit step. On the other hand, total_lock_wait_seconds is the aggregate lock time for the period, so the time looks much longer than the transaction commit time.

Now that we've confirmed the lock wait time is closely related to the increase in write latency, we'll investigate in the next step which rows and columns cause the long wait.

Discover which row keys and columns had long lock wait times during the selected period

To find out which row keys and columns experienced the high lock wait times during the period we are investigating, we query the LOCK_STAT_TOP_10MINUTE table, which lists the row keys and columns that contribute the most to lock wait.

The CAST() function in the following query converts the row_range_start_key BYTES field to a STRING.

SELECT CAST(s.row_range_start_key AS STRING) AS row_range_start_key,
       t.total_lock_wait_seconds,
       s.lock_wait_seconds,
       s.lock_wait_seconds/t.total_lock_wait_seconds frac_of_total,
       s.sample_lock_requests
FROM spanner_sys.lock_stats_total_10minute t, spanner_sys.lock_stats_top_10minute s
WHERE
  t.interval_end = "2020-11-12T22:50:00Z" and s.interval_end = t.interval_end;

From this table of results, we can see the conflict happened on the Singers table at key SingerId=32. The Singers.SingerInfo is the column where the lock conflict happened between ReaderShared and WriterShared.

This is a common type of the conflict when there's one transaction trying to read a certain cell and the other transaction is trying to write to the same cell. We now know the exact data cell for which the transactions are contending the lock, so in the next step we'll identify the transactions that are contending for the locks.

Find which transactions are accessing the columns involved in the lock conflict

To identify the transactions that are experiencing significant commit latency within a specific time interval due to lock conflicts, you need to query for the following columns from the SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE table:

• fprint
• read_columns
• write_constructive_columns
• avg_commit_latency_seconds

You need to filter for locked columns identified from the SPANNER_SYS.LOCK_STATS_TOP_10MINUTE table:

• Transactions that read any column that incurred a lock conflict when attempting to acquire the ReaderShared lock.

• Transactions that write to any column that incurred a lock conflict when attempting to acquire a WriterShared lock.

SELECT
  fprint,
  read_columns,
  write_constructive_columns,
  avg_commit_latency_seconds
FROM spanner_sys.txn_stats_top_10minute t2
WHERE (
  EXISTS (
    SELECT * FROM t2.read_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "ReaderShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
OR
  EXISTS (
    SELECT * FROM t2.write_constructive_columns columns WHERE columns IN (
      SELECT DISTINCT(req.COLUMN)
      FROM spanner_sys.lock_stats_top_10minute t, t.SAMPLE_LOCK_REQUESTS req
      WHERE req.LOCK_MODE = "WriterShared" AND t.interval_end ="2020-11-12T23:50:00Z"))
)
AND t2.interval_end ="2020-11-12T23:50:00Z"
ORDER BY avg_commit_latency_seconds DESC;

The query result is sorted by the avg_commit_latency_seconds column so that you see the transaction experiencing the highest commit latency first.

The query results show that two transactions tried to access the Singers.SingerInfo column, which is the column that had lock conflicts during the time period. Once you identify the transactions causing the lock conflicts, you can analyze the transactions using their fingerprint, fprint, to identify potential issues that contributed to the lock conflict.

After reviewing the transaction with fprint=1866043996151916800, you can use the read_columns and write_constructive_columns columns to identify which part of your application code triggered the transaction. You can then view the underlying DML that's not filtering on the primary key, SingerId. This caused a full table scan and locked the table until the transaction was committed.

To resolve the lock conflict, you can do the following:

1. Use a read-only transaction to identify the required SingerId values.
2. Use a separate read-write transaction to update the rows for the required SingerId values.

Apply best practices to reduce lock contention

In our example scenario, we were able to use lock statistics and transaction statistics to narrow down our problem to a transaction that wasn't using the primary key of our table when making updates. We came up with ideas to improve the transaction based on whether we knew the keys of the rows we wanted to update beforehand or not.

When looking at potential issues in your solution, or even when designing your solution, consider these best practices to reduce the number of lock conflicts in your database.

• Avoid large reads inside read-write transactions.

• Use read-only transactions whenever possible, because they don't acquire any locks.

• Avoid full table scans in a read-write transaction. This includes writing a DML conditional on the primary key or assigning a specific key range when using the Read API.

• Keep the locking period short by committing the change as soon after you read the data as possible in a read-write transaction. A Read-write transaction guarantees that the data remains unchanged after you read the data until you successfully commit the change. To achieve this, the transaction requires locking the data cells during the read and during the commit. As a result, if you can keep the locking period short, transactions are less likely to have lock conflicts.

• Favor small transactions over large transactions, or consider Partitioned DML for long running DML transactions. A long running transaction acquires a lock for a long time, so consider breaking a transaction which touches thousands of rows into multiple smaller transactions which update hundreds of rows whenever possible.

• If you don't need the guarantee provided by a read-write transaction, avoid reading any data in the read-write transaction before committing the change, for example, by reading the data in a separate read-only transaction. Most lock conflicts occur due to the strong guarantee, to ensure data remain unchanged between the read and commit. So, if the read-write transaction doesn't read any data, it doesn't need to lock the cells for a long time.

• Specify only the minimal set of columns required in a read-write transaction. As Spanner locks are per data cell, when a read-write transaction reads excessive columns, it acquires a ReaderShared lock on these cells. This might cause lock conflicts when other transactions acquire a WriterShared lock on writes to the excessive columns. For example, consider specifying a set of columns instead of * on read.

• Minimize API calls in a read-write transaction. The latency of API calls might lead to lock contention in Spanner, as API calls are subject to network delays as well as service-side delays. We recommend making API calls outside of read-write transactions whenever possible. If you must execute API calls inside a read-write transaction, make sure to monitor the latency of your API calls to minimize the impact on the lock acquisition period.

• Follow schema design best practices.

What's next

• Learn about other Introspection tools.
• Learn about other information Spanner stores for each database in the databases information schema table.
• Learn more about SQL best practices for Spanner.