Statistik kunci

Dokumen ini menjelaskan cara menggunakan tabel bawaan statistik kunci Spanner untuk mengidentifikasi row key dan kolom tabel yang menyebabkan konflik kunci transaksi di database Anda selama jangka waktu tertentu. Anda dapat mengambil statistik dari tabel SPANNER_SYS.LOCK_STATS* ini menggunakan pernyataan SQL.

Transaksi yang menggunakan kontrol konkurensi optimis tidak memperoleh kunci saat menjalankan pembacaan dan kueri. Sebagai gantinya, transaksi tersebut memperoleh kunci pada waktu commit, sehingga statistik kunci untuk transaksi ini hanya mencakup konflik yang terjadi selama commit.

Statistik kunci akses

Spanner menyediakan statistik kunci dalam skema SPANNER_SYS. Anda dapat menggunakan cara berikut untuk mengakses data SPANNER_SYS:

  • Halaman Spanner Studio database di konsol Google Cloud

  • Perintah gcloud spanner databases execute-sql.

  • Dasbor Insight kunci.

  • Metode executeSql atau executeStreamingSql.

    Metode baca tunggal berikut yang disediakan Spanner tidak mendukung SPANNER_SYS:

    • Melakukan pembacaan yang kuat dari satu baris atau beberapa baris dalam tabel.
    • Melakukan pembacaan usang dari satu baris atau beberapa baris dalam tabel.
    • Membaca dari satu baris atau beberapa baris dalam indeks sekunder.

Statistik kunci berdasarkan row key

Tabel berikut melacak row key dengan waktu tunggu tertinggi:

  • SPANNER_SYS.LOCK_STATS_TOP_MINUTE: Row key dengan waktu tunggu kunci tertinggi selama interval 1 menit.

  • SPANNER_SYS.LOCK_STATS_TOP_10MINUTE: Row key dengan waktu tunggu penguncian tertinggi selama interval 10 menit.

  • SPANNER_SYS.LOCK_STATS_TOP_HOUR: Row key dengan waktu tunggu penguncian tertinggi selama interval 1 jam

Tabel ini memiliki properti berikut:

  • Setiap tabel berisi data untuk interval waktu yang tidak tumpang-tindih dengan durasi yang ditentukan oleh nama tabel.

  • Interval didasarkan pada waktu jam. Interval 1 menit berakhir pada menit, interval 10 menit berakhir setiap 10 menit mulai dari jam, dan interval 1 jam berakhir pada jam. Setelah setiap interval, Spanner mengumpulkan data dari semua server, lalu menyediakan data tersebut di tabel SPANNER_SYS tidak lama setelahnya.

    Misalnya, pada pukul 11.59.30, interval terbaru yang tersedia untuk kueri SQL adalah:

    • 1 menit: 11.58.00–11.58.59
    • 10 menit: 11.40.00–11.49.59
    • 1 jam: 10.00.00–10.59.59
  • Spanner mengelompokkan statistik berdasarkan rentang row key awal.

  • Setiap baris berisi statistik untuk total waktu tunggu penguncian dari rentang row key awal tertentu yang statistiknya diambil oleh Spanner selama interval yang ditentukan.

  • Jika Spanner tidak dapat menyimpan informasi tentang setiap rentang row key untuk waktu tunggu penguncian selama interval, sistem akan memprioritaskan rentang row key dengan waktu tunggu penguncian tertinggi selama interval yang ditentukan.

  • Semua kolom dalam tabel dapat bernilai null.

Skema tabel

Nama kolom Jenis Deskripsi
INTERVAL_END TIMESTAMP Akhir interval waktu terjadinya konflik kunci yang disertakan.
ROW_RANGE_START_KEY BYTES(MAX) Row key tempat konflik kunci terjadi. Jika konflik melibatkan rentang baris, nilai ini mewakili kunci awal rentang tersebut. Tanda plus, +, menandakan rentang. Untuk mengetahui informasi selengkapnya, lihat Apa yang dimaksud dengan kunci awal rentang baris.
LOCK_WAIT_SECONDS FLOAT64 Waktu tunggu penguncian kumulatif dari konflik penguncian yang dicatat untuk semua kolom dalam rentang row key, dalam detik.
SAMPLE_LOCK_REQUESTS ARRAY<STRUCT<
  column STRING,
  lock_mode STRING,
   transaction_tag STRING>>
