How to handle concurrent queries that delete and create the same rows?

Question

I have a table that looks like this:

game_stats table:

id | game_id | player_id | stats | (many other cols...) ---------------------- 1 | 'game_abc' | 8 | 'RRABS' | ... 2 | 'game_abc' | 9 | BAS' | ... 

The user downloads data for this game in bulk, while sending data to both players. For instance:

 "game": { id: 'game_abc', player_stats: { 8: { stats: 'RRABS' }, 9: { stats: BAS' } } } 

Sending this to my server should result in the first table.

Instead of updating existing rows when the same data is sent again (for example, with revisions), which I do in my controller, first delete all existing rows in the game_stats table that have the given game_id:

 class GameStatController def update GameStat.where("game_id = ?", game_id).destroy_all params[:game][:player_stats].each do |stats| game_stat.save end end end 

This works fine with a single threaded or single process server. The problem is that I am running Unicorn, which is a multiprocessor server. If two requests arrive simultaneously, I get a race condition:

 Request 1: GameStat.where(...).destroy_all Request 2: GameStat.where(...).destroy_all Request 1: Save new game_stats Request 2: Save new game_stats 

Result: multiple lines of game_stat with the same data.

I believe that somehow locking rows or tables is a way to prevent multiple updates at the same time, but I cannot figure out how to do this. The combination with the transaction seems correct, but I do not understand why.

EDIT

To find out why I cannot figure out how to use lock: I cannot lock one row at a time, since the row is simply deleted and not changed.

Answer 1

AR does not support table-level locking by default. You need to either execute db-specific SQL or use a gem like Monogamy

Completing save operations in a transaction will speed things up if nothing else.

Another option is to implement locking using Redis. Gems such as redis-lock are also available. This is likely to be less risky, as it does not apply to the database, and you can set Redis keys for expiration.