MySQL 5.6：如何防止并发读取指定行及同作者关联行

Hey there! Let's break down your problem with AWS Aurora Serverless MySQL concurrency and those duplicate author email issues. I'll walk through both your proposed solutions, including the nitty-gritty details you're asking about, and tie it to Rails Active Record since you're familiar with that.

First, a quick recap of your context: you're working with AWS Aurora Serverless MySQL 5.6 (auto-commit disabled by default), using Lambda for high-concurrency, millisecond-scale requests to the Hobby_ideas_articles table. Your goal is to make sure the same author doesn't get multiple emails in a short window, but your current SQL logic lets duplicates slip through in concurrent scenarios.

方案1：SELECT ... FOR UPDATE行锁 + 状态标记 #

Let's tackle each of your questions one by one:

1.1 修正给定的SQL代码（Rails Active Record + 原生SQL） #

Assuming your original query (without locking, which causes duplicates) looked like this in Rails:

# Original problematic code
articles = HobbyIdeasArticle.where(hobby_id: target_hobby_id, customer_id: target_customer_id, currently_locked_for_emailing: false)

To add row locking and status marking, wrap everything in a transaction and use lock! (Rails' equivalent of SELECT ... FOR UPDATE):

HobbyIdeasArticle.transaction do
  # Lock the 20 eligible rows first
  articles = HobbyIdeasArticle.where(hobby_id: target_hobby_id, customer_id: target_customer_id, currently_locked_for_emailing: false)
                              .limit(20)
                              .lock!
  # Batch update their status to mark as locked
  articles.update_all(currently_locked_for_emailing: true, locked_at: Time.current)
end

If you prefer raw SQL, here's the equivalent:

BEGIN;
-- Lock the rows we want to process
SELECT * FROM hobby_ideas_articles 
WHERE hobby_id = ? AND customer_id = ? AND currently_locked_for_emailing = 0 
LIMIT 20 FOR UPDATE;

-- Update their status in bulk
UPDATE hobby_ideas_articles 
SET currently_locked_for_emailing = 1, locked_at = NOW() 
WHERE id IN (/* IDs from the SELECT above */);
COMMIT;

1.2 先锁后更新状态是否合理？能否先更新再锁？ #

Lock first, then update is 100% the right approach—and trying to update first would break your logic entirely.

Here's why: if you update without locking first, concurrent requests can all target the same rows before any locks are applied. You'd still get duplicates because there's no guardrail to stop race conditions. Locking first ensures that once one request grabs those rows, all others have to wait until the transaction commits (and locks are released) before they can touch them.

Updating first is useless here—by the time you attempt to lock, the damage is done, and you can't reliably filter for unprocessed rows anymore.

1.3 如何对SELECT结果行批量更新状态？ #

In Rails, the update_all method I used above handles bulk updates for the locked rows perfectly. For raw SQL, you can also use a JOIN to do it in a single query (more efficient than two separate steps):

UPDATE hobby_ideas_articles a
JOIN (
  SELECT id FROM hobby_ideas_articles 
  WHERE hobby_id = ? AND customer_id = ? AND currently_locked_for_emailing = 0 
  LIMIT 20 FOR UPDATE
) b ON a.id = b.id
SET a.currently_locked_for_emailing = 1, a.locked_at = NOW();

This locks and updates in one go, avoiding an extra round trip to the database.

1.4 事务提交后锁是否立即释放？ #

Yes! In InnoDB (which powers Aurora Serverless MySQL), row locks are released as soon as the transaction commits or rolls back. As long as you commit your transaction right after updating the status, the locks won't hang around and block other requests.

1.5 行锁仅锁定SELECT结果行（含LIMIT 20的20行）而非全表？ #

Exactly—SELECT ... FOR UPDATE only locks the rows that are returned by your query (the 20 you limited to). That said, this only holds true if your query uses indexes efficiently (more on that in the next question).

1.6 非索引列过滤是否会导致全表锁？需创建哪些索引？ #

If your filter columns (hobby_id, customer_id, currently_locked_for_emailing) don't have an index, MySQL will do a full table scan. When you add FOR UPDATE to a full table scan, it locks every row it scans (even if only 20 are returned)—which is effectively a full table lock and will kill your concurrency.

Fix this with a composite index that covers all your filter conditions:

CREATE INDEX idx_hobby_customer_locked ON hobby_ideas_articles (hobby_id, customer_id, currently_locked_for_emailing);

In Rails, you can add this via a migration:

add_index :hobby_ideas_articles, [:hobby_id, :customer_id, :currently_locked_for_emailing], name: 'idx_hobby_customer_locked'

This index lets MySQL jump straight to the rows you need, locking only those 20 instead of scanning the whole table.

方案2：锁定同一作者的所有行（避免不同hobby_id下重复选中） #

This is a totally valid approach—your core goal is to prevent the same author from being picked multiple times, regardless of the hobby_id. Locking all of an author's eligible rows ensures that only one request can process their articles at a time.

实现方式（Rails + 原生SQL） #

Assuming your table has an author_id column to identify the same author:

HobbyIdeasArticle.transaction do
  # Lock ALL of the author's unlocked rows first
  author_articles = HobbyIdeasArticle.where(author_id: target_author_id, currently_locked_for_emailing: false)
                                     .lock!
  # Pick 20 from the locked set to mark as processed
  author_articles.limit(20).update_all(currently_locked_for_emailing: true, locked_at: Time.current)
end

Raw SQL equivalent (using a JOIN for efficiency):

UPDATE hobby_ideas_articles a
JOIN (
  SELECT id FROM hobby_ideas_articles 
  WHERE author_id = ? AND currently_locked_for_emailing = 0 
  LIMIT 20 FOR UPDATE
) b ON a.id = b.id
SET a.currently_locked_for_emailing = 1, a.locked_at = NOW();

This locks all of the author's unlocked rows, so even concurrent requests targeting different hobby_ids can't pick the same author until the transaction commits.

索引建议 #

To make this query fast and avoid unnecessary locks, create a composite index on author_id and currently_locked_for_emailing:

CREATE INDEX idx_author_locked ON hobby_ideas_articles (author_id, currently_locked_for_emailing);

Rails migration code:

add_index :hobby_ideas_articles, [:author_id, :currently_locked_for_emailing], name: 'idx_author_locked'

Quick Extra Tips #

1. Since Aurora Serverless disables auto-commit by default, always wrap these operations in a transaction—otherwise, locks won't release automatically, leading to lock leaks.
2. Add an expiration to your locked_at field: if a row is locked for over 10 minutes (e.g., if Lambda fails mid-execution), use a cron job or Rails task to reset its currently_locked_for_emailing status so it can be processed later.
3. In Rails, the transaction block automatically rolls back if an exception occurs, so locks will be released even if something goes wrong.

内容的提问来源于stack exchange，提问作者Mathieu