Setiap entri dalam array ini sesuai dengan contoh permintaan kunci yang berkontribusi pada konflik kunci, baik dengan menunggu kunci atau memblokir transaksi lain agar tidak mengambil kunci, pada kunci baris (rentang) yang diberikan. Jumlah maksimum sampel dalam array ini adalah 20.

Setiap sampel berisi tiga kolom berikut:
  • lock_mode: Mode kunci yang diminta. Untuk mengetahui informasi selengkapnya, lihat Mode kunci.
  • column: Kolom yang mengalami konflik kunci. Format nilai ini adalah tablename.columnname.
  • transaction_tag: Tag transaksi yang mengeluarkan permintaan. Untuk mengetahui informasi selengkapnya tentang penggunaan tag, lihat Memecahkan masalah dengan tag transaksi.
Semua permintaan kunci yang berkontribusi pada konflik kunci disampel secara acak dan seragam, sehingga mungkin hanya satu setengah dari konflik (baik pemegang maupun penunggu) yang dicatat dalam array ini.
SAMPLE_LOCK_REQUESTS_JSON_STRING STRING Representasi string yang kompatibel dengan JSON dari kolom SAMPLE_LOCK_REQUESTS. String JSON adalah objek JSON dengan struktur yang sama dengan STRUCT yang ditentukan di kolom SAMPLE_LOCK_REQUESTS.

Kolom ini didukung dalam database dialek GoogleSQL dan dialek PostgreSQL.

LOCK_WAIT_DISTRIBUTION ARRAY<STRUCT>

Histogram waktu tunggu kunci individual untuk konflik kunci yang dicatat dalam rentang row key. Tidak seperti kolom latensi lainnya dalam tabel ini, nilai dalam kolom ini menggunakan mikrodetik. Untuk mengonversinya menjadi detik, bagi dengan 1.000.000.

Array berisi satu elemen dan memiliki jenis berikut:
ARRAY<STRUCT<
  COUNT INT64,
  MEAN FLOAT64,
  SUM_OF_SQUARED_DEVIATION FLOAT64,
  NUM_FINITE_BUCKETS INT64,
  GROWTH_FACTOR FLOAT64,
  SCALE FLOAT64,
  BUCKET_COUNTS ARRAY<INT64>>>

Untuk mengetahui informasi selengkapnya tentang nilai, lihat Distribusi dan Eksponensial.

Untuk menghitung persentil waktu tunggu kunci dari distribusi, gunakan fungsi SPANNER_SYS.DISTRIBUTION_PERCENTILE(distribution, n FLOAT64).

LOCK_WAIT_DISTRIBUTION_JSON_STRING STRING

Representasi string yang kompatibel dengan JSON dari kolom LOCK_WAIT_DISTRIBUTION. String JSON adalah objek JSON dengan struktur yang sama dengan STRUCT di kolom tersebut. Seperti kolom LOCK_WAIT_DISTRIBUTION, nilai kolom ini dalam mikrodetik.

Kolom ini didukung dalam database dialek GoogleSQL dan dialek PostgreSQL.

Mode kunci

Operasi Spanner memperoleh kunci saat operasi tersebut merupakan bagian dari transaksi baca-tulis. Transaksi hanya baca tidak memperoleh kunci. Spanner menggunakan mode kunci yang berbeda untuk memaksimalkan jumlah transaksi yang memiliki akses ke sel data tertentu pada waktu tertentu. Kunci yang berbeda memiliki karakteristik yang berbeda. Misalnya, beberapa kunci dapat dibagikan di antara beberapa transaksi, sementara yang lain tidak.

