SELECT ... FOR UPDATE، SKIP LOCKED و ساخت صف کار و شماره‌ی فاکتور

قفل صریح، وقتی لازم است

SELECT ... FOR UPDATE ردیف‌های خوانده‌شده را با قفل انحصاری تا پایان تراکنش نگه می‌دارد؛ الگوی «بخوان، تصمیم بگیر، بنویس» را امن می‌کند. FOR SHARE قفل اشتراکی می‌گیرد: دیگران می‌توانند بخوانند اما نمی‌توانند تغییر دهند.

شماره‌ی فاکتور پشت‌سرهم و بدون شکاف

AUTO_INCREMENT شکاف دارد (فصل سوم). شماره‌ی فاکتور رسمی باید بدون شکاف و به تفکیک سال باشد:

CREATE TABLE invoice_counters (
  fiscal_year SMALLINT UNSIGNED PRIMARY KEY,   -- 1404
  last_no     INT UNSIGNED NOT NULL
);

START TRANSACTION;
SELECT last_no FROM invoice_counters WHERE fiscal_year = 1404 FOR UPDATE;
UPDATE invoice_counters SET last_no = last_no + 1 WHERE fiscal_year = 1404;
INSERT INTO invoices (fiscal_year, invoice_no, order_id, amount)
SELECT 1404, last_no, 5531, 480000000 FROM invoice_counters WHERE fiscal_year = 1404;
COMMIT;

اگر تراکنش ROLLBACK شود، شمارنده هم برمی‌گردد و شکافی نمی‌ماند. هزینه‌اش این است که صدور فاکتور سریالی می‌شود؛ پس این تراکنش را تا حد ممکن کوتاه نگه دارید.

صف کار با SKIP LOCKED

فرض کنید چند worker باید سفارش‌های تازه را برای کارخانه ارسال کنند. اگر همه با FOR UPDATE اولین کار آزاد را بخواهند، همه پشت یک ردیف صف می‌کشند. از 8.0 گزینه‌ی SKIP LOCKED ردیف‌های قفل‌شده را رد می‌کند و هر worker کار متفاوتی برمی‌دارد:

CREATE TABLE jobs (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kind        VARCHAR(30) NOT NULL,
  payload     JSON NOT NULL,
  status      ENUM('pending','running','done','failed') NOT NULL DEFAULT 'pending',
  run_after   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  INDEX ix_pick (status, run_after, id)
);

-- هر worker:
START TRANSACTION;
SELECT id, kind, payload FROM jobs
WHERE status = 'pending' AND run_after <= NOW()
ORDER BY id
LIMIT 5
FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'running', attempts = attempts + 1 WHERE id IN (101, 102, 103);
COMMIT;
-- کار را انجام بده، سپس:
UPDATE jobs SET status = 'done' WHERE id = 101;

تراکنش فقط برای «برداشتن» کار است و کوتاه می‌ماند؛ خود کار (ارسال پیامک، ساخت PDF) بیرون از تراکنش انجام می‌شود. برای کارهای «running» که worker آن‌ها مرده، یک ستون locked_at و یک کار دوره‌ای برای بازگرداندنشان لازم است.

NOWAIT و رزرو موجودی

-- اگر کس دیگری در حال ویرایش سفارش است، فوراً خطا بده، منتظر نمان
SELECT * FROM orders WHERE id = 5531 FOR UPDATE NOWAIT;
-- ERROR 3572: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

-- رزرو اتمی موجودی بدون قفل صریح
UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock >= 1;
-- rows affected = 1 : رزرو شد ؛ 0 : ناموجود

دستور آخر اتمی است: شرط و تغییر با هم انجام می‌شوند و موجودی هرگز منفی نمی‌شود، حتی با صد خرید هم‌زمان.

نکته‌هایی که کمتر کسی می‌داند

• FOR UPDATE بیرون از تراکنش (با autocommit روشن) بی‌معناست: قفل گرفته و بلافاصله با COMMIT خودکار آزاد می‌شود.
• SKIP LOCKED نتیجه‌ی ناسازگار می‌دهد و برای صف مناسب است، نه برای گزارش یا محاسبه‌ی موجودی.
• بدون ایندکسی مثل (status, run_after, id)، کوئری SKIP LOCKED کل جدول را پیمایش و قفل می‌کند و مزیتش از بین می‌رود.
• در جنگو معادل این‌ها select_for_update(skip_locked=True) و select_for_update(nowait=True) است که فقط داخل transaction.atomic() کار می‌کند.
• کارهای انجام‌شده را در جدول صف نگه ندارید؛ جدول jobs با میلیون‌ها ردیف done، اسکن‌ها و قفل‌ها را کند می‌کند. آن‌ها را به جدول آرشیو منتقل یا حذف کنید.