Pembacaan atom dan kemas kini dengan urutan pekerja serentak dalam MySQL

Question

Andaikan saya mempunyai berbilang pekerja yang boleh membaca dan menulis ke jadual MySQL pada masa yang sama (contohnya jobs). Tugas setiap pekerja ialah:

1. Cari pekerjaan 已排队 tertua
2. Tetapkan statusnya kepada RUNNING
3. Kembalikan ID yang sepadan.

Sila ambil perhatian bahawa apabila pekerja menjalankan langkah #1, mungkin tidak ada sebarang pekerjaan yang layak (iaitu QUEUED).

Saya mempunyai pseudokod berikut setakat ini. Saya percaya jika langkah #1 tidak mengembalikan kerja, saya perlu membatalkan (ROLLBACK) transaksi. Bagaimanakah saya melakukan ini dalam kod di bawah?

BEGIN TRANSACTION;

# Update the status of jobs fetched by this query:
SELECT id from jobs WHERE status = "QUEUED" 
ORDER BY created_at ASC LIMIT 1;

# Do the actual update, otherwise abort (i.e. ROLLBACK?)
UPDATE jobs
SET status="RUNNING"
# HERE: Not sure how to make this conditional on the previous ID
# WHERE id = <ID from the previous SELECT>

COMMIT;

Answer 1

Masih belum jelas apa yang anda mahukan. Tetapi katakan tugas anda ialah: cari QUEUED 作业。将其状态设置为RUNNING seterusnya dan pilih ID yang sepadan.

Dalam persekitaran satu benang, anda hanya boleh menggunakan kod anda. Ekstrak ID yang dipilih ke dalam pembolehubah dalam kod aplikasi dan hantarkannya kepada pertanyaan KEMASKINI dalam klausa WHERE. Anda tidak memerlukan transaksi kerana hanya terdapat satu kenyataan tulis. Anda boleh meniru ini dalam SQLscript.

Andaikan ini status semasa anda:

| id  | created_at          | status   |
| --- | ------------------- | -------- |
| 1   | 2020-06-15 12:00:00 | COMLETED |
| 2   | 2020-06-15 12:00:10 | QUEUED   |
| 3   | 2020-06-15 12:00:20 | QUEUED   |
| 4   | 2020-06-15 12:00:30 | QUEUED   |

Anda ingin memulakan kerja beratur seterusnya (id=2).

SET @id_for_update = (
  SELECT id
  FROM jobs
  WHERE status = 'QUEUED'
  ORDER BY id
  LIMIT 1
);

UPDATE jobs
SET status="RUNNING"
WHERE id = @id_for_update;

SELECT @id_for_update;

Anda akan dapat

@id_for_update
2

Mulakan dari pilihan terakhir. Jadual akan mempunyai status berikut:

| id  | created_at          | status   |
| --- | ------------------- | -------- |
| 1   | 2020-06-15 12:00:00 | COMLETED |
| 2   | 2020-06-15 12:00:10 | RUNNING  |
| 3   | 2020-06-15 12:00:20 | QUEUED   |
| 4   | 2020-06-15 12:00:30 | QUEUED   |

Lihat di DB Fiddle

Jika anda mempunyai pelbagai proses melancarkan kerja, anda perlu menggunakan FOR UPDATE 锁定该行。但可以使用LAST_INSERT_ID() untuk mengelakkan perkara ini:

Bermula dari keadaan di atas, job 2 sudah berjalan:

UPDATE jobs
SET status = 'RUNNING',
    id = LAST_INSERT_ID(id)
WHERE status = 'QUEUED'
ORDER BY id
LIMIT 1;

SELECT LAST_INSERT_ID();

Anda akan mendapat:

| LAST_INSERT_ID() | ROW_COUNT() |
| ---------------- | ----------- |
| 3                | 1           |

Status baharu ialah:

| id  | created_at          | status   |
| --- | ------------------- | -------- |
| 1   | 2020-06-15 12:00:00 | COMLETED |
| 2   | 2020-06-15 12:00:10 | RUNNING  |
| 3   | 2020-06-15 12:00:20 | RUNNING  |
| 4   | 2020-06-15 12:00:30 | QUEUED   |

Lihat di DB Fiddle

Jika kenyataan KEMASKINI tidak menjejaskan mana-mana baris (tiada baris beratur), ROW_COUNT() 将为 0.

Mungkin terdapat beberapa risiko yang saya tidak sedar - tetapi itu juga bukan cara saya menghadapinya. Saya lebih suka menyimpan maklumat lanjut dalam jadual jobs. Contoh mudah:

CREATE TABLE jobs (
  id INT auto_increment primary key,
  created_at timestamp not null default now(),
  updated_at timestamp not null default now() on update now(),
  status varchar(50) not null default 'QUEUED',
  process_id varchar(50) null default null
);

dan

UPDATE jobs
SET status = 'RUNNING',
    process_id = 'some_unique_pid'    
WHERE status = 'QUEUED'
ORDER BY id
LIMIT 1;

Kini kerja yang sedang dijalankan tergolong dalam proses tertentu, anda boleh memilihnya hanya menggunakan

SELECT * FROM jobs WHERE process_id = 'some_unique_pid';

Anda mungkin ingin mengetahui lebih lanjut - cth. queued_at、started_at、finished_at.

Answer 2

Minggu ini saya melaksanakan sesuatu yang hampir serupa dengan kes anda. Berbilang pekerja, masing-masing meraih "baris seterusnya" dalam satu set baris untuk diusahakan.

Kod pseudo adalah seperti ini:

BEGIN;

SELECT ID INTO @id FROM mytable WHERE status = 'QUEUED' LIMIT 1 FOR UPDATE;

UPDATE mytable SET status = 'RUNNING' WHERE id = @id;

COMMIT;

Menggunakan FOR UPDATE adalah penting untuk mengelakkan keadaan perlumbaan (iaitu berbilang pekerja cuba mengambil baris yang sama).

Lihat https://dev.mysql.com/ doc/refman/8.0/en/select-into.html untuk maklumat tentang SELECT ... INTO.