duanmen8491 2014-07-17 08:37
浏览 683
已采纳

mysql - 行锁定和释放

I have two table 'reservation' and 'spot'.during a reservation process the 'spotStatus' column in spot table is checked and if free, it is to be updated. A user is allowed to reserve only one spot so to make sure that no other user can reserve the same spot, what can i do?

referring to some answers here,i found row locking,table locking as solutions. should i perform queries like

"select * from spot where spotId = id for update;" 

and then performing necessary update to the status or is there other elegant ways to do it?

and my concern is what happens to the locked row if

1. Transaction doesnot complete successfully?  
2. what happens if both user tries to reserve the same row at the same time? are both transactions cancelled? 

and when is the lock released?

  • 写回答

3条回答 默认 最新

  • dousi1994 2014-07-17 08:54
    关注

    The problem here is in race conditions, that even transactions will not prevent by default if used naively - even if 2 reservations happen simultaneously, for example originating from 2 different Apache processes running PHP, transactional locking will just ensure the reservations are properly serialized, and as such the second one will still overwrite the first.

    Usually this situation is of no real concern, given the speed of databases and servers as a whole, compared to the load on an average reservation site, the chances of this ever causing a problem are less than winning the state lottery twice in a row. If however you are implementing a site that's going to sell 50k Coldplay concert tickets in 30 seconds, chances rise aggressively.

    A simple solution to this is to implement a sort of 'reservation intent' by not overwriting the spot reservation directly, but by appending the intent-to-reserve to a separate timestamped table. After this insertion you can then clean up this table for duplicates, preferring the oldest, and apply that one to the real-time data.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥35 引用csv数据文件(4列1800行),通过高斯-赛德尔法拟合曲线,在选取(每五十点取1点)数据,求该数据点的曲率中心。
  • ¥20 程序只发送0X01,串口助手显示不正确,配置看了没有问题115200-8-1-no,如何解决?
  • ¥15 Google speech command 数据集获取
  • ¥15 vue3+element-plus页面崩溃
  • ¥15 像这种代码要怎么跑起来?
  • ¥15 安卓C读取/dev/fastpipe屏幕像素数据
  • ¥15 pyqt5tools安装失败
  • ¥15 mmdetection
  • ¥15 nginx代理报502的错误
  • ¥100 当AWR1843发送完设置的固定帧后,如何使其再发送第一次的帧