8

I am migrating a SQL Server database schema over to MySQL. Some of the tables on SQL Server have a column of type rowversion. This is an integer value that is set when the row is first inserted and then again each time any column of the row is updated. The number is only ever incremented.

We make use of this to check for concurrency problems. So when an insert comes to the server we can check if the incoming value is different to the current stored value. If so, then the row has been updated since the read that has led to this insert. We can then reject the update because it is based on out of date values. Optimistic concurrency.

Is there an equivalent that will do the same for us in MySQL?

Phil Wright
  • 22,580
  • 14
  • 83
  • 137
  • 2
    Not that I know of. But if you’re in control of all UPDATE statements, shouldn’t this be easy enough to implement yourself? A column `myrowversion` that is initialized with 0 when a record is created, and on every UPDATE use `SET myrowversion = myrowversion + 1`. And if you combine it with a WHERE clause on the UPDATE statement, `UPDATE table SET …, myrowversion = myrowversion +1 WHERE record_id = … AND myrowversion = {value you read earlier}` – that should about achieve the same, right? – CBroe Oct 19 '15 at 22:29

2 Answers2

10

The MySQL TIMESTAMP data type may have initialize and auto-update features but the data type is date based and if the resolution is not in the ns dimension you will have problems. Even with ns resolution there still might be problems... So the answer is actually no.

Mantzas
  • 2,463
  • 3
  • 25
  • 35
9

The MySQL TIMESTAMP data type has the auto initialize and auto update features needed for optimistic concurrency tests. See, for example, http://www.mysqltutorial.org/mysql-timestamp.aspx

Honeyboy Wilson
  • 175
  • 2
  • 5