Konflik kunci dapat terjadi saat Anda mencoba mendapatkan salah satu mode kunci berikut dalam transaksi.

  • ReaderShared Lock - Kunci yang memungkinkan pembacaan lain tetap mengakses data hingga transaksi Anda siap di-commit. Kunci bersama ini diperoleh saat transaksi baca-tulis membaca data.

  • Kunci WriterShared - Kunci ini diperoleh saat transaksi baca-tulis mencoba meng-commit penulisan.

  • Kunci Exclusive - kunci eksklusif diperoleh saat transaksi baca-tulis, yang telah memperoleh kunci ReaderShared, mencoba menulis data setelah pembacaan selesai. Kunci eksklusif adalah upgrade dari kunci ReaderShared. Kunci eksklusif adalah kasus khusus dari transaksi yang menyimpan kunci ReaderShared dan kunci WriterShared secara bersamaan. Tidak ada transaksi lain yang dapat memperoleh kunci apa pun pada sel yang sama.

  • Kunci WriterSharedTimestamp - jenis kunci WriterShared khusus yang diperoleh saat menyisipkan baris baru ke dalam tabel yang memiliki stempel waktu commit sebagai bagian dari kunci utama. Jenis kunci ini mencegah peserta transaksi membuat baris yang sama persis dan, oleh karena itu, saling bertentangan. Spanner memperbarui kunci baris yang disisipkan agar sesuai dengan stempel waktu commit transaksi yang melakukan penyisipan.

Untuk mengetahui informasi selengkapnya tentang jenis transaksi dan jenis kunci yang tersedia, lihat Transaksi.

Konflik mode kunci

Tabel berikut menunjukkan kemungkinan konflik antara berbagai mode kunci.

Mode Kunci ReaderShared WriterShared Exclusive WriterSharedTimestamp
ReaderShared Tidak Ya Ya Ya
WriterShared Ya Tidak Ya Tidak berlaku
Exclusive Ya Ya Ya Tidak berlaku
WriterSharedTimestamp Ya Tidak berlaku Tidak berlaku Ya

Kunci WriterSharedTimestamp hanya digunakan saat menyisipkan baris baru dengan stempel waktu sebagai bagian dari kunci utamanya. Kunci WriterShared dan Exclusive digunakan saat menulis ke sel yang ada atau menyisipkan baris baru tanpa stempel waktu. Akibatnya, WriterSharedTimestamp tidak dapat bertentangan dengan jenis kunci lainnya, dan skenario tersebut ditampilkan sebagai Tidak berlaku dalam tabel sebelumnya.

Satu-satunya pengecualian adalah ReaderShared, yang dapat diterapkan ke baris yang tidak ada sehingga berpotensi bertentangan dengan WriterSharedTimestamp. Misalnya, pemindaian tabel penuh mengunci seluruh tabel bahkan untuk baris yang belum dibuat, sehingga ReaderShared dapat bertentangan dengan WriterSharedTimestamp.

Apa yang dimaksud dengan kunci awal rentang baris?

Kolom ROW_RANGE_START_KEY mengidentifikasi kunci utama gabungan, atau kunci utama awal rentang baris, yang memiliki konflik kunci. Skema berikut digunakan untuk mengilustrasikan contoh.

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);

Seperti yang ditunjukkan oleh tabel row key dan rentang row key berikut, rentang diwakili dengan tanda plus, '+', di kunci. Kunci dalam kasus tersebut mewakili kunci awal dari rentang kunci tempat terjadinya konflik kunci.

ROW_RANGE_START_KEY Penjelasan
singers(2) Tabel Singers di kunci SingerId=2
albums(2,1) Tabel Albums pada kunci SingerId=2,AlbumId=1
songs(2,1,5) Tabel Songs pada kunci SingerId=2,AlbumId=1,TrackId=5
songs(2,1,5+) Rentang kunci tabel Songs yang dimulai dari SingerId=2,AlbumId=1,TrackId=5
albums(2,1+) Rentang kunci tabel Albums yang dimulai dari SingerId=2,AlbumId=1
users(3, 2020-11-01 12:34:56.426426+00:00) Tabel Users pada kunci UserId=3, LastAccess=commit_timestamp

Statistik gabungan

SPANNER_SYS juga berisi tabel untuk menyimpan data gabungan untuk statistik kunci yang diambil oleh Spanner dalam jangka waktu tertentu:

  • SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Statistik gabungan untuk semua tunggu kunci selama interval 1 menit.

  • SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Statistik gabungan untuk semua tunggu kunci selama interval 10 menit.

  • SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Statistik gabungan untuk semua tunggu kunci selama interval 1 jam.

