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.
我有一个用于管理数据库的PHP/jQuery/AJAX/MySQL应用程序。我想实现防止多个用户同时编辑同一个数据库行的功能。
- What is this called?
- 这是什么叫什么?
- Do I use a token system and who ever has the token can edit it until they release the token?
- 我是否使用了令牌系统，并且谁有令牌可以编辑它，直到他们释放令牌?
- Do I use a "last edit date/time" to compare you loading the HTML form with the time in the database and if the database is the most resent edit then it warns you?
- 我是否使用“最后编辑日期/时间”来比较您在数据库中加载HTML表单的时间，如果数据库是最近的编辑，那么它会警告您吗?
- Do I lock the row using database functions?
- 是否使用数据库函数锁定行?
I'm just not sure which is the best. Assuming between 10 - 15 concurrent users
我只是不确定哪一个是最好的。假设10 - 15个并发用户
6 个解决方案
#1
16
There are two general approaches-- optimistic and pessimistic locking.
有两种通用的方法——乐观锁和悲观锁。
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.
乐观锁定通常更容易在基于web的环境中实现，因为它基本上是无状态的。它的规模也要大得多。缺点是它假定您的用户通常不会同时尝试编辑相同的行集。对于大多数应用程序来说，这是一个非常合理的假设，但是您必须验证您的应用程序并不是用户经常踩到对方脚趾的异常值之一。在乐观锁定中，您将会有某种last_modified_timestamp列，当用户获取数据时，您将选择该列，然后在更新日期时使用WHERE子句。
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>>
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).
如果更新了一行，你就知道你成功了。否则，如果它更新了0行，您就知道在此期间其他人已经修改了数据，您可以采取一些操作(通常向用户显示新数据并询问他们是否希望重写，但您可以采用其他冲突解决方法)。
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的应用程序中实现悲观锁定更具挑战性，尤其是当用户无需退出就可以关闭浏览器时，或者当用户开始编辑一些数据并在提交之前吃午饭时。它使得扩展更加困难，并且通常使应用程序更难管理。如果用户经常尝试更新相同的行，或者更新一行占用了大量的时间，值得考虑，所以值得让他们预先知道其他人已经锁定了这一行。
#2
7
I was going to implement this into one of my own systems.
我打算把它应用到我自己的系统中。
You could create new columns in your database of records, called timelocked.
您可以在记录数据库中创建新的列，称为timelocked。
When a record is opened, you would set the record they are opening's column for timelocked to the current time. During editing of the record, send a keepalive back to the server through ajax every 2 minutes. When sending the keepalive, the server will then increase the timelocked time to the current time the request was sent, and so fourth (this will make sense in a second). WHen the user is finished editing, set the timelocked to false.
当打开一个记录时，您将把它们正在打开的记录设置为timelocked到当前时间。在编辑记录期间，每2分钟通过ajax向服务器发送一个keepalive消息。在发送keepalive时，服务器将把时间戳时间增加到发送请求的当前时间，因此是第四个(这在稍后将变得有意义)。当用户完成编辑时，将timelocked设置为false。
Now, If someone went to open a record which is already open, the php would check - if timelocked == false - would mean it's not being edited,
现在，如果有人打开一个已经打开的记录，php会检查-如果timelocked == false -将意味着它没有被编辑，
otherwise, the record may be being edited, but what if the user closed their browser window. that's why the keepalive is used.
否则，记录可能被编辑，但是如果用户关闭了浏览器窗口。这就是为什么要使用keepalive。
if the difference between the current time and the timelocked is larger than 2 minutes, it means they're no longer lively editing, which would allow you to open it.
如果当前时间和时间间隔大于2分钟，这意味着它们不再是活跃的编辑，这将允许您打开它。
Hopefully you understand all that.
希望你们能理解。
#3
2
Don't try to prevent it. Let them decide what to do in the case of an edit conflict.
不要试图去阻止它。让他们决定在编辑冲突时该做什么。
Add a timestamp to the table. Compare the timestamp of when the row was retrieved with the current timestamp. Make them aware of changes between their load and their save, and let them decide what action to take.
向表添加时间戳。比较何时用当前时间戳检索行的时间戳。让他们意识到负载和保存之间的变化，让他们决定采取什么行动。
So yeah, number 3.
是的,数量3。
#4
0
I personally would not prevent this. If it was a requirement of the job I would track the users' current / last known location and disallow someone from editing the same line someone else is editing this way. I have seen people add a row to a table saying isLocked or isBeingWorkedOn etc... but I have seen this type of system fail far more often as well, or require moderation to unlock stuck tables if someone closed it while working on it etc...
我个人不会阻止这个。如果这是工作的要求，我将跟踪用户当前/最后的已知位置，并禁止某人以这种方式编辑同一行。我见过有人在桌子上加一行，说锁了，或者在工作，等等……但我也见过这种类型的系统更经常地失败，或者如果有人在工作时关闭了它，就需要适度地解锁卡滞的表……
#5
0
1) This is called locking. There are two main types of locking when referring to relational databases (like MySQL): table locking and row locking. Table locking ensures only one session at at time is making changes to a table, whereas row locking ensures only one session at a time is making changes to a particular row. You can think of row locking as a more fine-grained approach to concurrent access than table locking. Row locking is more complicated, but allows multiple concurrent sessions to write to the same table (important if your database has lots of concurrent writes--table locking should be fine for 10-15 users)
2-3) MySQL takes care of concurrent access for you! It automatically implements locking in the background. The type of locking (row or table) depends on which storage engine you use. For example, MyISAM uses table locking and InnoDB uses row locking. MySQL uses an internal table to manage this. You can query the status of this table (and all locks on your database) by checking the Table_locks_immediate and Table_locks_waited variables (it uses your option number 2).
When you issue an INSERT or UPDATE statement while another session is using the table (or row), the calling application (i.e. PHP in this case) will pause for a few milliseconds until the other session is done writing.
这叫做锁定。当引用关系数据库(如MySQL)时，有两种主要的锁定类型:表锁定和行锁定。表锁定只确保一个会话在时间上对表进行更改，而行锁定只确保每次只对特定行进行更改。可以将行锁定视为一种比表锁定更细粒度的并发访问方法。行锁更加复杂，但是允许多个并发会话写入同一个表(如果您的数据库有很多并发的写——10-15个用户可以使用表锁)2-3)MySQL会为您处理并发访问!它自动实现后台的锁定。锁定(行或表)的类型取决于使用哪个存储引擎。例如，ismyam使用表锁，InnoDB使用行锁。MySQL使用一个内部表来管理。您可以查询这个表的状态(和所有锁在你的数据库)通过检查Table_locks_immediate和Table_locks_waited变量(它使用选项2号)。当你的问题一个INSERT或UPDATE语句,而使用另一个会话表(或行),调用应用程序(比如PHP在这种情况下)将暂停几毫秒到其他会话完成写作。
4) Again, MySQL will automatically take care of locking, but you can manually manage table locking with the LOCK TABLES and UNLOCK TABLES commands. If you are using row locking with InnoDB, there is a host of functions you can use to manually manage concurrent access.
4)同样，MySQL会自动处理锁，但是您可以使用锁表手动管理表锁，并解锁表命令。如果您正在使用InnoDB的行锁，那么您可以使用许多函数来手动管理并发访问。
See MySQL's page on Internal Locking for an overview of MySQL's locking system, and Concurrent Inserts for InnoDB's row locking features.
请参阅MySQL的内部锁定页面，了解MySQL的锁定系统，并为InnoDB的行锁定特性提供并发插入。
#6
0
As others have said it's much easier to deal with a conflicting update. What you are suggesting is called pesimistic locking. It's called thate because it's all too likely that two users will try and edit the same record at the same time.
就像其他人说的那样，处理一个相互矛盾的更新要容易得多。您所建议的是所谓的灵命锁。它被称为thate，因为很有可能两个用户同时尝试和编辑同一条记录。
Is that true?
这是真的吗?
And is it a disaster if a user has to start again, because the data they tried to update was changed by someone else.
如果用户必须重新启动，因为他们试图更新的数据被其他人更改，这会是一场灾难吗?
Locking costs, you always lock in a pessimistic scheme, so you have an overhead, and that's before you start looking at related data and such.
锁定成本，你总是锁定一个悲观的方案，所以你有一个开销，那是在你开始查看相关数据之前。
Making it robust, dealing with no one can do it now coz sumfin' went wrong...
让它更有活力，不与任何人打交道，因为sumfin‘出了问题……
If I had something short of editing an entire file, that needed pessimistic locking, I'd be having a look at my design, on the basis that it isn't fit for purpose.
如果我没有编辑整个文件，这需要悲观锁定，我将会看到我的设计，基于它不适合的目的。
#1
16
There are two general approaches-- optimistic and pessimistic locking.
有两种通用的方法——乐观锁和悲观锁。
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.
乐观锁定通常更容易在基于web的环境中实现，因为它基本上是无状态的。它的规模也要大得多。缺点是它假定您的用户通常不会同时尝试编辑相同的行集。对于大多数应用程序来说，这是一个非常合理的假设，但是您必须验证您的应用程序并不是用户经常踩到对方脚趾的异常值之一。在乐观锁定中，您将会有某种last_modified_timestamp列，当用户获取数据时，您将选择该列，然后在更新日期时使用WHERE子句。
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>>
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).
如果更新了一行，你就知道你成功了。否则，如果它更新了0行，您就知道在此期间其他人已经修改了数据，您可以采取一些操作(通常向用户显示新数据并询问他们是否希望重写，但您可以采用其他冲突解决方法)。
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的应用程序中实现悲观锁定更具挑战性，尤其是当用户无需退出就可以关闭浏览器时，或者当用户开始编辑一些数据并在提交之前吃午饭时。它使得扩展更加困难，并且通常使应用程序更难管理。如果用户经常尝试更新相同的行，或者更新一行占用了大量的时间，值得考虑，所以值得让他们预先知道其他人已经锁定了这一行。
#2
7
I was going to implement this into one of my own systems.
我打算把它应用到我自己的系统中。
You could create new columns in your database of records, called timelocked.
您可以在记录数据库中创建新的列，称为timelocked。
When a record is opened, you would set the record they are opening's column for timelocked to the current time. During editing of the record, send a keepalive back to the server through ajax every 2 minutes. When sending the keepalive, the server will then increase the timelocked time to the current time the request was sent, and so fourth (this will make sense in a second). WHen the user is finished editing, set the timelocked to false.
当打开一个记录时，您将把它们正在打开的记录设置为timelocked到当前时间。在编辑记录期间，每2分钟通过ajax向服务器发送一个keepalive消息。在发送keepalive时，服务器将把时间戳时间增加到发送请求的当前时间，因此是第四个(这在稍后将变得有意义)。当用户完成编辑时，将timelocked设置为false。
Now, If someone went to open a record which is already open, the php would check - if timelocked == false - would mean it's not being edited,
现在，如果有人打开一个已经打开的记录，php会检查-如果timelocked == false -将意味着它没有被编辑，
otherwise, the record may be being edited, but what if the user closed their browser window. that's why the keepalive is used.
否则，记录可能被编辑，但是如果用户关闭了浏览器窗口。这就是为什么要使用keepalive。
if the difference between the current time and the timelocked is larger than 2 minutes, it means they're no longer lively editing, which would allow you to open it.
如果当前时间和时间间隔大于2分钟，这意味着它们不再是活跃的编辑，这将允许您打开它。
Hopefully you understand all that.
希望你们能理解。
#3
2
Don't try to prevent it. Let them decide what to do in the case of an edit conflict.
不要试图去阻止它。让他们决定在编辑冲突时该做什么。
Add a timestamp to the table. Compare the timestamp of when the row was retrieved with the current timestamp. Make them aware of changes between their load and their save, and let them decide what action to take.
向表添加时间戳。比较何时用当前时间戳检索行的时间戳。让他们意识到负载和保存之间的变化，让他们决定采取什么行动。
So yeah, number 3.
是的,数量3。
#4
0
I personally would not prevent this. If it was a requirement of the job I would track the users' current / last known location and disallow someone from editing the same line someone else is editing this way. I have seen people add a row to a table saying isLocked or isBeingWorkedOn etc... but I have seen this type of system fail far more often as well, or require moderation to unlock stuck tables if someone closed it while working on it etc...
我个人不会阻止这个。如果这是工作的要求，我将跟踪用户当前/最后的已知位置，并禁止某人以这种方式编辑同一行。我见过有人在桌子上加一行，说锁了，或者在工作，等等……但我也见过这种类型的系统更经常地失败，或者如果有人在工作时关闭了它，就需要适度地解锁卡滞的表……
#5
0
1) This is called locking. There are two main types of locking when referring to relational databases (like MySQL): table locking and row locking. Table locking ensures only one session at at time is making changes to a table, whereas row locking ensures only one session at a time is making changes to a particular row. You can think of row locking as a more fine-grained approach to concurrent access than table locking. Row locking is more complicated, but allows multiple concurrent sessions to write to the same table (important if your database has lots of concurrent writes--table locking should be fine for 10-15 users)
2-3) MySQL takes care of concurrent access for you! It automatically implements locking in the background. The type of locking (row or table) depends on which storage engine you use. For example, MyISAM uses table locking and InnoDB uses row locking. MySQL uses an internal table to manage this. You can query the status of this table (and all locks on your database) by checking the Table_locks_immediate and Table_locks_waited variables (it uses your option number 2).
When you issue an INSERT or UPDATE statement while another session is using the table (or row), the calling application (i.e. PHP in this case) will pause for a few milliseconds until the other session is done writing.
这叫做锁定。当引用关系数据库(如MySQL)时，有两种主要的锁定类型:表锁定和行锁定。表锁定只确保一个会话在时间上对表进行更改，而行锁定只确保每次只对特定行进行更改。可以将行锁定视为一种比表锁定更细粒度的并发访问方法。行锁更加复杂，但是允许多个并发会话写入同一个表(如果您的数据库有很多并发的写——10-15个用户可以使用表锁)2-3)MySQL会为您处理并发访问!它自动实现后台的锁定。锁定(行或表)的类型取决于使用哪个存储引擎。例如，ismyam使用表锁，InnoDB使用行锁。MySQL使用一个内部表来管理。您可以查询这个表的状态(和所有锁在你的数据库)通过检查Table_locks_immediate和Table_locks_waited变量(它使用选项2号)。当你的问题一个INSERT或UPDATE语句,而使用另一个会话表(或行),调用应用程序(比如PHP在这种情况下)将暂停几毫秒到其他会话完成写作。
4) Again, MySQL will automatically take care of locking, but you can manually manage table locking with the LOCK TABLES and UNLOCK TABLES commands. If you are using row locking with InnoDB, there is a host of functions you can use to manually manage concurrent access.
4)同样，MySQL会自动处理锁，但是您可以使用锁表手动管理表锁，并解锁表命令。如果您正在使用InnoDB的行锁，那么您可以使用许多函数来手动管理并发访问。
See MySQL's page on Internal Locking for an overview of MySQL's locking system, and Concurrent Inserts for InnoDB's row locking features.
请参阅MySQL的内部锁定页面，了解MySQL的锁定系统，并为InnoDB的行锁定特性提供并发插入。
#6
0
As others have said it's much easier to deal with a conflicting update. What you are suggesting is called pesimistic locking. It's called thate because it's all too likely that two users will try and edit the same record at the same time.
就像其他人说的那样，处理一个相互矛盾的更新要容易得多。您所建议的是所谓的灵命锁。它被称为thate，因为很有可能两个用户同时尝试和编辑同一条记录。
Is that true?
这是真的吗?
And is it a disaster if a user has to start again, because the data they tried to update was changed by someone else.
如果用户必须重新启动，因为他们试图更新的数据被其他人更改，这会是一场灾难吗?
Locking costs, you always lock in a pessimistic scheme, so you have an overhead, and that's before you start looking at related data and such.
锁定成本，你总是锁定一个悲观的方案，所以你有一个开销，那是在你开始查看相关数据之前。
Making it robust, dealing with no one can do it now coz sumfin' went wrong...
让它更有活力，不与任何人打交道，因为sumfin‘出了问题……
If I had something short of editing an entire file, that needed pessimistic locking, I'd be having a look at my design, on the basis that it isn't fit for purpose.
如果我没有编辑整个文件，这需要悲观锁定，我将会看到我的设计，基于它不适合的目的。