ProstgreSQL, MySQL optimistic concurrency

Question

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?

Answer 1

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

In PostgreSQL, there is a "hidden field" on every table called xmin that can be used to determine the row version.

Answer 2

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.

Answer 3

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.