Tabel statistik gabungan memiliki properti berikut:

  • Setiap tabel berisi data untuk interval waktu yang tidak tumpang-tindih dengan durasi yang ditentukan oleh nama tabel.

  • Interval didasarkan pada waktu jam. Interval 1 menit berakhir pada menit, interval 10 menit berakhir setiap 10 menit mulai dari jam, dan interval 1 jam berakhir pada jam.

    Misalnya, pada pukul 11.59.30, interval terbaru yang tersedia untuk kueri SQL pada statistik kunci gabungan adalah:

    • 1 menit: 11.58.00–11.58.59
    • 10 menit: 11.40.00–11.49.59
    • 1 jam: 10.00.00–10.59.59
  • Setiap baris berisi statistik untuk semua tunggu kunci pada database selama interval yang ditentukan, yang digabungkan bersama. Hanya ada satu baris per interval waktu.

  • Statistik yang dicatat dalam tabel SPANNER_SYS.LOCK_STATS_TOTAL_* mencakup tunggu kunci yang tidak dicatat Spanner dalam tabel SPANNER_SYS.LOCK_STATS_TOP_*.

  • Beberapa kolom dalam tabel ini ditampilkan sebagai metrik di Cloud Monitoring. Metrik yang ditampilkan adalah:

    • Waktu tunggu penguncian

    Untuk mengetahui informasi selengkapnya, lihat Metrik Spanner.

Skema tabel

Nama kolom Jenis Deskripsi
INTERVAL_END TIMESTAMP Akhir interval waktu terjadinya konflik kunci.
TOTAL_LOCK_WAIT_SECONDS FLOAT64 Total waktu tunggu penguncian untuk konflik penguncian yang dicatat untuk seluruh database, dalam detik.
LOCK_WAIT_DISTRIBUTION ARRAY<STRUCT> Histogram waktu tunggu kunci individual untuk konflik kunci yang dicatat untuk seluruh database, dalam mikrodetik, bukan detik. Strukturnya sama dengan kolom LOCK_WAIT_DISTRIBUTION di tabel SPANNER_SYS.LOCK_STATS_TOP_*. Database dialek PostgreSQL tidak mendukung kolom ini. Gunakan kolom LOCK_WAIT_DISTRIBUTION_JSON_STRING sebagai gantinya.
LOCK_WAIT_DISTRIBUTION_JSON_STRING STRING Representasi string yang kompatibel dengan JSON dari kolom LOCK_WAIT_DISTRIBUTION. Nilainya dalam mikrodetik. Kolom ini didukung di database dialek GoogleSQL dan dialek PostgreSQL.

Contoh kueri

Berikut adalah contoh pernyataan SQL yang dapat Anda gunakan untuk mengambil statistik kunci. Anda dapat menjalankan pernyataan SQL ini menggunakan library klien, gcloud spanner, atau konsolGoogle Cloud .

Mencantumkan statistik kunci untuk interval 1 menit sebelumnya

Kueri berikut menampilkan informasi tunggu kunci untuk setiap row key dengan konflik kunci, termasuk pecahan total konflik kunci, selama interval waktu 1 menit terbaru.

Fungsi CAST() mengonversi kolom BYTES row_range_start_key menjadi 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;
Output kueri
row_range_start_key total_lock_wait_seconds lock_wait_seconds frac_of_total sample_lock_requests
Songs(2,1,1) 2,37 1,76 0,7426 LOCK_MODE: ReaderShared

COLUMN: Singers.SingerInfo

LOCK_MODE: WriterShared

COLUMN: Singers.SingerInfo
Users(3, 2020-11-01 12:34:56.426426+00:00) 2,37 0,61 0,2573 LOCK_MODE: ReaderShared

COLUMN: users._exists1

LOCK_MODE: WriterShared

COLUMN: users._exists1

1 _exists adalah kolom internal yang digunakan untuk memeriksa apakah baris tertentu ada atau tidak.

Retensi data

