Keunikan mekanisme internal PostgreSQL memungkinkannya menjadi sangat cepat dalam beberapa situasi dan “tidak terlalu cepat” dalam situasi lain. Hari ini kita akan fokus pada contoh klasik konflik antara cara kerja DBMS dan apa yang dilakukan pengembang dengannya - Prinsip UPDATE vs MVCC.
Cerita singkat dari :
Ketika sebuah baris diubah dengan perintah UPDATE, dua operasi sebenarnya dilakukan: DELETE dan INSERT. DI DALAM versi string saat ini xmax diset sama dengan jumlah transaksi yang melakukan UPDATE. Kemudian itu dibuat versi baru baris yang sama; nilai xminnya sama dengan nilai xmax versi sebelumnya.
Beberapa saat setelah transaksi ini selesai, versi lama atau baru, tergantung COMMIT/ROOLBACK, akan dikenali "mati" (tupel mati) ketika lewat VACUUM sesuai tabel dan dibersihkan.
Tapi ini tidak akan terjadi segera, tapi masalah dengan "orang mati" bisa didapat dengan sangat cepat - dengan berulang atau di meja besar, dan sebentar lagi Anda akan menghadapi situasi yang sama .
#1: Saya Suka Memindahkannya
Katakanlah metode Anda bekerja berdasarkan logika bisnis, dan tiba-tiba metode tersebut menyadari bahwa bidang X perlu diperbarui di beberapa catatan:
UPDATE tbl SET X = <newX> WHERE pk = $1;Kemudian, seiring berjalannya eksekusi, ternyata bidang Y juga harus diperbarui:
UPDATE tbl SET Y = <newY> WHERE pk = $1;...dan kemudian juga Z - mengapa membuang waktu untuk hal-hal sepele?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Berapa banyak versi catatan ini yang sekarang kita miliki di database? Ya, 4 buah! Dari jumlah tersebut, satu relevan, dan 3 harus dibersihkan setelah Anda dengan [auto]VACUUM.
Jangan lakukan dengan cara ini! Menggunakan memperbarui semua bidang dalam satu permintaan — hampir selalu logika metode dapat diubah seperti ini:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Penggunaannya BERBEDA, Luke!
Jadi, kamu masih menginginkannya (misalnya saat menggunakan skrip atau konverter). Dan sesuatu seperti ini muncul di skrip:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Permintaan dalam bentuk seperti ini cukup sering terjadi dan hampir selalu bukan untuk mengisi kolom baru yang kosong, tetapi untuk memperbaiki beberapa kesalahan pada data. Pada saat yang sama, dia sendiri kebenaran data yang ada tidak diperhitungkan sama sekali - tapi sia-sia! Artinya, catatan tersebut ditulis ulang, meskipun berisi persis apa yang diinginkan - tetapi mengapa? Mari kita perbaiki:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Banyak orang tidak menyadari keberadaan operator hebat ini, jadi inilah lembar contekannya IS DISTINCT FROM dan operator logis lainnya untuk membantu:
...dan sedikit tentang operasi yang kompleks ROW()-ekspresi:
#3: Aku mengenali kekasihku dengan... memblokir
sedang diluncurkan dua proses paralel yang identik, yang masing-masing mencoba menandai entri yang sedang “sedang berlangsung”:
UPDATE tbl SET processing = TRUE WHERE pk = $1;Bahkan jika proses-proses ini benar-benar melakukan hal-hal yang independen satu sama lain, tetapi dalam ID yang sama, klien kedua akan “dikunci” pada permintaan ini hingga transaksi pertama selesai.
Solusi #1: tugas dikurangi ke tugas sebelumnya
Mari kita tambahkan lagi IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Dalam bentuk ini, permintaan kedua tidak akan mengubah apa pun di database, semuanya sudah sebagaimana mestinya - oleh karena itu, pemblokiran tidak akan terjadi. Selanjutnya, kami memproses fakta “tidak menemukan” catatan dalam algoritma yang diterapkan.
Solusi #2: kunci penasehat
Topik besar untuk artikel terpisah, yang dapat Anda baca .
Solusi #3: panggilan bodoh
Namun inilah yang seharusnya terjadi pada Anda bekerja secara simultan dengan catatan yang sama? Atau apakah Anda mengacaukan algoritme untuk memanggil logika bisnis di sisi klien, misalnya? Dan jika Anda memikirkannya?..
Sumber: www.habr.com