Pertanyaan Pilih baris yang tidak terkunci di Postgresql


Apakah ada cara untuk memilih baris di Postgresql yang tidak dikunci? Saya memiliki aplikasi multi-berulir yang akan dilakukan:

Select... order by id desc limit 1 for update

di atas meja.

Jika beberapa utas menjalankan kueri ini, keduanya mencoba menarik kembali baris yang sama.

Satu mendapat kunci baris, blok lainnya dan kemudian gagal setelah yang pertama memperbarui baris. Yang saya inginkan adalah utas kedua untuk mendapatkan baris pertama yang cocok WHERE klausa dan belum dikunci.

Untuk memperjelas, saya ingin setiap utas untuk segera memperbarui baris pertama yang tersedia setelah melakukan pemilihan.

Jadi jika ada baris dengan ID: 1,2,3,4 , utas pertama akan masuk, pilih baris dengan ID=4 dan segera perbarui.

Jika selama transaksi itu, benang kedua datang, saya ingin itu terjadi ID=3 dan segera perbarui baris itu.

Untuk Berbagi tidak akan menyelesaikan ini atau dengan nowait sebagai WHERE klausa akan cocok dengan baris yang dikunci (ID=4 in my example). Pada dasarnya apa yang saya suka adalah sesuatu seperti "DAN TIDAK TERKUNCI" di WHERE ayat.

Users

-----------------------------------------
ID        | Name       |      flags
-----------------------------------------
1         |  bob       |        0
2         |  fred      |        1
3         |  tom       |        0
4         |  ed        |        0

Jika kueri itu "Select ID from users where flags = 0 order by ID desc limit 1"dan ketika baris dikembalikan, hal berikutnya adalah"Update Users set flags = 1 where ID = 0"Maka saya ingin utas pertama untuk mengambil baris dengan ID 4 dan yang berikutnya untuk mengambil baris dengan ID 3.

Jika saya menambahkan "For Updatemsgstr "" "untuk memilih, maka thread pertama mendapat baris, blok kedua dan kemudian mengembalikan apa - apa karena setelah transaksi pertama melakukan perintah WHERE klausul tidak lagi puas.

Jika saya tidak menggunakan "For Update"maka saya perlu menambahkan klausa WHERE pada pembaruan berikutnya (WHERE flags = 0) sehingga hanya satu utas yang dapat memperbarui baris.

Thread kedua akan memilih baris yang sama dengan yang pertama tetapi update thread kedua akan gagal.

Either way thread kedua gagal untuk mendapatkan baris dan memperbarui karena saya tidak bisa mendapatkan database untuk memberikan baris 4 ke thread pertama dan baris 3 ke thread kedua, transaksi tumpang tindih.


32
2017-12-23 17:34


asal


Jawaban:


Fitur ini, SELECT ... SKIP LOCKED sedang dilaksanakan di Postgres 9.5. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/


20
2017-10-10 23:19



No No NOOO :-)

Saya tahu apa yang dimaksudkan oleh si penulis. Saya memiliki situasi yang serupa dan saya menemukan solusi yang bagus. Pertama saya akan mulai dari menggambarkan situasi saya. Saya memiliki tabel yang saya simpan pesan yang harus dikirim pada waktu tertentu. PG tidak mendukung pengaturan waktu fungsi sehingga kita harus menggunakan daemon (atau cron). Saya menggunakan skrip tertulis khusus yang membuka beberapa proses paralel. Setiap proses memilih satu set pesan yang harus dikirim dengan ketepatan +1 detik / -1 detik. Tabel itu sendiri diperbarui secara dinamis dengan pesan-pesan baru.

Jadi setiap proses perlu mengunduh serangkaian baris. Set baris ini tidak dapat diunduh oleh proses lain karena akan membuat banyak kekacauan (beberapa orang akan menerima beberapa pesan ketika mereka hanya menerima satu). Itulah mengapa kita perlu mengunci baris. Kueri untuk mengunduh serangkaian pesan dengan kunci:

FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE FOR UPDATE LOOP
-- DO SMTH
END LOOP;

proses dengan kueri ini dimulai setiap 0,5 detik. Jadi ini akan menghasilkan permintaan berikutnya menunggu kunci pertama untuk membuka kunci baris. Pendekatan ini menciptakan penundaan yang sangat besar. Bahkan ketika kita menggunakan NOWAIT, kueri akan menghasilkan Exception yang tidak kita inginkan karena mungkin ada pesan baru dalam tabel yang harus dikirim. Jika menggunakan HANYA UNTUK BERBAGI, kueri akan berjalan dengan baik, tetapi masih dibutuhkan banyak waktu untuk membuat penundaan besar.

Untuk membuatnya bekerja, kami melakukan sedikit sihir:

  1. mengubah kueri:

    FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE AND is_locked(msg_id) IS FALSE FOR SHARE LOOP
    -- DO SMTH
    END LOOP;
    
  2. fungsi misterius 'is_locked (msg_id)' terlihat seperti ini:

    CREATE OR REPLACE FUNCTION is_locked(integer) RETURNS BOOLEAN AS $$
    DECLARE
        id integer;
        checkout_id integer;
        is_it boolean;
    BEGIN
        checkout_id := $1;
        is_it := FALSE;
    
        BEGIN
            -- we use FOR UPDATE to attempt a lock and NOWAIT to get the error immediately 
            id := msg_id FROM public.messages WHERE msg_id = checkout_id FOR UPDATE NOWAIT;
            EXCEPTION
                WHEN lock_not_available THEN
                    is_it := TRUE;
        END;
    
        RETURN is_it;
    
    END;
    $$ LANGUAGE 'plpgsql' VOLATILE COST 100;
    

Tentu saja kita dapat menyesuaikan fungsi ini untuk bekerja pada setiap tabel yang ada di database Anda. Menurut pendapat saya lebih baik untuk membuat satu fungsi cek untuk satu meja. Menambahkan lebih banyak hal ke fungsi ini dapat membuatnya lebih lambat. Saya membutuhkan waktu lebih lama untuk memeriksa klausul ini sehingga tidak perlu membuatnya lebih lambat. Bagi saya ini solusi lengkap dan bekerja dengan sempurna.

Sekarang ketika saya memiliki 50 proses yang berjalan secara paralel, setiap proses memiliki satu set pesan baru yang unik untuk dikirim. Setelah dikirim, saya hanya memperbarui baris dengan terkirim = TRUE dan tidak pernah kembali lagi.

Saya harap solusi ini juga akan berhasil untuk Anda (penulis). Jika Anda memiliki pertanyaan, beri tahu saya :-)

Oh, dan beri tahu aku jika ini berhasil untukmu juga.


7
2017-07-14 02:52



Saya menggunakan sesuatu seperti ini:

select  *
into l_sms
from sms
where prefix_id = l_prefix_id
    and invoice_id is null
    and pg_try_advisory_lock(sms_id)
order by suffix
limit 1;

dan jangan lupa untuk memanggil pg_advisory_unlock


6
2017-12-25 14:02



Jika Anda mencoba menerapkan antrean, lihatlah PGQ, yang telah menyelesaikan masalah ini dan lainnya. http://wiki.postgresql.org/wiki/PGQ_Tutorial


4
2017-12-25 18:28



Tampaknya Anda mencoba melakukan sesuatu seperti mengambil item prioritas tertinggi dalam antrean yang belum ditangani oleh proses lain.

Kemungkinan solusi adalah menambahkan klausa tempat yang membatasi ke permintaan yang tidak ditangani:

select * from queue where flag=0 order by id desc for update;
update queue set flag=1 where id=:id;
--if you really want the lock:
select * from queue where id=:id for update;
...

Semoga, transaksi kedua akan memblokir sementara pembaruan ke bendera terjadi, maka itu akan dapat dilanjutkan, tetapi bendera akan membatasi ke baris berikutnya.

Kemungkinan juga bahwa menggunakan tingkat isolasi yang bisa diseragamkan, Anda bisa mendapatkan hasil yang Anda inginkan tanpa semua kegilaan ini.

Tergantung pada sifat aplikasi Anda, mungkin ada cara yang lebih baik untuk menerapkan ini daripada di database, seperti pipa FIFO atau LIFO. Selain itu, dimungkinkan untuk membalik urutan yang Anda butuhkan, dan gunakan urutan untuk memastikan bahwa mereka diproses secara berurutan.


2
2018-02-03 16:17



Ini dapat diselesaikan dengan SELECT ... NOWAIT; sebuah contoh adalah sini.