Setidaknya, Spanner menyimpan data untuk setiap tabel selama jangka waktu berikut:

  • SPANNER_SYS.LOCK_STATS_TOP_MINUTE dan SPANNER_SYS.LOCK_STATS_TOTAL_MINUTE: Interval yang mencakup 6 jam sebelumnya.

  • SPANNER_SYS.LOCK_STATS_TOP_10MINUTE dan SPANNER_SYS.LOCK_STATS_TOTAL_10MINUTE: Interval yang mencakup 4 hari sebelumnya.

  • SPANNER_SYS.LOCK_STATS_TOP_HOUR dan SPANNER_SYS.LOCK_STATS_TOTAL_HOUR: Interval yang mencakup 30 hari sebelumnya.

Memecahkan masalah konflik kunci di database Anda menggunakan statistik kunci

Anda dapat menggunakan SQL atau dasbor Lock insights untuk melihat konflik kunci di database Anda.

Topik berikut menunjukkan cara menyelidiki konflik kunci tersebut menggunakan kode SQL.

Pilih jangka waktu yang akan diselidiki

Anda memeriksa Metrik latensi untuk database Spanner dan menemukan jangka waktu saat aplikasi Anda mengalami latensi dan penggunaan CPU yang tinggi. Misalnya, masalah mulai terjadi sekitar pukul 22.50 pada 12 November 2020.

Menentukan apakah latensi commit transaksi meningkat seiring dengan waktu tunggu penguncian selama periode yang dipilih

Kunci diperoleh oleh transaksi, jadi jika konflik kunci menyebabkan waktu tunggu yang lama, kita akan dapat melihat peningkatan latensi commit transaksi seiring dengan peningkatan waktu tunggu kunci.

Setelah memilih jangka waktu untuk memulai investigasi, kita akan menggabungkan statistik transaksi TXN_STATS_TOTAL_10MINUTE dengan statistik kunci LOCK_STATS_TOTAL_10MINUTE sekitar waktu tersebut untuk membantu kita memahami apakah peningkatan latensi commit rata-rata disebabkan oleh peningkatan waktu tunggu kunci.

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;

Ambil data berikut sebagai contoh hasil yang kita dapatkan kembali dari kueri kita.

interval_end avg_commit_latency_seconds total_lock_wait_seconds
12-11-2020 21:40:00-07:00 0,002 0,090
12-11-2020 21:50:00-07:00 0,003 0,110
12-11-2020 22:00:00-07:00 0,002 0,100
12-11-2020 22:10:00-07:00 0,002 0,080
12-11-2020 22:20:00-07:00 0,030 0,240
2020-11-12 22:30:00-07:00 0,034 0,220
12-11-2020 22:40:00-07:00 0,034 0,218
2020-11-12 22:50:00-07:00 3.741 780.193
12-11-2020 23:00:00-07:00 0,042 0,240
12-11-2020 23:10:00-07:00 0,038 0,129
12-11-2020 23:20:00-07:00 0,021 0,128
2020-11-12 23:30:00-07:00 0,038 0,231

Hasil sebelumnya menunjukkan peningkatan dramatis dalam avg_commit_latency_seconds dan total_lock_wait_seconds selama jangka waktu yang sama dari 2020-11-12 22:40:00 hingga 2020-11-12 22:50:00, dan menurun setelahnya. Satu hal yang perlu diperhatikan adalah bahwa avg_commit_latency_seconds adalah waktu rata-rata yang dihabiskan hanya untuk langkah commit. Di sisi lain, total_lock_wait_seconds adalah waktu penguncian gabungan untuk periode tersebut, sehingga waktunya terlihat jauh lebih lama daripada waktu commit transaksi.

Setelah mengonfirmasi bahwa waktu tunggu penguncian terkait erat dengan peningkatan latensi tulis, pada langkah berikutnya, kita akan menyelidiki baris dan kolom mana yang menyebabkan waktu tunggu yang lama.

Temukan row key dan kolom yang memiliki waktu tunggu penguncian yang lama selama periode yang dipilih

Untuk mengetahui row key dan kolom mana yang mengalami waktu tunggu penguncian yang tinggi selama periode yang kita selidiki, kita mengkueri tabel LOCK_STAT_TOP_10MINUTE, yang mencantumkan row key dan kolom yang paling berkontribusi terhadap waktu tunggu penguncian.

