MySQL - 在x秒不活动后自动更新行（设置到期时间）

Question

For an art project I am trying to set up an order site. The concept allows users to book a max. of two time slots with each artist. There are 12 slots per artist, but each slot with a specific definition (so each is unique). The slots are only available for a very limited time and hopefully booked fast. So there will be a lot of requests in a short period of time. I have to make sure each article/slot is only offered to a single user at a time and cannot be double booked.

My idea was, to check for the next unbooked slot(s) (status="free) and on that request update the status of the corresponding row in the table to status="locked". If the user proceeds to actually book the slot, the status is updated to "booked".

If a user clicks "cancel" I can release the article by updating the row to status="free".

However, it is not unlikely that users just abandon the site and I don't see a way to check for that. The slot would remain "locked". I was thinking, there might be a way to automatically reset the status e.g. 120 seconds after is was "locked" and show a countdown to the users. This could even enhance the excitement factor.

I don't think a cron job would work as I need the anchor to be the last update of the row and not a specific datetime.

I looked into MySQL events but understood that I cannot manipulate the data of the table it is attached to.

I would greatly appreciate any ideas. Thanks, Sam

Answer 1

1. In your db your status table add a datetime field.

2. When someone lock a slot you also save the current time using NOW()

3. When someone consult the slots you perform and update and free the inactive slots

   Update slots
   SET locked = false 
   WHERE `datetime`> NOW() - INTERVAL 15 MINUTE;
   
   SELECT *
   FROM slots
   WHERE locked = false;