Untuk mencegah deadlock saat update PostgreSQL yang berjalan bersamaan saling tumpang tindih, kunci seluruh kumpulan target setiap transaksi dengan urutan kunci yang tidak berubah dan sama sebelum melakukan update. Pertahankan transaksi pada satu klien node-postgres dan ulangi kegagalan yang memenuhi syarat dari awal.

Panduan ini ditujukan bagi pengembang yang bekerja di Terminal macOS dan perlu memastikan dua batch yang berjalan bersamaan memperbarui baris artikel sintetis secara lengkap. Contoh ini menggunakan dokumentasi PostgreSQL terkini, yang mencantumkan PostgreSQL 18, serta dokumentasi node-postgres terkini. Cakupannya adalah daftar ID artikel yang sudah ada dan diketahui, dengan ID yang tetap tidak berubah selama pekerjaan bersamaan. PostgreSQL: Penguncian Eksplisit

Mengapa update yang tumpang tindih dapat menyebabkan deadlock

Pertimbangkan dua transaksi: A memperbarui artikel 1, lalu artikel 2; B memperbarui artikel 2, lalu artikel 1. Jika masing-masing memperoleh kunci baris pertamanya sebelum meminta kunci kedua, keduanya akan menunggu kunci yang dipegang transaksi lain. PostgreSQL mendeteksi siklus tersebut dan membatalkan salah satu transaksi; aplikasi tidak dapat menentukan transaksi mana yang dibatalkan. Update biasa dapat menimbulkan situasi ini tanpa pernyataan penguncian eksplisit. PostgreSQL: Penguncian Eksplisit

Perbedaan pentingnya adalah antara tumpang tindih dan siklus. Tumpang tindih mungkin mengharuskan transaksi menunggu. Siklus mencegah kedua transaksi melanjutkan. PostgreSQL menyarankan agar aplikasi menggunakan urutan perolehan kunci yang konsisten, dengan mode kunci terkuat yang diperlukan diperoleh terlebih dahulu. PostgreSQL: Penguncian Eksplisit

Untuk beban kerja dalam artikel ini, terapkan satu aturan: peroleh kunci untuk setiap baris artikel yang diminta dalam urutan id menaik sebelum mengubah artikel apa pun. Terapkan aturan tersebut ke seluruh transaksi, termasuk pernyataan sebelumnya yang menyentuh artikel.

Kunci seluruh kumpulan target sebelum melakukan update

Mengurutkan array JavaScript tidak menginstruksikan update SQL massal untuk mengunjungi baris dalam urutan tersebut. Sintaks UPDATE PostgreSQL tidak memiliki klausa ORDER BY langsung. Dokumentasinya menunjukkan penguncian terurut melalui kueri pemilihan dan menjelaskan bahwa urutan bersama dapat mencegah deadlock antaroperasi update. PostgreSQL: UPDATE

Gunakan pernyataan penguncian terpisah yang ditunggu hingga selesai di dalam transaksi. Transaksi SQL ilustratif ini menargetkan artikel 1, 2, dan 3; tabel untuk penyiapan ditampilkan di bagian berikutnya.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id
FROM deadlock_demo_articles
WHERE id = ANY(ARRAY[3, 1, 2]::integer[])
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo_articles
SET revision = revision + 1
WHERE id = ANY(ARRAY[3, 1, 2]::integer[]);

COMMIT;

Pernyataan pertama menetapkan urutan perolehan kunci. Update kemudian berjalan pada baris yang sudah dikunci oleh transaksi tersebut. Kunci baris biasanya tetap berlaku sampai transaksi berakhir. Jaga agar interval ini singkat: siapkan input sebelum memulai, dan hindari menunggu masukan dari desktop saat kunci sedang dipegang. PostgreSQL: Penguncian Eksplisit

Kondisi berikut menetapkan kontrak pengurutan untuk contoh ini:

  • Setiap batch yang bersaing menggunakan urutan artikel id menaik.
  • ID tetap tidak berubah selama pekerjaan bersamaan.
  • Setiap batch menentukan seluruh kumpulan targetnya sebelum melakukan penguncian.
  • Tidak ada pernyataan sebelumnya yang sudah mengunci baris artikel dalam urutan lain.
  • Tabel contoh tidak memiliki trigger atau operasi pada tabel terkait yang menimbulkan dependensi kunci tambahan.

Ini adalah prasyarat untuk menerapkan penalaran tersebut pada beban kerja ini. Transaksi yang mengunci artikel 9 terlebih dahulu lalu meminta artikel 1 sampai 3 sudah melanggar kontrak, sekalipun kueri terakhirnya memuat ORDER BY id.

Jalankan contoh node-postgres lengkap di Mac Anda

Prasyaratnya adalah server PostgreSQL lokal yang berjalan, database sementara yang sudah ada, Node.js dengan npm, dan psql yang tersedia di Terminal. Konfigurasikan koneksi database yang sudah ada melalui variabel lingkungan PostgreSQL. Pool node-postgres dan psql menggunakan variabel koneksi yang sama; tetapkan PGHOST secara eksplisit bila perlu agar kedua perintah terhubung ke server yang sama. node-postgres: Menghubungkan

