Alternative to timestamp for Optimistic Concurrency for MySql ?

antoan posted on Wednesday, February 07, 2007

The database of the sample project PTracker, in the book, makes use of tables with 'timestamp' columns to implement optimistic concurrency control.

Can anyone suggest a way to do the same on mysql, since it does not support the same kind binary timestamp datatype?

RockfordLhotka replied on Wednesday, February 07, 2007

Does it have any sort of trigger concept. That's the old-style solution: put an update trigger on the table to increment a column value on each update. In concept that's all the "timestamp" type does in SQL Server, though it is far more performant than using triggers.

Failing that, you might need to just ensure that all your stored procedures do an increment of a column value. That not ideal, because forgetting to include that in a sproc will result in badness, but technically it could work.

burmajam replied on Thursday, February 08, 2007

Hi there,

Here's information for MySql. From version 5.0.* there are triggers so you can do as Rocky said, but if you don't need high resolution (less then one second) you can use DateTime or TimeStamp field and treat it as other columns. Just pay attention what happens if you don't have application server or time synchronisation on clients, because of the problem with different clock settings.

Vaidal replied on Friday, February 09, 2007

We don't use timestamp because we don't need it. We use a byte datatype and the update sproc look like this:

PROCEDURE usp_MyTable_UPD (@IdMyTable int, @Description nvarchar(100), @RowVersion tinyint OUTPUT) AS

DECLARE @NewRowVersion

SET @NewRowVersion = (CASE WHEN @RowVersion = 255 THEN 0 ELSE @RowVersion+1 END)

UPDATE MyTable SET Description = @Description , RowVersion = @NewRowVersion

WHERE IdMyTable = @IdMyTable AND RowVersion = @RowVersion

IF @@rowcount>0 SET @RowVersion = @NewRowVersion

 

As you can figure, we have a DAL layer that checks if RowVersion changes on every update.