ProstgreSQL、MySQL 乐观并发
我有一个网络应用程序，用户可以同时更改数据。目前，我在每个表单中包含旧的行值，并且仅当数据相同时才更新该行。对于 SQLite，这是唯一的选择。这很丑陋，我考虑切换到另一个 SQL 数据库，如果它能提供更好的方法来做到这一点。 PostgreSQL 或 MySQL 是否具有可以使用的隐式行时间戳或版本号？
I have a web application in which data can be changed concurrently by the users. At the moment I include the old row values in each form and update the row only if the data is the same. With SQLite this to be the only option. This is ugly and I consider switching to another SQL database if it would provide a better way to do this. Do PostgreSQL or MySQL have implicit row timestamps or version numbers which one could use instead?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用数字计数器比使用时间戳更好。无论时间戳如何精确，数据的两个版本都可能同时提交并获得相同的时间戳。通过使用数字计数器（例如
update mytable set counter=counter+1, data=? where id=? and counter=?），每次行更改时都会获得一个唯一的计数器值。 （在where子句中提供原始计数器值，如果数据已被其他人更改，则不会匹配任何行。）虽然这不是一个“隐式”解决方案，但我认为这是可以的。像 Hibernate 这样的库可以让您自动执行此类操作，因此您的代码不必担心它。
Using a numerical counter is better than using a timestamp. However exact the timestamp, it's possible that two versions of the data could be committed at the same time and get the same timestamp. By using a numerical counter (e.g.
update mytable set counter=counter+1, data=? where id=? and counter=?) then each time the row gets changed it gets a unique counter value. (Supply the original counter value in the where clause, if the data has been changed by someone else then no rows will be matched.)Although this is not an "implicit" solution, I think it's OK. Libraries such as Hibernate have facilities to let you do this sort of thing automatically, so your code doesn't have to worry about it.
MySQL 有一个 可以使用的
TIMESTAMP数据类型为此，与DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP约束结合使用时。在 PostgreSQL 中，每个表上都有一个名为
xmin的“隐藏字段”，可用于确定行版本。MySQL has a
TIMESTAMPdata type that can be used for this purpose, when combined with theDEFAULT CURRENT_TIMESTAMPandON UPDATE CURRENT_TIMESTAMPconstraints.In PostgreSQL, there is a "hidden field" on every table called
xminthat can be used to determine the row version.AFAIK，在 Postgres 中获取更新时间戳需要触发器，请参阅这个非常相似的问题：
在 PostgreSQL 中更新行时更新时间戳
该问题（以及 Eric 的答案）指出 MySQL 支持此 w/oa 触发器。
AFAIK, getting an update timestamp in Postgres requires a trigger, see this very similar question:
Update timestamp when row is updated in PostgreSQL
That question (and Eric's answer) point out that MySQL supports this w/o a trigger.