Di folder kerja baru, instal dependensinya:

npm init -y
npm install pg

Simpan penyiapan lengkap berikut sebagai setup.sql. Penyiapan ini membuat tabel demonstrasi baru dan tiga artikel sintetis. Jalankan sekali di database sementara. Penyiapan ini sengaja gagal jika tabel tersebut sudah ada, alih-alih mengganti isinya.

BEGIN;

CREATE TABLE deadlock_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    revision integer NOT NULL DEFAULT 0
);

INSERT INTO deadlock_demo_articles (id, title)
VALUES
    (1, 'Paper Shapes'),
    (2, 'Color Notes'),
    (3, 'Desk Sketches');

COMMIT;

psql -v ON_ERROR_STOP=1 -f setup.sql

Simpan program lengkap berikutnya sebagai update-articles.mjs. Semua pernyataan yang termasuk dalam suatu percobaan menggunakan klien yang dikembalikan oleh pool.connect(). node-postgres secara eksplisit mensyaratkan penggunaan satu klien selama transaksi; menjalankan pernyataan transaksi melalui pool.query() tidak memenuhi persyaratan itu. node-postgres: Transaksi

import pg from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({ max: 2, connectionTimeoutMillis: 5000 });
pool.on('error', error => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

function normalizeIds(input) {
  if (!Array.isArray(input) || input.length === 0 ||
      input.some(id => !Number.isInteger(id) ||
        id < 1 || id > 2147483647)) {
    throw new Error('Use a nonempty array of positive PostgreSQL integer IDs');
  }
  return [...new Set(input)].sort((a, b) => a - b);
}

async function incrementRevisions(input) {
  const ids = normalizeIds(input);
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

      const locked = await client.query(
        `SELECT id
         FROM deadlock_demo_articles
         WHERE id = ANY($1::integer[])
         ORDER BY id
         FOR UPDATE`,
        [ids]
      );

      if (locked.rows.length !== ids.length) {
        throw new Error('A requested article is missing; batch cancelled');
      }

      const updated = await client.query(
        `UPDATE deadlock_demo_articles
         SET revision = revision + 1
         WHERE id = ANY($1::integer[])
         RETURNING id, revision`,
        [ids]
      );

      if (updated.rowCount !== ids.length) {
        throw new Error('Unexpected update count; batch cancelled');
      }

      await client.query('COMMIT');
      return updated.rows.sort((a, b) => a.id - b.id);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new Error('Rollback failed; connection discarded', {
          cause: rollbackError
        });
      }

      const retryable = error.code === '40P01' || error.code === '40001';
      if (!retryable || attempt === maxAttempts) throw error;
    } finally {
      client.release(discard);
    }

    // Illustrative bounded backoff, after releasing the client.
    await sleep(50 * 2 ** (attempt - 1) + Math.floor(Math.random() * 50));
  }
}