1
2017-12-23 17:56



Sepertinya Anda mencari SELECT FOR SHARE.

http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

UNTUK SHARE berperilaku serupa, kecuali bahwa ia memperoleh kunci yang dibagikan daripada eksklusif pada setiap baris yang diambil. Kunci bersama memblokir transaksi lain dari melakukan UPDATE, DELETE, atau SELECT FOR UPDATE pada baris ini, tetapi itu tidak mencegah mereka melakukan SELECT FOR SHARE.

Jika tabel tertentu dinamai pada FOR UPDATE atau FOR SHARE, maka hanya baris yang berasal dari tabel tersebut yang terkunci; tabel lain yang digunakan dalam SELECT hanya dibaca seperti biasa. A UNTUK UPDATE atau UNTUK klausa SHARE tanpa daftar tabel mempengaruhi semua tabel yang digunakan dalam perintah. Jika FOR UPDATE atau FOR SHARE diterapkan ke tampilan atau sub-kueri, itu memengaruhi semua tabel yang digunakan dalam tampilan atau sub-kueri.

Beberapa UNTUK UPDATE dan UNTUK klausa SHARE dapat ditulis jika perlu untuk menentukan perilaku penguncian yang berbeda untuk tabel yang berbeda. Jika tabel yang sama disebutkan (atau secara implisit dipengaruhi) oleh klausa FOR UPDATE dan FOR SHARE, maka akan diproses sebagai FOR UPDATE. Demikian pula, tabel diproses sebagai NOWAIT jika yang ditentukan di salah satu klausa yang mempengaruhinya.

UNTUK DIPERBARUI DAN UNTUK SAHAM tidak dapat digunakan dalam konteks di mana baris yang dikembalikan tidak dapat diidentifikasi secara jelas dengan baris tabel individu; misalnya mereka tidak dapat digunakan dengan agregasi.


0
2017-12-23 17:50



Apa yang ingin Anda capai? Bisakah Anda menjelaskan dengan lebih baik mengapa pembaruan baris tidak terkunci atau transaksi penuh tidak akan melakukan apa yang Anda inginkan?

Lebih baik lagi, bisakah Anda mencegah perselisihan dan hanya memiliki setiap utas menggunakan offset yang berbeda? Ini tidak akan berfungsi dengan baik jika bagian tabel yang relevan sering diperbarui; Anda masih akan memiliki tabrakan tetapi hanya selama beban insert yang berat.

Select... order by id desc offset THREAD_NUMBER limit 1 for update

0
2017-12-23 18:00



Karena saya belum menemukan jawaban yang lebih baik, saya memutuskan untuk menggunakan penguncian dalam aplikasi saya untuk menyinkronkan akses ke kode yang melakukan query ini.


0
2017-12-23 20:02



Bagaimana dengan yang berikut ini? Saya t mungkin diperlakukan lebih atomik daripada contoh lain tetapi seharusnya masih diuji untuk memastikan asumsi saya tidak salah.

UPDATE users SET flags = 1 WHERE id = ( SELECT id FROM users WHERE flags = 0 ORDER BY id DESC LIMIT 1 ) RETURNING ...;

Anda mungkin masih terjebak dengan skema penguncian apa pun yang digunakan secara internal untuk menyediakan hasil SELECT yang konsisten dalam menghadapi UPDATE secara bersamaan.


0
2017-12-24 17:48



Saya menghadapi masalah yang sama dalam aplikasi kami dan muncul dengan solusi yang sangat mirip dengan pendekatan Grant Johnson. Pipa FIFO atau LIFO bukan pilihan karena kami memiliki sekelompok server aplikasi yang mengakses satu DB. Apa yang kami lakukan adalah a

SELECT ... WHERE FLAG=0 ... FOR UPDATE
 segera diikuti oleh
UPDATE ... SET FLAG=1 WHERE ID=:id
 sesegera mungkin untuk menjaga waktu kunci serendah mungkin. Bergantung pada jumlah kolom tabel dan ukuran yang mungkin membantu untuk hanya mengambil ID di pemilihan pertama dan setelah Anda menandai baris untuk mengambil data yang tersisa. Prosedur yang tersimpan dapat mengurangi jumlah perjalanan pulang-pergi lebih banyak lagi.


0
2018-06-24 20:41