postgresql 9.4 – 阻止应用程序始终选择最新更新的行

概述我有一个(Ruby on Rails 4)应用程序,并使用postgresql查询,它基本上查看Opportunity表,在机会为’available = true’的行中随机搜索,并使用’available = false’更新这些选定的行. 每次用户点击“尝试机会”时,该应用都会使用以下查询. UPDATE opportunities sSET opportunity_availabl 我有一个(Ruby on Rails 4)应用程序,并使用postgreSQL查询,它基本上查看Opportunity表,在机会为’available = true’的行中随机搜索,并使用’available = false’更新这些选定的行.
每次用户点击“尝试机会”时,该应用都会使用以下查询.

UPDATE opportunitIEs sSET    opportunity_available = falseFROM  (   SELECT ID   FROM   opportunitIEs   WHERE  deal_ID = #{@deal.ID}   AND    opportunity_available   AND    pg_try_advisory_xact_lock(ID)   liMIT  1   FOR    UPDATE   ) subWHERE     s.ID = sub.IDRETURNING s.prize_ID,s.ID;// inspired by https://stackoverflow.com/questions/33128531/put-pg-try-advisory-xact-lock-in-a-nested-subquery

我已经苦苦挣扎了5天但我现在已经成功地理解了它的表现(糟糕)：现在我需要知道如何改变它.

事实上,应用程序“变得有点疯狂”并选择(并更新行)完全由我更新的最新.

让我明白我如何创造这些机会的过程

>我作为管理员通过我的应用程序创建20个机会(行)
>然后在我的管理面板中,我创建一个奖品,数量让我们说奖品ID = 45,数量= 4.应用程序将进入商机表并随机填写(此部分完美运行)4行,其中prize_ID = 45.

现在一个用户运行应用程序并且如上所述,应用程序不会随机选择一行,而是始终通过最新更新开始：它似乎带有奖品的一行,然后带有奖品的另一行,然后另一个带有奖品没有奖品的人(prized_ID =空)……

现在我做了一些手动实验：我尝试手动更改我的表上的值(通过pgadmin),最奇怪的事情发生：如果我修改第32行,然后第45行,然后第67行,当用户再次尝试播放时,猜猜是什么,随机选取的线正是我按相反顺序更新的线：它将选择线67然后线45然后线32 ..
它甚至不会考虑可以选择的其他行(所有其他可用的机会= true).

我也尝试过不用于更新或’pg_try_advisory_xact_lock(ID)’行,看起来它仍然有同样的问题.

作为管理员,我首先创建了总共20行,然后是4个获胜行,它们是最后一个被更新的行(即使在我的pgadmin屏幕上它们保持在同一行…也许在后台,postgresql将它们作为最后一次更新的顺序排序并选择它们？)这就是为什么只要选择4个这些获胜行中的一个,然后所有其他行都会跟随.

为了清楚起见,我可以通过逐行选择每个可用的机会(例如：第3行,然后第4行,然后按照我在pgadmin上看到的第5行,因为行已经完全随机地归结了奖品).问题是它没有这样做,它经常连续取得所有获胜的行….

我无言以对如何打破这种模式毫无头绪.

注意：这种模式不会在100％的情况下连续发生,但经常发生：例如,如果我有超过4个获胜行,如果我继续点击作为用户,它表现如此处所说,那就是2分钟,然后停止似乎(或者我可能是错的)表现正常然后再次&分钟将再次只选择获胜的行…

编辑1

以下是如何在商机表中注入奖品(例如,我创建奖品,其ID为21,奖品数量为3)=>它随机发送(据我所知)但只有在没有prize_ID的情况下(即如果机会有prize_ID =空,它可以把它放在那里)

sql (2.4ms)    UPDATE "opportunitIEs"  SET "prize_ID" = 21  WHERE "opportunitIEs"."ID"   IN (    SELECT "opportunitIEs"."ID"    FROM "opportunitIEs"    WHERE (deal_ID = 341 AND prize_ID IS NulL)    ORDER BY RANDOM()    liMIT 3) //   (0.9ms)  COMMIT

此SQL查询由Rails gem生成(称为Randumb：github.com/spilliton/randumb)

编辑2

我转到另一个更精确的问题：Postgresql 9.4 – FASTEST query to select and update on large dataset (>30M rows) with heavy writes/reads and locks

事实上,我认为问题实际上是我需要一个真正的RANDOM选择并远离ARBITRARY选秀权.

Erwin在Advisory locks or NOWAIT to avoid waiting for locked rows?已经说过了,但现在我明白了他的意思(“任意”也很重要,因为它暗示Postgresql通常会为同一个查询任意选择相同的行,这使得锁争用比真正的随机选择更大的问题可以.“postgresql可以自由选择输出’liMIT 1’的最快方式,并且它总是选择相同的行=那些已被提升的最后一行.但我不能这样做,因为最新的更新都是Winning机会.

只是一个想法：而不是调用random()使用它作为列的默认值(可以索引)类似的方法可以使用增量约为0.7 * INT_MAX的串行.

\i tmp.sqlCREATE table opportunitIEs    ( ID SERIAL NOT NulL PRIMARY KEY,deal_ID INTEGER NOT NulL DEFAulT 0,prize_ID INTEGER,opportunity_available boolean NOT NulL DEFAulT False            -- ----------------------------------------            -- precomputed random(),(Could be indexed),magic DOUBLE precision NOT NulL default RANDOM()    );INSERT INTO opportunitIEs(deal_ID)SELECT 341FROM generate_serIEs(1,20) gs    ;VACUUM ANALYZE opportunitIEs;PREPARE add_three (integer) AS (WITH zzz AS (  UPDATE opportunitIEs  SET prize_ID = 21,opportunity_available = True    -- updating magic is not *really* needed here ...,magic = random()  WHERE opportunitIEs.ID  IN (    SELECT opportunitIEs.ID    FROM opportunitIEs    WHERE (deal_ID =  AND prize_ID IS NulL)    -- ORDER BY RANDOM()    ORDER BY magic    liMIT 3)RETURNING ID,magic    ) -- SELECT * FROM zzz    );PREPARE draw_one (integer) AS (  WITH upd AS (  UPDATE opportunitIEs s  SET    opportunity_available = false  FROM  (     SELECT ID     FROM   opportunitIEs     WHERE  deal_ID =      AND    opportunity_available     AND    pg_try_advisory_xact_lock(ID)     ORDER BY magic     liMIT  1     FOR    UPDATE     ) sub  WHERE     s.ID = sub.ID  RETURNING s.prize_ID,s.ID,magic    )SELECT * FROM upd    );SELECT * FROM opportunitIEs;\echo add3EXECUTE add_three(341);SELECT * FROM opportunitIEs;\echo add3 moreEXECUTE add_three(341);SELECT * FROM opportunitIEs;\echo draw1EXECUTE draw_one(341);SELECT * FROM opportunitIEs;\echo draw2EXECUTE draw_one(341);SELECT * FROM opportunitIEs;VACUUM ANALYZE opportunitIEs;\echo draw3EXECUTE draw_one(341);SELECT * FROM opportunitIEs;\echo draw4EXECUTE draw_one(341);SELECT * FROM opportunitIEs;

总结

以上是内存溢出为你收集整理的postgresql 9.4 – 阻止应用程序始终选择最新更新的行全部内容，希望文章能够帮你解决postgresql 9.4 – 阻止应用程序始终选择最新更新的行所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。