Implementing RowVersion in MySQL

[Matthew Willcock]

I am in the processes of looking at MySQL as an alternative to our current MSSQl Database. We have several tables with > 500M rows which we need to be able to 'version'. We currently use a rowversion (http://msdn.microsoft.com/en-us/library/ms182776.aspx) column on these tables. The basic requirement is that any insert and update to a row bumps the rowversion column to the next database wide incrementing value, being database wide isn't critical to us, but ensuring the system does result in an update for each row change is.

I've implemented a similar system using triggers and a 'sequence' table to store the last used version number in MYSQL but am having issues with updates that result in multiple rows being updated (they all get the same 'version', and have concerns about how the locking on the sequence table will cope with the load - our current server runs at 10K QPS (though the plan is to split the datbase across multiple MySQL servers).

Has anyone implemented a similar system or have any suggestions??

Cheers,
Matt