dp7311 2016-01-14 17:31
浏览 21
已采纳

唯一代码 - 给予在同一时间点击脚本的两个用户

Hi have a bunch of unique codes in a database which should only be used once.

Two users hit a script which assigns them at the same time and got the same codes!

The script is in Magento and the user can order multiple codes. The issue is if one customer orders 1000 codes the script grabs the top 1000 codes from the DB into an array and then runs through them setting them to "Used" and assigning them to an order. If a second user hits the same script at a similar time the script then grabs the top 1000 codes in the DB at that point in time which crosses over as the first script hasn't had a chance to finish assigning them.

This is unfortunate but has happened quite a few times!

My idea was to create a new table, once the user hits the script a row is made with "order_id" "code_type". Then in the same script a check is done so if a row is in this new table and the "code_type" matches that of which the user is ordering it will wait 60 seconds and check again until the previous codes are issued and the table is empty where it will then create a row and off it goes.

I am not sure if this is the best way or if two users hit at the same second again whether two rows will just be inserted and off we go with the same problem!

Any advice is much appreciated!

  • 写回答

1条回答 默认 最新

  • doukuang8166 2016-01-14 18:58
    关注

    The correct answer depends on the database you use.

    For example in MySQL with InnoDB the possible solution is a transaction with SELECT ... LOCK IN SHARE MODE.

    Schematically it works this by firing following queries:

    START TRANSACTION;
    SELECT * FROM codes WHERE used = 0 LIMIT 1000 LOCK IN SHARE MODE;
    // save ids
    UPDATE codes SET used=1 WHERE id IN ( ...ids....);
    COMMIT;
    

    More information at http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能