Row versioning for MySql

Question

Is there a built-in row-versioning mechanism for MySQL? Something similar to the 'timestamp' column in MS SqlServer.

Dowser

Answer 1

If you add a "timestamp" field, it will update it automatically whenever you update the row - not exactly versioning though, but sounds like it might be what you are after.

Homophony

Thanks for that. The 'timestamp' data type should help. – Dowser

Answer 2

Mysql does not have a built in rowversioning mechanism. Whilst using a timestamp type may seem OK, you are going to fall foul of this for queries that update multiple rows simultaneously and take longer then the resolution of the timestamp as the value assigned is based upon the start of the change and not the end of the change.

So, if your update query affects 100 rows all rows will have the same timestamp (say a value of 2015-10-28 09:47:10.123456). But, it is possible that the rows will not be finished writing until 2015-10-28 09:47:10.654321.

If, separately, you are looking for all changed rows based upon some other timestamp, (say 2015-10-28 09:47:10.500000), you are not going to get the results you want. And depending upon your code, you may miss the 100 rows changed.

It is the fact that changes to rows now may have a timestamp in the past that you cannot simply compare 2 timestamps to get all changed rows.

Palaeozoology

I think the problem is more when 2 connections update the same record within a time that does not cause the time value to change because of the timestamp resolution. Looking for records that were changed at the same time is in my opinion a requirement unrelated to row versioning. – Anneal