ProstgreSQL, MySQL concurrency optimization

Question

I have a web application in which data can be changed simultaneously by users. At the moment, I am including the values ​​of the old row in each form and updating the row only if the data is the same. With SQLite, this is the only option. This is ugly, and I am considering switching to another SQL database if this provides the best way to do this. Does PostgreSQL or MySQL have implicit row timestamps or version numbers that could be used instead?

Answer 1

Using a numeric counter is better than using a timestamp. However, with an accurate time stamp, it is possible that two versions of the data can be captured at the same time and receive the same time stamp. Using a numerical counter (for example, update mytable set counter=counter+1, data=? where id=? and counter=? ), Each time the line changes, it gets a unique counter value. (Put the original value of the counter 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 that everything is in order. Libraries such as Hibernate have tools to let you do this kind of thing automatically, so your code should not worry about it.

Answer 2

MySQL has a TIMESTAMP data TIMESTAMP that can be used for this purpose , combined with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP constraints.

PostgreSQL has a "hidden field" for each table named xmin , which can be used to determine the version of the row.

Answer 3

AFAIK, getting update timestamps in Postgres requires a trigger, see this very similar question:

Update timestamp when row is updated in PostgreSQL

This question (and Eric's answer) indicates that MySQL supports this without a trigger.