Fungsi CAST() dalam kueri berikut mengonversi kolom BYTES row_range_start_key menjadi 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;
row_range_start_key total_lock_wait_seconds lock_wait_seconds frac_of_total sample_lock_requests
Penyanyi(32) 780.193 780.193 1 LOCK_MODE: WriterShared

COLUMN: Singers.SingerInfo

LOCK_MODE: ReaderShared

COLUMN: Singers.SingerInfo

Dari tabel hasil ini, kita dapat melihat konflik terjadi pada tabel Singers pada kunci SingerId=32. Singers.SingerInfo adalah kolom tempat konflik kunci terjadi antara ReaderShared dan WriterShared.

Ini adalah jenis konflik yang umum terjadi saat ada satu transaksi yang mencoba membaca sel tertentu dan transaksi lainnya mencoba menulis ke sel yang sama. Sekarang kita mengetahui sel data yang tepat yang diperebutkan oleh transaksi untuk mendapatkan kunci, jadi pada langkah berikutnya kita akan mengidentifikasi transaksi yang memperebutkan kunci.

Menemukan transaksi yang mengakses kolom yang terlibat dalam konflik kunci

Untuk mengidentifikasi transaksi yang mengalami latensi commit yang signifikan dalam interval waktu tertentu karena konflik kunci, Anda perlu membuat kueri untuk kolom berikut dari tabel SPANNER_SYS.TXN_STATS_TOTAL_10MINUTE:

  • fprint
  • read_columns
  • write_constructive_columns
  • avg_commit_latency_seconds

Anda perlu memfilter kolom terkunci yang diidentifikasi dari tabel SPANNER_SYS.LOCK_STATS_TOP_10MINUTE:

  • Transaksi yang membaca kolom apa pun yang mengalami konflik kunci saat mencoba memperoleh kunci ReaderShared.

  • Transaksi yang menulis ke kolom mana pun yang mengalami konflik kunci saat mencoba memperoleh kunci WriterShared.

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;

Hasil kueri diurutkan berdasarkan kolom avg_commit_latency_seconds sehingga Anda melihat transaksi yang mengalami latensi commit tertinggi terlebih dahulu.

fprint read_columns write_constructive_columns avg_commit_latency_seconds
1866043996151916800


['Singers.SingerInfo',
'Singers.FirstName',
'Singers.LastName',
'Singers._exists']
['Singers.SingerInfo'] 4,89
4168578515815911936 [] ['Singers.SingerInfo'] 3,65

Hasil kueri menunjukkan bahwa dua transaksi mencoba mengakses kolom Singers.SingerInfo, yang merupakan kolom yang mengalami konflik kunci selama jangka waktu tersebut. Setelah mengidentifikasi transaksi yang menyebabkan konflik kunci, Anda dapat menganalisis transaksi menggunakan sidik jarinya, fprint, untuk mengidentifikasi potensi masalah yang berkontribusi pada konflik kunci.

Setelah meninjau transaksi dengan fprint=1866043996151916800, Anda dapat menggunakan kolom read_columns dan write_constructive_columns untuk mengidentifikasi bagian mana dari kode aplikasi Anda yang memicu transaksi. Kemudian, Anda dapat melihat DML pokok yang tidak memfilter kunci utama, SingerId. Hal ini menyebabkan pemindaian tabel penuh dan mengunci tabel hingga transaksi di-commit.

Untuk menyelesaikan konflik kunci, Anda dapat melakukan hal berikut:

  1. Gunakan transaksi hanya baca untuk mengidentifikasi nilai SingerId yang diperlukan.
  2. Gunakan transaksi baca-tulis terpisah untuk memperbarui baris untuk nilai SingerId yang diperlukan.

Menerapkan praktik terbaik untuk mengurangi pertentangan kunci

Dalam contoh skenario, kami menggunakan statistik kunci dan statistik transaksi untuk mempersempit masalah ke transaksi yang tidak menggunakan kunci utama tabel kami saat melakukan update. Kami mengemukakan ide untuk meningkatkan transaksi berdasarkan apakah kami mengetahui kunci baris yang ingin kami perbarui sebelumnya atau tidak.