try {
  const outcomes = await Promise.allSettled([
    incrementRevisions([3, 1, 2]),
    incrementRevisions([3, 2])
  ]);

  for (const outcome of outcomes) {
    if (outcome.status === 'rejected') throw outcome.reason;
  }

  const result = await pool.query(
    'SELECT id, title, revision FROM deadlock_demo_articles ORDER BY id'
  );
  console.table(result.rows);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node update-articles.mjs

Array diteruskan sebagai satu parameter dan secara eksplisit dikonversi ke integer[]; node-postgres mendokumentasikan konversi array JavaScript menjadi nilai array PostgreSQL. Deduplicasi membuat jumlah yang diminta berarti jumlah artikel yang berbeda. ORDER BY dari database, bukan urutan array input, menetapkan urutan perolehan kunci. node-postgres: Kueri

Pada tabel yang baru diinisialisasi, tanpa penulis lain, dan dengan kedua batch berhasil, revisi yang diharapkan adalah 1 untuk artikel 1 dan 2 untuk artikel 2 dan 3. Nilai total ini mengikuti keanggotaan batch, terlepas dari batch mana yang selesai lebih dahulu. Eksekusi skrip berikutnya akan menaikkan nilai yang sudah ada lagi. Jika ID yang diminta tidak ditemukan, batch tersebut dibatalkan alih-alih diam-diam hanya memperbarui baris yang ditemukan.

Setiap batch merupakan transaksi terpisah. Jika salah satu berhasil dan yang lain gagal permanen, batch yang berhasil tetap tersimpan. Promise.allSettled() memungkinkan kedua percobaan selesai sebelum program menutup pool. pool.query() terakhir berjalan di luar kedua transaksi. API pool mendokumentasikan cara melepas klien yang sudah dipinjam, menghancurkan klien bila perlu, dan mengakhiri pool setelah pekerjaan skrip selesai. node-postgres: Pool

Pemeriksaan normalisasi dan perhitungan revisi ilustratif dijalankan menggunakan Node.js. PostgreSQL tidak tersedia di lingkungan riset, jadi SQL dan perilaku konkurensi database diperiksa berdasarkan dokumentasi, bukan dieksekusi.

Pertahankan tingkat isolasi yang dibutuhkan tugas Anda

Program ini secara eksplisit menggunakan READ COMMITTED untuk update dengan ID tetap. Pada tingkat ini, pernyataan penguncian dapat menunggu proses update lalu mengunci versi baris yang telah diperbarui, selama baris itu masih memenuhi kondisi. Setiap pernyataan mengambil snapshot baru. Karena itu, baris yang dihapus sebelum kunci diperoleh mungkin tidak ditemukan; program menangani hal tersebut melalui pemeriksaan jumlah baris. PostgreSQL: Isolasi Transaksi

Jangan menurunkan tingkat isolasi transaksi yang sudah ada hanya agar dapat menggunakan contoh ini. Dalam REPEATABLE READ, mencoba mengunci atau memperbarui baris yang berubah sejak snapshot transaksi dapat menyebabkan kegagalan serialisasi. SERIALIZABLE juga memerlukan penanganan percobaan ulang dan mencegah anomali serialisasi yang sudah dikomit; tingkat ini tidak menggantikan pengurutan konsisten untuk kunci baris biasa. PostgreSQL: Isolasi Transaksi

Mengunci baris yang diketahui tidak memastikan keanggotaan suatu predikat tetap sama. Misalnya, contoh ini tidak menerapkan “perbarui setiap artikel yang judulnya cocok dengan kondisi, termasuk penambahan yang terjadi secara bersamaan”. Itu adalah tugas konsistensi yang berbeda. Tentukan persyaratan isolasi dan koordinasinya sebelum menyesuaikan resep untuk ID tetap ini.

Pastikan juga kolom pengurutan tidak berubah. PostgreSQL mendokumentasikan bahwa pada READ COMMITTED, pengurutan terjadi sebelum penguncian; nilai yang berubah saat pernyataan menunggu dapat membuat baris yang dikembalikan tampak tidak berurutan. Menggunakan ID yang tidak berubah menghindari komplikasi tersebut. PostgreSQL: SELECT

Ulangi seluruh transaksi dan selidiki kegagalan berulang

PostgreSQL mengidentifikasi kegagalan deadlock dengan SQLSTATE 40P01 dan kegagalan serialisasi dengan 40001. Ulangi seluruh transaksi, termasuk keputusan yang bergantung pada database dan menentukan pernyataan atau nilainya. Mengulangi hanya update terakhir tidak memenuhi persyaratan tersebut. PostgreSQL: Penanganan Kegagalan Serialisasi

Contoh ini mengizinkan tiga percobaan secara keseluruhan. Penundaan eksponensial singkat dan jitter yang digunakan merupakan pilihan ilustratif pada tingkat aplikasi, bukan nilai default PostgreSQL. Klien dilepas sebelum jeda, proses langsung berhenti jika terjadi kesalahan lain, dan kegagalan setelah seluruh percobaan dilaporkan. PostgreSQL mengingatkan bahwa percobaan ulang tidak menjamin penyelesaian pada akhirnya. PostgreSQL: Penanganan Kegagalan Serialisasi

Gunakan panduan keputusan ini saat menyesuaikan implementasi:

Situasi Tindakan
Batch lain sudah memegang salah satu baris yang diminta Biarkan perolehan kunci terurut menunggu jika tugas mengharuskan semua baris yang diminta.
ID yang diminta tidak ditemukan Batalkan transaksi dan laporkan kondisi target yang hilang.
SQLSTATE adalah 40P01 atau 40001 Batalkan transaksi, lalu ulangi seluruh transaksi yang memenuhi syarat dalam batas percobaan tertentu.
Kegagalan berlanjut setelah penguncian terurut Periksa pernyataan sebelumnya, penulis lain, trigger, dan tabel tambahan untuk menemukan pelanggaran kontrak pengurutan.
Operasi menyentuh beberapa tabel Tetapkan urutan tabel bersama selain urutan baris untuk semua jalur yang bersaing.
Batch harus dikomit secara atomik Pertahankan satu transaksi; membagi dan mengomit bagian secara terpisah akan mengubah tugas.

Hindari menambahkan SKIP LOCKED ke batch yang menargetkan semua baris ini: opsi tersebut mengabaikan baris yang tidak dapat langsung dikunci. PostgreSQL menjelaskan bahwa opsi ini berguna bagi pemroses bergaya antrean dan tidak cocok untuk tampilan konsisten umum. NOWAIT melaporkan kesalahan alih-alih menunggu kunci baris yang berbenturan; opsi ini mengubah perilaku menunggu, tetapi tidak menyelesaikan batch. PostgreSQL: SELECT

Implementasi ini siap disesuaikan ketika Anda dapat menyebutkan seluruh kumpulan target, menjaga agar kunci pengurutannya tidak berubah, dan menerapkan aturan perolehan kunci yang sama di seluruh transaksi yang bersaing. Pertahankan pemeriksaan baris yang hilang dan percobaan ulang terbatas untuk seluruh transaksi agar kontensi atau input yang tidak lengkap menghasilkan keluaran yang jelas.

Sumber