确保两个人不要在我的Web应用程序上编辑同一行的最佳方法是什么? [英] What is the best method to make sure two people don't edit the same row on my web app?

问题描述

我有一个用于管理数据库的PHP/jQuery/AJAX/MySQL应用程序.我想实现阻止多个用户同时编辑同一数据库行的功能.

I have a PHP/jQuery/AJAX/MySQL app built for managing databases. I want to implement the ability to prevent multiple users from editing the same database row at the same time.

1. 这叫什么?
2. 我是否使用令牌系统，并且拥有令牌的人可以对其进行编辑，直到他们释放令牌?
3. 我是否使用上次编辑日期/时间"来比较您将HTML表单的加载时间与数据库中的时间进行比较，如果数据库是最近发送的内容，则它会向您发出警告?
4. 我是否使用数据库功能锁定行?

我不确定哪一个最好.假设有10-15个并发用户

I'm just not sure which is the best. Assuming between 10 - 15 concurrent users

推荐答案

有两种通用方法-乐观锁定和悲观锁定.

There are two general approaches-- optimistic and pessimistic locking.

乐观锁定通常在基于Web的环境中更容易实现，因为它基本上是无状态的.它的伸缩性也更好.缺点是它假定您的用户通常不会尝试同时编辑同一组行.对于大多数应用程序，这是一个非常合理的假设，但是您必须验证您的应用程序不是用户经常会踩到对方脚趾的异常值之一.在乐观锁定中，您将拥有某种last_modified_timestamp列，当用户获取数据时，该列将为SELECT，然后在您更新日期(即

Optimistic locking is generally much easier to implement in a web-based environment because it is fundamentally stateless. It scales much better as well. The downside is that it assumes that your users generally won't be trying to edit the same set of rows at the same time. For most applications, that's a very reasonable assumption but you'd have to verify that your application isn't one of the outliers where users would regularly be stepping on each other's toes. In optimistic locking, you would have some sort of last_modified_timestamp column that you would SELECT when a user fetched the data and then use in the WHERE clause when you go to update the date, i.e.

UPDATE table_name
   SET col1 = <<new value>>,
       col2 = <<new values>>,
       last_modified_timestamp = <<new timestamp>>
 WHERE primary_key = <<key column>>
   AND last_modified_timestamp = <<last modified timestamp you originally queried>>

如果该行更新1行，则说明您成功.否则，如果它更新0行，则说明您在此期间已修改了其他数据，并且可以采取一些措施(通常向用户显示新数据并询问他们是否要覆盖，但是您可以采用其他冲突解决方法).

If that updates 1 row, you know you were successful. Otherwise, if it updates 0 rows, you know that someone else has modified the data in the interim and you can take some action (generally showing the user the new data and asking them if they want to overwrite but you can adopt other conflict resolution approaches).

尤其是在基于Web的应用程序中，尤其是当用户可以关闭其浏览器而无需注销或用户可以开始编辑某些数据并在点击Submit之前先去吃午餐时，悲观锁定更具挑战性.它使扩展变得更困难，并且通常使应用程序更难以管理.真正值得考虑的是，用户是否会定期尝试更新相同的行，或者更新一行对于用户来说是否花费了大量时间，因此值得让他们事先知道其他人已锁定该行.

Pessimistic locking is more challenging to implement particularly in a web-based application particularly when users can close their browser without logging out or where users may start editing some data and go to lunch before hitting Submit. It makes it harder to scale and generally makes the application more difficult to administer. It's really only worth considering if users will regularly try to update the same rows or if updating a row takes a large amount of time for a user so it's worth letting them know up front that someone else has locked the row.

这篇关于确保两个人不要在我的Web应用程序上编辑同一行的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！