Saat melihat potensi masalah dalam solusi Anda, atau bahkan saat mendesain solusi, pertimbangkan praktik terbaik berikut untuk mengurangi jumlah konflik kunci dalam database Anda.

  • Hindari pembacaan dalam jumlah besar di dalam transaksi baca-tulis.

  • Gunakan transaksi hanya baca jika memungkinkan, karena transaksi tersebut tidak memperoleh kunci apa pun.

  • Hindari pemindaian tabel penuh dalam transaksi baca-tulis. Hal ini termasuk menulis DML bersyarat pada kunci utama atau menetapkan rentang kunci tertentu saat menggunakan Read API.

  • Persingkat periode penguncian dengan meng-commit perubahan sesegera mungkin setelah Anda membaca data dalam transaksi baca-tulis. Transaksi baca-tulis menjamin bahwa data tidak berubah setelah Anda membaca data hingga Anda berhasil meng-commit perubahan. Untuk mencapai hal ini, transaksi memerlukan penguncian sel data selama pembacaan dan selama commit. Oleh karena itu, jika Anda dapat menjaga periode penguncian tetap singkat, transaksi akan cenderung tidak mengalami konflik penguncian.

  • Pilih transaksi kecil daripada transaksi besar, atau pertimbangkan DML yang Dipartisi untuk transaksi DML yang berjalan lama. Transaksi yang berjalan lama memperoleh kunci untuk waktu yang lama, jadi pertimbangkan untuk memecah transaksi yang menyentuh ribuan baris menjadi beberapa transaksi yang lebih kecil yang memperbarui ratusan baris jika memungkinkan.

  • Jika Anda tidak memerlukan jaminan yang diberikan oleh transaksi baca-tulis, hindari membaca data apa pun dalam transaksi baca-tulis sebelum meng-commit perubahan, misalnya, dengan membaca data dalam transaksi hanya baca yang terpisah. Sebagian besar konflik kunci terjadi karena jaminan yang kuat, untuk memastikan data tetap tidak berubah antara pembacaan dan commit. Jadi, jika transaksi baca-tulis tidak membaca data apa pun, transaksi tersebut tidak perlu mengunci sel dalam waktu lama.

  • Tentukan hanya kumpulan kolom minimal yang diperlukan dalam transaksi baca-tulis. Karena kunci Spanner berlaku per sel data, saat transaksi baca-tulis membaca kolom yang berlebihan, transaksi tersebut akan memperoleh kunci ReaderShared pada sel ini. Hal ini dapat menyebabkan konflik kunci saat transaksi lain memperoleh kunci WriterShared pada penulisan ke kolom yang berlebihan. Misalnya, pertimbangkan untuk menentukan kumpulan kolom, bukan * pada pembacaan.

  • Meminimalkan panggilan API dalam transaksi baca-tulis. Latensi panggilan API dapat menyebabkan pertentangan kunci di Spanner, karena panggilan API tunduk pada penundaan jaringan serta penundaan sisi layanan. Sebaiknya lakukan panggilan API di luar transaksi baca-tulis jika memungkinkan. Jika Anda harus menjalankan panggilan API di dalam transaksi baca-tulis, pastikan untuk memantau latensi panggilan API guna meminimalkan dampak pada periode akuisisi kunci.

  • Ikuti praktik terbaik desain skema.

  • Jika kemungkinan write-skew tidak menjadi masalah bagi aplikasi Anda, pertimbangkan untuk menggunakan isolasi repeatable read dengan konkurensi optimis defaultnya untuk menghindari pertentangan baca-tulis. Untuk mengetahui informasi selengkapnya, lihat Isolasi pembacaan berulang.

  • Untuk workload yang memiliki pertentangan baca-tulis yang rendah, pertimbangkan untuk menggunakan isolasi serializable dengan kontrol konkurensi optimis untuk menghindari pemerolehan kunci dalam transaksi hingga waktu commit, sekaligus menerapkan bahwa semua pembacaan transaksi valid pada waktu commit. Untuk mengetahui informasi selengkapnya tentang kesesuaian opsi ini, lihat Kontrol konkurensi optimis.

Langkah berikutnya