optimistic concurrency - Does MySQL have an equivalent of SQL Server rowversion?

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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?

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, 2015 at 22:29

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question . Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers .